[20151208]關於Oracle Row Lock.txt
[20151208]關於Oracle Row Lock.txt
--參考連結
https://blogs.oracle.com/askmaclean/entry/know_more_about_oracle_row
大家都知道如何2個使用者修改相同的記錄,會出現enq: TX – row lock contention,另外一個使用者會等待前面的使用者修改的提交或者回
滾,如果不提交,該使用者會一直等待,除非前面的使用者給kill或者執行commit,rollback操作。而我們都知道在Oracle中實現了細粒度的
行鎖row lock,且在ORACLE的內部實現中沒有使用基於記憶體的行鎖管理器,row lock是依賴於資料塊本身實現的。換句話說判定一行資料
究竟有沒有沒鎖住,要求Server Process去pin住相應的block buffer並檢查才能夠發現。
--做這個測試僅僅是重複連結https://blogs.oracle.com/askmaclean/entry/know_more_about_oracle_row操作,自己加深理解。
1.環境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t_lock(id1 number,id2 number);
Table created.
SCOTT@book> insert into t_lock values (1,1);
1 row created.
SCOTT@book> commit ;
Commit complete.
--分析略。
SCOTT@book> select rowid,id1,id2 from t_lock ;
ROWID ID1 ID2
------------------ ---------- ----------
AAAWDnAAEAAAIU0AAA 1 1
SCOTT@book> @ &r/rowid AAAWDnAAEAAAIU0AAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
90343 4 34100 0 4,34100 alter system dump datafile 4 block 34100
2.在做測試前,先說明一下savepoint:
--oracle 在執行一系列事務時,可以使用savepoint建立一個儲存點,還可以使用rollback to 回滾到特定的儲存點。
--但是這裡面有1個小細節必須說明:
SCOTT@book> column XIDUSN_XIDSLOT_XIDSQN format a30
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
--可以發現當前會話沒有任何事務。
SCOTT@book> savepoint a;
Savepoint created.
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.16.1805
--當我們建立一個savepoint a時,也就建立了1個事務,但是如果我們執行如下:
SCOTT@book> rollback to a;
Rollback complete.
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.16.1805
--可以發現如果我們回滾到a點,但是事務依舊存在。這個有點ddl語句在執行前會隱式發1個commit一樣,也就是講在執行savepoint a;
--會第一個先建立一個事務的開始(當然事務這是不存在,如果存在不會),再建立儲存點,而rollback to a僅僅回滾到儲存點,並沒有
--回滾這個事務,僅僅commit或者rollback才能關閉這個事務。
SCOTT@book> rollback;
Rollback complete.
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
3.測試:
--session 1:
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID C50
---------- ---------- ------ ------- --------------------------------------------------
80 673 9118 31 alter system kill session '80,673' immediate;
SCOTT@book> column XIDUSN_XIDSLOT_XIDSQN format a30
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
SCOTT@book> savepoint a;
Savepoint created.
SCOTT@book> column XIDUSN_XIDSLOT_XIDSQN format a30
SCOTT@book> select dbms_transaction.local_transaction_id() XIDUSN_XIDSLOT_XIDSQN from dual ;
XIDUSN_XIDSLOT_XIDSQN
------------------------------
10.4.1809
SCOTT@book> update t_lock set id2=id2+2 where id1=1;
1 row updated.
--session 2:
SCOTT@book> @ &r/spid
SID SERIAL# SPID PID C50
---------- ---------- ------ ------- --------------------------------------------------
90 653 9124 32 alter system kill session '90,653' immediate;
SCOTT@book>update t_lock set id2=id2+3 where id1=1;
--掛起!因為修改是同一條記錄.
--session 3:
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- ----------- ----- --------------------
80 673 SCOTT oracle gxqyydg4 SQL*Plus DML(TM) Row-X (SX) None 90343 0 SCOTT TABLE T_LOCK No
90 653 SCOTT oracle gxqyydg4 SQL*Plus DML(TM) Row-X (SX) None 90343 0 SCOTT TABLE T_LOCK No 00000000845BF0D8
90 653 SCOTT oracle gxqyydg4 SQL*Plus Transaction None Exclusive 655364 1809 No 00000000845BF0D8
80 673 SCOTT oracle gxqyydg4 SQL*Plus Transaction Exclusive None 655364 1809 Yes
--如果這個時候回到session 1,執行:
--session 1:
SCOTT@book> rollback to a;
Rollback complete.
--檢視session 2,可以發現依舊掛起。
--session 3:
SCOTT@book> @ &r/viewlock
SID SERIAL# USERNAME OSUSER MACHINE MODULE LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 OWNER OBJECT_TYP OBJECT_NAME BLOCK LOCKWAIT
------ ---------- ---------- ---------- ---------- ------------ ------------ ---------- ---------- ---------- ---------- ------ ---------- -------------------- ----- --------------------
90 653 SCOTT oracle gxqyydg4 SQL*Plus DML(TM) Row-X (SX) None 90343 0 SCOTT TABLE T_LOCK No 00000000845BF0D8
90 653 SCOTT oracle gxqyydg4 SQL*Plus Transaction None Exclusive 655364 1809 No 00000000845BF0D8
80 673 SCOTT oracle gxqyydg4 SQL*Plus Transaction Exclusive None 655364 1809 Yes
SCOTT@book> select p1raw,p2raw,p3raw,p1,p2,p3,sid,serial#,seq#,event,state,wait_time_micro,seconds_in_wait from v$session where wait_class<>'Idle' order by event ;
P1RAW P2RAW P3RAW P1 P2 P3 SID SERIAL# SEQ# EVENT STATE WAIT_TIME_MICRO SECONDS_IN_WAIT
---------------- ---------------- ---------------- ---------- ---------- ---------- ------ ---------- ---------- ---------------------------------------- ------------------- --------------- ---------------
0000000062657100 0000000000000001 00 1650815232 1 0 101 181 102 SQL*Net message to client WAITED SHORT TIME 5 0
0000000054580006 00000000000A0004 0000000000000711 1415053318 655364 1809 90 653 29 enq: TX - row lock contention WAITING 2157565828 2158
--雖然這個這行已經不存在鎖,對它的修改事務已經取消,但是事SID=80的事務還存在。這個時候在session 3修改該行一點記錄一點問
--題都沒有。
--session 3:
SCOTT@book> update t_lock set id2=id2+10 where id1=1;
1 row updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select * from t_lock ;
ID1 ID2
---------- ----------
1 11
SCOTT@book> select * From v$Lock where (sid=80 or sid=90) and type<>'AE';
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ------ ----- ---------- ---------- ---------- ---------- ---------- -------
00000000845BF080 00000000845BF0D8 90 TX 655364 1809 0 6 951 0
00007FC106ADF388 00007FC106ADF3E8 90 TM 90343 0 3 0 951 0
0000000080C92AE0 0000000080C92B58 80 TX 655364 1809 6 0 1036 1
--轉抄:
https://blogs.oracle.com/askmaclean/entry/know_more_about_oracle_row
那麼SESSION 2 為什麼無謂地等待著呢?
這就涉及到ORACLE的內部實現機制了, 注意雖然很多時候我們把 TX lock叫做 row lock , 但是實際上它們是2回事。row lock是基於
資料塊實現的, 而TX lock則是透過記憶體中的ENQUEUE LOCK實現的。
問題在於若一個程式PROCESS K在DML過程中發現其所需要的資料行已經被其他程式鎖定了,如果不依賴於記憶體中的TX LOCK,這意味著
PROCESS Z需要定期去讀取檢查該資料行鎖在的資料塊以發現相應的ROW LOCK是否已經被釋放了,可以想象如果在OLTP環境中這樣去設計
所造成的效能損失將是巨大的。
所以ROW LOCK的Release 就需要依賴於TX的ENQUEUE LOCK,大致的過程是這樣的Process J 首先鎖定了資料塊中的一行,
Process K需要更新同樣的一行資料 ,Process K讀取該行鎖在資料塊,發現該row piece的lb不是0x0 ,而指向一個ITL,
Process Z分析該ITL就可以得到之前Process J的事務的XID,就可以找到Process J這個事務的TX lock,
PROCESS K 就會在TX resource的Enqueue Waiter Linked List上建立一個X mode(exclusive)的enqueue lock。
這樣當Process J釋放TX lock時,Process J就會檢視該TX resource的Enqueue Waiter Linked List
並發現Process K還在那裡等待,並會POST一個資訊給Process K說 TX lock已經被我釋放,
隱含的意思就是row lock也已經被我釋放,你可以繼續工作了。
--補充1點Process Z 應該是Process K。
--我的理解:(轉義1下)
--當session 2需要修改相同記錄時,讀取該行所在資料塊,發現該row piece的lb不是0x0,而指向一個ITL,透過ITL可以獲得session 1
--的事務的xid,就可以找到session 1事務的TX lock。
--session 2就會在TX resource的Enqueue Waiter Linked List上建立一個X mode(exclusive)的enqueue lock。
--這樣當session 1釋放TX lock時,session 2就會檢視該TX resource的Enqueue Waiter Linked List並發現session 2還在那裡等待,
--並會POST一個資訊給session 2說 TX lock已經被我釋放,隱含的意思就是row lock也已經被我釋放,你可以繼續工作了。
--轉抄:
https://blogs.oracle.com/askmaclean/entry/know_more_about_oracle_row
ROW LOCK的Release 就需要依賴於TX的ENQUEUE LOCK,大致的過程是這樣的Process J 首先鎖定了資料塊中的一行, Process K需要更新
同樣的一行資料 ,Process K讀取該行鎖在資料塊,發現該row piece的lb不是0x0 ,而指向一個ITL,Process Z分析該ITL就可以得到
之前Process J的事務的XID,就可以找到Process J這個事務的TX lock,PROCESS K 就會在TX resource的Enqueue Waiter Linked List上
建立一個X mode(exclusive)的enqueue lock。 這樣當Process J釋放TX lock時,Process J就會檢視該TX resource的Enqueue Waiter
Linked List 並發現Process K還在那裡等待,並會POST一個資訊給Process K說 TX lock已經被我釋放,隱含的意思就是row lock也已經
被我釋放,你可以繼續工作了。
--我的理解:(轉義1下)
ROW LOCK的Release 就需要依賴於TX的ENQUEUE LOCK,大致的過程是這樣的Session 1首先鎖定了資料塊中的一行, session 2需要更新
同樣的一行資料 ,session 2讀取該行鎖在資料塊,發現該row piece的lb不是0x0 ,而指向一個ITL,session 2分析該ITL就可以得到
之前session 1的事務的XID,就可以找到session 1這個事務的TX lock,session 2就會在TX resource的Enqueue Waiter Linked List上
建立一個X mode(exclusive)的enqueue lock。 這樣當session 1釋放TX lock時,session 2就會檢視該TX resource的Enqueue Waiter
Linked List 並發現Process K還在那裡等待,並會POST一個資訊給Process K說 TX lock已經被我釋放,隱含的意思就是row lock也已經
被我釋放,你可以繼續工作了。(好像是上面的重複)
--主要的理解就是session 2如果出現阻塞,除了第1次檢視資料塊外,其它情況會定期的檢查TX resource等待它的釋放。而不是再去檢
--查該資料行鎖在的資料塊以發現相應的ROW LOCK是否已經被釋放了,可以想象如果在OLTP環境中這樣去設計所造成的效能損失將是巨大
--的。
--劉工的測試還使用alter system set "_trace_events"='10000-10999:255:24';篇幅有點長,我給慢慢消化。
4.繼續測試:
--session 1:
SCOTT@book> rollback ;
Rollback complete.
--session 2:
SCOTT@book> update t_lock set id2=id2+3 where id1=1;
1 row updated.
SCOTT@book> select rowid,id1,id2 from t_lock ;
ROWID ID1 ID2
------------------ ---------- ----------
AAAWDnAAEAAAIU0AAA 1 14
--還可以修改要重讀該塊現在id2=14.
SCOTT@book> rollback;
Rollback complete.
SCOTT@book> select rowid,id1,id2 from t_lock ;
ROWID ID1 ID2
------------------ ---------- ----------
AAAWDnAAEAAAIU0AAA 1 11
總結:
--實際上這個測試很有意思,主要理解僅僅第1次檢視資料塊,出現阻塞後,會定期的檢查TX resource等待它的釋放,而不是檢查再檢查數
--據塊.有機會測試alter system set "_trace_events"='10000-10999:255:24';看看輸出的情況。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1868869/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 關於oracle中的row pieceOracle
- 關於hibernate的 No row with the given identifier existsIDE
- ORACLE ROW MOVEMENTOracle
- 關於 pipe row的用法(轉,待做實驗)
- 關於enq: TX - row lock contention行鎖的總結ENQ
- oracle效能診斷例項-row migration and row chainOracleAI
- 關於 oracle NULLOracleNull
- 關於oracle with as用法Oracle
- 關於ORACLE AUTOTRACEOracle
- 翻譯metalink關於WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章AIENQ
- Oracle vs PostgreSQL Develop(19) - PIPE ROWOracleSQLdev
- Oracle中rownum和row_number()Oracle
- 4.1 關於 Oracle RestartOracleREST
- [轉]關於oracle with as用法Oracle
- 關於oracle時區Oracle
- 關於oracle autonomous transactionOracle
- 關於oracle commitOracleMIT
- 關於informix和oracleORMOracle
- oracle關於分割槽相關操作Oracle
- 啟用oracle table rowdependencies trace row modificationOracle
- oracle 'row cache objects' 等待事件解釋OracleObject事件
- [ORACLE 11G]ROW CACHE LOCK 等待Oracle
- 1 關於 Oracle Data GuardOracle
- 4.2.1 關於配置Oracle RestartOracleREST
- 關於oracle的Spool命令Oracle
- 關於oracle的upsertOracle
- 關於oracle審計功能Oracle
- oracle 關於--控制檔案Oracle
- 關於Oracle Certificate ProfessionOracle
- 關於Oracle字元亂碼?Oracle字元
- 關於Oracle偽列rownumOracle
- 關於oracle中的undoOracle
- [20211026]關於18c row cache mutex.txtMutex
- Oracle如何精確計算row的大小Oracle
- Row Migration和row chainedAI
- 關於Oracle Database Vault介紹OracleDatabase
- 關於Oracle RAC後臺程式Oracle
- ORACLE關於NULL的總結OracleNull