| 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?
On Mon, 2004-02-16 at 13:39, Naveen, Nahata (IE10) wrote:
> Thanx Anjo,
>
> That answers 2 of my questions. But the most baffling one remains
> unanswered:
>
> 1. Why doesn't rollback of one transaction mean that the waiting session
> stops waiting and goes ahead with the delete?
Because the waiting process will actually wait on a specific ITL which is more or less randomly picked. So the rollback doesn't automatically mean that the ITL will be used
>
> 2. If another session can utilize the ITL Slot which just got vacated
> because one transaction rolled back, why doesn't the waiting session occupy
> the same transaction slot?
See before.
>
> 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.
>
The sleep doesn't mean a thing here, they get posted .....
> 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
> >-----------------------------------------------------------------
> >
> ----------------------------------------------------------------
> 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 - 07:10:41 CST
 |
 |