TX锁最常见的有以下4种情况：
- 由于行被活动事务锁住导致的等待。
- 由于唯一约束或主键约束导致的等待。
- 由于数据块ITL槽不足导致的等待。
- 由于行包含在同一个位图索引片段中导致的等待。
在LDY用户下创建一张表，插入几条数据：
CREATE TABLE tx_eg (id number,name varchar2(10),sex varchar2(10)) INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES (1,'liaowh','MALE');
INSERT into tx_eg VALUES (2,'liaody','MALE');
INSERT into tx_eg VALUES (3,'wanghh','MALE');
INSERT into tx_eg VALUES (4,'zhangxx','FEMALE');
INSERT into tx_eg VALUES (5,'linjx','MALE');
COMMIT;
1.由于行被活动事务锁住导致的等待：
两个session同时update同一条记录。
Ses#1: update tx_eg set name='yangkq' where id=5;
Ses#2: update tx_eg set name='yangkq' where id=5;
DBA用户查看：
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
144 TX 327692 8218 0 6
146 TX 327692 8218 6 0
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
-------------- ---------- ---------- ---------- ---------- --
Holder: 146 327692 8218 6 0 TX
Waiter: 144 327692 8218 0 6 TX
可以看出SID 144等待SID 146。SID 146持有1个排他锁(LMODE=6)，而SID 144同样请求以排他形式获得该锁(REQUEST=6)。
由enqueue的概念可知，通过TYPE-ID1-ID2标识一个锁，所以这两个锁是同一个锁。
可以通过查询V$SESSION视图，找出具体对象的具体行。
通过以下四个字段：
ROW_WAIT_OBJ# NUMBER
ROW_WAIT_FILE# NUMBER
ROW_WAIT_BLOCK# NUMBER
ROW_WAIT_ROW# NUMBER
DBA用户查看：
select ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#
from v$session
where sid=144;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
54398 14 102391 4
SID 144等待的对象号是54398，所在文件是FILE 14，等待的行所在的块号是102391，行号是4。
通过对象号，或是文件号+块号，都可以查找到该对象：
select owner,object_name,object_type from dba_objects where object_id=54398;
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- --------------------
LDY TX_EG TABLE
select segment_name, segment_type, owner
from dba_extents
where file_id = 14
and 102391 between block_id
and block_id + blocks -1;
SEGMENT_NAME SEGMENT_TYPE OWNER
---------------- ------------------ ----------
TX_EG TABLE LDY
而ROW_WAIT_ROW#表示ROWID中的row number，可以通过dbms_rowid.rowid_row_number(rowid)获得该行的row number。
可以查找到具体行的内容：
select * from ldy.tx_eg where DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)=4;
ID NAME SEX
---------- ---------- ----------
5 linjx MALE
这时session2由于等待而被挂起，需要等待session commit、rollback或转变问为一个in-doubt状态，才能继续。
2.由于唯一约束或主键约束导致的等待：
如果一张表存在主键约束，唯一性约束或唯一索引，该列就会因为唯一索引的约束强制该列的唯一性。如果两个session需要插入同一个键值，那么第二个session将会等待或收到ORA-0001错误。
为tx_eg表增加一个主键约束：
alter table tx_eg add constraint tx_eg_pk primary key(id);
两个session同时insert具有相同ID的记录。
Ses#1: insert into tx_eg values (10,'Xiongwh','MALE');
Ses#2: insert into tx_eg values (10,'Zengx',null);
DBA用户查看：
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
144 TX 393258 7967 0 4
144 TX 65576 6264 6 0
146 TX 393258 7967 6 0
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
------------- ---------- ---------- ---------- ---------- --
Holder: 146 393258 7967 6 0 TX
Waiter: 144 393258 7967 0 4 TX
可以看出SID 144等待SID 146。SID 146持有1个排他锁(LMODE=6)，而SID 144请求以共享模式获得该锁(REQUEST=4)。
除此之外，SID 144自身还持有1个排他锁(LMODE=6)。
这时session2由于等待而被挂起，需要等待session commit、rollback或转变问为一个in-doubt状态，才能继续。
如果session1 commit，session2 则收到一个错误：ORA-00001: 违反唯一约束条件 (LDY.TX_EG_PK)。
如果session1 rollback，session2则可以顺利insert。
如果insert不同的ID记录或没有唯一性约束，则只有有2个TX排他锁，不存在阻塞。
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
144 TX 458752 6034 6 0
146 TX 589845 7585 6 0
3.由于数据块ITL槽不足导致的等待：
Oracle在每个数据块的顶部区域中的'interested transaction list'(ITL)保存被事务锁定的行。一个对象中的任何一个块的ITL槽的数量由参数INITRANS和参数MAXTRANS约束。INITRANS表示数据块在被创建时初始化的ITL数量。MAXTRANS表示ITL可以达到的最大数目。
MAXTRANS设置了在某一时间内，在数据块可以并发的事务数的上限。
INITRANS指定了数据块最小并发。
CREATE TABLE MT (id number(10),name varchar2(20)) INITRANS 1 MAXTRANS 1;
insert into MT values (1,'First');
insert into MT values (2,'Second');
insert into MT values (3,'Third');
SQL> select owner, INI_TRANS,max_TRANS from dba_tables where table_name='MT';
OWNER INI_TRANS MAX_TRANS
--------------- ---------- ----------
SCOTT 1 1
Ses#1: update MT set name='Big' where ID=1;
Ses#2: update MT set name='Small' where ID=2;
Ses#3: update MT set name='Middle' where ID=3;
DBA用户查看:
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
9 TX 327716 328 6 0
13 TX 393243 331 6 0
14 TX 327716 328 0 4
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
--------------------- ---------- ---------- ---------- ---------- --
Holder: 9 327716 328 6 0 TX
Waiter: 14 327716 328 0 4 TX
从dump文件上看，尽管设置了MAXTRANS为1，但是每个数据块都至少有2个ITL事务槽，所以需要有3个session时，第3个session会被hang住。
下面是dump的部分截取，可以看到2个事务槽都满了。
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.002.0000012b 0x00800437.0052.57 ---- 1 fsc 0x0001.00000000
0x02 0x0001.029.000000bc 0x00800180.0036.01 ---- 1 fsc 0x0002.00000000
从Oracle 9.2开始，可以通过v$segment_statistics视图检查ITL等待。
查询语句如下：
set line 999
col OWNER for a20
col OBJECT_NAME for a30
col STATISTIC_NAME for a10
SELECT t.OWNER, t.OBJECT_NAME, t.OBJECT_TYPE, t.STATISTIC_NAME, t.VALUE
FROM v$segment_statistics t
WHERE t.STATISTIC_NAME = 'ITL waits'
AND t.VALUE > 0
ORDER BY VALUE DESC;
如有发现，可以适量增大INITTRANS和MAXTRANS。
关于ITL等待，需要之前的session1或session2处于决断状态(commit/rollback)之后才会统计ITL等待。
OWNER OBJECT_NAME OBJECT_TYPE STATISTIC_ VALUE
------------ ----------------- ------------------ ---------- ---------
SCOTT MT TABLE ITL waits 1
从10g开始表的MAXTRANS参数不能再设置，自动设置为255。从而不会导致ITL槽不足的而hang住的情况发生，但仍会等待分配ITL。
4.由于行包含在同一个位图索引片段中导致的等待:
位图索引的索引键值包含一个范围的ROWID，所以位图索引中的每个条目(entry)可能包含实际表中的许多行。如果两个session想要更新的行包含在同一个位图索引片段中，那么第二个session需要等待第一个session COMMIT或ROLLBACK，在此期间等待获得mode 4的TX锁。
在tx_eg表的sex字段上建立一个位图索引：
CREATE Bitmap Index tx_eg_bitmap on tx_eg ( sex );
2个session分别update 2条不同的记录，但是这2条记录的sex字段的内容都是MALE。
Ses#1: MALE->FEMALE
Ses#2: MALE->FEMALE
Ses#1: update tx_eg set sex='FEMALE' where id=2;
Ses#2: update tx_eg set sex='FEMALE' where id=3;
DBA用户查看：
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';
SID TY ID1 ID2 LMODE REQUEST
---------- -- ---------- ---------- ---------- ----------
144 TX 327719 8220 0 4
144 TX 589864 7586 6 0
146 TX 327719 8220 6 0
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request;
SESS ID1 ID2 LMODE REQUEST TY
------------- ---------- ---------- ---------- ---------- --
Holder: 146 327719 8220 6 0 TX
Waiter: 144 327719 8220 0 4 TX
可以看出SID 144等待SID 146。SID 146持有1个排他锁(LMODE=6)，而SID 144请求以共享模式获得该锁(REQUEST=4)。
除此之外，SID 144自身还持有1个排他锁(LMODE=6)。
这种情况很类似具有唯一索引的情况。
同样查看SID 144正在等待的对象：
DBA用户查看：
select ROW_WAIT_OBJ#,
ROW_WAIT_FILE#,
ROW_WAIT_BLOCK#,
ROW_WAIT_ROW#
from v$session
where sid=144;
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
------------- -------------- --------------- -------------
54482 0 0 0
SID 144等待的对象号是54482，但没有其他信息，通过对象号查找到等待是该表上的位图索引：
select owner,object_name,object_type from dba_objects where object_id=54482;
OWNER OBJECT_NAME OBJECT_TYPE
---------- -------------------- --------------------
LDY TX_EG_BITMAP INDEX
考虑到是否位图索引只锁住一部分，于是增加做了以下4个实验：
SQL> select * from tx_eg;
ID NAME SEX
---------- ---------- ----------
10 Xiongwh MALE
1 liaowh MALE
2 liaody MALE
3 wanghh MALE
4 zhangxx FEMALE
5 linjx MALE
实验1：
Ses#1: FEMALE->MALE
Ses#2: MALE->FEMALE
Ses#1: update tx_eg set sex='MALE' where id=4;
Ses#2: update tx_eg set sex='FEMALE' where id=3;
实验1结果：需要等待。
实验2：
Ses#1: MALE->MALE
Ses#2: MALE->FEMALE
Ses#1: update tx_eg set sex='MALE' where id=2;
Ses#2: update tx_eg set sex='FEMALE' where id=3;
实验2结果：不会等待，2个session都可以update。
实验3：
Ses#1: MALE->FEMALE
Ses#2: MALE->MALE
Ses#1: update tx_eg set sex='FEMALE' where id=2;
Ses#2: update tx_eg set sex='MALE' where id=3;
实验3结果：不会等待，2个session都可以update。
实验4：
Ses#1: MALE->FEMALE
Ses#2: FEMALE->FEMALE
Ses#1: update tx_eg set sex='FEMALE' where id=3;
Ses#2: update tx_eg set sex='FEMALE' where id=4;
实验4结果：不会等待，2个session都可以update。
实验结论：只要另一个session需要修改位图索引，则将位图索引的一个位图段锁住，另一个session想要修改位图索引的相同位图段，就必须等待。
