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