I am sorry, I was very unclear.
I mean Oracle doesn't provide support optimistic concurrency for business
transactions within the database kernel.
Cheers
-----Original Message-----
From: Tinou Bao [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 3:32 PM
To: [EMAIL PROTECTED]
Subject: Re: To scale or to be correct: that is the question
----- Original Message -----
From: "Jay Walters" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, June 29, 2001 2:37 PM
Subject: Re: [EJB-INT] To scale or to be correct: that is the question
> 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.
>
Can you explain this a little more...how Oracle doesn't provide support for
optimistic locking within the datbase kernel? Since Oracle doesn't do read
locks and if you have two transaction that update the same row (as in the
counter example) one of the transaction will get a can't serialize
exception..isn't this optimistic locking? This is from Oracle, it refers to
7 but I think the same applies to 8:
<oracle>
"The Oracle7 implementation of SERIALIZABLE transaction isolation can be
characterized as an 'optimistic' concurrency control scheme'. A
'pessimistic' system provides a transaction with a consistent set of data to
read and write by preventing a concurrent transaction from modifying data
read by the transaction. Instead, Oracle7 makes the 'optimistic' assumption
that the SERIALIZABLE transaction will not attempt to modify the same rows
that another transaction may change at about the same time....When the
underyling 'optimistic' expectation is not met, at least the statement (and
perhaps the transaction) must be roll back, wasting some work."
</oracle>
And this is from Weblogic's documentation:
<weblogic>"Special Note for Oracle Databases
Keep in mind that Oracle uses optimistic concurrency. As a consequence, even
with a setting of TRANSACTION_SERIALIZABLE, Oracle does not detect
serialization problems until commit time. The message returned is:
java.sql.SQLException: ORA-08177: can't serialize access for this
transaction
Even if you use the TRANSACTION_SERIALIZABLE setting for an EJB, you may
receive exceptions or rollbacks in the EJB client if contention occurs
between clients for the same rows. To avoid these problems, you must ensure
that clients catch and examine the SQL exceptions, and take appropriate
action, such as restarting the transaction.
With WebLogic Server, you can set the isolation level for transactions to
TRANSACTION_READ_COMMITTED_FOR_UPDATE for methods on which this is defined.
When set, every SELECT query from that point on, will have FOR_UPDATE added
to require locks on the selected data. This condition remains in effect
until the transaction does a COMMIT or ROLLBACK."</weblogic>
Is there something different that applies to the "kernel?"
> 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.
This thread caught my attention because people were saying your
database/data is getting corrupted...these "transactions" are user
transactions that span user think time...I don't think the database can do
anything here to help you because to it it's not a real transaction and
you'll have to do that version/timestamp check in your where clause or
something similiar.
>
> 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".
>
>
===========================================================================
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".
