I always thought that Oracle would only lock in the data that needed to be modified, but in parallel, the parallel update would lock the whole table, the following experiment:
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 Live
Session3:
Sql> Select Sid,type,id1,id2,lmode,request,ctime,block from V$lock where Sid in (8,11) the 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
One to 65927 1 3 0 1305 0
TX 196620 5212 6 0 64 0
PS 1 2 4 0 64 0
PS 1 3 4 0 64 0
TM 80189 0 6 0 64 1
PS 1 0 4 0 64 0
AE 100 0 4 0 2836 0
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
A misunderstanding of Oracle parallelism 2--actually locks the whole table
