-- 死锁查询语句
SELECT bs.username "Blocking User", bs.username "DB User",
ws.username "Waiting User", bs.SID "SID", ws.SID "WSID",
bs.serial# "Serial#", bs.sql_address "address",
bs.sql_hash_value "Sql hash", bs.program "Blocking App",
ws.program "Waiting App", bs.machine "Blocking Machine",
ws.machine "Waiting Machine", bs.osuser "Blocking OS User",
ws.osuser "Waiting OS User", bs.serial# "Serial#",
ws.serial# "WSerial#",
DECODE (wk.TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'USER Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL USER LOCK',
'DX', 'Distributed Xaction',
'CF', 'Control FILE',
'IS', 'Instance State',
'FS', 'FILE SET',
'IR', 'Instance Recovery',
'ST', 'Disk SPACE Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'LOG START OR Switch',
'RW', 'ROW Wait',
'SQ', 'Sequence Number',
'TE', 'Extend TABLE',
'TT', 'Temp TABLE',
wk.TYPE
) lock_type,
DECODE (hk.lmode,
0, 'None',
①., 'NULL',
TO_CHAR (hk.lmode)
) mode_held,
DECODE (wk.request,
TO_CHAR (wk.request)
) mode_requested,
DECODE
(hk.BLOCK,
0, 'NOT Blocking', /**//* Not blocking any other processes */
①., 'Blocking', /**//* This lock blocks other processes */
TO_CHAR (hk.BLOCK)
) blocking_others
FROM v$lock hk, v$session bs, v$lock wk, v$session ws
WHERE hk.BLOCK = 1
AND hk.lmode != 0
AND hk.lmode != 1
AND wk.request != 0
AND wk.TYPE(+) = hk.TYPE
AND wk.id1(+) = hk.id1
AND hk.SID = bs.SID(+)
AND wk.SID = ws.SID(+)
AND (bs.username IS NOT NULL)
AND (bs.username 'SYSTEM')
AND (bs.username 'SYS')
ORDER BY 1;
查询发生死锁的select语句
select sql_text from v$sql where hash_value in (
select sql_hash_value from v$session where sid in (select session_id from v$locked_object)
)
下面查询可以查到死锁,但是注意其实查询到的不是真正的死锁,查询到的只是是死锁发生前的等待条件,真正的死锁在10G中会被系统所阻止掉
此外,从V$LOCK的BLOCK字段可以看到,BLOCK0表示有等待发生了,注意是行级等待,而在表级没有死锁或等待
select * from v$lock where block0 ;
①可能在Oracle中可以有计时器,在频繁操作数据库,update,insert,delete语句,在jobs中能查看到（如图）
【注释】：此处更新数据量都比较大,比如说update的数据库条数可能在1000条以上,
导致其他操作在等待（update跟其他操作都是同一张表）
【注释】:在SqlServer数据库里面有一个隐式事务,关闭时候,每次修改插入都需要手动提交,不然就会导致死锁
用如下语句查询锁死的表：
select?p.spid,
a.serial#,
c.object_name,
b.session_id,
b.oracle_username,
b.os_user_name
from?v$process?p,?v$session?a,?v$locked_object?b,?all_objects?c
where?p.addr?=?a.paddr
and?a.process?=?b.process
and?c.object_id?=?b.object_id;
其中object_name就是被锁的表名,如图：
--?死锁查询语句
SELECT?bs.username?"Blocking?User",?bs.username?"DB?User",
ws.username?"Waiting?User",?bs.SID?"SID",?ws.SID?"WSID",
bs.serial#?"Serial#",?bs.sql_address?"address",
bs.sql_hash_value?"Sql?hash",?bs.program?"Blocking?App",
ws.program?"Waiting?App",?bs.machine?"Blocking?Machine",
ws.machine?"Waiting?Machine",?bs.osuser?"Blocking?OS?User",
ws.osuser?"Waiting?OS?User",?bs.serial#?"Serial#",
ws.serial#?"WSerial#",
DECODE?(wk.TYPE,
'MR',?'Media?Recovery',
'RT',?'Redo?Thread',
'UN',?'USER?Name',
'TX',?'Transaction',
'TM',?'DML',
'UL',?'PL/SQL?USER?LOCK',
'DX',?'Distributed?Xaction',
'CF',?'Control?FILE',
'IS',?'Instance?State',
'FS',?'FILE?SET',
'IR',?'Instance?Recovery',
'ST',?'Disk?SPACE?Transaction',
'TS',?'Temp?Segment',
'IV',?'Library?Cache?Invalidation',
'LS',?'LOG?START?OR?Switch',
'RW',?'ROW?Wait',
'SQ',?'Sequence?Number',
'TE',?'Extend?TABLE',
'TT',?'Temp?TABLE',
)?lock_type,
DECODE?(hk.lmode,
0,?'None',
①.,?'NULL',
TO_CHAR?(hk.lmode)
)?mode_held,
DECODE?(wk.request,
TO_CHAR?(wk.request)
)?mode_requested,
0,?'NOT?Blocking',??/**//*?Not?blocking?any?other?processes?*/
①.,?'Blocking',???/**//*?This?lock?blocks?other?processes?*/
TO_CHAR?(hk.BLOCK)
)?blocking_others
FROM?v$lock?hk,?v$session?bs,?v$lock?wk,?v$session?ws
WHERE?hk.BLOCK?=?1
AND?hk.lmode?!=?0
AND?hk.lmode?!=?1
AND?wk.request?!=?0
AND?wk.TYPE(+)?=?hk.TYPE
AND?wk.id1(+)?=?hk.id1
AND?hk.SID?=?bs.SID(+)
AND?wk.SID?=?ws.SID(+)
AND?(bs.username?IS?NOT?NULL)
AND?(bs.username?'SYSTEM')
AND?(bs.username?'SYS')
ORDER?BY?1;
select?sql_text?from?v$sql?where?hash_value?in?(
select?sql_hash_value?from?v$session?where?sid?in?(select?session_id?from?v$locked_object)