棉花说的是对的,level 3的TM lock是不会互相block的。
如:
session 1:
SQL> lock table hao in row exclusive mode;
Table(s) Locked.
session 2:
SQL> update hao set object_name='hao' where object_id=111;
1 row updated.
SQL> l
1* select * from v$lock where type='TM'
SQL> /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003C0756A50 00000003C0756A78 1969 TM 33478 0 3 0 96 0
00000003C0756950 00000003C0756978 1968 TM 33478 0 3 0 99 0
只有当有session占用了level4,5,6的TM lock之后,后续的dml请求level 3的TM lock才会受阻。
例如:
session 1:
SQL> lock table hao in share mode;
Table(s) Locked.
session 2:
SQL> update hao set object_name='hao' where object_id=111;
--hung
session 3:
SQL> select * from v$lock where type='TM';
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00000003C0756A50 00000003C0756A78 1969 TM 33478 0 0 3 48 0
00000003C0756950 00000003C0756978 1968 TM 33478 0 4 0 54 1