分享程序员开发的那些事...
更新时间：2022-09-10 16:19:41
[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';看看输出的情况。