建立t1表,进行作业。
a.模拟insert阻塞
当有唯一索引时,会发生insert阻塞。
为t1表建立唯一索引:
SQL> create unique index idx_t1_objectname on t1(object_id);
Index created
在session sid=159
SQL> insert into t1 select object_id,object_name from all_objects where object_id=20;
1 row inserted
在sid=141中
SQL> insert into t1 select object_id,object_name from all_objects where object_id=20;
v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE | |
1 | 2 | 29 | 1546 | 53019 | 141 | CTAISD | leon | 14184:12464 | 3 |
2 | 4 | 29 | 1528 | 53019 | 159 | CTAISD | leon | 14184:12464 | 3 |
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
19 | 39237168 | 39237284 | 141 | TX | 131101 | 1546 | 6 | 0 | 6 | 0 |
6 | 3A7B9AC0 | 3A7B9AD4 | 141 | TX | 262173 | 1528 | 0 | 4 | 6 | 0 |
16 | 391C3F90 | 391C3FA8 | 141 | TM | 53019 | 0 | 3 | 0 | 6 | 0 |
18 | 39236C3C | 39236D58 | 159 | TX | 262173 | 1528 | 6 | 0 | 573 | 1 |
17 | 391C403C | 391C4054 | 159 | TM | 53019 | 0 | 3 | 0 | 573 | 0 |
LOCK_SID | WAIT_SID | |
1 | 159 | 141 |
b.模拟update的场景
sid=159
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
159
SQL> update t1 set object_name='aaa' where object_id=20;
1 row updated
sid=141
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
141
SQL> update t1 set object_name='aaa' where object_id=20;
v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE | |
1 | 0 | 0 | 0 | 53019 | 141 | CTAISD | leon | 14184:12464 | 3 |
2 | 5 | 20 | 1641 | 53019 | 159 | CTAISD | leon | 14184:12464 | 3 |
v$lock
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 3A7B9AC0 | 3A7B9AD4 | 141 | TX | 327700 | 1641 | 0 | 6 | 54 | 0 |
2 | 391C403C | 391C4054 | 141 | TM | 53019 | 0 | 3 | 0 | 54 | 0 |
3 | 39236C3C | 39236D58 | 159 | TX | 327700 | 1641 | 6 | 0 | 60 | 1 |
4 | 391C3F90 | 391C3FA8 | 159 | TM | 53019 | 0 | 3 | 0 | 60 | 0 |
联合查询结果
LOCK_SID | WAIT_SID |
159 | 141 |
锁在159上,141等待159释放锁
c.模拟delete的场景
sid=159
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
159
SQL> delete from t1 where object_id=20;
1 row deleted
sid=141
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
141
SQL> delete from t1 where object_id=20;
v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE |
0 | 0 | 0 | 53019 | 141 | CTAISD | leon | 14184:12464 | 3 |
3 | 32 | 1531 | 53019 | 159 | CTAISD | leon | 14184:12464 | 3 |
v$lock
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 3A7B9AC0 | 3A7B9AD4 | 141 | TX | 196640 | 1531 | 0 | 6 | 810 | 0 |
2 | 391C403C | 391C4054 | 141 | TM | 53019 | 0 | 3 | 0 | 810 | 0 |
3 | 39236C3C | 39236D58 | 159 | TX | 196640 | 1531 | 6 | 0 | 816 | 1 |
4 | 391C3F90 | 391C3FA8 | 159 | TM | 53019 | 0 | 3 | 0 | 816 | 0 |
查询的结果
LOCK_SID | WAIT_SID |
159 | 141 |
锁在159上,141等待159释放锁
2.模拟RI锁定导致阻塞的场景,并分析v$lock相应的锁定信息,给出SQL演示。
实际上是数据一致性的锁,一般是在insert中出现。上一个作业中的insert场景即是这样的:
当有唯一索引时,会发生insert阻塞。 为t1表建立唯一索引: SQL> create unique index idx_t1_objectname on t1(object_id); Index created 在session sid=159 SQL> insert into t1 select object_id,object_name from all_objects where object_id=20; 1 row inserted 在sid=141中 SQL> insert into t1 select object_id,object_name from all_objects where object_id=20; v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE | |
1 | 2 | 29 | 1546 | 53019 | 141 | CTAISD | leon | 14184:12464 | 3 |
2 | 4 | 29 | 1528 | 53019 | 159 | CTAISD | leon | 14184:12464 | 3 |
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
19 | 39237168 | 39237284 | 141 | TX | 131101 | 1546 | 6 | 0 | 6 | 0 |
6 | 3A7B9AC0 | 3A7B9AD4 | 141 | TX | 262173 | 1528 | 0 | 4 | 6 | 0 |
16 | 391C3F90 | 391C3FA8 | 141 | TM | 53019 | 0 | 3 | 0 | 6 | 0 |
18 | 39236C3C | 39236D58 | 159 | TX | 262173 | 1528 | 6 | 0 | 573 | 1 |
17 | 391C403C | 391C4054 | 159 | TM | 53019 | 0 | 3 | 0 | 573 | 0 |
LOCK_SID | WAIT_SID | |
1 | 159 | 141 |
3.自己构想一个使用手工锁定解决一种业务需求的场景,并给出SQL演示。
常见的场景是:我们录入一条数据,但是如果同时有其他人在操作时,会出现重复提交的情况,因此需要进行锁定后进行操作。
建立排他锁,此时其他session是无法进行update/insert/delete等操作的
SQL> lock table t1 in exclusive mode; Table(s) locked
其他session 进行insert :SQL> insert into t1 select object_id,object_name from all_objects where object_id=20;已经被阻塞了。
4.给出从mode 2-6 的TM锁相互间的互斥示例。
锁模式:
LMODE 含义
2 row share
3 row exclusive
4 share
5 share row exclusive
6 exclusive
2:
SQL> lock table t1 in row share mode; Table(s) locked
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 391C3F90 | 391C3FA8 | 142 | TM | 53019 | 0 | 2 | 0 | 36 | 0 |
2 | 39219E88 | 39219FA4 | 142 | TX | 655385 | 1694 | 6 | 0 | 36 | 0 |
3:
SQL> update t1 set object_name='11' where object_id=20; 1 rows updated
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 391C3F90 | 391C3FA8 | 142 | TM | 53019 | 0 | 3 | 0 | 6 | 0 |
2 | 39219E88 | 39219FA4 | 142 | TX | 655385 | 1694 | 6 | 0 | 204 | 0 |
4:SQL> lock table t1 in share mode; Table(s) locked
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 391C3F90 | 391C3FA8 | 141 | TM | 53019 | 0 | 4 | 0 | 6 | 0 |
2 | 3921F1A0 | 3921F2BC | 141 | TX | 262144 | 1530 | 6 | 0 | 6 | 0 |
5:SQL> lock table t1 in share row exclusive mode; Table(s) locked
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 391C3F90 | 391C3FA8 | 141 | TM | 53019 | 0 | 5 | 0 | 3 | 0 |
2 | 3921F1A0 | 3921F2BC | 141 | TX | 393227 | 1588 | 6 | 0 | 3 | 0 |
6:SQL> update t1 set object_name='11' where object_id=20; 1 rows updated
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
1 | 391C3F90 | 391C3FA8 | 142 | TM | 53019 | 0 | 3 | 0 | 6 | 0 |
2 | 39219E88 | 39219FA4 | 142 | TX | 655385 | 1694 | 6 | 0 | 204 | 0 |
5.给出一个导致死锁的SQL示例。
SQL> create table t1 as select object_name from all_objects;
Table created
一、
session 1 :
sid 156
SQL> update t1 set object_name='aaa' where object_name='TEST';
1 row updated
SQL> update t1 set object_name='aaa' where object_name='TEST1';
19 rows updated
session2:
sid 143
SQL> update t1 set object_name='aaab' where object_name='TEST';
此时,session2被锁:
select * from v$lock
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
17 | 391C403C | 391C4054 | 143 | TM | 52926 | 0 | 3 | 0 | 67 | 0 |
6 | 3A7B9AC0 | 3A7B9AD4 | 143 | TX | 524307 | 1588 | 0 | 6 | 67 | 0 |
16 | 391C3F90 | 391C3FA8 | 156 | TM | 52926 | 0 | 3 | 0 | 87 | 0 |
18 | 3922AC60 | 3922AD7C | 156 | TX | 524307 | 1588 | 6 | 0 | 87 | 1 |
select * from v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE | |
1 | 0 | 0 | 0 | 52926 | 143 | CTAISD | leon | 212848:23628 | 3 |
2 | 8 | 19 | 1588 | 52926 | 156 | CTAISD | leon | 212848:23628 | 3 |
session3:
sid 142
SQL> update t1 set object_name='aaa' where object_name='TEST1';
此时session3也被锁了。
二、
然后再增加一个会话
session4:
sid 139
SQL> update t1 set object_name='aaa' where object_name='TEST2';
11 rows updated
因为是行级锁,可以正常使用。
三、
session 1 :
sid 156
SQL> update t1 set object_name='aaa' where object_name='TEST2';
此时:
session1 阻塞了 session2
session1 阻塞了 session3
session4 阻塞了 session1
select * from v$lock
ADDR | KADDR | SID | TYPE | ID1 | ID2 | LMODE | REQUEST | CTIME | BLOCK | |
21 | 391C4194 | 391C41AC | 139 | TM | 52926 | 0 | 3 | 0 | 465 | 0 |
22 | 391FC160 | 391FC27C | 139 | TX | 131109 | 1529 | 6 | 0 | 465 | 1 |
20 | 391C40E8 | 391C4100 | 142 | TM | 52926 | 0 | 3 | 0 | 480 | 0 |
7 | 3A7B9B1C | 3A7B9B30 | 142 | TX | 524307 | 1588 | 0 | 6 | 480 | 0 |
6 | 3A7B9AC0 | 3A7B9AD4 | 143 | TX | 524307 | 1588 | 0 | 6 | 622 | 0 |
19 | 391C403C | 391C4054 | 143 | TM | 52926 | 0 | 3 | 0 | 622 | 0 |
18 | 391C3F90 | 391C3FA8 | 156 | TM | 52926 | 0 | 3 | 0 | 642 | 0 |
8 | 3A7B9B78 | 3A7B9B8C | 156 | TX | 131109 | 1529 | 0 | 6 | 459 | 0 |
23 | 3922AC60 | 3922AD7C | 156 | TX | 524307 | 1588 | 6 | 0 | 642 | 1 |
select * from v$locked_object
XIDUSN | XIDSLOT | XIDSQN | OBJECT_ID | SESSION_ID | ORACLE_USERNAME | OS_USER_NAME | PROCESS | LOCKED_MODE | |
1 | 2 | 37 | 1529 | 52926 | 139 | CTAISD | leon | 212848:23628 | 3 |
2 | 0 | 0 | 0 | 52926 | 142 | CTAISD | leon | 212848:23628 | 3 |
3 | 0 | 0 | 0 | 52926 | 143 | CTAISD | leon | 212848:23628 | 3 |
4 | 8 | 19 | 1588 | 52926 | 156 | CTAISD | leon | 212848:23628 | 3 |
此时可以从v$lock中看出,所有session的tm锁的id1都是52926(即都是在t1表上被锁,可以从v$locked_object中看出来)
并且有3个session的request=6,说明有3个在等待的。
可以发现有2个session的lmode=6,即这里有两个锁,一个是1529上的,一个是1588上的。
有2个id2为1588的request=6(sid:142 143),在等待id2=1588的lmode=6(sid:156)的tx锁。
有1个id2为1529的request=6(sid:156),在等待id2=1529的lmode=6(sid:139)的tx锁。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25164132/viewspace-750426/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/25164132/viewspace-750426/