Oracle的table lock

 

先来一段Oracle Concepts里的话：

A table lock can be held in any of several modes: row share (RS), row exclusive (RX), share (S), share row exclusive (SRX), and exclusive (X). The restrictiveness of a table lock's mode determines the modes in which other table locks on the same table can be obtained and held. 

 

 

 

                                                                                  

                                                    Lock Modes Permitted?

SQL Statement	Mode of Table Lock	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 table FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	N
LOCK TABLE table IN ROW SHARE MODE	RS	Y	Y	Y	Y	N
LOCK TABLE table IN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N
LOCK TABLE table IN SHARE MODE	S	Y	N	Y	N	N
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N
LOCK TABLE table IN EXCLUSIVE MODE	X	N	N	N	N	N

RS: row share    RX: row exclusive    S: share    SRX: share row exclusive    X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.

 

难怪在如下所示的例子中，在一个会话中用select … for update之后，另一个会话中的lock table in share mode语句会被挂起：

scott@ORA10G> select * from test for update;

A
----------
bbb
ccc

scott@ORA10G> lock table test in share mode;

必须commit释放锁之后，第二个会话才能锁定表。因为根据上表的结果，*Yes表示第二个锁需要的资源与第一个锁的资源有冲突，因此需要等待。

scott@ORA10G> commit;

提交完成。

表已锁定。

而下面这种就互不影响锁定了：

scott@ORA10G> select * from test where a='bbb' for update;

A
----------
bbb

scott@ORA10G> select * from test where a='ccc' for update;

A
----------
ccc

或者是这样：

scott@ORA10G> lock table scott.test in share mode;

表已锁定。

scott@ORA10G> lock table scott.test in share mode;

表已锁定。

042里就有一道类似的考题，当时只知道正确答案却不知道为什么，现在终于明白了。。。

ps: Oracle Concepts真的很值得好好读几遍啊