分类： Oracle
2008-04-11 10:14:06
| 来源：赛迪网 作者：Alice |
|
3.2.1 showlock.sql
第一个脚本 showlock.sql，该脚本通过连接 v$locked_object 与 all_objects 两视图，显示
哪些对象被哪些会话锁住：
/* showlock.sql */
column o_name format a10
column lock_type format a20
column object_name format a15
select rpad(oracle_username,10) o_name,session_id sid,
decode(locked_mode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type,
object_name ,xidusn,xidslot,xidsqn
from v$locked_object,all_objects
where v$locked_object.object_id=all_objects.object_id;
3.2.2 showalllock.sql
第二个脚本showalllock.sql，该脚本主要显示当前所有 TM、TX锁的信息；
/* showalllock.sql */
select sid,type,id1,id2,
decode(lmode,0,'None',1,'Null',2,'Row share',
3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive')
lock_type,request,ctime,block
from v$lock
where TYPE IN('TX','TM');
4.Oracle多粒度封锁机制示例
以下示例均运行在Oracle 8.1.7上，数据库版本不同，其输出结果也可能有所不同。首
先建立3个会话，其中两个（以下用 SESS#1、SESS#2 表示）以SCOTT用户连入数据库，以操作 Oracle 提供的示例表（DEPT、EMP）；另一个（以下用 SESS#3 表示）以 SYS 用户
连入数据库，用于监控；
4.1 操作同一行数据引发的锁阻塞
SESS#1：
SQL> select * from dept for update;
DEPTNO DNAME LOC
---------- -------------- -------------
10 account 70
20 research 8
30 sales 8
40 operations 8
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 17 Row share DEPT 8 2 5861
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
17 TX 524290 5861 Exclusive 0 761 0
17 TM 32970 0 Row share 0 761 0
如第一个脚本showlock 所示，执行完SELECT…FOR UPDATE 语句后， SESS#1（SID
为 17）在 DEPT 表上获得 Row share 锁；如第二个脚本 showalllock 所示，SESS#1 获得的
TX锁为Exclusive，这些都验证了上面的理论分析。另外，我们可以将TX锁的 ID1按如下
方法进行分解：
SQL> select trunc(524290/65536) xidusn,mod(524290,65536) xidslot from dual;
XIDUSN XIDSLOT
------ -------
8 2
分解结果与第一个脚本直接查出来的XIDUSN与XIDSLOT相同，而TX锁的ID2 （5861）
与XIDSQN相同，可见当LOCK TYPE 为TX 时，ID1 实际上是该事务所占用的回滚段段号
与事务表中的槽（SLOT）号的组合，ID2 即为该槽被重用的次数，而这三个值实际上可以
唯一地标识一个事务，即TRANSACTION ID，这三个值从系统表 v$transaction 中也可查到。
另外， DEPT 表中有 4 条记录被锁定，但 TX 锁只有 1 个，这也与上面的理论分析一
致。继续进行操作：
SESS#2：
SQL> update dept set loc=loc where deptno=20;
该更新语句被阻塞，此时再查看系统的锁情况：
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN ---------- ----- --------------- --------------- ------ ------- ------
SCOTT 17 Row share DEPT 8 2 5861
SCOTT 19 Row Exclusive DEPT 0 0 0
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
17 TX 524290 5861 Exclusive 0 3462 1
17 TM 32970 0 Row share 0 3462 0
19 TM 32970 0 Row Exclusive 0 7 0
19 TX 524290 5861 None 6 7 0
在 DEPT 表上除了 SESS#1（SID 为 17）持有Row share 锁外，又增加了 SESS#2（SID
为19）持有的Row Exclusive 锁，但还没有为SESS#2 分配回滚段（XIDUSN、XIDSLOT、
XIDSQN 的值均为 0）；而从第二个脚本看到，SESS#2 的 TX 锁的 LOCK_TYPE 为 None，
其申请的锁类型（REQUEST）为 6（即 Exclusive），而其 ID1、ID2 的值与 SESS#1 所持有
的 TX 锁的 ID1、ID2 相同，SESS#1 的 TX 锁的阻塞域（BLOCK）为 1，这就说明了由于
SESS#1 持有的 TX 锁，阻塞了 SESS#2 的更新操作（SESS#2 所更新的行与SESS#1 所锁定
的行相冲突）。还可以看出，SESS#2 先申请表级的 TM锁，后申请行（事务）级的TX 锁，
这也与前面的理论分析一致。
下面，将SESS#1的事务进行回滚，解除对SESS#2 的阻塞，再对系统进行监控。
SESS#3：
SQL> @showlock
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ----- --------------- --------------- ------ ------- ------
SCOTT 19 Row Exclusive DEPT 2 10 5803
SQL> @showalllock
SID TY ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
----- -- ---------- ---------- --------------- ---------- ---------- ----------
19 TX 131082 5803 Exclusive 0 157 0
19 TM 32970 0 Row Exclusive 0 333 0
可以看到，SESS#1 的事务所持有的锁已经释放，系统为SESS#2的事务分配了回滚段，
而其TX 锁也已经获得，并且 ID1、ID2是其真正的 Transaction ID。再将会话 2 的事务进行
回滚。
SESS#2：
SQL> rollback;
Rollback complete.
检查系统锁的情况：
SESS#3：
SQL> @showlock
no rows selected
SQL> @showalllock
no rows selected
可以看到，TM与TX锁已全部被释放。 4.2 实体完整性引发的锁阻塞
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锁的释放。 |
