[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';看看輸出的情況。