Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How do commits release row level locks?
To answer them:
Anjo.
On Mon, 2004-02-16 at 13:10, Naveen, Nahata (IE10) wrote:
> 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_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Mon Feb 16 2004 - 06:25:17 CST
![]() |
![]() |