Re: Row and Table Locking

Nadeem,

What you are looking at here is an example of pessimistic locking...

The database locks the rows ahead of an update to ensure no other process
can modify them and invalidate the consistency of the update. As a note, this
is the only method available in the Rdb database.

The advantage is that you are guarenteed the consistency and integrity of the
data. The disadvantage is that you can get into lock contention problems and
even deadlocks which can cripple a high-volume production database.

The alternative - using Oracle's default row locking mechanism at the point of
update - is known as optimistic locking. In this scenario we assume optimistically
that the data we are updating is good and so do not lock the row explicitly. The
advantage is that this approach completely eliminates the lock contention and
deadlock problems of the pessimistic approach. The dangerous downside is
that if two or more processes are trying to update the same rows of data

simultaneously - updates can be buried leading to inconsistent data.

What you ideally need is optimistic locking with concurrency enabled. That is,
if the row being updated has changed since you read from it, your write will be
rejected. Unfortunately, Oracle does not support this feature as of 8.1.7. Until
they do, you'll have to implement your own solution if you want to optimistic locking
with concurrency. There are many resources out on the web that describe ideas on how
to do this. I have put together some battle-tested ideas on the subject which you can
find at the following link:

http://www.orafaq.org/papers/locking.pdf

I hope it answers some of your questions.

Good luck

Graham

Nadeem Kafi wrote:

> Hello All,
>
> I want to confirm that there is no need to explicitely lock
> tables and rows during updates and deletes after the row
> level lock provided by Oracle after Version 6. i.e.
>
> Before each update/delete to a row in a table there is no need to
> lock the row. Oracle will do it automatically.
>
> One of my inherited application contains PL/SQL code which lock
> tables in share update mode (or using select...for update nowait)
> before every update and delete. Now using Oracle 8.1.6, I feel
> there is no need. BTW, there are no explicity application specific
> locking requirements.
>
> Best Regards,
> Nadeem Kafi.
Received on Mon Aug 27 2001 - 11:25:06 CDT