oracle锁mode,Mode=4的TX锁小结

--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

SQL>