Oracle多粒度锁的验证

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维护行级锁的开销，使其在数据库并发控制方面有着明显的优势。