oracle 锁的工作机制

锁的类型：

0 - none

1 - null (NULL)

2 - row-S (SS)

3 - row-X (SX)

4 - share (S)

5 - S/Row-X (SSX)

6 - exclusive (X)

0 - 没有

1 - 空（NULL）

2 - 行子共享模式（SS）

3 - 行共享互斥模式（SX）

4 - 共享模式（S）

5 - 行子共享互斥模式

6 - 互斥模式（X）

DML 操作引起的锁的类型和描述：

Operation Lock Mode LMODE Lock Description

- -

Select NULL 1 null

Select for update SS 2 sub share

Insert SX 3 sub exclusive

Update SX 3 sub exclusive

Delete SX 3 sub exclusive

Lock For Update SS 2 sub share

Lock Share S 4 share

Lock Exclusive X 6 exclusive

Lock Row Share SS 2 sub share

Lock Row Exclusive SX 3 sub exclusive

Lock Share Row Exclusive SSX 5 share/sub exclusive

Alter table X 6 exclusive

Drop table X 6 exclusive

Create Index S 4 share

Drop Index X 6 exclusive

Truncate table X 6 exclusive

-

锁的兼容性，YES表示锁可 以共同存在，NO表示互斥。

NULL SS SX S SSX X

-

NULL YES YES YES YES YES YES

SSYES YES YES YES YES NO

SXYES YES YES NO NO NO

SYES YES NO YES NO NO

SSXYES YES NO NO NO NO

XYES NO NO NO NO NO

 

LOCK的基本情况：

update, insert ,delete, select ... for update会LOCK相应的ROW 。

只有一个TRANSACTION可以LOCK相应的行，也就是说假如一个ROW已经LOCKED了，那就不能被其他TRANSACTION所 LOCK了。

LOCK由statement产生但却由TRANSACTION结尾（commit，rollback），也就是说一个SQL完成后LOCK还会 存在，只有在COMMIT/ROLLBACK后才会RELEASE。

SELECT
.... 
FOR
 
UPDATE
 [
OF
 cols] [NOWAIT];
OF
 cols
SELECT
 cols 
FROM
 tables [
WHERE
...] 
FOR
 
UPDATE
 [
OF
 cols] [NOWAIT];

SELECT.... FOR UPDATE [OF cols] [NOWAIT];
OF cols
SELECT cols FROM tables [WHERE...] FOR UPDATE [OF cols] [NOWAIT]; 

前面的FOR UPDATE省略，下面我们来讲一下OF。


transaction A运行

select
 a.object_name,a.object_id 
from
 wwm2 a,wwm3 b
2 where
 b.status=
'VALID'
 
and
 a.object_id=b.object_id
3* for
 
update
 
of
 a.status

select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status='VALID' and a.object_id=b.object_id
3* for update of a.status 

则transaction B可以对b表wwm3的相应行进行DML操作,但不能对a表wwm2相应行进行DML操作.

反一下看看。


transaction A运行

select
 a.object_name,a.object_id 
from
 wwm2 a,wwm3 b
2 where
 b.status=
'VALID'
 
and
 a.object_id=b.object_id
3* for
 
update
 
of
 b.status

select a.object_name,a.object_id from wwm2 a,wwm3 b
2 where b.status='VALID' and a.object_id=b.object_id
3* for update of b.status 

则transaction B可以对a表wwm2的相应行进行DML操作,但不能对b表wwm3相应行进行DML操作.

也就是说LOCK的还是行,只是假如不加OF的话会对所有涉及的表LOCK的,加了OF后只会LOCK OF 字句所在的TABLE.

NOWAIT（假如一定要用FOR UPDATE，我更建议加上NOWAIT）

当有LOCK冲突时会提示错误并结束STATEMENT而不是在那里等待.返回错误是"ORA-00054: resource busy and acquire with NOWAIT specified"

另外如下用法也值得推荐，应该酌情考虑使用。


FOR UPDATE WAIT 5

5秒后会出现提示：


ORA-30006: resource busy; acquire with WAIT timeout expired
FOR UPDATE NOWAIT SKIP LOCKED;

出现提示：


no rows selected
TABLE LOCKS
LOCK TABLE table(s) IN EXCLUSIVE MODE [NOWAIT];

同样也是在transaction结束时才会释放lock。

DEADLOCK:


transaction a lock rowA , then transaction b lock rowB
then transaction a tries to lock rowB,
and transaction b tries to lock rowA

也就是说两个transaction都相互试图去lock对方已经lock的ROW，都在等待对方释放自己的lock，这样就使死锁。另 外，deadlock也会有600提示。

 

在现代的多用户多任务系统中，必然会出现多个用户同时访问共享的某个对象，这个对象可能是表，行，或者内存结构，为了解决多个用户并发性访问带来的数据的 安全性，完整性及一致性问题，必须要有一种机制，来使对这些共享资源的并发性访问串行化，oracle中的锁就可以提供这样的功能，当事务[/B] [/U]在对某个对象进行操作前，先向系统发出请求，对其加相应的锁，加锁后该事务就对该数据对象有了一定的控制权限，在该事务释放锁之前，其他的事务不 能对此数据对象进行更新操作（可以做select动作，但select 利用的是undo中的前镜像数据了）.Oracle锁的分类Oracle锁基本上可以分为二类：

a：共享锁（share locks）  也称读锁，s锁

加了共享锁的数据对象可以被其他事务读取，但不能修改。

b：排它锁 (exclusive locks) 也称写锁，x锁

 

按锁保护的内容分类oracle提供多粒度封锁机制，按保护对象来分，据此又可以分为

a：dml锁， data locks 数据锁，用来保护数据的完整性和一致性

b：ddl锁， dictionary locks 字典锁，用来保护数据对象的结构，如table，index的定义

c：内部锁和闩 internal locks and latchs 用来保护数据库内部结构，如sga内存结构

 

在 Oracle数据库中，当一个事务首次发起一个DML语句时就获得一个TX锁，该锁保持到事务被提交或回滚。在数据行上只有X锁（排他锁），就是说TX锁 只能是排他锁，在记录行上设置共享锁没有意义。当两个或多个会话在表的同一条记录上执行DML语句时，第一个会话在该条记录上加锁，其他的会话处于等待状 态。当第一个会话提交后，TX锁被释放，其他会话才可以加锁。

在数据表上，oracle默认是共享锁，在执行dml语句的时 候，oracle会先申请对象上的共享锁，防止其他会话在这个对象上做ddl语句，成功申请表上的共享锁后，再在受影响的记录上加排它所，防止其他会话对 这些做修改动作。

这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。 TM锁包括了SS、SX、S、X等多种模式，在数据库中用0－6来表示。不同的SQL 操 作产生不同类型的TM锁。如表1所示。

和锁相关的性能视图介绍
v$lock
SID 会话的sid，可以和v$session 关联
TYPE 区分该锁保护对象的类型，如tm，tx，rt，mr等
ID1 锁表示1，详细见下说明
ID2 锁表示2，详细见下说明
LMODE 锁模式，见下面说明
REQUEST 申请的锁模式，同lmode
CTIME 已持有或者等待锁的时间
BLOCK 是否阻塞其他会话锁申请 1:阻塞 0:不阻塞

LMODE取值0,1,2,3,4,5,6, 数字越大锁级别越高, 影响的操作越多。
1级锁：
Select，有时会在v$locked_object出现。
2级锁即RS锁
相 应的sql有：Select for update ,Lock xxx in Row Share mode，select for update当对
话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独
占式锁定，其他对象 只能查询这些数据行，不能进行update、delete或select for update
操作。
3级锁即RX锁
相应的 sql有：Insert, Update, Delete, Lock xxx in Row Exclusive mode，没有commit
之 前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们
必须释放掉上一个才能继续工作。
4级锁即S锁
相 应的sql有：Create Index, Lock xxx in Share mode
5级锁即SRX锁
相应的sql有：Lock xxx in Share Row Exclusive mode，当有主外键约束时update
/delete ... ; 可能会产生4,5的锁。
6级锁即X锁
相应的sql有：Alter table, Drop table, Drop Index, Truncate table, Lock xxx in Exclusive
mode

ID1,ID2的取值含义根据type的取 值而有所不同
对于TM 锁
ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0
对于TX 锁
ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号,其组形式:
0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER
ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数

v$locked_object
XIDUSN undo segment number ， 可以和v$transaction关联
XIDSLOT undo slot number
XIDSQN 序列号
OBJECT_ID 被锁定对象的object_id ， 可以和dba_objects关联
SESSION_ID 持有该锁的session_id， 可以和v$session关联
ORACLE_USERNAME 持有该锁的oracle帐号
OS_USER_NAME 持有该锁的操作系统帐号
PROCESS 操作系统的进程号，可以和v$process关联
LOCKED_MODE 锁模式，含义同v$lock.lmode

Dba_locks 和v$lock 内容差不多,略

V$session 如果某个session被因为某些行被其他会话锁定而阻塞，则该视图中的下面四个字段列出了这些行所属对象的相关信息
ROW_WAIT_FILE# 等待的行所在的文件号
ROW_WAIT_OBJ# 等待的行所属的object_id
ROW_WAIT_BLOCK# 等待的行所属的block
ROW_WAIT_ROW# 等待的行在blcok中的位置

手工释放锁
alter system kill session 'sid,serial#';