死鎖分析

一個死鎖的分析！

先看報錯的資訊！

*** 2008-05-05 10:24:51.933

*** SESSION ID:(9.14897) 2008-05-05 10:24:51.924

DEADLOCK DETECTED

Current SQL statement for this session:

DELETE FROM ZZRK121R WHERE (ZZRK121R.出租屋編號 IS NOT NULL) AND (ZZRK121R.F00 = '37070500020110')

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TM-0000c01b-00000000 16 9 SX SSX 13 57 SX SSX

TM-0000c01b-00000000 13 57 SX SSX 16 9 SX SSX

session 9: DID 0001-0010-00000002 session 57: DID 0001-000D-00000002

session 57: DID 0001-000D-00000002 session 9: DID 0001-0010-00000002

Rows waited on:

Session 57: obj - rowid = 00011BD3 - AAAAAAAALAABKlsAAA

(dictionary objn - 72659, file - 11, block - 305516, slot - 0)

Session 9: obj - rowid = 00011A66 - AAAAAAAARAAAeu8AAA

(dictionary objn - 72294, file - 17, block - 125884, slot - 0)

Information on the OTHER waiting sessions:

Session 57:

pid=13 serial=12588 audsid=1054039 user: 63/MISDORACLE

O/S info: user: SYSTEM, term: unknown, ospid: , machine: zhcxwas1

program: JDBC Thin Client

Current SQL Statement:

DELETE FROM ZZRK121R WHERE ZZRK121R.F00 = '37078500640103'

End of information on OTHER waiting sessions.

很明顯發生了死鎖！

關於死鎖的的SQL語句很明顯！

DELETE FROM ZZRK121R WHERE (ZZRK121R.出租屋編號 IS NOT NULL) AND (ZZRK121R.F00 = '37070500020110')

下面執行這條語句發生的語句鎖資訊

select v$lock.sid,

decode(v$lock.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 Invalida-tion',

'LS', 'Log Start or Switch',

'RW', 'Row Wait',

'SQ', 'Sequence Number',

'TE', 'Extend Table',

'TT', 'Temp Table',

'Unknown') LockType,

rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name,

decode(lmode, 0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive', 'Unknown') LockMode,

decode(request, 0, 'None',

1, 'Null',

2, 'Row-S',

3, 'Row-X',

4, 'Share',

5, 'S/Row-X',

6, 'Exclusive', 'Unknown') RequestMode,

ctime, block b

from v$lock, all_objects

where sid > 6

and v$lock.id1 = all_objects.object_id;

會得到如下的結果！！

1 12 DML TABLE MISDORACLE.ZZRK121R Row-X None 3176 0
2 12 DML TABLE MISDORACLE.ZZRK121R1 Row-XNone 3176 0
3 12 DML TABLE MISDORACLE.ZZRK121R2 Row-X None 3176 0
4 12 DML TABLE MISDORACLE.ZZRK121R3 Row-X None 3176 0

鎖的級別太高啦 外來鍵 或者主鍵沒有建立索引！！

1 12 DML TABLE MISDORACLE.ZZRK121R Row-X None 3176 0
2 12 DML TABLE MISDORACLE.ZZRK121R1 Row-S None 3176 0
3 12 DML TABLE MISDORACLE.ZZRK121R2 Row-S None 3176 0
4 12 DML TABLE MISDORACLE.ZZRK121R3 Row-S None 3176 0
建立索引後的情況！！

T2 T1	S	X	RS	RX	SRX	-
S	Y	N	Y	N	N	Y
X	N	N	N	N	N	Y
RS	Y	N	Y	Y	Y	Y
RX	N	N	Y	Y	N	Y
SRX	N	N	Y	N	N	Y
-	Y	Y	Y	Y	Y	Y

鎖模式	鎖描述	解釋	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行級共享鎖，其他物件只能查詢這些資料行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行級排它鎖，在提交前不允許做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共享鎖	Create index、Lock share
5	SSX(S/Row-X)	共享行級排它鎖	Lock share row exclusive
6	X(Exclusive)	排它鎖	Alter table、Drop table、Drop index、Truncate table 、Lock exclusive

導致死鎖的原因主要有兩個,一先外來鍵沒有使用索引,二是點陣圖的併發更新

[@more@]