Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Row and Table Locking

Re: Row and Table Locking

From: Graham Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Tue, 04 Sep 2001 18:45:19 -0500
Message-ID: <3B95678F.9ECC6785@ln.ssw.abbott.com>

Nuno Souto wrote:

<snip>

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

Pretty simple....

(example is using standard, default Oracle locking - i.e. not using FOR..UPDATE ) You read a row from a table, and based on the information you find there you make a change using some typical Oracle-Forms based GUI app.

I read the same row and make a different change using the same GUI app.

You write your changes back to the database and commit your transaction ( you hit Save ).

I write my changes back to the database and commit my transaction ( I hit Save ).

Your changes are lost or 'buried' by mine.

As Galen pointed out, you can avoid this situation by setting the transaction isolation
level to serializable for every transaction. That works, but you're delegating transaction
control right down to the user/developer level - that is you've got to figure out how to
force eveyone who connects to your database to set their transaction isolation level.
You're also losing your read-commited isolation level. You'll have to decide if that's
okay for your environment.

I've also seen alot of reports of bugs with serializable transactions on the Linux and
Solaris ports, so I'd be very interested in hearing from anyone who's using this approach
in a production system - especially with the Parallel Server Option.

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

Even better still set the SERIALIZABLE parameter to TRUE in the INIT.ORA file - desupported since 8.0 but still there on my 8.1.6 on Solaris port. Wow,
that one will really trash your performance! :-)

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

I was impressed to see the SilverStream Web-Server supports optimistic locking with concurrency right out-of-the-box. The BEA guys told us we'd have to spend an extra $100K for a third-party transaction control system to do that with their web-server. We said no-thanks.

Graham Received on Tue Sep 04 2001 - 18:45:19 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US