Postgres also support FOR UPDATE.
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT [ ALL ] } select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ FOR UPDATE [ OF tablename [, ...] ] ]
[ LIMIT { count | ALL } [ { OFFSET | , } start ]]
I've been doing a bit of research. Seems that if you do not want to purchase
the SQL92 standard the best place to look is the Postgres HTML manual, where
for each SQL sentence (such as SELECT) you can see where they comply and
where not to SQL92 standards. They do not mention anything about standard
compliance the FOR UPDATE modifier, that means that it must be included in
the standard.
Could Sybase not be SQL92 fully compliant? There are different ways to
adhere to the standard, but they don't say in which one they fit.
On the other thread, David, I find your point enlightening =) Anyway, in
most systems where updates occur 1/10th times than reads, I find doing
'select for update' a quite serious performance issue. There are
alternatives (optimistic locks, the 'token pattern' for duplicate form
postings) that in most cases would work fine. But I now am of the same
opinion that locking on read could be good in some concrete situations.
Ignacio.
> -----Mensaje original-----
> De: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]En nombre de James
> Cook
> Enviado el: lunes, 26 de noviembre de 2001 3:27
> Para: 'Dain Sundstrom'; [EMAIL PROTECTED]
> Asunto: RE: [JBoss-dev] what DBMS's support select for update?
>
>
> 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
>
>
_______________________________________________
Jboss-development mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/jboss-development