ORACLE DML語句鎖機制導致事務等待示例

UPDATE產生鎖示例
session 1：
SQL> conn dayonger/dayonger;
Connected.
SQL> create table test(id number primary key,name varchar2(10)); --建立測試表
Table created.

SQL> conn / as sysdba
Connected.
SQL> select sid from v$mystat where rownum=1;
SID
----------
131

SQL> conn dayonger/dayonger;
Connected.
SQL> insert into test values(1,'dayonger'); --插入測試資料
1 row created.
SQL> insert into test values(2,'xiaoru');
1 row created.
SQL> commit;
Commit complete.
SQL> update test set name='eryonger' where id=2; --update修改一行資料，不提交該事務
1 row updated.

session 2：
SQL> select sid from v$mystat where rownum=1;
SID
----------
147
SQL> update dayonger.test set name='madaha' where id=2; --由於session 1對該行資料修改未結束，該事務將等待
session 3：
SQL> select sid from v$mystat where rownum=1;
SID
----------
148
SQL> select sid,type,id1,id2,lmode,request,block from v$lock where sid in (131,147) order by 1,2;

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
131 TM 52647 0 3 0 2
131 TX 65580 410 6 0 1
147 TM 52647 0 3 0 2
147 TX 65580 410 0 6 0

SQL> select sid,event from v$session_wait where sid in (131,147);

SID EVENT
---------- ----------------------------------------------------------------
131 SQL*Net message from client
147 enq: TX - row lock contention

批註：
v$lock的lmode和request相應數值的含義是：

TM是一個表級鎖（段鎖），表示此表的記錄在修改時，不允許對錶進行DDL操作；
TX是一個行級鎖（事務鎖），表示不允許對錶修改的記錄進行DML操作；

當TY=TM and ID2=0時，ID1的值為此表（段）的object_id；
當TY=TX and ID2<>0時，ID1+ID2構成了這個事務在回滾段中的位置；
LMODE=3表示一個行級排它鎖，LMODE=6表級的排他鎖；
REQUEST=6 表示當前會話正待等待一個LMODE=6 的鎖，表明這個會話正在被阻塞；
block=1是表示這個會話正在阻塞其它會話；
DELETE產生鎖示例
session1：
SQL>set sqlprompt '&window>'
Enter value for window: session1
session1>select sid from v$mystat where rownum=1;
SID
----------
148
session1>select * from dayonger.test;
ID NAME
---------- ----------
1 dayonger
2 xiaoru
session1>delete from dayonger.test where id=2; --執行delete，該語句不提交
1 row deleted.

session2：
session2>select sid from v$mystat where rownum=1;
SID
----------
131
SQL> set sqlprompt '&window>'
Enter value for window: session2
session2>delete from dayonger.test where id=2; --此時刪除同行資料時產生等待
session3：
session3>select sid,type,id1,id2,lmode,request,block from v$lock
2 where sid in(131,148) order by 1,2;

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
131 TM 52647 0 3 0 2
131 TX 393249 411 0 6 0
148 TM 52647 0 3 0 2
148 TX 393249 411 6 0 1

session3>select sid,event from v$session_wait where sid in(131,148);

SID EVENT
---------- ----------------------------------------------------------------
131 enq: TX - row lock contention
148 SQL*Net message from client

批註:
TM是一個表級鎖（段鎖），表示此表的記錄在修改時，不允許對錶進行DDL操作；
TX是一個行級鎖（事務鎖），表示不允許對錶修改的記錄進行DML操作；
當TY=TM and ID2=0時，ID1的值為此表（段）的object_id；
當TY=TX and ID2<>0時，ID1+ID2構成了這個事務在回滾段中的位置；
LMODE=3表示一個行級排它鎖，LMODE=6表級的排他鎖；
REQUEST=6 表示當前會話正待等待一個LMODE=6 的鎖，表明這個會話正在被阻塞；
block=1是表示這個會話正在阻塞其它會話；

INSERT產生鎖示例
session1：
session1>select sid from v$mystat where rownum=1;
SID
----------
148
session1>insert into dayonger.test values (3,'demaxiya'); --插入資料不提交
1 row created.

session2：
session1>select sid from v$mystat where rownum=1;
SID
----------
131
session2>insert into dayonger.test values (3,'sameID'); --插入相同的ID號，該表中ID為主鍵，事務將等待
session3：
session3>select sid,type,id1,id2,lmode,request,block from v$lock
2 where sid in(131,148) order by 1,2;

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
131 TM 52647 0 3 0 2
131 TX 655390 424 6 0 2
131 TX 589846 428 0 4 0
148 TM 52647 0 3 0 2
148 TX 589846 428 6 0 1

session3>select sid,event from v$session_wait where sid in(131,148);

SID EVENT
---------- ----------------------------------------------------------------
131 enq: TX - row lock contention
148 SQL*Net message from client

批註：
這裡和update產生鎖的情況不一樣的是，session2在持有一個LMODE=6的排他鎖和等待一個LMODE=4的鎖。
因插入的並不是同一條記錄，session2插入時的記錄沒被阻塞，而是對錶的資料塊頭修改的阻塞，所以會話在請求一個LMODE=4的鎖。

SELECT ... FOR UPDATE產生鎖示例
session1：
session1>select sid from v$mystat where rownum=1;
SID
----------
148
session1>select * from dayonger.test for update;

ID NAME
---------- ----------
3 demaxiya
1 dayonger
2 xiaoru

session2：
session2>select * from dayonger.test;

ID NAME
---------- ----------
3 demaxiya
1 dayonger
2 xiaoru
session2>update dayonger.test set name='douzhanshenghuo' where id=3;
session3：
session3>select sid,type,id1,id2,lmode,request,block from v$lock
2 where sid in(131,148) order by 1,2;

SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
131 TM 52647 0 3 0 2
131 TX 393236 412 0 6 0
148 TM 52647 0 3 0 2
148 TX 393236 412 6 0 1

session3>select sid,event from v$session_wait where sid in(131,148);

SID EVENT
---------- ----------------------------------------------------------------
131 enq: TX - row lock contention
148 SQL*Net message from client
補充：可以使用如下語句殺掉導致等待事件的會話
session3>alter system kill session '148,131';
System altered.
session3>select sid,type,id1,id2,lmode,request,block from v$lock
2 where sid in(131,148) order by 1,2;
no rows selected

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29634949/viewspace-1277294/，如需轉載，請註明出處，否則將追究法律責任。

ORACLE DML語句鎖機制導致事務等待示例

相關文章