Re: UPDATE statements and locks

f1fteen_at_hotmail.com (Paul) wrote:
> Hi all,
>
> Can someone tell me exactly how locking is implemented in Oracle when
> an UPDATE statement is executed.
>
> eg. UPDATE table
> SET fields
> WHERE condition;
>
> The way I understand it is that only the rows that satisfy the WHERE
> clause are locked.
>
> So, if the WHERE clause takes a long time to evaluate ie. a complex
> join of the form _WHERE EXISTS(SELECT ...)_, how does it know in
> advance which rows to lock. Or does it lock the whole table until the
> "update set" is realised?

I can't give you an authoritative answer, but I can give you my mental image, in the hopes someone can correct me as well as answer you if I am wrong.

The documentation says that UPDATE takes a RX lock on the table. Apparently an RX lock doesn't prevent anything except changing the table definition, and some explicit lock statements that I've never seen anyone use in practise anyway. So for most intents and purposes, it doesn't seem lock the table.

But the way I visualize it working, it the update statement first set's itself up some kind of psuedo-savepoint (so it can rollback itself without rollback the containing transactions) and then progress through the table using whatever access method is appropriate, locking and changing rows as it encounters them. In the process of doing this it of course has to set up a consistent view back to the transaction start time (for serializable), or back to the statement start time (for read committed). If it finds that a row it is trying to change has to be undone back through someone elses commit, then the UPDATE fails, rolls back to its pseudo-savepoint, and either tries again (read committed) or throws an exception (serializable).

So the row locks are acquired progressively, as the update gets to each row.

That's my imagination, anyways.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB