| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Locking issues
On 17 Feb 1997 13:47:05 GMT, "David Kaiser" <davidk_at_sfmic.com> wrote:
>I'll reply to one place here. I've received a number of answers on this,
>but it continues to point to a basic flaw in Oracle's locking strategy.
>
Not a flaw but protection from corruption (corruption introduced by lost updates).
>The NOWAIT option is a good one, except it only works with SELECT FOR
>UPDATE... statements. This isn't a viable solution for C/S applications.
why (isn't it viable)?
>I wouldn't want to select a record and always automatically lock it - I'd
no one said that, it has been proposed that upon the client attempting to modify a COPY of the record(s), you then lock them and simultaneously ensure that they have not been modified since you copied them to the client. If you don't want to do it then, then you need to save the old copy of the record in the client and update a local copy of it so you can use the old values when you actually go to do the update (to ensure you don't lose an update). You'll need to send two copies of the record (the old copy and the modified copy) no matter what if you want your program to be correct. The user can either find out before they waste time modifying the record or after they modified the record that the record has already been changed by someone else. (or you can opt to never let them know but blow away someone elses changes)
>rather have the update check for a lock on the record. SELECT FOR UPDATE
>works great for batch update programs, but can be trouble interactively
>(expecially when users walk away from their computers while displaying
>something).
>
Here's the client/server scenario:
If you are worried about someone walking away from a terminal, perhaps your client server application should have a timeout in it. If no keyboard activity/ whatever, issue a message, give them one more minute or something, and then rollback.
What has been suggested as a locking scheme is:
1.) I read out a list of records and display them (no locks)
2) user attempts to update COPY of record 1, goto database and lock record 1
ensuring that the local copy matches the database copy, return error if record
is locked OR has been modified.
2a) do the same for records 3 and 5
3) program issues 3 updates
4) database is consistent.
>Other database products will simply tell you that a record is locked when
>you try to update/delete it.
Informix has a "set lock mode to wait N". If you use this AND you use it on an update AND you block for N-1 seconds, you have just overwritten someone elses information (classic lost update).
If you use this AND its on an update and you block for N seconds, you just got lucky. You won't have a lost update.
Even if you don't use this and you neglect the select for update, you will have lost updates (unless your users never update the same records but then we wouldn't be having this thread)
>If Oracle is smart enough to busy wait for a
>lock, it should also have an option that can be set that tells it to return
>a lock error when this happens. It is not easy to write the extra
>statements for SELECT TO UPDATE in many of the newer tools. Plus --
then the 'newer' tools have taken you a step backwards in database consistency.
>needing to write a SELECT..UPDATE before updating a record is a performance
>hit.
>
It is not easy to write error handling code either (but if you don't do it, what then?). Error handling does add to the number of lines of code a program must execute to perform its function, hence slowing it down. But what would happen without it?
Basically, if you value your data, you'll take the time.
>If anyone has any communication channels on this, I'd like to hear Oracle's
>official opinion on this (and if they plan on enhancing it).
>
I am not an official Oracle spokesperson. This is not an official statement on Oracles part. This is my opinion.
>Thanks everyone.
>
>David Kaiser
>
>Greg Burris <greg.burris_at_MCI2000.com> wrote in article
><330652D0.7E22_at_MCI2000.com>...
>> David Kaiser wrote:
>> >
>> > Oracle's default locking method causes C/S applications to wait for a
lock
>> > to clear. This can make the user think that their program crashed,
when
>> > they are actually waiting for a lock.
>> >
>> > The only way around this that I've seen is to have the client program
>> > timeout after waiting for a certain period of time.
>> >
>> > Is there anyway with Oracle 7.3 to have Oracle return an error message
>> > notifying the client program that the record it is trying to update is
>> > locked?
>>
>> Look into the SQL Language Reference for the NOWAIT clause. It will
>> cause Oracle not to wait for lock releases, and return a specific
>> message to the application to the effect of "resource not available and
>> NOWAIT specified..." or something like that.
>>
>> Good luck!
>> Greg Burris
>> Practice Manager, Oracle Services
>> Performance Architecture Group
>> Troy, MI
>>
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |