Oracle 加在表上的锁类型

下表是在Oracle文档里里面列出的操作会对表加上什么类型的lock，以及该锁和其他类型的操作的兼容性。

Table 13-3 Summary of Table Locks

SQL Statement	Mode of Table Lock	Lock Modes Permitted?
		RS	RX	S	SRX	X
SELECT...FROM table...	none	Y	Y	Y	Y	Y
INSERT INTO table...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table...	RX	Y*	Y*	N	N	N
SELECT ... FROM tableFOR UPDATE OF ...	RS（自己测试为RX）	Y*	Y*	Y*	Y*	N
LOCK TABLE tableIN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE tableIN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE tableIN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE tableIN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE tableIN EXCLUSIVE MODE	X	N	N	N	N	N

与v$lock里面LMODE字段对应关系为：

none：0

RS: row share ：2

RX: row exclusive：3

S: share：4

SRX: share row exclusive：5

X: exclusive：6

1，select 操作不会加任何锁。

select * from v$lock where sid=‘当前session的id’ 不会查询到结果。

2，insert into test values(3,'C');在表示加上RX锁。查看v$lock,select * from v$lock where sid=144看到

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32E29C04 32E29D20        144 TX     458760        685          6          0         36          0
32DC4224 32DC423C        144 TM      54148          0          3          0         36          0

type为TM的为在表上加的锁，这里LMODE为3，对应RX，block为0表示没有阻塞别的session。

typeweiTX的表示在行上加的锁，这里LMODE为6，对应X，block为0表示没有阻塞别的session。

如果在两session中同时对同一条记录update，会得到下面的结果：

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32DC4224 32DC423C        144 TM      54148          0          3          0        132          0
32E29C04 32E29D20        144 TX     458760        685          6          0        132          1
32DC42E8 32DC4300        159 TM      54148          0          3          0          6          0
33834450 33834464        159 TX     458760        685          0          6          6          0

SID 144先做，block为1表示阻塞了别的session。SID 159 TX类型的锁LMODE为0，而request为6表示申请X锁，但是没有获得，意味着被阻塞了。

3，lock table test in ROW SHARE MODE;加的是RS锁。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32DC4224 32DC423C        159 TM      54148          0          2          0          6          0

执行select name from test where id=3 for update of name;发现和文档说的不一样，看到别人也遇到同样迷惑：

http://www.itpub.net/thread-1145275-1-1.html ： 经过测试和查询发现，实际上在oracle 8i和9i（早版本）中，select .... for update产生的确实是RS锁。

但是在后来的版本中产生的是RX锁。不过oracle的文档一直到10g都没有更新这一变化。这一点非常坑爹。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32E29C04 32E29D20        144 TX     589869        828          6          0          6          0
32DC4224 32DC423C        144 TM      54148          0          3          0          6          0

4，LOCK TABLE test IN SHARE MODE;发现加的是S锁。另外创建index的时候也会加上这个锁，会阻塞更新操作。

当然如果表上面有更新事务，也是不能建index的。Oracle10G现在可以用create index idx_id_test on test(id) online来创建index而不依赖事务更新的影响。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32DC4224 32DC423C        144 TM      54148          0          4          0          3          0

在另一个session  做update test set name='A' where id=3;会发现申请RX锁被阻塞，同时看到v$lock只有两条记录，

证明该session在申请表级锁被阻塞后，没有继续申请TX的X锁。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32DC4224 32DC423C        144 TM      54148          0          4          0         42          1
32DC42E8 32DC4300        159 TM      54148          0          0          3          3          0

将144 session 提交，看到159 session 的LMODE由0变为3，同时获取了一个TX类型的X锁。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
32E0D0AC 32E0D1C8        159 TX     589842        829          6          0          3          0
32DC42E8 32DC4300        159 TM      54148          0          3          0          3          0

Lock table语句只会加表级别的锁，insert，update，delete，select for update会先加表级别的锁，然后还会加行级别的锁。

DML Statement	Row Locks?	Mode of Table Lock
SELECT ... FROM table
INSERT INTO table...	X	RX
UPDATE table ...	X	RX
DELETE FROM table...	X	RX
SELECT ... FROM table...    FOR UPDATE OF ...	X	RS（自己测试为RX）
LOCK TABLE tableIN ...
ROW SHARE MODE		RS
ROW EXCLUSIVE MODE		RX
SHARE MODE		S
SHARE EXCLUSIVE MODE		SRX
EXCLUSIVE MODE		X

DDL 锁：当DDL操作进行的时候，它参照的表会加上相应的DDL lock，如一个procedure在编译的时候，不能alter或者drop它引用到的表。当alter一个表正在进行的时候不能drop这个表。

下面是一些经常用到的和锁相关的SQL：

查看数据库中session的锁的信息，阻塞了别的进程的block为1，被阻塞的session的id1和id2与造成阻塞的session的id1，id2值相同。

select sid,type,id1,id2,block,
decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
decode(request,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') reqType
from v$lock order by sid,id1,id2

查看在哪些对象上有锁发生，xidusn,xidslot,xidsqn值为0的是被阻塞的session

select rpad(oracle_username,10) o_name,session_id sid,object_name ,xidusn,xidslot,xidsqn,
decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type
from v$locked_object,all_objects where v$locked_object.object_id=all_objects.object_id order by object_name;

查看被阻塞的SQL语句：

select st.sql_text from v$sqltext st,v$session se where st.address = se.SQL_ADDRESS and se.sid=147 （147是被阻塞的session的sid）

得到Kill掉阻塞进程的SQL，执行得到SQL可以kill掉session：

select Distinct 'alter system kill session '||chr(39)||b.sid||','||b.serial#||chr(39)||';'As SQL_KILL, 
b.username,b.logon_time from v$locked_object a,v$session b
where a.session_id=b.sid and a.xidusn!=0 order by b.logon_time

去掉 a.xidusn!=0可以得到kill阻塞的和被阻塞的session的SQL。