In my previous post I had demonstrated how a Direct Path Insert in one session blocks another Direct Path Insert from another session, except when the two sessions explicitly name separate target Partitions.
Here, I will lock at how Oracle creates Locks for such operations. In the output below, the SQLPrompt is set to either 'Sesn_1' or 'Sesn_2' or 'SYSTEM' (the DBA session) to indicate which session is executing the SQL statement.
First, running the Direct Path Insert without specifying the target Partition name.
SYSTEM>l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from dba_objects
3 where owner = 'HEMANT'
4 and object_name in ('MY_PART_TABLE')
5 and object_type in ('TABLE','TABLE PARTITION')
6* order by 1, 2 nulls first, 4
SYSTEM>/
OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
MY_PART_TABLE TABLE 81817
MY_PART_TABLE P_100 TABLE PARTITION 81818 81827
MY_PART_TABLE P_200 TABLE PARTITION 81819 81828
MY_PART_TABLE P_300 TABLE PARTITION 81820 81820
SYSTEM>
SYSTEM>truncate table hemant.my_part_table;
Table truncated.
SYSTEM>
SYSTEM>select count(*) from v$locked_object;
COUNT(*)
----------
0
SYSTEM>
Sesn_1>select distinct sid from v$mystat;
SID
----------
138
Sesn_1>
Sesn_1>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/
50 rows created.
Sesn_1>
Sesn_2>select distinct sid from v$mystat;
SID
----------
384
Sesn_2>
Sesn_2>l
1 insert /*+ APPEND */ into my_part_table
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/
---------- Sesn_2 is now in a Wait
SYSTEM>select event from v$session where sid=384;
EVENT
----------------------------------------------------------------
enq: TM - contention
SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /
SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 6
384 81817 0
SYSTEM>
---------- Object ID 81817 is the Table itself, locked by Session 138 -- Sesn_1
SYSTEM>select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4 order by 1
5 /
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 TM 81817 0 6 0 1
138 AE 134 4057974068 4 0 0
138 TX 393246 7880 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 0 6 0
SYSTEM>
---------- Session 138 has TM Lock Mode6 on Object 81817 -- the Table itself
---------- Session 384 (Sesn_2, Waiting) is requesting a TM Lock Mode6
So, it is clear that Session 1 (SID 138) had a TM Lock (Mode->6) on the Table, blocking Session 2 (384) (which is requesting the same Mode->6 lock) when the Direct Path Insert specified the Table name alone.
The TX lock by SID 138 is the Transaction Row Exclusive Lock. What is blocking Session 2 (SID 384) is that it is requesting a Mode->6 lock on the same Object (the Table) as already held by Session 1 (SID 138)
Then, when I issue a Rollback (or Commit) from Sesn_1, the Insert by Sesn_2 goes through.
Before this next test, I rollback both the Inserts
Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6 by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.
Next, with the target Partition named
Sesn_1>l
1 insert /*+ APPEND */ into my_part_table partition (p_100)
2 select rownum, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_1>/
50 rows created.
Sesn_1>
Sesn_2>l
1 insert /*+ APPEND */ into my_part_table partition (p_200)
2 select rownum+101, dbms_random.string('X',12)
3 from dual
4* connect by rownum < 51
Sesn_2>/
50 rows created.
Sesn_2>
SYSTEM>select event from v$session where sid=384;
EVENT
----------------------------------------------------------------
SQL*Net message from client
SYSTEM>
SYSTEM>select session_id, object_id, locked_mode
2 from v$locked_object
3 /
SESSION_ID OBJECT_ID LOCKED_MODE
---------- ---------- -----------
138 81817 3
138 81818 6
384 81817 3
384 81819 6
SYSTEM>
SYSTEM>l
1 select sid, type, id1, id2, lmode, request, block
2 from v$lock
3 where sid in (138,384)
4* order by 1,2,3
SYSTEM>/
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
138 AE 134 4057974068 4 0 0
138 TM 81817 0 3 0 0
138 TM 81818 0 6 0 0
138 TX 65540 6296 6 0 0
384 AE 134 4057974068 4 0 0
384 TM 81817 0 3 0 0
384 TM 81819 0 6 0 0
384 TX 458781 6211 6 0 0
8 rows selected.
SYSTEM>
---------- Both sessions SIDs 138 (Sesn_1) and 384 (Sesn_2) have the Table Object (81817) locked in Mode3, not Mode6
---------- But the Partitions (Objects 81818 and 81819) are locked in Mode6
---------- Neither is SID 138 Blocking any other session nor is SID 384 being blocked
Now we see that the Table (object 81817) is locked in Mode->3 and not Mode->6 by by *both sessions* while the respective Partitions P_100 (object 81818) and P_200 (81819) are locked in Mode->6 without session 384 (Sesn_2) waiting for a block.
Thus, as the Table itself is not locked in Mode->6, Sesn_2 is allowed a Direct Path Insert into another Partition -- only so as long as it explicitly names the Target Partition. (If Sesn_2 attempts to do a Direct Path Insert without naming a Target Partition, it will, again begin waiting on Sesn_1 without the REQUEST being evident in v$lock)
Mode 6 is Lock Table in Exclusive Mode in the first case.
In the second case, the Table is locked in Mode 3 which is Row-Exclusive, not Table level.
The "TX" is a Transaction Enqueue
The "AE" Lock type is "Edition Enqueue" --- which we are not concerned with in this test, as it relates to Editioning (which I am not currently using) and will appear even if Editioning is not being used.
No comments:
Post a Comment