《让Oracle跑得更快1》第二章 锁和阻塞

1.分别模拟insert，update和delete造成阻塞的示例，并对v$lock中的相应的信息进行说明，给 出SQL演示。
建立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

v$lock

	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

锁在159上，141等待159释放锁
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

v$lock

	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

测试发现159的TX锁是LMODE 6 但是他阻塞的141 这个session的request = 4



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/