oracle 表容易死锁优化,oracle 表的死锁以及解决之道

1.查询锁定的对象

SELECT S.SID SESSION_ID,

S.USERNAME,

DECODE (LMODE,

0, ' None ',

1, ' Null ',

2, ' Row-S(SS) ',

3, ' Row-X(SX) ',

4, ' Share',

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

6, 'Exclusive ',

TO_CHAR (LMODE))

MODE_HELD,

DECODE (REQUEST,

0, ' None ',

1, ' Null ',

2, ' Row-S(SS) ',

3, ' Row-X(SX) ',

4, ' Share',

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

6, 'Exclusive ',

TO_CHAR (REQUEST))

MODE_REQUESTED,

O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) '

AS OBJECT_NAME,

S.TYPE LOCK_TYPE,

L.ID1 LOCK_ID1,

L.ID2 LOCK_ID2

FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S

WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND object_name = 'DAT_USERS';

DAT_USERS  -> 此处为对象名，可以是表，也可以是索引。

2.查询出来之后，进行释放操作：

alter system kill session 'sid, serial#'

此处的sid, serial:

SELECT SID,SERIAL#

FROM V$SESSION

WHERE SID IN (SELECT S.SID SESSION_ID

FROM V$LOCK L, SYS.DBA_OBJECTS O, V$SESSION S

WHERE L.SID = S.SID AND L.ID1 = O.OBJECT_ID AND object_name = 'DAT_USERS'

);