Oracle doesn't provide support for optimistic locking within the database
kernel. I believe that Sybase does (adds a timestamp column and maintains
the value in the column, though you might have to check it explicitly in
your SQL?). I believe that Informix does not provide any support for this
either. I don't know what other databases provide this.
What this means is that you (the database client) need to do the work (add a
version/timestamp column and put a check in your where clause, etc). I
believe where this whole thread started was that some EJB containers provide
optimistic locking features within their CMP implementations and that
perhaps BEA does not.
If you're doing BMP you can always do it yourself.
Cheers
-----Original Message-----
From: Lawrence Bruhmuller [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 2:32 PM
To: [EMAIL PROTECTED]
Subject: Re: To scale or to be correct: that is the question
Well, if we had islocation level of "TX serializable" then we could never do
concurrent reads, as I understand it. And "read committed" means just that,
that an open TX can see changes committed by another (which we usually
want).
But what people have been talking about here is a behavior where the
verified update functionality happens at the DB level, not at the query
(SQL) level, and an exception occurs upon commit of an update if another TX
has updated since the current TX started.
This is truly optimistic locking (hope that no other thread is modifying the
data that you plan to write), waiting until commit time to check for
integrity.
What I have observed in Oracle does not result in this exception occurring,
and therefore is a poor implementation of optimisitic locking, it seems to
me. And so we have to consider verified update SQL like you and others have
outlined.
- Lawrence Bruhmuller
On Fri, 29 Jun 2001 13:54:13 -0400, Tinou Bao <[EMAIL PROTECTED]> wrote:
>Isn't this what transaction isolation levels are for? If you do a set
>transaction isolation level serializable Oracle will give you a can't
serial
>exception. If you need higher performance then you can keep the default
>read committed but have to accept that data consistency will be
>comprised...so my question then is it better to set transaction isolation
>level serializable or to do a verified update with the where clause...
>
>--
>Tinou Bao
>www.baosys.com
>
>
>----- Original Message -----
>From: "Lawrence Bruhmuller" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Friday, June 29, 2001 1:09 PM
>Subject: Re: [EJB-INT] To scale or to be correct: that is the question
>
>
>> Cedric, I have been reading this whole thread and I feel that the
>disconnect boils down to this:
>>
>> You are saying that most DBs, such as Oracle, will not allow concurrent
>updates to happen, that would otherwise corrupt the database (someone
>earlier kept mentioning "corrupting" the DB).
>>
>> However, maybe what we mean as data corruption are two different things.
>What I want to avoid is an update of data that has been updated since the
>read that led to doing the second update in the first place (in one TX).
>>
>> Check out this example. What you have said in this thread would lead me
>to believe that an exception would occur, but I have verified that in
effect
>an exception does NOT occur. Perhaps this example is not what the kind of
>scenario to which you were referring.
>>
>> <an example (as it happens in Oracle)>
>>
>> A row exists with some field value of 1. Two different threads
associated
>with different TXs want to increment this value by one. Thread A reads the
>row, see val=1. Thread A updates the row to val = 2. No commit of the TX
>yet.
>>
>> Then Thread B reads the row, sees val = 1 (default isolation used,
>read-committed). Then Thread B tries to update the row to val = 2, but
>hangs, because there is an uncommitted update on that row in another TX.
>>
>> Thread A commits its TX. Now the update in Thread B's TX executes, and
>the val is set to 2 (which is already is, due to Thread 1). Now Thread B
>commits its TX. NO EXCEPTION IS THROWN.
>>
>> The val in the DB is 2.
>>
>> <end of example>
>>
>> So now, both threads have incremented the val by one, but the val has
only
>been incremented once, in effect. This is because Thread 2 was allowed to
>update the row based on a read that became stale.
>>
>> Thread 2 getting an error an rolling back would be great. But it is not
>happening. So, I am forced to use the following scheme: Delineating
betwen
>"reading" and "reading with the intention to write in the same TX". the
>former is a SQL select, and the latter is a SQL select for update, which
>will pessemistically lock the row from others attempting to do the same
>thing (but others may do a regular select).
>>
>> That will ensure that I don't get stomped-on changes (as in the example),
>but complicates my business logic, as well as might reduce performance (if
>there are cases where concurrent reads with the intention to write could
>happen, but one thread ends up not writing).
>>
>> Can you comment on this? Are we on the same page?
>>
>> - Lawrence Bruhmuller
>>
>>
> ==========================================================================
>> To unsubscribe, send email to [EMAIL PROTECTED] and include in the
>body
>> of the message "signoff EJB-INTEREST". For general help, send email to
>> [EMAIL PROTECTED] and include in the body of the message "help".
>>
>>
>
> ==========================================================================
>To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
>of the message "signoff EJB-INTEREST". For general help, send email to
>[EMAIL PROTECTED] and include in the body of the message "help".
>
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
===========================================================================
To unsubscribe, send email to [EMAIL PROTECTED] and include in the body
of the message "signoff EJB-INTEREST". For general help, send email to
[EMAIL PROTECTED] and include in the body of the message "help".
