对Oracle 并行的误解2--其实会锁全表
时间：2021-07-01 10:21:17
帮助过：4人阅读
session1:
SQL> select sid from v$mystat where rownum=1;
SID
----------
11
SQL> select sid from v$mystat where rownum=1;
SQL> create table test as select * from dba_objects;
SQL> alter session force parallel dml;
SQL> update /*+parallel(t,4)*/ test t set object_name=‘ggg‘ where object_type=‘TABLE‘;
session2:
SQL> select sid from v$mystat where rownum=1;
SID
----------
8
SQL> update test t set object_name=‘ggg‘ where object_type=‘INDEX‘;--hang住
session3:
SQL> select sid,type,id1,id2,lmode,request,ctime,block from v$lock where sid in(8,11) order by sid;
SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
8 TM 80189 0 0 3 61 0
8 AE 100 0 4 0 1871 0
11 TO 65927 1 3 0 1305 0
11 TX 196620 5212 6 0 64 0
11 PS 1 2 4 0 64 0
11 PS 1 3 4 0 64 0
11 TM 80189 0 6 0 64 1
11 PS 1 0 4 0 64 0
11 AE 100 0 4 0 2836 0
11 PS 1 1 4 0 64 0
SQL> select object_id,locked_mode from v$locked_object where session_id in(8,11);
OBJECT_ID LOCKED_MODE
---------- -----------
80189 6
80189 0
SQL> select sid,event from v$session_wait where wait_class <>‘Idle‘ and sid in(8,11);
SID EVENT
---------- -------------------------------------------------------------------------
8 enq: TM - contention对Oracle 并行的误解2--其实会锁全表
标签：
