Thanks for the pointers to the manual. On my part this is strictly an issue
of poor communication.
Sorry, I was quite imprecise in my use of the word support and then in
backing away dug myself in deeper. So onward, perhaps I will get to China
this time or the next (I was in Boston before I started digging!-)
Consider Oracle's kernel based support for OCC without consistency (again a
bad choice of words, but read on) within their default isolation level of
read committed. This is the issue where there is no feature of Oracle's SQL
or PL/SQL that checks to see if an intervening database transaction has
updated the data. Oracle provides no help here, it just blindly lets the
update succeed. This is of course good and bad.
For example (assume a table foo ( id number, salary number) with a single
row, id = 1, name = 5000. If you're using SQL*Plus to try this at home
remember "set autocommit off" first.
We are modelling an entity bean with a business method that adds 1000 to the
salary. We also need the server to be the type which starts multiple
instances of the bean in memory if there are multiple clients.
Connection 1 (models ejbLoad)
select salary from foo where id = 1; Reads 5000
Connection 2 (models ejbLoad)
select salary from foo where id = 1; Reads 5000
Bean1.addToSalary(1000); Salary = 5000 + 1000 = 6000.
Bean2.addToSalary(1000); Salary = 5000 + 1000 = 6000.
-> Already here we
can start to see the problem with optimistic locking if the
bean developer doesn't have a
clue, or the CMP Implementation doesn't do the
work for them.
Connection 1 (models ejbStore)
update foo set salary = 6000;
commit
Connection 2 (models ejbStore)
update foo set salary = 6000; I hope this doesn't work!
commit
Of course the salary value is now 6000 since Connection 2 didn't see
Connection 1's changes and the Oracle Kernel does not provide any support
for identifying that there is any type of consistency (careful of this word)
issue here.
Alternatively we could also go for set transaction isolation level
serializable. In this case Oracle will block connection 2's update with an
ORA-08177 error, can't serialize transaction.
Now of course the DBA will tell you it is foolish to write code like this,
if you are going to add 1000 to the salary then just do that,
update foo set salary = salary + 1000.
This will work because of the lock that connection 1 has on the row, but
this is not how entity beans want to work.
If I modify my table foo and add a version/timestamp field, or save the
original state in my client then I can get the consistency that I want using
my entity bean.
Connection 1 (models ejbLoad)
select salary,version from foo where id = 1; Reads salary = 5000,
version = 1
Connection 2 (models ejbLoad)
select salary,version from foo where id = 1; Reads salary = 5000,
version = 1
Connection 1 (models ejbStore)
update foo set salary = 5000 + 1000, version = 1+1 where id = 1 and version
= 1
commit
Connection 2 (models ejbStore)
update foo set salary = 5000 + 1000, version = 1+1 where id = 1 and version
= 1
<Fails to update any rows - concurrent change is detected>
rollback?
So it is easy to implement optimistic locking with consistency (bad word
choice) with Oracle, or other databases, but of course it takes some work on
the developers point of view.
Now the Borland and Sybase folks tell us their CMP Implementation handles
version/timestamp or initial state comparison to verify that there have been
no concurrent updates. We have yet to hear from Cedric or other
knowledgable individual about BEA's CMP implementation, but as you can see
from the first example, doing nothing just isn't enough.
Cheers
Jay
-----Original Message-----
From: John Harby [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 29, 2001 5:27 PM
To: [EMAIL PROTECTED]
Subject: Re: To scale or to be correct: that is the question
Here's a link to some official Oracle documentation that covers read locks
(you may have to register):
http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/
a76965/c23cnsis.htm#6321
>From: Tinou Bao <[EMAIL PROTECTED]>
>Reply-To: Tinou Bao <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Subject: Re: To scale or to be correct: that is the question
>Date: Fri, 29 Jun 2001 15:31:39 -0400
>
>----- 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
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
===========================================================================
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".
