Insert hold a RX Table Lock The following guides should be used in interpreting the above: Row-share lock (RS) Least restrictive of the table locks; also known as a subshare lock (SS). Allows other transactions to insert, update, delete, or lock other rows in the same table. Prevents a LOCK TABLE...IN EXCLUSIVE MODE operation by another transaction. Row-exclusive lock (RX) Almost identical to a row-share lock but further limits the types of lock operations that other transactions can use; also known as a subexclusive table lock (SX). Allows insert, update, delete, and lock of other rows. Prohibits LOCK TABLE commands with the following options: IN SHARE MODE, IN SHARE EXCLUSIVE MODE, and IN EXCLUSIVE MODE. Share lock (S) Explicitly acquired through use of the LOCK TABLE...IN SHARE MODE command. Allows query, SELECT...FOR UPDATE, or other LOCK TABLE...IN SHARE MODE operations. However, it restricts updates from transactions other than the one holding the share lock. If multiple transactions hold the share lock, no transaction can update the table. The following are prohibited operations in tables that have a share lock:
UPDATES LOCK TABLE...IN SHARE ROW EXCLUSIVE MODE IN EXCLUSIVE MODE IN ROW EXCLUSIVE MODE Share-row-exclusive lock (SRX) More restrictive than a share lock; also known as a share-subexclusive table lock (SSX). Explicitly acquired through the LOCK TABLE...IN SHARE ROW EXCLUSIVE MODE command. Only one transaction can acquire an SRX lock on a specific table at any time. Other transactions can query the table or issue SELECT...FOR UPDATE commands, but cannot update data until the lock is released. Prohibits almost all other locking, specifically the following lock operations:LOCK TABLE...IN SHARE MODE IN SHARE ROW EXCLUSIVE MODE IN ROW EXCLUSIVE MODE IN EXCLUSIVE MODEw Exclusive lock (X) The most restrictive of locks. Only a single transaction can lock a table in X mode. A table locked in X mode can be queried only by other transactions; all other operations are prohibited until the X lock is lifted.
UPDATES
LOCK TABLE...IN SHARE ROW EXCLUSIVE MODE
IN EXCLUSIVE MODE
IN ROW EXCLUSIVE MODE Share-row-exclusive lock (SRX) More restrictive than a share lock; also known as a share-subexclusive table lock (SSX). Explicitly acquired through the LOCK TABLE...IN SHARE ROW EXCLUSIVE MODE command. Only one transaction can acquire an SRX lock on a specific table at any time. Other transactions can query the table or issue SELECT...FOR UPDATE commands, but cannot update data until the lock is released. Prohibits almost all other locking, specifically the following lock operations:LOCK TABLE...IN SHARE MODE
IN SHARE ROW EXCLUSIVE MODE
IN ROW EXCLUSIVE MODE
IN EXCLUSIVE MODEw Exclusive lock (X) The most restrictive of locks. Only a single transaction can lock a table in X mode. A table locked in X mode can be queried only by other transactions; all other operations are prohibited until the X lock is lifted.
对于更新操作,ORACLE不仅可以作到行锁,同时还可以作到列锁。比如ORACLE允许多个连接同时修改一个表中同一条记录的不同的列。