Re: Row and Table Locking

On Mon, 27 Aug 2001 11:25:06 -0500, Graham Thornton <graham.thornton_at_ln.ssw.abbott.com> wrote:

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

I'm having trouble seeing how updates can be buried in ORACLE, Graham. Given that once a row is locked, all other updates are queued until lock is released by an end transaction (or rollback). Between tables maybe, but on a single table I can't see it happening.

BTW, the rdb-style locking is possible in O using the FOR UPDATE construct on a prior SELECT. You prolly know this anyway, just thought I'd mention it.

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

Indeed. I've done this recently for a JSP application, using timestamps. Works nifty. As you say, there are quite a few solutions.

Cheers
Nuno Souto
nsouto_at_optushome.com.au.nospam Received on Tue Aug 28 2001 - 05:22:21 CDT