| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How do commits release row level locks?
Thanx Anjo,
That answers 2 of my questions. But the most baffling one remains unanswered:
I gave the waiting session enough time to wake up from the sleep, before starting a new session and trying to issue a delete from it.
Regards
Naveen
>-----Original Message-----
>From: Anjo Kolk [mailto:anjo_at_oraperf.com]
>Sent: Monday, February 16, 2004 5:55 PM
>To: oracle-l_at_freelists.org
>Subject: RE: How do commits release row level locks?
>
>
>To answer them:
>
>1) initrans will allocate the slots, maxtrans will only limit youif the
>space is available. If there is no space left in the block, you will
>only have the ITL entries that you specified by initrans.
>
>2) The wait for an TX enqueue is 3 seconds in this case. So
>oracle waits
>3 seconds, wakes up checks a couple of things and goes back to sleep if
>it didn't get the enqueue. So that is why you see the 3 seconds.
>
>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
>> -----------------------------------------------------------------
>>
>>
>
>----------------------------------------------------------------
>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:39:18 CST
 |
 |