Lock Table In SHARE/EXCLUSIVE Mode

<<写完之后，忽然发现之前的理解不够啊.... Oracle会隐式地给表上加锁的，获取share mode的锁之后，如果更改了该表的数据，这个时候相当于获取了exclusive的锁了，而不再是share lock了... >>

 

 

有这么一道题目：

 

Which of the following lock modes permits concurrent queries on a table but prohibits updates to the locked table?

A. ROW SHARE

B. ROW EXCLUSIVE

C. EXCLUSIVE

D. SHARE ROW EXCLUSIVE

E. SHARE

 

 给的答案是E, 但是我觉得C也可以。官方文档给出的关于EXCLUSIVE 和 SHARE Lock 模式的解释如下，

 

 

Share Lock Mode 

Allows the associated resource to be shared, depending on the operations involved. Multiple users reading data can share the data, holding share locks to prevent concurrent access by a writer (who needs an exclusive lock). Several transactions can acquire share locks on the same resource.

 

Exclusive Lock Mode 

Prevents the associates resource from being shared. This lock mode is obtained to modify data. The first transaction to lock a resource exclusively is the only transaction that can alter the resource until the exclusive lock is released. 

 

我是怎么看也没有看出有啥区别:( 不管是SHARE 还是 Exclusive, 都是支持并发读，不支持并发写嘛！ 有实验为证：

 

 首先通过share mode 锁住表dept

--  Session 1
SQL> show user
USER is "SCOTT"
SQL> lock table dept in share mode;

Table(s) Locked.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

 

在另外一个Session中，尝试：

 

-- Session 2
SQL> show user
USER is "SYS"
SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>

 

 

 可以看到两个session 都是可以访问表dept. 只是第二个session 不能够修改dept中的数据，如下所示

 

-- Session 2
SQL> update scott.dept set loc='test' where deptno=10;

 

 

 

 可以看到Session 2被挂起了。 但是回到Session 1中，却是可以随便更改dept数据的，

 

-- Session 2
SQL> update dept set loc='test' where deptno=10;

1 row updated.

SQL> rollback;

Rollback complete.

SQL>

 

 

最后释放这个lock,可以看到session 2中的update语句可以执行了。

 

现在来尝试下EXCLUSIVE MODE的锁，重复上面的步骤，

 

 -- Session 1

SQL> lock table dept in exclusive mode;

Table(s) Locked.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update dept set loc='test' where deptno=10;

1 row updated.

SQL>

 

 

可以看到在session 1中是可以更改表dept的，现在看看session 2

 

-- session 2
SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL> update scott.dept set loc='test' where deptno=10;

 

 

可以看到Session2是可以读取scott.dept表中的数据的，但是显然是不能更改表中的数据，session2被挂起了。

 

可以看到，无论是SHARE MODE 还是 EXCLUSIVE MODE都是支持并发读，而只能获得锁的那个session可以更改数据。

 

那么这两者到底有啥不同呢？？？ 好让人费解....  

 

 <<获取share lock之后如果更改数据，就自然变成了exclusive mode的lock了， 这个时候其他session也就不能获取share mode的锁了>>

 

后来发现者两者的区别在于， share mode的lock可以被多个session获取，如果多个session 都获取到share lock, 则所有的session都不能对表进行更改操作，只能进行查询，这也许就是SHARE的含义，大家只许看不许摸，谁也没有特权！

而exclusive mode的lock只能被一个session获取，而且其他session也不能对该表加上share 锁来企图阻止获取exclusive 锁的session对表进行更改，这个也是很好理解的嘛，这叫独占式，人家花钱买到的属于自己的东西，你凭啥不让人家碰碰呢！

 

实验如下：

 

-- Session 1
SQL> lock table dept in share mode;

Table(s) Locked.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

 

 

-- Session 2
SQL> lock table scott.dept in share mode;

Table(s) Locked.

SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

 

 

可以看到两个session都能读取表dept中的数据，这个很应该的嘛。但是如果session1 或 session2想要更改数据，会怎么样呢？

 

-- Session 1
SQL> update dept set loc='test' where deptno=10;
update dept set loc='test' where deptno=10
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource


SQL>

 

 

 

-- session 2
SQL> update scott.dept set loc='test' where deptno = 20;

 

 

 可以看到两个session首先都是被挂起的，过了一会，session 1因为数据库检索到deadlock而被强行断开，而session2还是被挂起，因为session 1也拥有表dept的share锁，因此session 2更改不了，这个时候只有session 1释放该锁 （rollback 或 commit)，session 2才会恢复。 

 

 现在来看看EXCLUSIVE MODE的情况： 

 

-- Session1

SQL> lock table dept in exclusive mode;

Table(s) Locked.

SQL>

 

 

-- Session 2
SQL> lock table scott.dept in share mode nowait;
lock table scott.dept in share mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL>

 

 

可以看到session 2不能对表dept加上share 锁，因为session 1已经获取了exclusive 锁。

 

 

 Oracle 还提供其他三种类型的lock -- ROW SHARE / ROW EXCLUSIVE / SHARE ROW EXCLUSIVE

ROW SHARE 和 ROW EXCLUSIVE很好理解，就是SHARE 和 EXCLUSIVE的行级别的类比。 最奇怪的就是这个SHARE ROW EXCLUSIVE, 是在很难理解。

 

 SHARE ROW EXCLUSIVE 比 SHARE MODE 限制更强一些，它只允许其他的session查询或者用select for update来锁定记录，但是不允许其他session获取SHARE mode的锁，更不要提更改数据了。而SHARE mode，允许其他session 或许share mode的锁，也可以通过select for update 来锁定记录。

 

 测试如下：

 

-- session 1

SQL> lock table dept in share row exclusive mode;

Table(s) Locked.

SQL>

 

 

 

-- session 2
SQL> lock table scott.dept in share mode nowait;
lock table scott.dept in share mode nowait
                 *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL>

 

 

 可以看到session 2是不能够获取到share mode的锁的，更不要提SHARE ROW EXCLUSIVE 的锁了。但是允许session 2以select for update 的模式锁定记录，但是会被挂起，如下

 

-- session 2
SQL> select * from scott.dept for update;

 

 

 总结下，以下是五种锁模式的强弱其别：

 

 EXCLUSIVE > ROW SHARE EXCLUSIVE > SHARE > ROW EXCLUSIVE > ROW SHARE