Sybase does not support "select for update". Standard SQL dictates (and Sybase supports) a "select at serializable" mechanism which will change the isolation level on the transaction to serializable.
Of course, Oracle does not support serializable isolation. -jim > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] On > Behalf Of David Budworth > Sent: Saturday, November 24, 2001 1:22 PM > To: [EMAIL PROTECTED] > Subject: Re: [JBoss-dev] what DBMS's support select for update? > > > I would totally disagree with you on this one. > > select for update is the only way (AFAIK) to ensure DB > consistency in a multi host web environment. > > example: Load balances web servers, client clicks "submit" > twice, sending the request to 2 different web hosts. If > select for update is not used, each host will step on the > other. This is partly why poorly coded ecomerce sites end up > double charging credit cards. > > As for performance, I'm not sure how MySQL implements this, > but in Oracle, you get a performance boost, since any row > that was "select for update" enabled, remains in cache, so > updates to it are quicker, since the row(s) in questions, are > already bound to the session, and no lookup is required. > > It does, however, place a slight resource drain on your DB if > you use it 100% of the time, but rarely make updates. > > But since EJB has no concept of "open read/write" vs. "open > readonly" (unless you deploy the bean twice with different > descriptors), you don't really get much of a choice. > > In a clustered situation you could make sure no two app > servers are using the same bean at the same time, but that's > much more expensive than using select for update. Plus, I > can't imagine any real enterprise application being the only > accessor of the DB. Other apps (ie CRM systems), use select > for update to play nice with eachother and avoid dirty writes. > > It does add some work for the DBA, since they are usually the > only ones that can tell you why your code keeps locking up > (like, server 1 locks on submit, because server2 decided to > select for update and then go into an infinite loop). But > that is far easier than trying to figure out why, every time > you click submit, your data doesn't store, even though you > server.log shows the update happening. > > Not using it would be acceptable if there was only one > process talking to the db. > > Just my opinion. > > -David > > > On Sat, 24 Nov 2001, Ignacio Coloma wrote: > > > MySQL does too. > > > > SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] > > [SQL_BUFFER_RESULT] > > [HIGH_PRIORITY] > > [DISTINCT | DISTINCTROW | ALL] > > select_expression,... > > [INTO {OUTFILE | DUMPFILE} 'file_name' export_options] > > [FROM table_references > > [WHERE where_definition] > > [GROUP BY {unsigned_integer | col_name | formula} > [ASC | DESC], ...] > > [HAVING where_definition] > > [ORDER BY {unsigned_integer | col_name | formula} > [ASC | DESC] ,...] > > [LIMIT [offset,] rows] > > [PROCEDURE procedure_name] > > [FOR UPDATE | LOCK IN SHARE MODE]] > > > > My personal feelings about it: > > (a) IIRC it's SQL-92 standard > > (b) I would never recommend using it, even less in a web-enabled > > system. It degrades performance and you have to be aware about > > administration issues such as row-level locking and autogenerated > > keys. > > > > Of course, it also supports this even with the new > transaction-aware > > table types (InnoDB et al). > > > > Ignacio. > > > > > -----Mensaje original----- > > > De: [EMAIL PROTECTED] > > > [mailto:[EMAIL PROTECTED]]En > nombre de > > > David Jencks Enviado el: sábado, 24 de noviembre de 2001 5:08 > > > Para: [EMAIL PROTECTED] > > > CC: Jboss-User @ Lists . Sourceforge . Net; > Jboss-Development @ Lists . > > > Sourceforge . Net > > > Asunto: Re: [JBoss-dev] what DBMS's support select for update? > > > > > > > > > Firebird/interbase supports select for update. I don't know much > > > about it. The documentation I found says: > > > > > > syntax... at the end of select statement, > > > > > > [FOR UPDATE [OF col [, col...]]] > > > > > > FOR UPDATE specifies columns listed after the SELECT cluase of a > > > DECLARE CURSOR statement that can be updated using a > WHERE CURRENT > > > OF clause. > > > > > > I don't know if the interclient driver supports this. If > it doesn't > > > work yet, I'd want to add something appropriate to the jca-jdbc > > > driver. How do you use this stuff? > > > > > > david jencks > > > \ > > > On 2001.11.23 23:05:05 -0500 Bill Burke wrote: > > > > Oracle does, > > > > > > > > What about Informix, Sybase, Postgres, MySQL, HypersonicSQL, > > > > etc....Who knows the syntax for this on these platforms too? > > > > Thanks in advance. > > > > > > > > > > > > Bill > > > > > > > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> > > > > <HTML><HEAD> <META http-equiv=Content-Type content="text/html; > > > > charset=iso-8859-1"> <META content="MSHTML 5.50.4807.2300" > > > > name=GENERATOR></HEAD> <BODY> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > size=2>Oracle > > > > does,</FONT></SPAN></DIV> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > > > > size=2></FONT></SPAN> </DIV> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > size=2>What about > > > > Informix, > > > > Sybase, Postgres, MySQL, HypersonicSQL, etc....Who > knows the syntax for > > > > this on > > > > these platforms too? Thanks in > advance.</FONT></SPAN></DIV> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > > > > size=2></FONT></SPAN> </DIV> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > > > > size=2></FONT></SPAN> </DIV> > > > > <DIV><SPAN class=750430304-24112001><FONT face=Arial > > > > size=2>Bill</FONT></SPAN></DIV></BODY></HTML> > > > > > > > > > > _______________________________________________ > > > Jboss-development mailing list > > > [EMAIL PROTECTED] > > > https://lists.sourceforge.net/lists/listinfo/jboss-development > > > > > > > > > > > > _______________________________________________ > > Jboss-development mailing list > [EMAIL PROTECTED] > > https://lists.sourceforge.net/lists/listinfo/jboss-development > > _______________________________________________ > Jboss-development mailing list [EMAIL PROTECTED] > https://lists.sourceforge.net/lists/listinfo/jboss-development > _______________________________________________ Jboss-development mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-development
