Oracle多粒度锁的验证

Oracle多粒度锁的验证

 

创建测试表

　　[e-mail]>create table test (a number,b number);

　　表已创建。

　　1 对于未提交的insert操作

　　[e-mail]>insert into test values(10,20);

　　已创建 1 行。

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 12AB9394 12AB93A8 17 TM 29512 0 3 0 84 0 12AF9218 12AF9324 17 TX 589841 5506 6 0 84 0

　　可见,对于未提交的insert操作,会产生两个锁,其类型(TYPE)分别为TM和TX,也就是表级意向锁和事务锁.

　　表级意向锁的模式(LMODE)为:3,表示是row exclusive,即表示此表中的某行获得了行排他锁.

　　事务锁的模式(LMODE)为:6, 表示是exclusive,即排他锁,表示此事务获得了排他锁.

　　BLOCK表示此锁是否阻塞了其它的锁,即发生死锁;此处没有.

　　2 对于提交的insert操作

　　[e-mail]>commit;

　　提交完成。

　　SQL> select * from v$lock;

　　ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK

　　此处已没有记录,说明在提交后,即完成了锁的释放.

　　3 对于未提交的update操作

　　[e-mail]>update test set a=11 where a=10;

　　已更新 1 行。

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 12AB9394 12AB93A8 17 TM 29512 0 3 0 3 0 12AF9218 12AF9324 17 TX 262153 5590 6 0 3 0

　　可见update操作所引起的锁的信息完全等同于insert操作..

　　4 对于提交的update操作

　　[e-mail]>commit;

　　提交完成。

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK

　　此处已没有记录,说明在提交后,即完成了锁的释放.

5 对于select操作

[e-mail]>select * from test where a=11; A B ---------- ---------- 11 20 SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK

　　此处已没有记录,说明select操作不会引起任何锁.

　　这是与sql server等数据库不同的,这些数据库select操作也会引起锁,以取得一致读;

　　而oracle是通过回滚机制实现一致读的,所以不需要引入锁机制,这极大增强了oracle的并发度.

　　6 for update操作

[e-mail]>select * from test for update; A B ---------- ---------- 11 20 SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 12AB9394 12AB93A8 17 TM 29512 0 2 0 0 0 12AF9218 12AF9324 17 TX 393224 5558 6 0 0 0

　　可见,for update操作会引起两个锁,分别是表级意向锁(TM)和事务锁(TX);

　　表级意向锁锁定模式为:2(row share),这表示属于此表中的某行获得了共享锁;相比较DML操作,此处锁级别低了一级,DML的是3;其实在oracle中没有行级共享锁.

　　TX的锁定模式为6,表示行级排他锁,这与DML的效果一致.

　　7 for update操作:commit后

　　当commit后,就会发现锁已被释放.

　　8 for update与update互锁问题

　　1) session 1中:

[e-mail]>select * from test for update; A B ---------- ---------- 11 20

　　2) session 2中:

　　[e-mail]>update test set a=12 where a=11;

　　此时,这条语句处于阻塞状态,说明等待锁;

　　查看锁:

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 132D2CCC 132D2CDC 16 TX 196624 5616 0 6 41 0 12AB9418 12AB942C 16 TM 29512 0 3 0 41 0 12AB9394 12AB93A8 17 TM 29512 0 2 0 86 0 12AF9218 12AF9324 17 TX 196624 5616 6 0 86 1

发现有两个会话处于有锁的活动;

　　发出for update操作的session 1(sid=17)的有模式为2(row share)的行级共享意向表级锁;模式为6(exclusive)行级排他锁;

　　发出update操作的session 2(sid=16)的模式为3(row exclusive)的行级排他意向锁;模式为0(None)的行级锁;

　　这说明,第二个session(sid=16)由于是后发出的操作,它会首先去检索将要操作的表是否存在锁,此处由于存在,故就堵塞了,所以没有获得行级锁;

　　这也就说,两个session在检测操作对象是否处于被锁状态时,是首先检测其表级锁,这就避免了去检测没一行的锁,这就提升了性能.

　　像这里的情况,我们所操作的对象是行,但所利用的检测锁机制是在表级.

　　同时,会发现session 1(sid=17)的TX锁的BLOCK为1,这表示此锁堵住了另外的锁;同时我们会看到session 2(sid=16)的TX锁等待的对象ID1和ID2与sid=16的相同,这说明sid=17的堵住了sid=16的.

　　8 rollback第一个会话的for update操作

　　[e-mail]>rollback;

　　回退已完成。

　　查看锁:

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 2AB9418 12AB942C 16 TM 29512 0 3 0 1296 0 12AE60B0 12AE61BC 16 TX 131089 5605 6 0 6 0

　　可见,第一锁的信息已没有.

　　此时只有session 2的锁的信息;而且session 2已获得锁.

　　如果再将session 2进行回滚,就会发现session 2的锁也没有了.

　　9实体完整性引发的锁阻塞

　　在具有primary key约束的表中,在两个session中插入同样的记录

　　[e-mail]>alter table test add constraint pk_a primary key(a);

　　表已更改。

　　Session 1中:

　　[e-mail]>insert into test(a) values(101);

　　已创建 1 行。

　　Session 2中:

　　[e-mail]>insert into test(a) values(101);

　　session 2处于阻塞状态.

　　可见,在session1没有提交的情况,实体完整性约束就会阻塞住session 2;

　　查看锁:

SQL> select * from v$lock; ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 132D2CCC 132D2CDC 16 TX 131081 5627 0 4 164 0 12AE60B0 12AE61BC 16 TX 458759 5513 6 0 164 0 12AB9418 12AB942C 16 TM 29512 0 3 0 164 0 12AB9394 12AB93A8 17 TM 29512 0 3 0 188 0 12AE649C 12AE65A8 17 TX 131081 5627 6 0 188 1

　　可见,session 1(sid=17)已获得TM和TX锁,并且阻塞住了其它的锁;

session 2(sid=16)被阻塞,

　　可以发现, session 2已获得了行排他锁:

　　12AE60B0 12AE61BC 16 TX 458759 5513 6 0 164 0

　　已经完全分配了新的事务;所以session 2不是被堵在和session 1竞争同一个数据块上(如上面的例子),而是被堵在了完整行约束上:

　　132D2CCC 132D2CDC 16 TX 131081 5627 0 4 164 0

　　这个锁请求的类型为4 (share);

　　Sessio 1:

　　[e-mail]>rollback;

　　回退已完成。

　　Session 2:

　　[e-mail]>insert into test(a) values(101);

　　已创建 1 行。

ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK 2AB9418 12AB942C 16 TM 29512 0 3 0 764 0 12AE60B0 12AE61BC 16 TX 458759 5513 6 0 764 0

　　可见,session 2所持有的锁剩余两个,那个原来等待session 1的锁已释放.

　　10参照完整性引发的锁阻塞

　　[e-mail]>create table test_child(c number,a number not null constra

　　int pk_a_ref references test(a));

　　表已创建。

　　[e-mail]>insert into test(a) values(101);

　　已创建 1 行。

　　Session 1:

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- -- ------ AAA 17 Row Exclusive TEST 10 21 5678 AAA 17 Row share TEST_CHILD 10 21 5678

　　可以发现,有两个对象被锁住: TEST和TEST_CHILD

[e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- ---- ------ 17 TM 29512 0 Row Exclusive 0 75 0 17 TM 29515 0 Row share 0 75 0 17 TX 655381 5678 Exclusive 0 75 0

　　可见有三个锁;

SQL> select object_name from dba_objects where object_id=29512; OBJECT_NAME --------------- TEST SQL> select object_name from dba_objects where object_id=29515; OBJECT_NAME --------------- TEST_CHILD

　　可见,除了TEST表需要的TM和TX锁外,

　　还同时将TEST_CHILD表锁住了:其锁类型为Row share

　　session 2:

　　[e-mail]>insert into test_child(c,a) values(11,101);

　　插入外键值为101的,语句的执行会停顿.

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- - ------ AAA 16 Row share TEST 7 18 5521 AAA 16 Row Exclusive TEST_CHILD 7 18 5521 AAA 17 Row Exclusive TEST 10 21 5678 AAA 17 Row share TEST_CHILD 10 21 5678

　　这时会发现,被锁住的对象有4个;这是因为在子表中的插入同时会锁住父表和子表.

[e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- - ------ 16 TX 655381 5678 None 4 79 0 16 TM 29512 0 Row share 0 79 0 16 TM 29515 0 Row Exclusive 0 79 0 16 TX 458770 5521 Exclusive 0 79 0 17 TM 29512 0 Row Exclusive 0 508 0 17 TX 655381 5678 Exclusive 0 508 1 17 TM 29515 0 Row share 0 508 0

　　已选择7行。

分析锁的情况.

　　Session 2(sid=16)有四个锁:分别是子表的TM和TX锁:

　　16 TM 29515 0 Row Exclusive 0 79

　　0

　　16 TX 458770 5521 Exclusive 0 79

　　父表的TM和TX锁:

　　16 TX 655381 5678 None 4 79

　　0

　　16 TM 29512 0 Row share 0 79

　　0

　　这是因为参照完整性需要父表在参照的过程中不能发生改变,所以要对父表加上这些限制.

　　Session 1:

　　[e-mail]>rollback;

　　回退已完成。

　　Session 2:

　　[e-mail]>insert into test_child(c,a) values(11,101);

　　insert into test_child(c,a) values(11,101)

　　*

　　ERROR 位于第 1 行:

　　ORA-02291: 违反完整约束条件 (AAA.PK_A_REF) - 未找到父项关键字

　　11 更新子表时

　　[e-mail]>update test_child set a=0 where 1=0;

　　已更新0行。

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- ---- ------ AAA 17 Row share TEST 0 0 0 AAA 17 Row Exclusive TEST_CHILD 0 0 0 [e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- ---- ------ 17 TM 29512 0 Row share 0 61 0 17 TM 29515 0 Row Exclusive 0 61 0

　　可见,当更新子表时,会锁住父子两个表,即使实际上没有更新数据

12 当更新父表时:

　　[e-mail]>update test set a=0 where 1=0;

　　已更新0行。

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- ---- ------ AAA 17 Row Exclusive TEST 0 0 0 [e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- ---- ------ 17 TM 29512 0 Row Exclusive 0 15 0 > 可见,更新父表只会锁住父表

　　13 当父子两个表同时更新时:

　　session 1:

　　[e-mail]>update test_child set a=0 where 1=0;

　　已更新0行。

　　Session 2

　　[e-mail]>update test set a=1 where 1=0;

　　session 2会被锁住.

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- ---- ------ AAA 16 Row Exclusive TEST 0 0 0 AAA 16 None TEST_CHILD 0 0 0 AAA 17 Row Exclusive TEST 0 0 0 AAA 17 Row Exclusive TEST_CHILD 0 0 0 [e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- ---- ------ 16 TM 29512 0 Row Exclusive 0 72 0 16 TM 29515 0 None 4 72 0 17 TM 29512 0 Row Exclusive 0 358 0 17 TM 29515 0 Row Exclusive 0 95 1

　　可见,会发生死锁.是由于第二个session 申请子表的share锁时发生的.

14对外键建立索引

　　[e-mail]>create index idx_child on test_child(a);

　　索引已创建。

　　当父子两个表同时更新时:

　　session 1:

　　[e-mail]>update test_child set a=0 where 1=0;

　　已更新0行。

　　Session 2

　　[e-mail]>update test set a=1 where 1=0;

　　已更新0行。

　　可见,不会发生死锁.

[e-mail]>@showlockedobj O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ---------- --------------- --------------- ---------- ---------- ---- ------ AAA 16 Row Exclusive TEST 0 0 0 AAA 16 Row share TEST_CHILD 0 0 0 AAA 17 Row share TEST 0 0 0 AAA 17 Row Exclusive TEST_CHILD 0 0 0 [e-mail]>@showlocks SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK ---------- ---- ---------- ---------- --------------- ---------- ---------- ---- ------ 16 TM 29512 0 Row Exclusive 0 15 0 16 TM 29515 0 Row share 0 15 0 17 TM 29512 0 Row share 0 23 0 17 TM 29515 0 Row Exclusive 0 23 0

　　可以发现,session 1获得TEST_CHILD行级排他意向表锁,同时获得TEST表的行级共享排他意向锁;

　　session 2获得TEST行级排他意向表锁,同时获得TEST_CHILD表的行级共享排他意向锁;

　　与上个例子相比,区别在于前面的例子中,session 1 获得TEST_CHILD和TEST行级排他意向表锁.

　　也就是说,对外键建立索引,可以防止两个表的死锁.

　　15 总结

　　Oracle通过具有意向锁的多粒度封锁机制进行并发控制，保证数据的一致性。其DML锁(数据锁)分为两个层次(粒度)：即表级和行级。通常 的DML操作在表级获得的只是意向锁(RS或RX)，其真正的封锁粒度还是在行级;另外，在Oracle数据库中，单纯地读数据(SELECT)并不加 锁，这些都极大地提高了系统的并发程度。

　　在支持高并发度的同时，Oracle利用意向锁及数据行上加锁标志位等设计技巧，减小了Oracle维护行级锁的开销，使其在数据库并发控制方面有着明显的优势。