前陣子有一個網友在群里問了一個關于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 GEGAAC1coABNAAALEKAAC GEGAAC1coABNAAALEKAAD GEGAAC1coABNAAALEKAAE GEGAAC1coABNAAALEKAAF KLBAAC1coABNAAALEKAAG GEGAAC1coABNAAALEKAAH GEGAAC1coABNAAALEKAAI GEGAAC1coABNAAALEKAAJ GEG

?

首先，在會話1（SID=925）里面執行下面SQL語句，刪除FINAL_GARMENT_FACTORY_CD ='KLB'的兩條記錄

?

SQL> SELECT USERENV('SID') FROM DUAL;?USERENV('SID')--------------?????????? 925?SQL> 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')--------------?????????? 917?SQL> DELETE?FROM TEST.INV_TEST WHERE ROWID ='AAC1coABNAAALEKAAA';

?

?

此時，在會話3，我們使用下面SQL語句查詢，就會發現會話2（SID=917）被會話1（SID=925）阻塞了。

?

?

SQL> COLUMN blockeduser FORMAT a30 SQL> SET linesize 480SQL> 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 21? FROM?? V$LOCK L, 22???????? V$SESSION S, 23???????? DBA_OBJECTS O 24? WHERE? L.SID = S.SID 25???????? AND USERNAME != 'SYSTEM'? 26???????? AND O.OBJECT_ID(+) = L.ID1; ???? SID USERNAME???? MACHINE??????????????? TYPE?????? OBJECT_NAME????? LMODE??????????? REQUEST?? BLOCK------- ------------ ------------------ ---------- ---------------- ---------------- ------------ -------??? 917 TEST??? DB-Server.localdomain????? TM???????? INV_TEST???????? Row Exlusive???? None????????? 0??? 925 TEST??? DB-Server.localdomain????? TM???????? INV_TEST???????? Row Exlusive???? None????????? 0??? 925 TEST??? DB-Server.localdomain????? TX????????????????????????? Exclusive??????? None????????? 1??? 917 TEST??? DB-Server.localdomain????? TX????????????????????????? None???????????? Exclusive???? 0

?

?

使用下面腳本，我們知道，會話197在ROW_ID=AAC1coABNAAALEKAAA 這條記錄上等待獲取TX鎖，從而導致他被阻塞了。

?

?

COL object_name FOR A32;COL row_id FOR A32;SELECT???? s.p1raw,???? o.owner,???? o.object_name,???? dbms_rowid.rowid_create(1,o.data_object_id,f.relative_fno,s.row_wait_block#,s.row_wait_row#) row_id?FROM???? v$session s???? JOIN dba_objects o ON s.row_wait_obj# = o.object_id???? JOIN dba_segments m ON o.owner = m.owner??????????????????????????? AND o.object_name = m.segment_name???? JOIN dba_data_files f ON s.row_wait_file# = f.file_id????????????????????????????? AND m.tablespace_name = f.tablespace_name?WHERE???? s.event LIKE?'enq: TX%'

?

?

?

?

?

其實到這里就可以回答之前網友的問題了。 其實很簡單，就是ORACLE數據庫的鎖機制實現的。我們知道TX鎖稱為事務鎖或行級鎖。當Oracle執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，系統再自動申請TX類型的鎖，并將實際鎖定的數據行的鎖標志位進行置位。

?

在數據行上只有X鎖（排他鎖）。在 Oracle數據庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行 DML語句時，第一個會話在該條記錄上加鎖，其他的會話處于等待狀態。當第一個會話提交后，TX鎖被釋放，其他會話才可以加鎖。由于第一個SQL語句的執行計劃走全表掃描，所以導致這個事務的時間很長，會話2就一直被阻塞，直到第一個會話提交或回滾。

?

另外，我們都知道在Oracle中實現了細粒度的行鎖row lock，且在ORACLE的內部實現中沒有使用基于內存的行鎖管理器，row lock是依賴于數據塊本身實現的。換句話說判定一行數據究竟有沒有沒鎖住，要求Server Process去pin住相應的block buffer并檢查才能夠發現。所以，對于會話1（SID=925），我們無法定位到那些行獲取了TX鎖。這個可以參考https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9533876300346704362

?

那么問題來了，對于會話1的SQL走全表掃描，找到FINAL_GARMENT_FACTORY_CD ='KLB'的記錄就會在對應的數據行的鎖標志進行置位。假如FINAL_GARMENT_FACTORY_CD ='KLB'的記錄位于掃描位置的末端呢？ 這個實驗會是什么樣的結果呢？我們用下面SQL找出一些記錄。

?

SELECT ROWID, T.* FROM INV_TEST T WHERE STOCK_DATE > SYSDATE -120

?

然后我們將其中一條記錄使用下面腳本更新。

?

SQL> UPDATE INV_TEST SET FINAL_GARMENT_FACTORY_CD='KLB'?WHERE ROWID='AAC1coAB4AAEuXrAAM';?1 row updated.?SQL> COMMIT;?Commit complete.

?

然后我們接下來繼續上面實驗， 不過第二個SQL是刪除ROWID='AAC1coAB4AAEuXrAAM'這條記錄，我們看看實驗結果

?

?

SQL> SELECT USERENV('SID') FROM DUAL;?USERENV('SID')--------------?????????? 925?SQL> DELETE?FROM INVSUBMAT.INV_TEST WHERE FINAL_GARMENT_FACTORY_CD ='KLB';

?

?

等了大概10秒左右，我們在會話2執行第二個SQL，發現這個時候，這個SQL2馬上執行完成了。跟之前的實驗現象完全不同

?

?

?

其實出現這樣的現象，是因為第二個會話（SID=917)首先獲取了這一行的TX鎖， 而第一個會話由于走全表掃描，它還沒掃描到這條記錄。可以說在一個事務中，對記錄持有X鎖是有順序和時間差的。也就是說會話（SID=917）首先在一行上獲取了TX鎖。

?

?

另外需要注意的是：其實關于Oracle的row lock或TX鎖，雖然很多時候我們把 TX lock叫做row lock ， 但是實際上它們是兩回事。row lock是基于數據塊實現的，而TX lock則是通過內存中的ENQUEUE LOCK實現的.它是一種保護共享資源的鎖定機制，一個排隊機制，先進先出(FIFO). 關于這個，這里不展開敘說。

?

?

?

?

?

轉載于:https://www.cnblogs.com/kerrycode/p/10900096.html

總結

以上是生活随笔為你收集整理的Oracle关于TX锁的一个有趣的问题的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。