Oracle的锁机制

Oracle的锁机制

1 锁的概念

1.1 作用

访问数据时，保证数据读的一致性、数据的完整性和数据的并行性的一种机制；

1.2 分类

1.2.1 按是否独占分类

1)     排它锁（Exclusive Locks，即X锁）

2)       共享锁（Share Locks，即S锁）

l  共享方式表封锁常用于一致性查询过程，即在查询数据期间表中的数据不发生改变。

l  独占方式封锁通常用于更新数据，当某个更新事务涉及多个表时，可减少发生死锁。

1.2.2 按数据的锁定时间分类

1)     悲观锁:假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁定住，如表级锁；

2)       乐观锁:假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测和处理，如行级锁；

1.2.3 按保护对象的不同

1)     内部锁和闩（internal locks and latches），保护数据库的内部结构，由系统内部实现，用户不能访问

2)     DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；用于保护数据字典和数据定义改变时的一致性和完整性。它是系统在对SQL定义语句作语法分析时自动地加锁，无需用户干预

3)     DML锁（data locks，数据锁）：用于保护数据的完整性和完整性，其封锁对象可以是表或行

①     共享锁：

LOCK TABLE <表名>[,<表名>]....
IN Share MODE [NOWAIT]

②     独占锁：

LOCK TABLE <表名>[,<表名>]....
IN EXCLUSIVE MODE [NOWAIT]

③     共享更新锁：

LOCK TABLE <表名>[,<表名>]....
IN Share Update MODE [NOWAIT]

1.2.4 按锁的粒度分类

1)     行级锁(TX)

2)     表级锁(TM)

①     共享锁(S)：Share Lock

②     排它锁(X)：Exclusive Lock

③     行级共享锁(RS)：Row Share Lock

④     行级排它锁(RX)：Row Exclusive Lock

⑤     共享行级排它锁(SRX)：Share Row Exclusive Lock

l  封锁的粒度：指封锁对象的大小

l  封锁的强度：指对其它锁的排斥程序

l  DML锁按封锁粒度又可分为行锁和表锁

l  1个TX锁可对应多个被该事务锁定的数据行

l  ITL：Interested Transaction List，事务相关列表

1.2.5 按加锁的意向分类

1)     意向共享锁(IS)：Intent Share Lock，若父结点加IS锁，则表示拟向子结点加共享锁

2)     意向排它锁(IX)：Intent Exclusive Lock，若父结点加IX锁，表示拟向子结点加排它锁

3)     共享意向排它锁(SIX)：Share Intent Exclusive Lock，若父结点加SIX锁，表示对父结点先加S锁，再加IX锁。

l  意向锁：若某个结点加上意向锁，则其子结点计划被加锁；对任一结点加锁时，必先对其父结点加意向锁。

l  具有意向锁的多粒度封锁方法提高了系统的并发度，减少了加锁和解锁的开销；

TM锁与意向锁相容对应矩阵

Lmode	T1    T2	S	X	RS	RX	SRX	-
4	S	Y	N	Y	N	N	Y
6	X	N	N	N	N	N	Y
2	IS（RS）	Y	N	Y	Y	Y	Y
3	IX（RX）	N	N	Y	Y	N	Y
5	SIX（SRX）	N	N	Y	N	N	Y
	-	Y	Y	Y	Y	Y	Y

 

2 表级锁(TM锁)

2.1 行级排他锁（Row Exclusive，简称RX锁）

当我们进行DML时会自动在被更新的表上添加RX锁，或者也可以通过执行lock命令显式的在表上添加RX锁。在该锁定模式下，允许其他的事务通过DML语句修改相同表里的其他数据行(RX)，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。

DML(insert/update/delete)à RXà RS/RX—×à S/X/RSX

Lock table in row exclusive modeà RXà RS/RX—×à S/X/RSX

2.2行级共享锁（Row Shared，简称RS锁）

通常是通过select … from for update语句添加的，同时该方法也是我们用来手工锁定某些记录的主要方法。比如，当我们在查询某些记录的过程中，不希望其他用户对查询的记录进行更新操作，则可以发出这样的语句。当数据使用完毕以后，直接发出rollback命令将锁定解除。当表上添加了RS锁定以后，不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行。

select … from for updateà RSàS/ RS/RX/RSX—×à X

Lock table in row share modeà RSà S/ RS/RX/RSX—×à X

2.3 共享锁（Share，简称S锁）

通过lock table in share mode命令添加该S锁。在该锁定模式下，不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁。

Lock table in share modeà S——select … from for updateà RS

Lock table in share modeà S —×à X/RX/RSX

2.4 排他锁（Exclusive，简称X锁）

通过lock table in exclusive mode命令添加X锁。在该锁定模式下，其他用户不能对表进行任何的DML和DDL操作，该表上只能进行查询。

Lock table inexclusive modeà X—×à S/X/RS/RX/RSX

2.5 共享行级排他锁（Share Row Exclusive，简称SRX锁）

通过lock table in share row exclusive mode命令添加SRX锁。该锁定模式比行级排他锁和共享锁的级别都要高，这时不能对相同的表进行DML操作，也不能添加共享锁。

Lock table in rowexclusive modeà RSXàRS

 

TM锁的兼容性

—	S	X	RS	RX	SRX	N/A
S	√	×	√	×	×	√
X	×	×	×	×	×	√
RS	√	×	√	√	√	√
RX	×	×	√	√	×	√
SRX	×	×	√	×	×	√
N/A	√	√	√	√	√	√

 

 

TM锁小结

SQL语句	表锁模式	允许的锁模式	备注
Select * from tb	无	R、S、RS、RX、SRX
Select * from tb for update Lock table tb in row share mode	RS	RS、RX、S、SRX	不允许其他事务对相同的表添加排他锁，但是允许其他的事务通过DML语句或lock命令锁定相同表里的其他数据行
Insert into tb Update tb Delete from tb Lock table tb in row exclusive mode	RX	RS、RX	允许其他的事务通过DML语句修改相同表里的其他数据行(RX)，或通过lock命令对相同表添加RX锁定，但是不允许其他事务对相同的表添加排他锁（X锁）。
Lock table tb in share mode	S	S、RS	不允许任何用户更新表。但是允许其他用户发出select …from for update命令对表添加RS锁或S锁
Lock table tb in share row exclusive mode	SRX	RS	只允许查询和添加RS锁，不能对相同的表进行DML操作，也不能添加共享锁
Lock table tb in exclusive mode	X	无	只允许查询，其他用户不能对表进行任何的DML和加锁操作，该表上只能进行查询

 

3 行级锁(TX锁)

Oracle行级锁只对用户正在访问的行进行锁定。可以更好的保证数据的安全性。行级锁是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作，也不会锁定同一个数据块中的其他数据行。insert、update、delete操作默认加行级锁，其原理和操作与select for update并无两样。

行级锁的机制。当一个事务开始时必选先申请一个TX锁（保护回滚段、回滚数据块），只有先申请到回滚段资源后才能开始一个事务，才能进行DML操作。这个动作完成后，事务就可以开始修改数据了。当修改数据表的记录时，遵循以下的操作顺序：

1)     获得表的TM锁（保护事务执行过程中其它用户不能修改表结构）

2)     在要修改的数据块头部的ITL表中申请一个空闲表项，记录事务号，实际记录的是这个事务要使用的回滚段地址。

3)     修改数据块的某条记录时，设置该记录头部的ITL索引指向第2步申请的表项，然后再修改记录内容。修改前需奥在回滚段对记录修改前的记录状态做一个copy。

4)     当其它用户想并发修改这条记录时，会根据记录头的ITL索引读取数据块头部ITL表项的内容，查看这个事务是否已经提交。

5)     如果没有提交，这个用户会等待前一个用户的TX锁的释放。

 

DML锁的明细分类图

DML锁
大类	小类	本用户					其它用户
		查询	更新	加共享锁	加共享 更新锁	加独占锁	查询	更新	加共享锁	加共享 更新锁	加独占锁
表级锁 (TM)	共享封锁方式	√	√	√	√	√	√	×	√	√	×
	独占封锁方式	√	√	√	√	√	√	×	×	×	×
行级锁(TX)	共享更新封锁	√	√	√	√	√	√	√	√	√	×

 

Oracle的TM锁类型
锁模式	锁描述	解释	SQL操作
0	none
1	NULL	空	Select
2	SS(Row-S)	行级共享锁 其他对象只能查询这些数据行	Select for update、Lock for update、Lock row share
3	SX(Row-X)	行级排它锁 在提交前不允许做DML操作	Insert、Update、Delete、Lock row share
4	S(Share)	共享锁	Create index、Lock share
5	SSX(S/Row-X)	共享行级排它锁	Lock share row exclusive
6	X(Exclusive)	排它锁	Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

Ø  locked_mode为2、3、4时，不影响本用户及其它用户的部分或全部DML（insert、delete、update、select）操作

 

select * from Tb for update的情况

1)        For update时，本用户和其它用户只能进行insert和select操作?

2)        Insert时，本用户和其它用户能进行for update、insert、delete、update、select所有操作

3)        Update时，本用户和其它用户不能进行for update操作，但能进行insert、delete、update、select操作

4)        Delete时，本用户和其它用户不能进行for update操作，但能进行insert、delete、update、select操作

 

select * from tb where … for update的锁情况

Ø  两个或两个以上相同用户或不同用户的select for update、insert、update或delete同时操作的数据行应该不一样，否则会产生堵塞；

Ø  下图的select for update指的是select * from tb where … for update

 

select * from tb where … for update的锁模式

Select for update	锁模式	本用户				其它用户
		Select for update	Insert	Update	Delete	Select for update	Insert	Update	Delete
表锁(TM)	RX	RX	RX	RX	RX	RX	RX	RX	RX
行锁(TX)	X	X	X	X	X	X	X	X	X
可否进行	Y	Y	Y	Y	Y	Y	Y	Y	Y

Ø  但若select * from Tb for update，则锁的粒度为整个表，此时不管本用户还是其它用户，都只能使用insert.

Insert的锁模式

insert	锁模式	本用户					其它用户
		Select for update	Select for update	Insert	Update	Delete	Insert	Update	Delete
表锁(TM)	RX	RX	RX	RX	RX	RX	RX	RX	RX
行锁(TX)	X	X	X	X	X	X	X	X	X
可否进行	Y	Y	Y	Y	Y	Y	Y	Y	Y

 

Update的锁模式

update	锁模式	本用户				其它用户
		Select for update	Insert	Update	Delete	Select for update	Insert	Update	Delete
表锁(TM)	RX	RX	RX	RX	RX	RX	RX	RX	RX
行锁(TX)	X	X	X	X	X	X	X	X	X
可否进行	Y	Y	Y	Y	Y	Y	Y	Y	Y

 

Delete的锁模式

delete	锁模式	本用户				其它用户
		Select for update	Insert	Update	Delete	Select for update	Insert	Update	Delete
表锁(TM)	RX	RX	RX	RX	RX	RX	RX	RX	RX
行锁(TX)	X	X	X	X	X	X	X	X	X
可否进行	Y	Y	Y	Y	Y	Y	Y	Y	Y

 

Ø  若子表有主外键，则对子表进行insert和delete时，同时会在主表（即以外键为主键的表）上加上RS锁；

Ø  若子表有主外键，则对主表进行insert数据时，同时会在子表自动加上RS锁；

1)       A用户进行select for update时，A用户可同时进行select, insert, update, delete操作，但其它用户只能进行insert操作；

2)       A用户进行update Tb时，B用户可同时进行insert/update/delete操作，但不能进行select for update的操作；

Ø  一旦用户对某个行施加了共享更新封锁，则该用户可以查询也可以更新被封锁的表；其它用户都能查询，且若本用户未更新表之前，其它用户也能更新被封锁的表．

Ø  一旦用户对某个行施加了行级封锁，则该用户可以查询也可以更新被封锁的数据行，其它用户只能查询但不能更新被封锁的数据行．如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式封锁表，一直到提交或复原该事务为止。行锁永远是独占方式锁。

Ø  在Oracle系统中能自动发现死锁，并选择代价最小的，即完成工作量最少的事务予以撤消，释放该事务所拥有的全部锁，记其它的事务继续工作下去。
从系统性能上考虑，应该尽可能减少资源竞争，增大吞吐量，因此用户在给并发操作加锁时，应注意以下几点：
１、对于UPDATE和DELETE操作，应只封锁要做改动的行，在完成修改后立即提交。
２、当多个事务正利用共享更新的方式进行更新，则不要使用共享封锁，而应采用共享更新封锁，这样其它用户就能使用行级锁，以增加并行性。
３、尽可能将对一个表的操作的并发事务施加共享更新锁，从而可提高并行性，即不要使用for update，而是直接insert/update/delete
４、在应用负荷较高的期间，不宜对基础数据结构（表、索引、簇和视图）进行修改

 

4 锁阻塞模拟

1)       操作同一行数据引发的锁阻塞

Ø  Sess#1：

Select * from scott.dept for update

Ø  Sess#2:

update scott.dept set dname='IT' where deptno=80;

从监控可看出Sess#2申请的锁类型(Request)为6，即独占锁，处于阻塞状态，故只能等Sess#1提交或回滚后，Sess#2才能获得独占锁；

2)       实体完整性引发的锁阻塞

Ø  Sess#1：

insert into scott.dept(deptno,dname) values(60,'IT');

Ø  Sess#2:

insert into scott.dept(deptno,dname) values(60,'IT');

从监控可看出Sess#2申请的锁类型(Request)为4，即共享锁，处于阻塞状态，但Sess#1已经先对数据行加了X锁，故只能等Sess#1回滚后，Sess#2才能获得独占锁；

3)       参照完整性引发的锁阻塞

Ø  Sess#1：

insert into scott.dept(deptno,dname) values(60,'IT');

Ø  Sess#2:

insert into scott.emp(empno,ename,deptno) values(1234,'Bryan',60);

从监控可看出Sess#2申请的锁类型(Request)为4，即共享锁，处于阻塞状态，但Sess#1已经先对数据行加了X锁，故只能等Sess#1提交后，Sess#2才能获得独占锁；

4)       外键未加索引引发的锁阻塞

Ø  Sess#1：

delete from scott.emp where 0=1;

Ø  Sess#2:

delete from scott.dept where 0=1;

从监控可看出Sess#2申请的锁类型(Request)为4，即共享锁，处于阻塞状态，但Sess#1已经先对数据行加了X锁，故只能等Sess#1提交或回滚后，Sess#2才能获得独占锁；

5)       部分回滚引发的锁阻塞

Ø  Sess#1(Scott)：

savepoint A;

select * from scott.dept where deptno=10 for update;

savepoint B;

update scott.emp set job='CEO' where empno=7369;

Ø  Sess#2(System)：

update scott.dept set loc='Macao' where deptno=10;

此时，Sess#2申请的锁类型(Request)为6，即独占锁，处于阻塞状态，故只能等Sess#1提交或回滚后，Sess#2才能获得独占锁；

Ø  Sess#3(Test)：

lock table scott.emp in share mode;

此时，Sess#3申请的锁类型(Request)为4，即共享锁，但Sess#1已经先对数据行加了X锁，处于阻塞状态，故只能等Sess#1提交或回滚后，Sess#2才能获得独占锁；

Ø  Sess#1：

Rollback to B;

此时，由于Sess#1释放掉表emp上的独占锁，故Sess#3获得了共享锁S；但Sess#2申请的锁类型(Request)为6，即独占锁仍没有被释放，处于阻塞状态，。

Rollback to A;

此时，Sess#1虽然释放掉了Dept的表级锁(TM)，但并没有同时释放掉行级锁(TX)，故Sess#2仍处于阻塞状态；不过，此时若其它Session对表Dept进行修改，则不会被阻塞，而其它Session再对表Dept进行修改时，生成的TX锁是新锁，虽然与Sess#1生成的TX锁虽然封锁的数据行是一样的，但却是不同的TX锁(id1和id2不一样)，况且冲突事实不存在，故该Session不会被阻塞。所以，某个Session的TX锁只能等冲突时的本事务和其它事务全部结束后才能被释放掉；

Ø  Sess#3(Test)：

update scott.dept set loc='Macao' where deptno=10;

6)       锁的排队机制引发的锁阻塞

Ø  Sess#1：

update scott.emp set sal=5555 where empno=1001;

Ø  Sess#2：

lock table scott.emp in share mode;

此时，由于Sess#1在表emp上获得RX锁，故Sess#2申请的S锁被阻塞；

Ø  Sess#1：

       Rollback;

 

7)       ITL Slot(并发修改的最大事务数)引发的锁阻塞

Ø  若并发事务超过ITL的Slot长度，即定义数据对象时指定的Maxtrans参数，则会引起阻塞；

5 监控系统中的锁

表名	作用	备注
V$LOCK	描述锁的名称、类型、锁模式等信息
V$LOCKED_OBJECT	描述被锁住的对象名称、会话ID、Oracle用户、OS名称等
V$SESSION	描述会话信息
DBA_OBJECT	描述所有用户对象

 

V$Lock视图主要字段说明

字段名称	类型	说明
Lmode	Number	锁模式: 0(none)、1(null)、2(row share)、3(row exclusive)、4(share)、5(share row exclusive)、6(exclusive)
Request	Number	申请的锁模式，具体值同上
ID1	Number	TM	被修改表的标志（object_id）
		TX	回滚段号和锁标志位号
ID2	Number	TM	0
		TX	锁标志位被重用的次数

 

锁定模式	锁定简称	编码数值
Row Exclusive	RX	3
Row Shared	RS	2
Share S	S	4
Exclusive	X	6
Share Row Exclusive	SRX	5
NULL	N/A	0或者1

 

5.1 如何检测是否有锁现象

/* 列出死锁

select sid,id1,id2,lmode,request,type from v$lock

where id1 in (select id1 from v$lock where lmode = 0)

order by id1,request;

*/ 

或 --查找TM或TX锁

select sid,type,id1,id2,
     decode(lmode,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') lock_type,
     request,ctime,block
from v$lock
where type in('TM','TX');

或 --查找TM或TX锁

select type || '-' || id1 || '-' || id2 "resource", sid,
     decode(lmode,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') lock_type,
     decode(request,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') request,
     ctime,block
from v$lock
where type in('TM','TX');

5.2 如何查找被锁住的会话ID、对象名称？

select l.os_user_name, s.username,s.sid,s.serial#,

decode(l.locked_mode,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') lock_type,

o.object_name, o.object_type, s.logon_time

from v$locked_object l,v$session s , dba_objects o

where l.session_id=s.sid

       and l.object_id=o.object_id

order by s.logon_time;

或

select l.oracle_username o_name, l.session_id sid,
    decode(l.locked_mode,0,'NONE',1,'NULL',2,'RS',3,'RX',4,'S',5,'RSX',6,'X') lock_type,
    o.object_name, o.object_type,l.xidusn, l.xidslot, l.xidsqn
from v$locked_object l, all_objects o
where l.object_id=o.object_id;

order by sid;

1)       如何查找引起锁的SQL语句？

①       查询是否存在正在等待获得表锁的进程：

select count（*） from v$lock where  lmode=0;

②       如果存在等待锁，则再查询等待获得表锁的所有进程信息：

select sid,id1,id2,lmode,request,type from v$lock

where id1 in (select id1 from v$lock where lmode = 0)

order by id1,request;

③       查询进程执行的语句对应的Hash值：

select hash_value,sql_text from v$sqlarea where hash_value in (0,291585936);

④       根据语句对应的Hash(hash_value)值查询具体的语句：

select l.oracle_username,

                      l.os_user_name,

                      l.SESSION_ID,

                      l.OBJECT_ID,

                      o.owner,

                      o.object_name,

                      o.object_type

from v$locked_object l,dba_objects o

where l.object_id = o.object_id

order by o.object_id,1 desc;

 

2)       如何杀死锁？

①       先查找锁的信息

select t2.username,t2.sid,t2.serial#,t2.logon_time

from v$locked_object t1,v$session t2

where t1.session_id=t2.sid order by t2.logon_time;

②       杀死锁进程

--格式为：alter system kill session 'sid,serial#';

alter system kill session '146,21177';

 

6 死锁的产生与避免

6.1 定义

指两个或更多用户都在等待对方持有的锁定资源而形成的局面。

6.2 避免死锁的方法

1)        不同的程序员应该按照一定的顺序对不同的表依次加锁；

2)        首先申请高强度的锁，再申请低强度的锁；

3)        只要在使用完该行后即时的释放即可，比如在更新语句后加判断后提交或回滚

6.3 死锁示例

Ø  Sess#1：

select * from scott.dept where deptno=10 for update;

Ø  Sess#2：

select * from scott.dept where deptno=20 for update;

Ø  Sess#1：

select * from scott.dept where deptno=20 for update;

Ø  Sess#2：

select * from scott.dept where deptno=10 for update;

6.4 释放锁的条件

1)     执行COMMIT或ROLLBACK语句

2)     退出数据库（LOG OFF）

3)     程序停止运行

7.5 如何禁止表锁/加锁

alter table emp disable table lock;

select table_name, table_lock from user_tables;

Ø  如何加锁：

alter table emp enable table lock;

Lock table emp in share mode;

select table_name, table_lock from user_tables;