TM LOCK MODE

1，在DML操作时，自动获取表上的row exclusive mode（lmod=3）的锁。

2，可以在线创建索引，但无法在线删除索引或修改为UNUSABLE（被dml操作阻塞），报“ORA-00054: resource busy and acquire with NOWAIT specified”；一般通过多次尝试删除。

--可以尝试如下方式删除吗？
lock table ta in row exclusive mode;
drop index idx_ta;
--问题：drop 操作时，首先提交，然后才执行删除操作，由于锁定表的事务结束，删除操作需要再次排队获取TM锁，实际上该会话并不一定能顺利获取锁定。

3，不同锁模式的兼容性：

lockmode:
ROW SHARE(lmod=2)：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(lmod=3)：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(lmod=2)：See ROW SHARE.

SHARE SHARE(lmod=4)：permits concurrent queries but prohibits updates to the locked table.
--允许并发查询，阻止更新

SHARE ROW EXCLUSIVE(lmod=5)：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(lmod=6)：EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
--阻止所有其他锁定

lock table ta in row share mode nowait;
commit;

lock table ta in row exclusive mode nowait;
commit;

lock table ta in share mode nowait;
commit;

lock table ta in share row exclusive mode nowait;
commit;

lock table ta in exclusive mode nowait;
commit;

4,兼容性测试：
4.1会话1：
lock table ta in row share mode;

会话2：
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> lock table ta in row share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.2会话1：
lock table ta in row exclusive mode;

会话2：
SQL>
SQL> lock table ta in row share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
表已锁定。
SQL> commit;
提交完成。

 

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
           *
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
           *
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
           *
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.3会话1：
lock table ta in share mode;

会话2：
SQL> lock table ta in row share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.4会话1：
lock table ta in share row exclusive mode;

会话2：
SQL>
SQL> lock table ta in row share mode nowait;
表已锁定。
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

4.5会话1：
lock table ta in exclusive mode;

会话2：
SQL> lock table ta in row share mode nowait;
lock table ta in row share mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> commit;
提交完成。

SQL>
SQL> lock table ta in row exclusive mode nowait;
lock table ta in row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share mode nowait;
lock table ta in share mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in share row exclusive mode nowait;
lock table ta in share row exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

SQL>
SQL> lock table ta in exclusive mode nowait;
lock table ta in exclusive mode nowait
*
第 1 行出现错误:
ORA-00054: resource busy and acquire with NOWAIT specified
SQL> commit;
提交完成。

 

 

 

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-1069663/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-1069663/