Re: How do commits release row level locks?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 16 Feb 2004 16:29:54 -0000
Session 1 grabs an ITL slot
Session 2 grabs an ITL slot
Session three see all slots full, so picks and ITL
slot 'at random' and (in version 9) sits on the
transaction table entry (in the Undo segment header)
for 5 seconds - if nothing happens in this time, the
transaction goes round each slot in turn, just once,
waiting for 5 seconds on each, hoping to find an
empty slot. (This is v9 behaviour only, v8 would
just stick on the first one).
After trying every slot for 5 seconds, the transaction
sits on the either the first or last one it tried (I can't
remember which off-hand), and waits indefinitely for
it to commit or rollback.
If one of the other transactions rolls back or commits,
then an ITL slot is free, but the waiting transaction does
not see it. The waiting transaction sets a call-back to itself
every three seconds (possibly to allow an moment for a deadlock
detection message to be delivered).
An even stranger effect than the one you've described - if
the transaction that yours is waiting on rolls back to a savepoint
(leaving some data still changed) that frees the ITL in the
block that is causing the problem, your transaction will not
see it, because it's no longer looking at the ITL list, it's
looking at the transaction table. So it will still wait.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
----- Original Message -----
From: "Naveen, Nahata (IE10)" <Naveen.Nahata@xxxxxxxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, February 16, 2004 12:24 PM
Subject: RE: How do commits release row level locks?
One more addition to it.
If I open another session and issue the delete statement from that session,
the delete goes ahead.
It seems once you wait, you can only get in if all the slots are completely
empty. But if you have not started waiting, you can get in if their is
atleast one slot available.
I don't think this is the desired functionality. Is it a bug? Or obviously
I'm missing something!
Naveen
>-----Original Message-----
>From: Naveen, Nahata (IE10) [mailto:Naveen.Nahata@xxxxxxxxxxxxx]
>Sent: Monday, February 16, 2004 5:40 PM
>To: oracle-l@xxxxxxxxxxxxx
>Subject: RE: How do commits release row level locks?
>
>
>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
>
----------------------------------------------------------------
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:
