Re: Usage of RowId...

From: Brian K. Lawton <NOSPAM.Lawton_at_RDAConsultants.Com>
Date: 1998/04/15
Message-ID: <6h3e07$crp$1_at_ha2.rdc1.md.home.com>#1/1

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.

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.

One way of solving this problems is by keeping some type of flag that gets read with each query and persisted to the client. At update time, the client then appends a where clause to the update statement to only update the row if the flag is the same as when it was initially read. At the same time, the client also increments/modifies the flag to signify that the row was changed. This provides for optimistic concurrency at the row level.

Another way of solving this is to always is to provide optimistic concurrency at the column level. To do this you need to save the original values from your query and compare them back against the table during update. If any values have changed, then fail the update.

SQL Server handles the row level concurrency through the use of a datatype called timestamp. The timestamp datatype (which has nothing to do with time) is simply a binary value that the server automatically increments each time the row is modified.

What I was looking for from Oracle is how to get row level optimistic concurrency in the same manner as I do with SQL Server's timestamp datatype without having to write my own code to maintain the flag.

Serialization will work, but it comes with a high cost.

Sorry for the confusion.

---

Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com) RDA Consultants Limited (http://www.RDAConsultants.com)

Nuno Guerreiro wrote in message <35348f0d.5516251_at_news.telecom.pt>...
>On Sat, 11 Apr 1998 09:16:48 -0400, "Brian K. Lawton"
><NOSPAM.Lawton_at_RDAConsultants.Com> wrote:
>
>>If I'm trying to implement optimistic concurrency within my database, is
 the
>>RowId attribute the correct place to be checking? If not, what is
 Oracle's
>>equivalent to SQL Server's timestamp datatype?
>>
>>________________________________________________
>>Brian K. Lawton (mailto:Lawton_at_RDAConsultants.com)
>>RDA Consultants Limited (http://www.RDAConsultants.com)
>
>You should describe your problem more precisely. I'll try answering
>your question, anyway.
>
>By default, Oracle statement-level read consistency, i.e., when any
>SQL statement is issued (SELECT, DELETE, UPDATE, INSERT), it is
>guaranteed that it will operate or select the records that existed on
>a table at the exact time the statement was issued.
>
>There are some situations in which you may need transaction-level read
>consistency, i.e., if you issue a e.g. a SELECT statement several
>times during your transaction, the result set should always be the
>same, even if there are other transactions performing UPDATE, DELETE,
>etc. on the table. By default, Oracle doesn't provide this level of
>consistency.
>
>If by 'optimistic' concurrency you mean 'extremely secure'
>concurrency, then you may force Oracle to provide transaction-level
>read consistency for every transaction in every session. You should
>issue the following statement at the beginning of each database
>session:
>
>ALTER SESSION SET ISOLATION_LEVEL SERIALIZABLE;
>
>If you want to enable this level of isolation for only one
>transaction, then issue the following command at the beginning of the
>transaction:
>
>SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
>
>The effect of issuing these commands is that you'll get a database
>environment in which several transactions change or read the database
>and although their executions are overlapped, it is exactly as if they
>were executed one after the other.
>
>These are the standard ways of doing things in Oracle - I wouldn't try
>ANY alternative method involving timestamps, since you'll be
>reinventing the wheel. These commands enable you to control everything
>you'll ever need in order to have a consistent view of the database
>throughout any transaction.
>
>The 'Oracle 7 Server Concepts' manual, chapter 10 - Data Concurrency
>provides a detailed description of how Oracle implements data
>concurrency.
>
>Good Luck,
>
>
>Nuno Guerreiro
>
Received on Wed Apr 15 1998 - 00:00:00 CEST