Re: Usage of RowId...

From: Nuno Guerreiro <nuno-v-guerreiro_at_telecom.pt>
Date: 1998/04/16
Message-ID: <3536299c.4250682_at_news.telecom.pt>#1/1


On Wed, 15 Apr 1998 18:55:45 -0400, "Brian K. Lawton" <NOSPAM.Lawton_at_RDAConsultants.Com> wrote:

>Let me clarify what I meant by optimistic concurrency. By optimistic
>concurrency, I mean always assume that your update is going to be
>successful. However, if the data/row has changed since the values were
>initially read, then fail the update.
>

Do you want the update to fail even if the rows to be updated were changed by a transaction which already ended?

If so, then I'd go with the SERIALIZABLE isolation level, unless there is a great chance that several transactions will be updating the same rows.

If not, then I believe it's possible to tell Oracle not to have transactions wait for others to release resources and that would solve your problem.

Another approach is to have each transaction lock in advance the rows it will update, by issuing SELECT * FROM table WHERE ... FOR UPDATE;

>For example:
>
>Step #1: Two users (call them U1 & U2) both select row #9 (call it R9) out
>of table X (call it TX). We are in a stateless environment, so no locks are
>acquired. By this I mean that the users issue their queries via a middle
>layer (an intranet server) therefore, a lock is not held between the read
>and update.
>
>Step #2: Based on the values selected, U1 updates R9 and commits the
>changes. R9 now becomes R9+.
>
>Step #3: Meanwhile, U2 attempts to update R9 however the database (or
>update statement) releases that R9 has been changed (its now R9+) and rolls
>back U2's update.

If U2 attempts to update R9 after it has been updated and commited by user U1 like you wrote, then no rollback will take place, since after U1 issues the COMMIT statement, the row is no longer locked by U1. This way, U2 will be able to perform his update normally.

However, if U2 tries to update R9 after it has been updated by U1 but not yet commited, then U2 will wait (that's the default behaviour) for U1 to issue either a COMMIT or ROLLBACK statement and release the lock. Only after any of these statements is issued will U2 be able to perform the UPDATE statement.

Good luck,

Nuno Guerreiro Received on Thu Apr 16 1998 - 00:00:00 CEST

Original text of this message