I guess I was stating that the "select for update" concept of forcing a "lock" does not exist using that syntax in Sybase. The closest thing would be "select ... holdlock", although I am not sure that has exactly the same effect. Using the keywork "holdlock" has the same effect as using transaction isolation level 3 (serializable).
Using "holdlock" basically acquires a readlock on the row/page/table as appropriate. This read lock is only in place for the duration of the transaction. I don't know the details of "select for update", but I imagine it would be similar. I have heard mention of people using a "select for update" in situations that span transactions (and a relatively great deal of time), but I always thought they were mistaken. The Transact-SQL documentation for Sybase is online and easily accessible. http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug -jim > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]] On > Behalf Of Dain Sundstrom > Sent: Sunday, November 25, 2001 6:57 PM > To: 'James Cook'; [EMAIL PROTECTED] > Subject: RE: [JBoss-dev] what DBMS's support select for update? > > > What is the syntax in Sybase? Or did I miss the point of this email? > > -dain > > > -----Original Message----- > > From: James Cook [mailto:[EMAIL PROTECTED]] > > Sent: Sunday, November 25, 2001 3:50 PM > > To: [EMAIL PROTECTED] > > Subject: RE: [JBoss-dev] what DBMS's support select for update? > > > > > > 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 > > > > _______________________________________________ > 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
