前陣子有一個網友在群里問了一個關於Oracle資料庫的TX鎖問題，問題原文如下： 請教一個問題： 兩個會話執行不同的delete語句，結果都是刪除同一個行。先執行的會話里where條件不加索引走全表掃描，表很大，執行很慢；後執行的用where條件直接用rowid進行delete。 Oracle的什麼... ...
前陣子有一個網友在群里問了一個關於Oracle資料庫的TX鎖問題，問題原文如下：
請教一個問題： 兩個會話執行不同的delete語句，結果都是刪除同一個行。先執行的會話里where條件不加索引走全表掃描，表很大，執行很慢；後執行的用where條件直接用rowid進行delete。 Oracle的什麼機制使第二個會話執行後一直是等待第一個會話結束的呢。
那麼我們先動手實驗一下，來看看這個問題吧，首先，我們需要一個數據量較大的表（數據量大，全表掃描時間長，方便構造實驗效果）, 這裡實驗測試的表為INV_TEST，該表在欄位FINAL_GARMENT_FACTORY_CD上沒有索引。因為我們要構造一個SQL走全表掃描去刪除數據。我們更新了兩條記錄,設置欄位FINAL_GARMENT_FACTORY_CD ='KLB'。 如下所示：
SQL> SELECT ROWID, T.FINAL_GARMENT_FACTORY_CD FROM TEST.INV_TEST T WHERE ROWNUM <=10;
ROWID FINAL_GARM
------------------ ----------AAC1coABNAAALEKAAA KLBAAC1coABNAAALEKAAB GEG
AAC1coABNAAALEKAAC GEG
AAC1coABNAAALEKAAD GEG
AAC1coABNAAALEKAAE GEG
AAC1coABNAAALEKAAF KLB
AAC1coABNAAALEKAAG GEG
AAC1coABNAAALEKAAH GEG
AAC1coABNAAALEKAAI GEG
AAC1coABNAAALEKAAJ GEG
首先，在會話1（SID=925）裡面執行下麵SQL語句，刪除FINAL_GARMENT_FACTORY_CD ='KLB'的兩條記錄
SQL> SELECT USERENV('SID') FROM DUAL;
USERENV('SID')-------------- 925SQL> DELETE FROM TEST.INV_TEST WHERE FINAL_GARMENT_FACTORY_CD ='KLB';
在會話1（SID=925)執行後，我們在會話2（SID=197)裡面執行一個DELETE語句（刪除ROWID ='AAC1coABNAAALEKAAA'的記錄），其實就是刪除第一條FINAL_GARMENT_FACTORY_CD ='KLB'的記錄。不過我們使用的是ROWID這個條件。
SQL> SELECT USERENV('SID') FROM DUAL;
USERENV('SID')-------------- 917SQL> DELETE FROM TEST.INV_TEST WHERE ROWID ='AAC1coABNAAALEKAAA';
此時，在會話3，我們使用下麵SQL語句查詢，就會發現會話2（SID=917）被會話1（SID=925）阻塞了。
SQL> COLUMN blockeduser FORMAT a30
SQL> SET linesize 480
SQL> BREAK ON BlockingInst SKIP 1 ON BlockingSid skip 1 ON BlockingSerial SKIP 1
SQL> SELECT DISTINCT s1.INST_ID BlockingInst,
2 s1.SID BlockingSid,
3 s1.SERIAL# BlockingSerial,
4 s2.INST_ID BlockedInst,
5 s2.SID BlockedSid,
6 s2.USERNAME BlockedUser,
7 s2.SECONDS_IN_WAIT BlockedWaitTime
8 FROM gv$session s1,
9 gv$lock l1,
10 gv$session s2, 11 gv$lock l2
12 WHERE s1.INST_ID = l1.INST_ID 13 AND l1.BLOCK IN ( 1, 2 )
14 AND l2.REQUEST != 0 15 AND l1.SID = s1.SID 16 AND l1.ID1 = l2.ID1 17 AND l1.ID2 = l2.ID2 18 AND s2.SID = l2.SID 19 AND s2.INST_ID = l2.INST_ID 20 ORDER BY 1,
21 2,
22 3
23 /
BLOCKINGINST BLOCKINGSID BLOCKINGSERIAL BLOCKEDINST BLOCKEDSID BLOCKEDUSER BLOCKEDWAITTIME
------------ ----------- -------------- ----------- ---------- ------------ --------------- 1 925 11600 1 917 TEST 30
SQL> COL SID FOR 999999;
SQL> COL USERNAME FOR A12;
SQL> COL MACHINE FOR A40;
SQL> COL TYPE FOR A10;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL LMODE FOR A16;
SQL> COL REQUEST FOR A12;
SQL> COL BLOCK FOR 999999;
SQL> SELECT S.SID SID,
2 S.USERNAME USERNAME,
3 S.MACHINE MACHINE,
4 L.TYPE TYPE,
5 O.OBJECT_NAME OBJECT_NAME,
6 DECODE(L.LMODE, 0, 'None', 7 1, 'Null', 8 2, 'Row Share', 9 3, 'Row Exlusive', 10 4, 'Share', 11 5, 'Sh/Row Exlusive', 12 6, 'Exclusive') LMODE, 13 DECODE(L.REQUEST, 0, 'None', 14 1, 'Null', 15 2, 'Row Share', 16 3, 'Row Exlusive', 17 4, 'Share', 18 5, 'Sh/Row Exlusive', 19 6, 'Exclusive') REQUEST, 20 L.BLOCK BLOCK
