小程序引擎如何促进企业在金融行业的数字化转型及合规运营
314
2023-12-29
Oracle中的锁机制及实验的分析
Oracle中的锁机制及实验的分析
Oracle中的锁机制及实验的分析，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。
使用锁的目的是什么：为了解决多用户环境下并发操作相同的资源而造成的错误修改数据的问题。单用户环境下不需要考虑锁，因为所有操作都是串行的。下面的文章简要的介绍了一下 要点:锁的分类异常复杂，enqueue、latch、mutex等，都是为了解决并发存在的，自己也有些混乱，所以也不过多解释了。下面列举一些对于lock的要点内容。 排他锁：不允许相关的资源被共享。一个资源在一个时间点内只有一个事务能够获取该资源的排他锁，只有持有该锁的事务能够修改相关的资源,其他想要获取锁的事务只能等待该事务因为commit或者rollback而释放排他锁。共享锁：允许相关的资源被共享。也就是说允许多个事务同时持有某个资源的共享锁。对于一个dml操作，会对表以及行加锁，也就是v$lock中的TM锁和TX锁。 行级锁基本原理：行级锁的信息是置于数据块中的，如果要修改某一条记录的值，其实就是在访问相应的block，并且分配一个ITL，然后通过rowid访问rowpiece header ，如果第二个字节lock byte(lock byte只占用1个字节，最大值为255，这也是为什么maxtrans最大为255）为0，则将其改为分配的ITL slot number。另外一个事务如果也想要修改数据，就会发现lock byte不为0，如果第一个事务还没有结束，则第二个事务进入enqueue等待，也就是transaction enqueue。 对于Table lock来说可以分为以下几种类型: 1. Row Share (RS|SS) 2. Row Exclusive Table Lock (RX|SX)3. Share Table Lock (S) 4. Share Row Exclusive Table Lock (SRX|SSX) 5. Exclusive Table Lock (X)以下是v$lock.LMODE字段中的数字对应的锁类型 LMODE(Lockmode in which the session holds the lock): 0 -none 1 -null (NULL) 2 -row-S (SS) 3 -row-X (SX)4 -share (S) 5 -S/Row-X (SSX) 6 -exclusive (X) 为了更好的开展下面的内容，这里列举一下各种TM锁类型的兼容情况。 详细验证情况会在4中给出。 顺便引用一下经典内容：只有被修改时，行才会被锁定。 当一条语句修改了一条记录，只有这条记录上被锁定，在Oracle数据库中不存在锁升级。 当某行被修改时，它将阻塞别人对它的修改。当一个事务修改一行时，将在这个行上加上行锁（TX），用于阻止其它事务对相同行的修改。 读永远不会阻止写。 读不会阻塞写，但有唯一的一个例外，就是select ...for update。 写永远不会阻塞读。当一行被修改后，Oracle通过回滚段提供给数据的一致性读
1.分别模拟insert，update和delete造成阻塞，
一个update更新语句的简单描述
当我们更新一个表的记录的时候，会有两种锁产生，一种是DML锁（TM）也可以称作table lock 还有一种事务锁（TX）也可以称作行锁 在v$lock中可以查看到。 例如下面的例子当中： SQL> select * from tt; ID NAME---------- -------------------- 1 aaa 2 aaa 3 aaa 4 aaa 5 aaa SQL> update tt set id=7; 5 rows updated.SQL> select sid,type,lmode,request,block from v$lock where sid =(select sid from v$mystat where rownum<2);SID TY LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- 1 TO 3 0 01 AE 4 0 0 1 TM 3 0 01 TX 6 0 0 AE是和版本化（Workspace Manager）相关的东西，这里不再过多描述。 从上面的查询结果可以看到更新的时候会添加一个3级的表锁，也就是 row-X (SX)锁，保证在事务结束之前，表的结构不会被更改。多个事务可以同时持有相同表的sx锁。还有一个6级的行锁，exclusive (X)，保证在事务结束之前，相关的行信息不会被更改。(锁信息存放于block中)ok简单示例后，来进行这一小节的主要内容，阻塞示例。insert 阻塞insert操作会对表加3级rx锁，和行排他锁，但是一般不会发生阻塞，因为读一致性的关系，在没提交之前只有当前session才可以操作新插入的行，对于其他事务来说 新增的记录是不可见的。 下面列举几种特殊的阻塞情况。 直接路径加载引发的阻塞 在11gr2中，可以使用insert /*+ append */ intoselect 的方式执行直接路径加载。或者 insert /*+append_values */ into values 的方式。 这里使用第二种。 Session1 session_id=1SQL> select sid from v$mystat where rownum<2; SID ---------- 1 SQL> insert /*+ append_values */ into tt values (8,b);1 row created.SQL> select sid , type , lmode , request , block from v$lock where sid = (select sid from v$mystat where rownum<2) ; SID TY LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- 1 TO 3 0 0 1 AE 4 0 01 TM 6 0 0 1 TX 6 0 0 可以看到使用直接路径加载的时候会对表加6级排他锁。根据表1，它会阻塞所有试图在表上加锁的事务。Session2 session_id=30 SQL> update tt set id=9; waiting... 看一下锁的情况：select sid , type , id1 , lmode , request , block from v$lock l where sid in (select session_id from v$locked_object) and type in (TM, TX)order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ----------1 TM 89451 6 0 1 --session1 包含了表6级锁，它正在阻塞其他的事务 1 TX 262155 6 0 030 TM 89451 0 3 0 --session2 它正在请求表的3级锁。 所以在直接路径加载的时候会对表加6级锁，阻塞其他事务对表加任意类型锁的操作。（sqlldr 并行+直接路径加载的时候会加4级锁）因为主键|唯一键引发的阻塞
SQL> truncate table tt; Table truncated.SQL> insert into tt values (1,a);1 row created. SQL> insert into tt values (2,b); 1 row created. SQL> commit; Commit complete. session1 session_id=1:SQL> alter table tt add primary key (id); Table altered. SQL> SQL> insert into tt values (3,c);1 row created. session2 session_id=30: SQL> insert into tt values (3,d); waiting...SQL> select sid , type , id1 , lmode , request , block from v$lock l where sid in (select session_id from v$locked_object)and type in (TM, TX) order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ---------- 1 TX 458773 6 0 11 TM 89451 3 0 0 30 TX 524308 6 0 030 TX 458773 0 4 0 30 TM 89451 3 0 00SQL> set lines 200 pages 999 SQL> select sid,seq#,event from v$session_wait where sid=30; SID SEQ# EVENT---------- ---------- ----------------------------------------------------------------30 24 enq: TX - row lock contention 这里发生了row lock等待事件。 可以看到因为在拥有primary key 列上插入了相同的值，第二个session除了持有自己本事务的6级排他锁之外，还在请求一个4级共享锁。这里发生了阻塞。如果第一个session 提交 。 第二个session会报错。 SQL> insert into tt values (3,d); insert into tt values (3,d) *ERROR at line 1: ORA-00001: unique constraint (SYS.SYS_C0011620) violatedUpdate阻塞这一部分的阻塞比较简单，只要发生update操作，就会对已有的行加6级排他锁，表上加3级共享锁。 session1 session_id=1: SQL> select * from tt; ID NAME---------- -------------------- 1 a 2 b 3 c SQL> update tt set name=AA where id=1; 1 row updated. session2 session_id=30:SQL> update tt set name=BB where id=2; 1 row updated. session3 session_id=32:SQL> update tt set name=ABC where id=1; waiting... 来看一下锁的情况： SQL> select sid , type , id1 , lmode , request , block 2 from v$lock l3 where sid in (select session_id from v$locked_object) 4 and type in (TM, TX) 5 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ----------1 TX 196629 6 0 1 --session1正在阻塞 session 31 TM 89451 3 0 0 30 TX 327706 6 0 030 TM 89451 3 0 0 32 TX 196629 0 6 032 TM 89451 3 0 0 6 rows selected. 由上可以看到，对单个表可以加多个3级共享锁。 session2因为修改的是id=2 的记录，所以可以正常执行。session3由于修改的是id=1 的记录，session1这个时候正在修改，并且对这一行的资源加了6级的排他锁。所以session3 发生了阻塞需要等待session 1 释放后才可以顺利执行。
Delete阻塞
其实对于delete、update、insert操作加锁操作大致相同，都会对表加3级共享锁，对修改的行加排他锁。 所以只要想要并发的修改表中相同的行，在第一个获取锁的事务没有结束前，后面的时候都会发生阻塞。SQL> select * from tt; ID NAME ---------- -------------------- 1 ABC 2 BB 3 c session1 session_id=1 :delete from tt where id=1; 1 row deleted. session2 session_id=30 : SQL> delete from tt where id >1;2 rows deleted. session3 session_id=32 SQL> delete tt; waiting... SQL>select sid , type , id1 , lmode , request , block from v$lock l where sid in (select session_id from v$locked_object) and type in (TM, TX)order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ---------- 1 TX 262174 6 0 11 TM 89451 3 0 0 30 TX 655368 6 0 030 TM 89451 3 0 0 32 TX 262174 0 6 032 TM 89451 3 0 0 6 rows selected. 发生了阻塞，只有当session 1 和session 2 的事务结束后，session 3 才可以顺利完成。下面有两个有趣的实验
有趣小实验1
SQL> insert into tt values (1,a); 1 row created. SQL> insert into tt values (2,b); 1 row created.SQL> insert into tt values (3,c); 1 row created. SQL> commit; Commit complete. SQL> select * from tt; ID NAME---------- -------------------- 1 a 2 b 3 c session1 session_id=1 SQL> delete from tt where id=2; 1 row deleted.session2 session_id=32 SQL> update tt set name=wang where id>1; waiting... session3 session_id=32SQL> delete from tt where id=3; 1 row deleted. 查看一下锁的情况： select sid , type , id1 , lmode , request , blockfrom v$lock l where sid in (select session_id from v$locked_object) and type in (TM, TX) order by 1 ;SID TY ID1 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ----------1 TX 655382 6 0 1 1 TM 89451 3 0 030 TX 655382 0 6 0 30 TM 89451 3 0 032 TX 196631 6 0 0 32 TM 89451 3 0 06 rows selected.这里比较有趣了，因为session 2 update 的记录包括id=2这一行，所以在id=2这一行加锁的时候，这里发生了transaction enqueue，它还没来得及对任何记录加锁，就已经进入了等待中。而session3执行的时候发现id=3 的这一行还没有锁标示，所以它顺利的对id=3 的记录加了锁。 这个时候我们rollback 第一条记录后 session1 ：SQL> rollback; Rollback complete. 发现session2 依然处于等待状态中 再看一下锁的情况：SQL> select sid , type , id1 , lmode , request , block from v$lock l where sid in (select session_id from v$locked_object) and type in (TM, TX)order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ---------- 30 TX 196631 0 6 030 TM 89451 3 0 0 30 TX 327712 6 0 032 TX 196631 6 0 1 32 TM 89451 3 0 0这个时候我们可以看到session2又在等待session3的事务结束以便获取id=3这条记录的锁。
有趣小实验2
SQL> select * from tt; ID NAME ---------- -------------------- 1 a 2 wangSQL> insert into tt values (3,c); 1 row created. SQL> commit; Commit complete. SQL> select * from tt; ID NAME---------- -------------------- 1 a 3 c 2 wang session1 session_id=1 SQL> delete from tt where id =3; 1 row deleted.session2 session_id=30 SQL> update tt set name=dddddddddd where id >1; waiting.. session3 session_id=32SQL> delete from tt where id =2; 1 row deleted. SQL> select sid , type , id1 , lmode , request , block2 from v$lock l 3 where sid in (select session_id from v$locked_object) 4 and type in (TM, TX)5 order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ---------- 1 TX 131087 6 0 11 TM 89451 3 0 0 30 TX 458774 6 0 130 TX 131087 0 6 0 30 TM 89451 3 0 032 TX 458774 0 6 0 32 TM 89451 3 0 0 7 rows selected.session 3 也进入了等待中，因为session2 先获取了id=2 的行锁，然后等待id=3 的行锁。
ITL 引起的阻塞
当block中没有多余的空间来添加ITL entry的时候，就会发生阻塞。具体可以看下面的例子：SQL> create table tb_itl (id int , name varchar2(4000)) pctfree 0 initrans 1 ; Table created.SQL> insert into tb_itl select level , d from dual connect by level <= 10000 ; 10000 rows created. SQL> commit; Commit complete.SQL> update tb_itl set name=lpad(x,2000,name) ; 10000 rows updated. SQL> commit; Commit complete.上面的操作保证至少第一个block中不会有多余的空间 select t.id, dbms_rowid.rowid_relative_fno(t.rowid)as "FNO#", dbms_rowid.rowid_block_number(t.rowid)as "BLK#", dbms_rowid.rowid_row_number(t.rowid) as"ROW#" from tb_itl t where rownum<5 ; SQL> select t.id, 2 dbms_rowid.rowid_relative_fno(t.rowid)as "FNO#", 3 dbms_rowid.rowid_block_number(t.rowid)as "BLK#", 4 dbms_rowid.rowid_row_number(t.rowid) as"ROW#" 5 from tb_itl t 6 where rownum<5 ; ID FNO# BLK# ROW#---------- ---------- ---------- ---------- 1 1 94905 0 2 1 94905 13 1 94905 2 4 1 94905 3 先dump一下看一下block中剩余有几个itl slotItl Xid Uba Flag Lck Scn/Fsc0x01 0x0006.016.00000a60 0x00c000ef.0284.14 C--- 0 scn 0x0000.003d7a84 0x02 0x0003.01c.000009ea 0x00c00153.028c.1c ---- 733 fsc 0x0000.00000000只有2个事务槽了。 下面内容引用自网络。 每个ITL entry包括以下的内容：Transactionid（Xid）: 8bytes。其中包括rollback segment number, transaction table中的slot number等。Undoblock address（Uba）: 8bytes。其中包括rollback segment block的DBA，sequence number等。 Flags:1nibble。 ---- =transaction is active, or committed pending cleanoutC--- =transaction has been committed and locks cleaned out -B-- =this undo record contains the undo for this ITL entry--U- =transaction committed (maybe long ago); SCN is an upper bound---T =transaction was still active at block cleanout SCN Locks:3nibbles. 也就是所谓的行级锁（row-level locks）SCN orfree space credit: 6bytes. 如果这个事务已经clean out，这个值就是SCN；否则，前两个字节表示由这个事务释放的此block中的空间数。 我们来尝试更改一下数据 session1 session_id=1SQL>update tb_itl set name=lpad(x,2000,name) where id =1 ; 1 rowupdated. session2 session_id=30SQL> update tb_itl set name=lpad(x,2000,name)where id =2 ; 1 rowupdated. session3 session_id=30SQL>update tb_itl set name=lpad(x,2000,name) where id =3 ; waiting... 看一下锁信息: select sid , type , id1 , lmode , request , block from v$lock lwhere sid in (select session_id from v$locked_object) and type in (TM, TX) order by 1 ;SID TY ID1 LMODE REQUEST BLOCK ---------- -- ---------- ---------- ---------- ----------1 TX 327705 6 0 1 1 TM 89470 3 0 030 TX 131081 6 0 0 30 TM 89470 3 0 032 TX 327705 0 4 0 ---申请4级别锁32 TM 89470 3 0 0 6 rows selected. SQL> set lines 200SQL> select sid,seq#,event from v$session_wait where sid=32; SID SEQ# EVENT---------- ---------- ---------------------------------------------------------------- 32 67 enq: TX - allocate ITL entry因为在block 94905中无法添加更多的ITL中无法添加更多的ITL entry（拓展一个只需要24b）而引发的阻塞。 通常情况下不会发生这种情况。解决办法：设置表的inittrans 参数为合理值。Bitmap 引起的阻塞
SQL> create table tb_bitmap_test (id number , gender varchar2(1)) ; Table created.SQL> insert into tb_bitmap_test select level , Ffrom dual connect by level <= 3; 3 rows created.SQL> insert into tb_bitmap_test select level , Mfrom dual connect by level <= 2; 2 rows created.SQL> create bitmap index tb_bitmap_test_btidx1 on tb_bitmap_test(gender) ; Index created. SQL> select * from tb_bitmap_test ; ID G ---------- - 1 F 2 F 3 F 1 M 2 Msession1 session_id=1: SQL> update tb_bitmap_test set gender=M where id=1 and gender=F ; 1 row updated.session2 session_id=30: SQL> delete tb_bitmap_test where gender=M and id = 1; waiting... session3 session_id=32SQL> insert into tb_bitmap_test values (1,S) ; 1 row created. --锁情况：select sid , type , id1 , lmode , request , block from v$lock l where sid in (select session_id from v$locked_object) and type in (TM, TX)order by 1 ; SID TY ID1 LMODE REQUEST BLOCK---------- -- ---------- ---------- ---------- ---------- 1 TM 89471 3 0 01 TX 262147 6 0 1 30 TX 589837 6 0 030 TM 89471 3 0 0 30 TX 262147 0 4 032 TM 89471 3 0 0 32 TX 196608 6 0 0 7 rows selected.不管是gender=M 或者 F ,只要涉及到这两个字段的值的dml操作都将进入等待当中（包括insert）因为第一个session 锁住了整个bitmap segment。但是只要gender的值不涉及M或者F即可顺利执行。所以session3 顺利的执行。
2.模拟RI锁定导致阻塞的场景。--初始化环境SQL> create table tun2_p (id int primary key) ;Table created.SQL> create table tun2_c (pid references tun2_p(id));Table created.SQL> insert into tun2_c values (1); insert into tun2_c values (1)*ERROR at line 1:ORA-02291: integrity constraint (SYS.SYS_C0011622) violated - parent key not found这里因为有引用完整性约束，子表中的内容必须与父表中的内容匹配。因为父表中没有id=1的记录，所以这里报错--主表插入SQL> insert into tun2_p values (2) ;1 row created.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ; SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- --------------------------------1 TX 589833 6 0 0 TUN2_P 1 TM 89475 3 0 0 TUN2_P1 TM 89473 3 0 0 TUN2_C1 TX 589833 6 0 0 TUN2_C 1 TM 89475 3 0 0 TUN2_C1 TM 89473 3 0 0 TUN2_P6 rows selected.SQL> select * from TUN2_P; ID---------- 2SQL> select * from TUN2_C;no rows selected--主表更新（子表中没有引用的记录）update tun2_p set id=3 where id=2 ;1 row updated.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ------------------- 1 TM 89473 3 0 0 TUN2_P1 TX 655386 6 0 0 TUN2_P---主表删除（子表中没有引用的记录）SQL> delete tun2_p where id=3 ;1 row deleted.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d 2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX)3 order by 1 ; SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ----------------------1 TM 89473 3 0 0 TUN2_P 1 TX 655386 6 0 0 TUN2_PSQL> commit;Commit complete.如果upadte和delete操作中不包含子表引用的记录，就不会对子表加锁。而insert相对比较复杂一点，它会级联的将子表锁定。如果在子表引用的记录上发生更改，则会报错。例如：updatetun2_p set id=3 where id=1ERROR atline 1:ORA-02292:integrity constraint (DEXTER.SYS_C0014143) violated - child record found子表插入--查询：SQL> select * from tun2_p; ID---------- 2插入子表：SQL> insert into tun2_c values (2) ;1 row created.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ; SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ------------------------1 TX 524302 6 0 0 TUN2_P 1 TM 89475 3 0 0 TUN2_P1 TM 89473 3 0 0 TUN2_C 1 TX 524302 6 0 0 TUN2_C1 TM 89475 3 0 0 TUN2_C 1 TM 89473 3 0 0 TUN2_P6 rows selected.--子表更新：SQL> update tun2_c set pid=1 where pid=2 ;update tun2_c set pid=1 where pid=2*ERROR at line 1:ORA-02291: integrity constraint (SYS.SYS_C0011622) violated - parent key not found--增加父键SQL> insert into tun2_p values(1);1 row created.SQL> commit;Commit complete.--更新子表SQL> update tun2_c set pid=1 where pid=2 ;1 row updated.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ------------------1 TX 196632 6 0 0 TUN2_P 1 TM 89475 3 0 0 TUN2_P1 TM 89473 3 0 0 TUN2_C1 TX 196632 6 0 0 TUN2_C 1 TM 89475 3 0 0 TUN2_C1 TM 89473 3 0 0 TUN2_P6 rows selected.--子表删除SQL> delete from tun2_c where pid=1 ;1 row deleted.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d 2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ----------------------------1 TX 196632 6 0 0 TUN2_P 1 TM 89475 3 0 0 TUN2_P1 TM 89473 3 0 0 TUN2_C 1 TX 196632 6 0 0 TUN2_C1 TM 89475 3 0 0 TUN2_C 1 TM 89473 3 0 0 TUN2_P6 rows selected.子表的记录一定会引用到父表的记录，所以在对子表进行dml操作的时候，都会锁定父表。复杂示例两个表中现在么有任何记录。session1 session_id=1SQL> select sid from v$mystat where rownum<2; SID---------- 1:SQL> commit;Commit complete.SQL> select * from tun2_p; ID---------- 1 2SQL> select * from tun2_c;no rows selectedSQL> insert into tun2_p values (3);1 row created.SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d 2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX)3 order by 1 ; SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- 1 TX 524309 6 0 0 TUN2_P1 TM 89475 3 0 0 TUN2_P 1 TM 89473 3 0 0 TUN2_C1 TX 524309 6 0 0 TUN2_C 1 TM 89475 3 0 0 TUN2_C1 TM 89473 3 0 0 TUN2_P6 rows selected.可以看到，当向父表中插入记录的时候，会同时锁定父表和子表，加表的3级共享锁。session1没提交之前其他事务无法看到父表中的id=1的记录，我们再来尝试一下向子表中插入pid=1的记录session2 session_id=30:SQL>insert into tun2_c values (3);waiting ...可以看到session2 进入了阻塞状态，我们来查看一下锁的情况SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ------------------------- 1 TX 524309 6 0 1 TUN2_P1 TM 89473 3 0 0 TUN2_P 1 TM 89475 3 0 0 TUN2_P1 TM 89473 3 0 0 TUN2_C 1 TM 89475 3 0 0 TUN2_C1 TX 524309 6 0 1 TUN2_C 30 TM 89473 3 0 0 TUN2_C30 TM 89475 3 0 0 TUN2_C30 TX 262146 6 0 0 TUN2_C 30 TX 524309 0 4 0 TUN2_C30 TM 89473 3 0 0 TUN2_P 30 TM 89475 3 0 0 TUN2_P30 TX 262146 6 0 0 TUN2_P 30 TX 524309 0 4 0 TUN2_P14 rows selected.首先我们可以看到，session2也有两个TM表锁，分别锁定了子表和父表。这说明在子表更新数据的时候，也会对引用的对象加锁。然后我们还看到，子表陷入了等待当中。这是因为session2 中的事务是否能够成功执行，取决于session1 中的事务状态。而session1 中的事务现在是悬而未决的状态。是不是有点和读一致性搞混了？觉得第二个session中的事务不应该进入阻塞当中，而是直接报错？它不像读一致性，可以在查询的时候根据undo获取一个一致性视图。在事务执行的时候，只和数据的当前状态相关。第一个session的事务rollback后session2就会报错SQL>insert into tun2_c values (1) ;insert intotun2_c values (1)ERROR atline 1:ORA-02291:integrity constraint (DEXTER.SYS_C0014143) violated - parent key not found3.从mode 2-6 的TM锁相互间的互斥示例介绍一些操作：下面的示例演示验证上表的内容Row Share (RS)Also called a subshare table lock (SS)Session1 session_id=1 :SQL>create table tun2_tab (x int) ;Tablecreated.SQL>lock table tun2_tab in ROW SHARE mode nowait ;Table(s)Locked.session2 session_id=30:SQL> lock table tun2_tab in ROW SHARE mode ;Table(s) Locked.SQL> commit;Commit complete.SQL> lock table tun2_tab in ROW EXCLUSIVE mode ;Table(s) Locked.SQL> commit;Commit complete.SQL> lock table tun2_tab in SHARE MODE ;Table(s) Locked.SQL> commit ;Commit complete.SQL> lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;Table(s) Locked.SQL> commit;Commit complete.SQL> lock table tun2_tab in EXCLUSIVE MODE ;waiting ...看一下锁的情况SQL> select l.sid,l.type,l.id1,l.lmode,l.request,l.block,d.object_name from v$lock l,v$locked_object o,dba_objects d2 where l.sid=o.session_id and o.object_id=d.object_id and l.type in (TM, TX) 3 order by 1 ;SID TY ID1 LMODE REQUEST BLOCK OBJECT_NAME---------- -- ---------- ---------- ---------- ---------- ------------1 TM 89479 2 0 1 TUN2_TAB 30 TM 89479 0 6 0 TUN2_TABRS|SS锁和X锁是不能并发的，但是可以兼容其他类型的锁。Row Exclusive TableLock （RX|SX）Also called a subexclusive table lock (SX)Session1 session_id=1SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;Table(s)Locked.Session2 session_id=30SQL>lock table tun2_tab in ROW SHARE mode ;Table(s)Locked.SQL>commit ;Commitcomplete.SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;Table(s)Locked.SQL>commit ;Commitcomplete.SQL>lock table tun2_tab in SHARE MODE ;waiting ...看一下锁的情况SQL>/ SID TY ID1 LMODE REQUEST BLOCK------------ ---------- ---------- ---------- ---------- 35 TM 76917 3 0 1160 TM 76917 0 4 0RX|SX 与 S 锁是无法并发的，经测试SRX|SSX锁也一样无法与RX|SX锁并发。Share Table Lock（S）Session1 session_id=1 :SQL>lock table tun2_tab in SHARE MODE ;Table(s)Locked.Session2 session_id=30SQL>lock table tun2_tab in ROW SHARE mode ;Table(s)Locked.SQL>commit ;Commitcomplete.SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;waiting ...锁情况：SQL>/SID TY ID1 LMODE REQUEST BLOCK------------ ---------- ---------- ---------- ----------35 TM 76917 4 0 1 160 TM 76917 0 3 0S 锁比较特殊，它不允许与RX|SX 也就是3级锁并发，但是允许多个S锁并发的在多个事务中持有。例如两个sessoin同时执行下面的命令lock table tun2_tab in SHARE MODE ;可以看到下面的lock信息：SQL>/SID TY ID1 LMODE REQUEST BLOCK------------ ---------- ---------- ---------- ----------35 TM 76917 4 0 0 129 TM 76917 4 0 0但是S锁无法与SRX|SSX和X锁并发。Share Row ExclusiveTable Lock (SRX|SSX）Also called a share-subexclusive table lock (SSX)Session1 session_id=1 :SQL>lock table tun2_tab in SHARE ROW EXCLUSIVE MODE ;Table(s)Locked.Session2 session_id=129：SQL>lock table tun2_tab in ROW SHARE mode ;Table(s)Locked.SQL>commit ;Commitcomplete.SQL>lock table tun2_tab in ROW EXCLUSIVE mode ;waiting ...锁情况：SQL>@lockSID TY ID1 LMODE REQUEST BLOCK------------ ---------- ---------- ---------- ----------35 TM 76917 5 0 1 129 TM 76917 0 3 0SRX|SSX锁无法与RX|SX以上的锁并发持有。Exclusive Table Lock(X)Session1 session_id=1 :SQL>lock table tun2_tab in EXCLUSIVE MODE ;Table(s)Locked.Session2 session_id=129：SQL>lock table tun2_tab in ROW SHARE mode ;waiting ...X锁无法与任何锁并发。4.导致死锁的SQL示例。下面给出一个最简单的示例SQL> create table a (x int);Table created.SQL> create table b(x int);Table created.SQL> insert into a values(1);1 row created.SQL> insert into a values(2);1 row created.SQL> insert into b values(1);1 row created.SQL> insert into b values(2);1 row created.SQL> commit;Commit complete.SQL>select * from a ; X------------ 1 2SQL>select * from b ; X---------- 1 2s1 t1:SQL>update b set x=3 where x= 1 ;1 row updated.s2 t2:SQL>update a set x=3 where x=1 ;1 row updated.s1 t3:SQL>update a set x=5 where x= 1 ;s2 t4:SQL>update b set x=5 where x=1 ;s1 t5:SQL>update a set x=5 where x= 1 ;update aset x=5 where x= 1ERROR atline 1:ORA-00060:deadlock detected while waiting for resourceraise error00600 deadlockSQL>select * from b ; X---------- 3 2s2 t6:still waiting直到s1 结束事务从这里可以看到，由于逻辑错误锁引发的死锁。两个事务都在等待对方释放锁资源。
第一个争抢资源导致死锁的语句会被取消（只是取消这一个语句，而不是结束整个事务）
看完上述内容，你们掌握Oracle中的锁机制及实验的分析的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注行业资讯频道，感谢各位的阅读！
版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。
相关文章
发表评论
最近发表
推荐文章
热评文章
暂时没有评论，来抢沙发吧~