Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Row and Table Locking
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