标签归档:Data Definition Language

Oracle table lock的几种模式


Oracle中的锁定可以分为几类:DML lock(data lock),DDL lock(dictionary lock)和internal lock/latch。
DML lock又可以分为row lock和table lock。row lock在select.. for update/insert/update/delete时隐式自动产生,而table lock除了隐式产生,也可以调用lock table <table_name> in </table_name> name来显示锁定。

如果不希望别的session lock/insert/update/delete表中任意一行,只允许查询,可以用lock table table_name in exclusive mode。(X)这个锁定模式级别最高,并发度最小。

如果允许别的session查询或用select for update锁定记录,不允许insert/update/delete,可以用lock table table_name in share row exclusive mode。(SRX)

如 果允许别的session查询或select for update以及lock table table_name in share mode,只是不允许insert/update/delete,可以用lock table table_name in share mode。(share mode和share row exclusive mode的区别在于一个是非抢占式的而另一个是抢占式的。进入share row exclusive mode后其他session不能阻止你insert/update/delete,而进入share mode后其他session也同样可以进入share mode,进而阻止你对表的修改。(S)

还有两种锁定模式,row share(RS)和row exclusive(RX)。他们允许的并发操作更多,一般直接用DML语句自动获得,而不用lock语句。

lockmode Clause

Specify one of the following modes:

ROW SHARE
ROW SHARE permits concurrent access to the locked table but prohibits users from locking the entire table for exclusive access. ROW SHARE is synonymous with SHARE UPDATE, which is included for compatibility with earlier versions of Oracle Database.

ROW EXCLUSIVE
ROW EXCLUSIVE is the same as ROW SHARE, but it also prohibits locking in SHARE mode. ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.

SHARE UPDATE
See ROW SHARE.

SHARE
SHARE permits concurrent queries but prohibits updates to the locked table.

SHARE ROW EXCLUSIVE
SHARE ROW EXCLUSIVE is used to look at a whole table and to allow others to look at rows in the table but to prohibit others from locking the table in SHARE mode or from updating rows.

EXCLUSIVE
EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.

Examples
LOCK TABLE employees@remote
IN SHARE MODE;