全部博文（323）
分类： Oracle
2008-05-27 14:52:02
DEPT（部门）表有如下字段DEPTNO（部门编号），DNAME（部门名称），LOC（部门位置）；其中DEPTNO列为主键。
SESS#1
SQL> INSERT INTO DEPT(DEPTNO) VALUES(50);
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 6 88 29
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 393304 29 Exclusive 0 6 0
7 TM 3574 0 Row Exclusive 0 6 0
向DEPT表中插入一条DEPTNO为50的记录后，SESS#1（SID为7）在DEPT表上获得Row Exclusive锁，并且由于进行了数据插入，该事务被分配了回滚段，获得TX锁。
SESS#2
INSERT INTO DEPT(DEPTNO) VALUES(50);
这时，SESS#2（SID为8）也向DEPT表中插入一条DEPTNO为50的记录，该语句被阻塞，检查锁情况：
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Row Exclusive DEPT 7 75 30
SCOTT 7 Row Exclusive DEPT 6 88 29
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 393304 29 Exclusive 0 92 1
7 TM 3574 0 Row Exclusive 0 92 0
8 TX 458827 30 Exclusive 0 22 0
8 TM 3574 0 Row Exclusive 0 22 0
8 TX 393304 29 None 4 22 0
SESS#2在DEPT表上也获得了Row Exclusive锁，同样也获得了回滚段的分配，得到TX锁，但是由于其插入的记录与SESS#1插入的记录的DEPTNO均为50，该语句成功与否取决于SESS#1的事务是提交还是回滚，所以SESS#2被阻塞，表现为SESS#2以Share方式（REQUEST=4）等待SESS#1所持有的TX锁的释放。
这时，如果SESS#1进行回滚：
SESS#1
SQL> ROLLBACK;
Rollback complete.
SESS#2
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Row Exclusive DEPT 7 75 30
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
8 TX 458827 30 Exclusive 0 136 0
8 TM 3574 0 Row Exclusive 0 136 0
SESS#2的阻塞将被解除，SESS#2只持有原先已有的TM与TX锁，其等待的TX锁（由SESS#1持有）也消失了。
如果SESS#1提交而不是回滚，在SESS#2上将会出现如下提示：
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_DEPT) violated错误。
即发生主键冲突，SESS#1与SESS#2的所有锁资源均被释放。
4.3 参照完整性引发的锁阻塞EMP（员工）表有如下字段：EMPNO（员工编号），ENAME（员工姓名），DEPTNO（员工所在部门编号），其中DEPTNO列为外键，其父表为DEPT。
SESS#1
SQL> insert into dept(deptno) values(60);
1 row created.
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 2 6 33
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 131078 33 Exclusive 0 148 0
7 TM 3574 0 Row Exclusive 0 148 0
SESS#1（SID为7）在DEPT表中先插入一条DEPTNO为60的记录，SESS#1获得了DEPT表上的Row Exclusive锁，及一个TX锁。
SESS#2
insert into emp(empno,deptno) values(2000,60)；
被阻塞
SESS#3
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 2 6 33
SCOTT 8 Row Exclusive EMP 3 20 31
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TX 131078 33 Exclusive 0 228 1
7 TM 3574 0 Row Exclusive 0 228 0
8 TX 196628 31 Exclusive 0 9 0
8 TM 3576 0 Row Exclusive 0 9 0
8 TX 131078 33 None 4 9 0
SESS#2（SID为8）向EMP表中出入一条新记录，该记录DEPT值为60（即SESS#1刚插入，但还未提交的记录的DEPTNO值），SESS#2获得了EMP表上的Row Exclusive锁，另外由于插入记录，还分配了回滚段及一个TX锁，但由于SESS#2的插入语句是否成功取决于SESS#1的事务是否进行提交，所以它被阻塞，表现为SESS#2以Share（REQUEST=4）方式等待SESS#1释放其持有的TX锁。这时SESS#1如果提交，SESS#2的插入也将执行成功，而如果SESS#1回滚，由于不符合参照完整性，SESS#2将报错：
SESS#2
insert into emp(empno,deptno) values(2000,60)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not
Found
SESS#2持有的锁也被全部释放。
4.4 外键未加索引引发的锁阻塞EMP表上的DEPTNO列为外键，但没有在该列上建索引。
SESS#1
SQL> delete emp where 0=1;
0 rows deleted.
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive EMP 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3576 0 Row Exclusive 0 10 0
首先SESS#1（SID为7）做了一个删除操作，但由于条件（0=1）为永假，所以实际上并没有一行被删除，从监控脚本可以看出SESS#1在EMP表上获得Row Exclusive锁，但由于没有实际的行被删除，所以并没有TX锁，也没有为SESS#1分配回滚段。
SESS#2：
SQL> delete dept where 0=1;
该语句虽然也不会删除实际数据，但却被阻塞，查看系统的锁情况：
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 None EMP 0 0 0
SCOTT 7 Row Exclusive EMP 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3576 0 Row Exclusive 0 31 1
8 TM 3576 0 None 4 12 0
SESS#2申请在EMP表上加SHARE锁（REQUEST=4），但该申请被SESS#1阻塞，因为SESS#1已经在EMP表上获得了Row Exclusive锁，与SHARE锁不相容。
下面我们对SESS#1进行回滚后，再进行监控。
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 8 Share EMP 0 0 0
SCOTT 8 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
8 TM 3574 0 Row Exclusive 0 16 0
8 TM 3576 0 Share 0 16 0
SESS#2在EMP表上获得Share锁后，又在DEPT表上获得Row Exclusive锁，由于没有实际的行被修改，SESS#2并没有获得TX锁。
在Oracle8中，如果子表的外键上没有加索引，当在父表上删除记录时，会先在子表上申请获得Share锁，之后再在父表上申请Row Exclusive锁。由于表级Share锁的封锁粒度较大，所以容易引起阻塞，从而造成性能问题。
当在外键上建立索引后，在父表上删除数据将不再对子表上加Share锁，如下所示：
SESS#1：
SQL> create index i_emp_deptno on emp(deptno);
Index created.
SQL> delete dept where 0=1;
0 rows deleted.
SQL>
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 7 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
7 TM 3574 0 Row Exclusive 0 9 0
可以看到，在EMP表DEPTNO列上建立索引后，在DEPT表上执行DELETE操作，不再要求在EMP表上加Share锁，只是在DEPT表上加Row Exclusive锁，封锁的粒度减小，引起阻塞的可能性也减小。
5 Oracle 多粒度封锁机制总结Oracle通过具有意向锁的多粒度封锁机制进行并发控制，保证数据的一致性。其DML锁（数据锁）分为两个层次（粒度）：即表级和行级。通常的DML操作在表级获得的只是意向锁（RS或RX），其真正的封锁粒度还是在行级；另外，在Oracle数据库中，单纯地读数据（SELECT）并不加锁，这些都极大地提高了系统的并发程度。
在支持高并发度的同时，Oracle利用意向锁及数据行上加锁标志位等设计技巧，减小了Oracle维护行级锁的开销，使其在数据库并发控制方面有着明显的优势。
最近，在Oracle专题深入讨论区，对我这篇文章进行了讨论，见http://www.itpub.net/270059.html
现在我想概括总结一下:
在Oracle数据库并发环境下，对于表，既要保护其数据，又要保护其结构（结构信息存储在数据字典中），很自然，针对这两种保护要求，就有了两种类型（Type）的锁：保护数据的锁我们叫数据锁（DML locks或data locks），保护结构的我们叫字典锁（DDL locks或dictionary locks）。
对于数据锁，为了适应不同事务的不同要求，提供两个层次（粒度）的锁，即表级锁（Table Locks ，TM）与行级锁（Row Locks ，TX）。
在这两个层次上，经典的上锁的方式（Mode）有两种，即共享锁（S锁）与排它锁（X锁）。
为了方便地进行锁冲突的检测，希望在表（上）级能够标识行（下）级加锁的情况，这就引入了“意向锁”（intention lock mode）的概念。根据在行级要加S锁或X锁的不同，在表级相应的就有两种意向锁，即：如果事务要在行级获得S锁，它需要首先在表级获得意向共享锁（Intent Share Lock,IS锁）；如果事务要在行级获得X锁，它需要首先在表级获得意向排它锁（Intent Exclusive Lock,IX锁）。这两种意向锁与经典的S锁、X锁组合，还会产生一种新的锁：共享意向排它锁（Share Intent Exclusive Lock,SIX锁），它表示持有该锁的事务在表级加了传统的共享锁（S锁），同时该事务还将对表中的某些行加排它锁（X锁）。
这样，在表级数据锁的加锁方式就有了5种：即S、X、IS、IX、SIX。
在Oracle中，与IS对应的锁叫做Row Share Table Locks (RS)或subshare table lock, SS，它可以通过SELECT ... FOR UPDATE语句获得，但需要注意的是，这个语句在行级获得的也是排它锁，即如果两个SELECT ... FOR UPDATE语句要选定同一行，则后面的事务将被阻塞，这是与上面一般意义的定义有所不同，这时RS锁可以理解为持有该锁的事务将要修改选定的某些行。
在Oracle中，与IX对应的锁叫做Row Exclusive Table Locks (RX)或subexclusive table lock, SX，它可以通过INSERT/UPDATE/DELETE语句获得，它表征持有该锁的事务已经修改了表中的某些行。
在Oracle中，与SIX对应的锁叫做Share Row Exclusive Table Locks (SRX)或sshare-subexclusive table lock, SSX。
这样，Oracle对表级数据锁的加锁方式就有：即S、X、RS、RX、SRX等5种；而在行级只有X锁。
在Oracle中，对于字典锁，也有共享与排它之分：Share DDL Locks与Exclusive DDL Locks。对于要获得Exclusive DDL Locks的DDL操作（如ALTER、DROP等），它还必须要获得该表排它的数据锁，这样如果在某个表上有未提交的事务（即该表有某种类型的表级锁），其它SESSION提出的DROP表的操作就不会成功，因为它要向表施加X锁，与表上已有的锁不相容。
所以说，表级的数据锁既能防止与之冲突的DML操作，也能防止与之冲突的DDL操作，但不能因此就把表级的数据锁与字典锁混为一谈。
