| 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?
Hi Stepan,
You got my first name right :-) I know the naming convention being followed by the Mail Administrators (First Name, Last Name) does create the confusion.
My intuition says it should not work this way.
I'm sure some expert here will point out the blatantly obvious which I'm missing here.
Regards
Naveen
>-----Original Message-----
>From: Stefan Jahnke [mailto:Stefan.Jahnke_at_bov.de]
>Sent: Monday, February 16, 2004 5:38 PM
>To: 'oracle-l_at_freelists.org'
>Subject: AW: How do commits release row level locks?
>
>
>Hi Naveen (sorry if I got the first an last name mixed up)
>
>Good one. I can just guess (maybe one of the experts has an
>answer): Is =
>it
>possible that, once you have a transaction waiting for a free itl - =
>slot,
>the itl is only cleared after all the transactions that were
>holding a =
>slot
>commited? Otherwise the waiting transaction should have gotten the =
>"free
>again" slot, right?
>
>Probably wrong, but worth a try ;).
>
>Stefan
>
>
>-----Urspr=FCngliche Nachricht-----
>Von: Naveen, Nahata (IE10) [mailto:Naveen.Nahata_at_honeywell.com]
>Gesendet: Montag, 16. Februar 2004 13:10
>An: oracle-l_at_freelists.org
>Betreff: 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 =3D 1"
>Step 6. From the second session issue "DELETE TEMP WHERE col1 =3D 2"
>Step 7. From the third session issue "DELETE TEMP WHERE col1 =3D 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.=20
>
>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
>-----------------------------------------------------------------
>
>
>=20
>----------------------------------------------------------------
>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:28:09 CST
 |
 |