transaction缩写为什么是tx_TX Transaction locks常见的4种情况

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想要修改位图索引的相同位图段，就必须等待。