Oracle多粒度锁的验证

创建测试表

aaa@AAA.US.ORACLE.COM create table test (a number,b number);

表已创建。

1 对于未提交的insert操作

aaa@AAA.US.ORACLE.COM 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操作

aaa@AAA.US.ORACLE.COM commit;

提交完成。

SQL select * from v$lock;

ADDR KADDR SID TYPE ID1 D2 LMODE REQUEST CTIME BLOCK

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

3 对于未提交的update操作

aaa@AAA.US.ORACLE.COM 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操作

 

 

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

 

5 对于select操作

aaa@AAA.US.ORACLE.COM select * from test where a=11;



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

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

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

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

6 for update操作

aaa@AAA.US.ORACLE.COM select * from test for update;



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

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互锁问题

 

 

 

 

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

查看锁:

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

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操作

aaa@AAA.US.ORACLE.COM 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中插入同样的记录

aaa@AAA.US.ORACLE.COM alter table test add constraint pk_a primary key(a);

表已更改。

 

aaa@AAA.US.ORACLE.COM insert into test(a) values(101);

已创建 1 行。

 

 

ession 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锁,并且阻塞住了其它的锁;

ession 2(sid=16)被阻塞,

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

 

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

 

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

 

 

 

 

 

已创建 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的锁已释放.