一个死锁的分析！

先看报错的信息！

*** 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
创建索引后的情况！！