Re: UPDATE statements and locks

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?
>

Oracle will lock -- against further update -- those rows that are changed as a result of the UPDATE statement. That is all. Oracle NEVER escalates locks above the row level -- no blcok or table level locking.

The rows will remain locked until the transaction updating them either commits or rolls back.

The longer the update transaction takes to complete the longer the rows will remain locked.

Users selecting rows your transaction are updating (have changed) after your transaction has begun but before it commits|rolls back, will see the data the way it was before your transaction started updating. That is 'read consistency' and is one of the uses of the rollback segments -- read up on it, it is interesting and useful stuff to know.

Users attempting to update the same rows you have updated will wait until your transaction commits|rolls back before proceeding.

You can test this out yourself using a couple of SQL*Plus session. Take the time for a little self discovery about how this works and you will come away understanding a lot more about concurrency and consistency in Oracle.

--
Andrew Allen
Sr. Oracle DBA