具有位图索引的表更新产生lock 竞争分析

文档上关于 Bitmap indexes 的引起的lock竞争：
Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each 'entry' in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.

创建表：
create table t3 as select * from all_objects
where rownum<30;
SQL> select * from t3;

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ----------------
SYS ICOL$ 20 2 TABLE
SYS I_USER1 44 44 INDEX
SYS CON$ 28 28 TABLE
SYS UNDO$ 15 15 TABLE
SYS C_COBJ# 29 29 CLUSTER
SYS I_OBJ# 3 3 INDEX
SYS PROXY_ROLE_DATA$ 25 25 TABLE
SYS I_IND1 39 39 INDEX
SYS I_CDEF2 51 51 INDEX
SYS I_PROXY_ROLE_DATA$_1 26 26 INDEX
SYS FILE$ 17 17 TABLE

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ----------------
SYS UET$ 13 8 TABLE
SYS I_FILE#_BLOCK# 9 9 INDEX
SYS I_FILE1 41 41 INDEX
SYS I_CON1 48 48 INDEX
SYS I_OBJ3 38 38 INDEX
SYS I_TS# 7 7 INDEX
SYS I_CDEF4 53 53 INDEX
SYS IND$ 19 2 TABLE
SYS SEG$ 14 8 TABLE
SYS C_TS# 6 6 CLUSTER
SYS I_FILE2 42 42 INDEX

OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ------------------------------ ------------------------------ ---------- -------------- ----------------
SYS COL$ 21 2 TABLE
SYS I_TS1 43 43 INDEX
SYS I_UNDO2 35 35 INDEX
SYS CLU$ 5 2 TABLE
SYS PROXY_DATA$ 23 23 TABLE
SYS I_PROXY_DATA$ 24 24 INDEX
SYS I_OBJ1 36 36 INDEX
建位图索引：
SQL> create bitmap index ind_b_t3 on t3(object_type);

Index created.

SQL> analyze table t3 compute statistics;

Table analyzed.

SQL> analyze index ind_b_t3 compute statistics;

Index analyzed.

session 1更新操作：
SQL> update t3 set OBJECT_TYPE='YYYY'
2 where OBJECT_ID=21;
1 row updated.
session 2更新操作：
update t3 set OBJECT_TYPE='YYYY'
2 where OBJECT_ID=23;

此时会话挂起，正是以上那段话所说的。
SQL> select sid,event from v$session_wait;

SID EVENT
---------- ----------------------------------------------------------------
145 jobq slave wait
146 SQL*Net message from client
147 enq: TX - row lock contention
150 Streams AQ: qmn slave idle wait
152 Streams AQ: waiting for time management or cleanup tasks
154 Streams AQ: qmn coordinator idle wait
158 SQL*Net message from client
160 rdbms ipc message
161 rdbms ipc message
162 rdbms ipc message
163 rdbms ipc message

SID EVENT
---------- ----------------------------------------------------------------
164 smon timer
165 rdbms ipc message
166 rdbms ipc message
167 rdbms ipc message
168 rdbms ipc message
169 rdbms ipc message
170 pmon timer

18 rows selected.

1 select sid, chr(bitand(p1, -16777216)/16777215) || chr(bitand(p1,16711680)/65535) "Name",
2* (bitand(p1, 65535)) "Mode" from v$session_wait where event like 'enq%'
SQL> /

SID Name Mode
---------- ---- ----------
147 TX 4
进一步分析：
SQL> select * from v$lock
2 where request>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
1EC344AC 1EC344C0 147 TX 131074 563 0 4 78 0

SQL> SELECT DECODE(request,0,'Holder: ','Waiter: ') ||
2 sid sess, id1, id2, lmode, request, type
3 FROM V$LOCK
4 WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)
5 ORDER BY id1, request;

SESS ID1 ID2 LMODE REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 158 131074 563 6 0 TX
Waiter: 147 131074 563 0 4 TX

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472967/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472967/