RE: How do commits release row level locks?
- From: "Naveen, Nahata (IE10)" <Naveen.Nahata@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 16 Feb 2004 05:10:13 -0700
Hi All,
Can someone please explain me why the following happens:
Step 1. CREATE TABLE TEMP (COL1 NUMBER) PCTFREE 0 INITRANS 2 MAXTRANS 3
Step 2. INSERT values from 1 to 100,000 in the table
Step 3. COMMIT
Step 4. Since the insert was sequential, I assume the values 1, 2, 3, 4, 5
should be in the same block (I can test the same using rowid as well)
Step 5. From one session issue "DELETE TEMP WHERE col1 = 1"
Step 6. From the second session issue "DELETE TEMP WHERE col1 = 2"
Step 7. From the third session issue "DELETE TEMP WHERE col1 = 3"
Step 8. The third session waits on "enqueue". This is understandable since
PCTFREE is 0 and the block has no space to create a third ITL Slot.
Step 9. ROLLBACK the second session. This should release the ITL Slot (???)
Step 10. Still the third session waits on "enqueue". Should it not go ahead
with the delete now that one transaction has rolled back?
Step 11. ROLLBACK the first session and the delete issued by the third
session goes ahead.
I tried the same experiment with INITRANS set to 3, and the fourth session
hangs, and doesn't move ahead until all the other three session rollback or
commit. It seems that the waiting session doesn't go ahead until all the
sessions have finished their transactions.
Moreover, when I look at V$SESSION_WAIT, it shows the session waiting on
"enqueue" and SECONDS_IN_WAIT increment from 0 to 3 and then again get reset
to 0.
Can someone please explain this? It seems I'm missing something, but what?
9.2.0.3 on Solaris
Regards
Naveen
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
