Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Releasing row locks with rollback to savepoint

Re: Releasing row locks with rollback to savepoint

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: 1997/07/11
Message-ID: <01bc8e36$10ca1ab0$294b989e@WORKSTATION>#1/1

Interesting point.

I think this is due to the way that Oracle is designed to allow unlimited
row-level locking with minimal real-time overhead.

My hypothesis is this:
When the second session tries to lock the row, it finds an active transaction
locking that row, and takes out a lock on the rollback segment header entry
for that entry as the optimum way of being alerted when the transaction commits
or rolls back. You can see this in v$lock, type = 'TX': session 1 will have a lock
with LMODE=6, and session 2 will have a lock with REQUEST=6 - both on the
same value of ID1 and ID2.

When you Rollback to A, this does not change the status of Session 1's TX lock,
so session 2 is not aware of it, but the rollback actually rewrites the rows status byte
and clears the session A transaction entry from the block, thus allowing session 3 to
take out a lock on the row.

In case you're wondering why session 2 can't simply monitor the row itself, remember that
the COMMIT (when/if it occurs) need not change the content of the block at all. The lock
flag and ITL entry can stay in place until some other session does a 'delayed block cleanout'.

Hope this is of interest -
If anyone has any corrections to make I'd be pleased to here them.

Jonathan Lewis.

tld_at_accessig.com wrote in article <868636514.28035_at_dejanews.com>...
>
> Session 1:
>
> SELECT * FROM TESTTABLE WHERE ID = 'AAA' FOR UDPATE;
> SAVEPOINT A;
> SELECT * FROM TESTTABLE2 WHERE ID = 'BBB' FOR UDPATE;
>
> Session 2:
> SELECT * FROM TESTTABLE2 WHERE ID = 'BBB' FOR UPDATE;
> (this of course goes into a wait status)
>
> Session 1:
> ROLLBACK TO A;
>
> This statement is supposed to release the row lock that
> occured after savepoint A. It does, to the extent that
> a third process can now lock that row. However, the
> second session stays in a wait status until the entire
> transaction in session 1 is ended with a COMMIT or a
> ROLLBACK (without the TO SAVEPOINT clause). If the
> ROLLBACK TO A statement truely is releasing the row lock,
> I would expect session 2 to select and lock the row. Why
> doesn't this occur?
>
Received on Fri Jul 11 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US