关于Share Row Exclusive Table Locks，SELECT with the FOR UPDATE clause

武汉-SSH

Share Row Exclusive Table Locks (SRX)A share row exclusive table lock (also sometimes called a share-subexclusive table lock, SSX) is more restrictive than a share table lock. A share row exclusive table lock is acquired for a table as follows:
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; Permitted Operations: Only one transaction at a time can acquire a share row exclusive table lock on a given table. A share row exclusive table lock held by a transaction allows other transactions to query or lock specific rows using SELECT with the FOR
UPDATE clause, but not to update the table.
Prohibited Operations: A share row exclusive table lock held by a transaction prevents other transactions from obtaining row exclusive table locks and modifying the same table. A share row exclusive table lock also prohibits other transactions from obtaining share, share row exclusive, and exclusive table locks, which prevents other transactions from executing the following statements:
LOCK TABLE table IN SHARE MODE; LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE; LOCK TABLE table IN ROW EXCLUSIVE MODE; LOCK TABLE table IN EXCLUSIVE MODE;


能否给个例子？

武汉-SSH

11G 的 Concepts 靠谱一点。。。。

Share Row Exclusive Table Lock (SRX)

This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.

Maclean Liu(刘相兵

参考 http://www.oracledatabase12g.com ... acle-lock-mode.html

武汉-SSH

哦，原来如此，怪不得我自己没试出来啊。。。。。

Maclean Liu(刘相兵

11.2
http://docs.oracle.com/cd/E14072_01/server.112/e10592/ap_locks001.htm

A share row exclusive table lock (SRX), also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query or lock specific rows using SELECT ... FOR UPDATE, but not to update the table.

官方文档也有错啊。

武汉-SSH

http://docs.oracle.com/cd/B19306 ... sist.htm#sthref2063

Maclean Liu(刘相兵

这个看的是 什么版本的文档？


SHARE ROW EXCLUSIVE MODE;   SSX/SRX


SELECT with the FOR UPDATE clause:

Until 9.2.0.5/6 "select for update"    =>  Sub-share (SS)  Row-share (RS)
"select for update" from 9.2.0.5/6     =>  Sub-exclusive(SX)   Row-exclusive(RX)


SSX, SRX is compatible with  SS,RS