Re: table locking question

From: Kenneth Koenraadt <plovmand_at_mail-online.dk>
Date: 4 Feb 2003 08:03:39 -0800
Message-ID: <25fb645f.0302040803.2eac429f@posting.google.com>

ctcgag_at_hotmail.com wrote in message news:<20030203190729.048$bf_at_newsreader.com>...
> I think this was actually a row locking question despite the
> subject line, but...
>

>> This suggests to me that if a process encounters a row locked by a

> transaction being rolled back by pmon, that it will just sit and wait
> while pmon slogs through 10,000 blocks that no one cares about
> to get to this one. Is that the way it works? Or can the server process
> take the bull by the horns, so to speak, and clean out the offending
> block for itself.
>
> Xho

Hi Xho,

Frankly, I do not know the exact algorithm which Oracle uses. I know that Oracle/PMON will keep the lock until the transaction on the locked row(s) is rolled back.
 Let's say one transaction contains 100 update statements, each one updating ,say , one row. It has issued 50 of these updates without committing them.
The connection dies, PMON sees this and it starts to rollback. Now, it can't release any of the locks, since though they are obtained through 50 single update statements, they are all part of this single transaction.

Thus releasing the lock on just some of the rows could violate read consistency. So even though PMON *physically* has rolled back 99 of the update statements, it still has to keep the lock on all 100 rows until completely done.

This is my understanding. It could be wrong. Any other views on this interesting subject ?

• Kenneth Koenraadt

Received on Tue Feb 04 2003 - 10:03:39 CST