Hi: I am trying to understand why one would ever want to use pessimistic locking in the server in the case of a shared database (Option A). In the case of WebLogic 6.0, this corresponds to: concurrency-strategy=Exclusive db-is-shared=True For this scenario, the app server needs to do an ejbLoad() for every new transaction, so you are not saving database reads (as you would if db-is-shared=False). And the pessimistic locking in the server limits concurrency. Why would the above be used instead of optimistic locking in the server and a serializable isolation level (Option B)? In the case of WebLogic 6.0, this corresponds to: concurrency-strategy=Database isolation-level=Serializable (or TRANSACTION_READ_COMMITTED_FOR_UPDATE? I found the quote below in the WLS 6.0 docs, but the documentation for the deployment descriptor "isolation-level" doesn't list this as a valid value.) In the case of WebLogic, where optimistic locking in the server means "leave it to the database", is the value of Option A (pessimistic locking in the server) that it reduces the number of SQLException's "ORA-08177: can't serialize access for this transaction" in the case of Oracle? If the RDBMS uses pessimistic locking, does Option A have any advantages? If the app server used optimistic locking in the server as described by Evan and Jonathan, would Option A have any advantages? In general, I'm trying to get a handle on what locking in the server buys me ... and in what scenarios. Thanks. Laurel -----Original Message----- From: Tinou Bao [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 ----- 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".
