会产生4级表锁的几种情况:
- bitmap索引
--session 1
SQL> create table t1_tx(id number,name varchar2(20)) ;
Table created.
SQL> insert into t1_tx select rownum,chr(97+mod(rownum,2)) from dual connect by level<=10;
10 rows created.
SQL> commit;
Commit complete.
SQL> select * from t1_tx;
ID NAME
---------- ----------------------------------------
1 b
2 a
3 b
4 a
5 b
6 a
7 b
8 a
9 b
10 a
10 rows selected.
SQL> create bitmap index idx_bitmap_name on t1_tx(name);
Index created.
SQL> select sid from v$mystat where rownum=1;
SID
----------
63
SQL> update t1_tx set name='tx' where id=3;
1 row updated.
SQL> -- not commit
-- session 2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
5
SQL> update t1_tx set name='bitmap' where id=5;
--hang
---session 3
SQL> select sid,
2 chr(bitand(p1, -16777216) / 16777215) ||
3 chr(bitand(p1, 16711680) / 65535) "Name",
4 (bitand(p1, 65535)) "Mode",
5 event,
6 sql_id,
7 FINAL_BLOCKING_SESSION
8 from v$session
9 where event like 'enq%';
SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
5 TX 4 enq: TX - row lock contention 1qtvgrv88q2dj 63
- 主外键关系,主键表插入数据不提交,外键表插入数据被阻塞
--session 1
SQL> create table parent(id number primary key);
Table created.
SQL> create table child(id number references parent,name varchar2(20));
Table created.
SQL> insert into parent values(1);
1 row created.
SQL>--not commit;
--session 2:
SQL> insert into child values(1,'a');
--hang
--session 3:
SQL> l
1 select sid,
2 chr(bitand(p1, -16777216) / 16777215) ||
3 chr(bitand(p1, 16711680) / 65535) "Name",
4 (bitand(p1, 65535)) "Mode",
5 event,
6 sql_id,
7 FINAL_BLOCKING_SESSION
8 from v$session
9* where event like 'enq%'
SQL> /
SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
5 TX 4 enq: TX - row lock contention d8gc19unfrcsw 63
- 插入同一主键值
--session 1:
SQL> create table mytab(id number primary key);
Table created.
SQL> insert into mytab values(1);
1 row created.
SQL>
--session 2:
SQL> insert into mytab values(1);
--hang
--session 3:
SQL> l
1 select sid,
2 chr(bitand(p1, -16777216) / 16777215) ||
3 chr(bitand(p1, 16711680) / 65535) "Name",
4 (bitand(p1, 65535)) "Mode",
5 event,
6 sql_id,
7 FINAL_BLOCKING_SESSION
8 from v$session
9* where event like 'enq%'
SQL> /
SID Name Mode EVENT SQL_ID FINAL_BLOCKING_SESSION
---------- ---------------- ---------- ------------------------------ -------------------------- ----------------------
5 TX 4 enq: TX - row lock contention 2srnv7c1ummk0 63
- ITL(事务槽)争用,其实这个可以明显看出来,因为在10g后,就已经有enq:TX – allocate ITL entry,但是我在这里还是把它分类在mode=4的TX锁。
关于等待事件enq: TX - allocate ITL entry的一次故障处理_killvoon的博客
注:9.2.0.8环境,因为10g以上环境,设置maxtrans无效,自动变成255
--session 1
SQL> create table test(x number) initrans 2 maxtrans 2;
Table created.
SQL> insert into test select rownum from dual connect by level<=3;
3 rows created.
SQL> commit;
Commit complete.
SQL> select * from test;
X
----------
1
2
3
SQL>
SQL> update test set x=99 where x=1;
1 row updated.
SQL>
--session 2
SQL> update test set x=99 where x=2;
1 row updated.
SQL>
--session 3:
SQL> update test set x=99 where x=3;
--hang
- 索引分裂,会伴随着enq: TX – index contention的等待。