Oracle数据库中的TM锁模式

Oracle数据库支持如下TM锁（表锁）模式:

• Row Share (RS)

  This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.

• Row Exclusive Table Lock (RX)

  This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.

• Share Table Lock (S)

  A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.

• 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.

• Exclusive Table Lock (X)

  This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.

在RAC环境中， GES (global enqueue resources) 对这些锁模式有不同的称呼，对应关系如下：

#define KJUSERNL 0          /* no permissions */    (Null)

#define KJUSERCR 1          /* concurrent read */   (Row-S (SS))

#define KJUSERCW 2          /* concurrent write */  (Row-X (SX))

#define KJUSERPR 3          /* protected read */    (Share)

#define KJUSERPW 4          /* protected write */   (S/Row-X (SSX))

#define KJUSEREX 5          /* exclusive access */  (Exclusive)

这些锁模式的相互兼容性如下：

compatible ?	SS,RS,CR	SX,RX,CW	S,PR	SSX,SRX,PW	X,EX
SS,RS,CR	yes	yes	yes	yes	no
SX,RX,CW	yes	yes	no	no	no
S,PR	yes	no	yes	no	no
SSX, SRX,PW	yes	no	no	no	no
X,EX	no	no	no	no	no

下面列出了一些常见DML语句需要获得的锁模式：

SQL Statement	Row Locks	Table Lock Mode	RS	RX	S	SRX	X
SELECT ... FROM table...	—	none	Y	Y	Y	Y	Y
INSERT INTO table ...	Yes	SX	Y	Y	N	N	N
UPDATE table ...	Yes	SX	Y*	Y*	N	N	N
MERGE INTO table ...	Yes	SX	Y	Y	N	N	N
DELETE FROM table ...	Yes	SX	Y*	Y*	N	N	N
SELECT ... FROM table FOR UPDATE OF ...	Yes	SX	Y*	Y*	N	N	N
LOCK TABLE table IN ...	—
ROW SHARE MODE		SS	Y	Y	Y	Y	N
ROW EXCLUSIVE MODE		SX	Y	Y	N	N	N
SHARE MODE		S	Y	N	Y	N	N
SHARE ROW EXCLUSIVE MODE		SSX	Y	N	N	N	N
EXCLUSIVE MODE		X	N	N	N	N	N
* Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.