Oracle未提交事務引起的鎖

標籤：font   sql語句   weight   個數   null   into   mit   idt   解釋   

筆者所在公司最近上了一套系統，系統使用中介軟體串連Oracle資料庫，使用一段時間之後系統就會停止回應。發現問題在於2點：

1. 中介軟體有線程執行了Delete語句之後，一直處於等待狀態，沒有COMMIT提交事務，對錶施加了行鎖且該線程無法複用（中介軟體匯流排程數有限制）

2. 對錶施加了行鎖之後，後續中介軟體線程如果需要修改該行，將被阻塞。

以上兩個因素不斷髮生，最終導致中介軟體線程數耗盡，系統停止回應。

 

下面會分兩個部分，第一個部分是直接給出判斷以上故障的SQL語句，第二部分是通過做實驗的方法重現問題

 

 

 1.直接判斷未提交事務引起的表的行鎖

1.1　　判斷哪個SESSION執行了DML（Insert/Update/Delete） 但是未提交 （Commit ），引起的行鎖

1.2　　判斷哪些SESSION被阻塞了，且定位到是誰阻塞了，是由於哪個表哪個行引起的阻塞

  2.通過實驗過程重現整個過程

首先說明，Oracle資料庫是寫阻塞寫，讀寫之間是互相不阻塞的，也就是以下兩個語句在不同的視窗（SESSION）執行的話，由於它們都是更新同一行，如果第一個執行的沒有COMMIT，後執行那個會一直處於被阻塞狀態：

--第一個視窗執行如下語句UPDATE TESTLOCKSET AAA=11WHERE AAA=1--第二個視窗執行以下語句UPDATE TESTLOCKSET AAA=12WHERE AAA=1

 

下面開始我們的實驗，Oracle的版本是11G 帶著如下問題：

1. 如何知道一個串連修改了資料，但是未提交，導致對錶產生了鎖定
2. 對於1的疑問，可否知道具體鎖定了哪個表的哪個記錄？

首先我們在PS/SQL用戶端建立三個視窗,分別為：

測試視窗1

測試視窗2

觀察視窗

1. 在測試視窗1建立測試用的表，並插入10條記錄

/*初始化測試表*/ --建立一個測試表 create table TESTLOCK (   aaa number not null,   bbb nvarchar2(10) not null,   ccc nvarchar2(10) not null ); create INDEX PK_TESTLOCK on TESTLOCK (aaa);   --隨便插入點資料 INSERT INTO TESTLOCK VALUES (‘1‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘2‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘3‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘4‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘5‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘6‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘7‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘8‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘9‘,‘BBB‘,‘CCC‘); INSERT INTO TESTLOCK VALUES (‘10‘,‘BBB‘,‘CCC‘);   --然後我們的表裡面就有了一些資料 SELECT * FROM TESTLOCK;   COMMIT;

 

1. 在測試視窗2，查詢測試表

SELECT * FROM TESTLOCK;

 

1. 在觀察視窗觀察當前Session的情況和表鎖的情況

--當前的Session情況 SELECT SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,         BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION FROM V$SESSION WHERE USERNAME=‘TEST‘;   --當前TESTLOCK表鎖的情況 SELECT * FROM V$LOCK WHERE TYPE=‘TM‘ AND ID1=(SELECT OBJECT_ID FROM DBA_OBJECTS                  WHERE OBJECT_NAME=‘TESTLOCK‘);   SELECT XIDUSN,XIDSLOT,XIDSQN,OBJECT_ID,SESSION_ID,ORACLE_USERNAME,PROCESS,LOCKED_MODE FROM V$LOCKED_OBJECT WHERE OBJECT_ID=(SELECT OBJECT_ID FROM DBA_OBJECTS                  WHERE OBJECT_NAME=‘TESTLOCK‘);

 

從可以看出TEST帳號一共產生了4個SESSION，分別是PLSQL本身串連到資料庫和我們建立的三個視窗

 

 

 

 

 

 

 

我們關注幾個欄位：

EVENT:oracle的session正在等待的資料或者事件

WAIT_CLASS:等待事件的名稱

blocking_session_status:如果blocking_session_status欄位是VALID,表示該SESSION被阻塞了

blocking_session:被哪個Session阻塞

 

通過以上4項觀察，沒有任何Session被阻塞，當前三個Session處於等待用戶端訊息狀態（EVENT=SQL*Net message from client， WAIT_CLASS=Idle）,剩下一個Session正在向用戶端發送訊息（EVENT=SQL*Net message to client，WAIT_CLASS=Network）,就是我們當前的觀察視窗

 

後面兩個查詢V$LOCK和V$LOCKED_OBJECT的語句沒有任何返回，表示當前TESTLOCK表沒有被鎖定

 

 

 

 

 

1. 在測試視窗1更新TESTLOCK，但是不要Commit

UPDATE TESTLOCK SET AAA=11 WHERE AAA=1

 

 

1. 再次在觀察視窗執行步驟3的語句：

通過對V$LOCK和V$LOCKED_OBJECT的查詢可以知道，SID=1947 鎖定了TESTLOCK表，其中LMODE=3（行級獨佔鎖定，我們這裡是通過UPDATE產生的）

 

 

 

 

 

 

 

鎖模式	鎖描述	解釋	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行級共用鎖定，其他對象只能查詢這些資料行	Select for update Lock for update Lock row share
3	SX(Row-X)	行級排它鎖,在提交前不允許做DML操作	Insert/update/Delete Lock row share
4	S(Share)	共用鎖定	Create index Lock share
5	SSX(S/Row-X)	共用行級排它鎖	Lock share row exclusive
6	X(Exclusive)	排它鎖	Alter table Drop able Drop index Truncate table Lock exclusive

 

 

1. 回到之前的第一個問題，如何知道一個SESSION修改了資料但是沒COMMIT，在觀察視窗執行如下語句：

--找到修改了資料，但是未提交的Session,選擇WAIT_CALSS=‘Idle‘，也就是Session處於休息狀態，但是有鎖定的表 SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.LOCKED_MODE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE FROM V$SESSION A INNER JOIN V$LOCKED_OBJECT B ON A.SID=b.SESSION_ID INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID=c.OBJECT_ID WHERE A.WAIT_CLASS=‘Idle‘ AND A.SECONDS_IN_WAIT>10/*SESSION空閑後一段時間還鎖定的才算有問題,這裡隨便給了個數值10秒*/ AND USERNAME=‘TEST‘;

 

只需要判斷WAIT_CLASS=‘Idle‘,同時在V$LOCKED_OBJECT存在鎖定的對象且SESSION空閑了一段時間，，就可以判斷SID=1947 鎖定了TESTLOCK表，SECONDS_IN_WAIT就可以認為是鎖定的時間長度，單位是秒

 

 

 

 

1. 在觀察視窗執行以下語句，從事務的角度觀察

--從事務角度觀察，串連v$session和v$transaction SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.START_DATE FROM v$session a INNER JOIN v$transaction b ON a.taddr=b.addr WHERE USERNAME=‘TEST‘

 

可以觀察到PREV_EXEC_START 和v$transaction的START_DATE 是一致的

 

 

 

1. 在前面一個UPDATE沒有提交的情況下，另外一個Session修改TESTLOCK的同一條記錄，會發生什嗎？

在測試視窗2執行以下語句：

--更新和測視窗1相同的記錄 UPDATE TESTLOCK SET AAA=12 WHERE AAA=1

 

該語句會一直處於“正在執行”狀態，實際上就是TESTLOCK上面有行鎖，該SESSION一直在等待之前的行鎖釋放

 

 

 

1. 再次在觀察視窗執行步驟3的語句：

 

觀察SID=9，顯示EVENT=‘enq: TX - row lock contention‘ ,表示正在等待一個行鎖釋放，BLOCKING_SESSION 說明該SESSION被SID=1947 阻塞了，也就是測試視窗1的SESSION

 

 

觀察V$LOCK其實區分不了哪個LOCK是沒提交，哪個是，兩個LOCK的顯示都是一樣的，這點倒很奇怪

 

 

 

觀察V$LOCKED_OBJECT，可以通過XINUSN/XIDSLOT/XIDSQN 判斷，這三個欄位是和復原相關的欄位，如果都為0，可以判斷為被阻塞

 

 

1. 有沒有辦法知道表具體的哪行被鎖定了？

在觀察視窗執行如下語句：

--當SESSION被阻塞，通過ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#這幾個欄位找到ROWID，然後通過ROWID找到被鎖定的記錄 SELECT SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,EVENT,WAIT_CLASS,SECONDS_IN_WAIT,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,         BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE         ,dbms_rowid.rowid_create(1,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#) FROM V$SESSION A INNER JOIN V$LOCKED_OBJECT B ON A.SID=b.SESSION_ID INNER JOIN DBA_OBJECTS C ON B.OBJECT_ID=c.OBJECT_ID WHERE USERNAME=‘TEST‘ AND BLOCKING_SESSION IS NOT NULL ;   --通過前面的函數rowid_create獲得具體的ROWID，然後在鎖定表中查詢記錄 SELECT * FROM TESTLOCK where ROWID=‘AAJ2QDAAnAAGrwnAAA‘

 

 

 

獲得具體被阻塞表的ROWID

 

 

 

查詢獲得具體的被阻塞記錄，正好是我們在測試視窗1  Update的記錄

 

 

 

 

 

 

V$SESSION的欄位解釋可參見：

https://docs.oracle.com/cd/E18283_01/server.112/e17110/dynviews_3016.htm

 

 

Oracle未提交事務引起的鎖