Misunderstanding of Oracle parallelism 2: locking the entire table

Misunderstanding of Oracle parallelism 2: locking the entire table

I have written a misunderstanding about Oracle parallelism before. 1. In fact, you didn't use parallelism. I always thought that Oracle would only lock the data to be modified. But in parallel, this is the case, parallel update locks the entire table. The following experiment:

Session1:

SQL> select sid from v $ mystat where rownum = 1; SID----------11SQL> 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----------8SQL> update test t set object_name = 'ggg 'where object_type = 'index'; -- hang in 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 08 AE 100 0 4 0 1871 011 TO 65927 1 3 0 1305 011 TX 196620 5212 6 0 64 011 PS 1 2 4 0 64 011 PS 1 3 4 0 64 011 TM 80189 0 6 0 64 111 PS 1 0 4 0 64 011 AE 100 0 4 0 2836 011 PS 1 1 4 0 64 0SQL> select object_id, locked_mode from v $ locked_object where session_id in (8, 11); OBJECT_ID LOCKED_MODE ---------- ----------- 80189 680189 0SQL> select sid, event from v $ session_wait where wait_class <> 'idle' and sid in (8, 11); sid event ---------- listen 8 enq: TM-contention

V $ LOCK View Structure

TYPE

Differentiate the lock protection object types (table 4)
TM-DML enqueue
TX-Transaction enqueue
UL-User supplied
-We mainly focus on the TX and TM locks.
-UL locks are defined by users and are rarely defined.
-All others are system locks, which will be released soon without attention

ID1
ID2

The value meanings of ID1 and ID2 vary according to the value of type.
For tmlock
ID1 indicates that the object_id of the locked table can be associated with the dba_objects view to obtain specific table information. The ID2 value is 0.
For TX? Lock
ID1 uses a decimal value to indicate the rollback segment number occupied by the firm and the slot number in the transaction slot, in the form of a group:
0 xRRRRSSSS, RRRR = RBS/undo number, SSSS = SLOT NUMBER
ID2 represents the number of times that wrap in decimal format, that is, the number of times that the transaction slot is reused.

LMODE

0-none
1-null (NULL)
2-row-S (SS)
3-row-X (SX)
4-share (S)
5-S/Row-X (SSX)
6-exclusive (X)

REQUEST

Same as LMODE. If it is greater than 0, the current session is blocked. Other sessions occupy the modified lock mode.

BLOCK

Whether to block other session lock applications 1: Blocking 0: not blocking