如何查詢oracle的死鎖？

下面查詢可以查到死鎖，但是注意其實(shí)查詢到的不是真正的死鎖，查詢到的只是是死鎖發(fā)生前的等待條件，真正的死鎖在10G中會(huì)被系統(tǒng)所阻止掉

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),東陽(yáng)企業(yè)網(wǎng)站建設(shè),東陽(yáng)品牌網(wǎng)站建設(shè),網(wǎng)站定制,東陽(yáng)網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,東陽(yáng)網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

select t2.username||' '||t2.sid||' '||t2.serial#||' '||t2.logon_time||' '||t3.sql_text

from v$locked_object t1,v$session t2,v$sqltext t3

where t1.session_id=t2.sid

and t2.sql_address=t3.address

order by t2.logon_time ;

此外，從V$LOCK的BLOCK字段可以看到，BLOCK0表示有等待發(fā)生了，注意是行級(jí)等待，而在表級(jí)沒有死鎖或等待

select * from v$lock where block0 ;

oracle 如何查看有沒有死鎖

--?死鎖查詢語(yǔ)句

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',

1,?'NULL',

2,?'ROW-S?(SS)',

3,?'ROW-X?(SX)',

4,?'SHARE',

5,?'S/ROW-X?(SSX)',

6,?'EXCLUSIVE',

TO_CHAR?(hk.lmode)

)?mode_held,

DECODE?(wk.request,

0,?'None',

1,?'NULL',

2,?'ROW-S?(SS)',

3,?'ROW-X?(SX)',

4,?'SHARE',

5,?'S/ROW-X?(SSX)',

6,?'EXCLUSIVE',

TO_CHAR?(wk.request)

)?mode_requested,

TO_CHAR?(hk.id1)?lock_id1,?TO_CHAR?(hk.id2)?lock_id2,

DECODE

(hk.BLOCK,

0,?'NOT?Blocking',??????????/**//*?Not?blocking?any?other?processes?*/

1,?'Blocking',??????????????/**//*?This?lock?blocks?other?processes?*/

2,?'Global',???????????/**//*?This?lock?is?global,?so?we?can't?tell?*/

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?wk.id2(+)?=?hk.id2

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;

查詢發(fā)生死鎖的select語(yǔ)句

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)

)

怎么查看oracle是否有死鎖

-- 死鎖查詢語(yǔ)句

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',

1, 'NULL',

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (hk.lmode)

) mode_held,

DECODE (wk.request,

0, 'None',

1, 'NULL',

2, 'ROW-S (SS)',

3, 'ROW-X (SX)',

4, 'SHARE',

5, 'S/ROW-X (SSX)',

6, 'EXCLUSIVE',

TO_CHAR (wk.request)

) mode_requested,

TO_CHAR (hk.id1) lock_id1, TO_CHAR (hk.id2) lock_id2,

DECODE

(hk.BLOCK,

0, 'NOT Blocking', /**//* Not blocking any other processes */

1, 'Blocking', /**//* This lock blocks other processes */

2, 'Global', /**//* This lock is global, so we can't tell */

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 wk.id2(+) = hk.id2

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;

查詢發(fā)生死鎖的select語(yǔ)句

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)

)

oracle怎么查看表死鎖住的原因

1.可能在Oracle中可以有計(jì)時(shí)器,在頻繁操作數(shù)據(jù)庫(kù),update,insert,delete語(yǔ)句,在jobs中能查看到（如圖）

2.可能在.net程序中使用到timer控件,頻繁的操作數(shù)據(jù)庫(kù)update,insert,delete語(yǔ)句（如圖）

3.可能在.net程序中使用到Thead(線程）頻繁的操作數(shù)據(jù)庫(kù)update,insert,delete語(yǔ)句（這個(gè)條為個(gè)人觀點(diǎn)，還未得到證實(shí)）

【注釋】：此處更新數(shù)據(jù)量都比較大,比如說(shuō)update的數(shù)據(jù)庫(kù)條數(shù)可能在1000條以上，

導(dǎo)致其他操作在等待（update跟其他操作都是同一張表）

4.可能使用到事務(wù)處理（Tran）,沒有提交事務(wù)（CommintTran）或者沒有回滾事務(wù)(Rollback)

【注釋】:在SqlServer數(shù)據(jù)庫(kù)里面有一個(gè)隱式事務(wù),關(guān)閉時(shí)候,每次修改插入都需要手動(dòng)提交,不然就會(huì)導(dǎo)致死鎖