SELECT .. FOR UPDATE
Poster was emulating ISAM-style reads (ISGTEQ) on Informix Dynamic Server and hit the restriction that SELECT ... FOR UPDATE cannot include ORDER BY (nor can views be used). The workaround offered: run a plain SELECT with ORDER BY, fetch a row, then issue a separate SELECT ... WHERE primary key = value FOR UPDATE to lock just that row. Replies discussed the reason — ORDER BY often builds a temp table, so WHERE CURRENT OF has nothing to update, plus lock volume — with disagreement over whether the restriction is standard SQL or an Informix implementation limit; no definitive answer on that point.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all I'm programming an interface to simulate ISAM access to Informix Dynamic Server, Yes - it ain't easy !!! Now, to simulate an isread( ISGTEQ (Greater than or Equal ), much like a ordinary cursor in Informix 4GL, I need to do a SELECT .. FOR UPDATE. since ISAM record always come sorted using current index, i need to do an ORDER BY on the select. BUT, NO -- SELECT .. FOR UPDATE cannot contain, among others, ORDER BY. A solution to the problem could be to use a view, but here you also cannot use order by. What can this poor soul do ??? Is it STANDARD SQL that you cannot use ORDER BY in SELECT ..FOR UPDATE or is it an Informix "feature" ??? Please respond, as this is an urgent problem !!! Lars Johansson System Consultant lajo@Q8.dk
"Lars Johansson" <lajo@q8.dk> writes:
> Hi all
>
> I'm programming an interface to simulate ISAM access
> to Informix Dynamic Server, Yes - it ain't easy !!!
I'am working on that problem too, but with Informix-SE via ODBC.
> Now, to simulate an isread( ISGTEQ (Greater than or Equal ), much
> like a ordinary cursor in Informix 4GL, I need to do a
> SELECT .. FOR UPDATE. since ISAM record always come sorted
> using current index, i need to do an ORDER BY on the select.
>
> BUT, NO -- SELECT .. FOR UPDATE cannot contain, among others, ORDER BY.
I fount this trick:
1) I perform a SELECT with ORDER BY;
2) I fetch the first row;
3) I perform a SELECT FOR UPDATE (without ORDER BY) selecting
only the row with the same primary key of the row selected
with the first SELECT statement.
SELECT ... FROM table
WHERE greater_than_or_equal_condition
ORDER BY key_fields;
> A solution to the problem could be to use a view,
> but here you also cannot use order by.
I think you cannot perform a SELECT FOR UPDATE using views.
> Is it STANDARD SQL that you cannot use ORDER BY in SELECT ..FOR UPDATE
Yes, it is STANDARD.
--
am
In article <77cg3u$5rf4$1@news-inn.inet.tele.dk>, "Lars Johansson" <lajo@q8.dk> wrote: > Hi all > > I'm programming an interface to simulate ISAM access > to Informix Dynamic Server, Yes - it ain't easy !!! > > Now, to simulate an isread( ISGTEQ (Greater than or Equal ), much > like a ordinary cursor in Informix 4GL, I need to do a > SELECT .. FOR UPDATE. since ISAM record always come sorted > using current index, i need to do an ORDER BY on the select. > > BUT, NO -- SELECT .. FOR UPDATE cannot contain, among others, ORDER BY. > > A solution to the problem could be to use a view, > but here you also cannot use order by. > > What can this poor soul do ??? > > Is it STANDARD SQL that you cannot use ORDER BY in SELECT ..FOR UPDATE > or is it an Informix "feature" ??? > > Please respond, as this is an urgent problem !!! > > Lars Johansson > System Consultant > lajo@Q8.dk > > I couldn't find a reason, why ORDER BY is not allowed in SELECT ... FOR UPDATE, but I haven't my Informix manuals at hand. It seems at the 1st sight to be an Informix "feature", particularly because it will be correct parsed in SQL2 mode of the Big Brother's precompiler. Regards Ch. Danielewicz -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
In article <77de40$v79$1@nnrp1.dejanews.com>, christophd@bigfoot.com writes > >I couldn't find a reason, why ORDER BY is not allowed in SELECT ... FOR >UPDATE, but I haven't my Informix manuals at hand. It seems at the 1st sight I would have though that it would lock all the rows which match the select before it could return the first row of the results set (Since you have ot fetch every row to order them). Hence you hold a lot of locks. SELECT...ORDER BY SELECT...WHERE primary key = value FOR UPDATE would only lock one row at a time. >to be an Informix "feature", particularly because it will be correct parsed >in SQL2 mode of the Big Brother's precompiler. > >Regards > >Ch. Danielewicz > >-----------== Posted via Deja News, The Discussion Network ==---------- >http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own -- David Williams
David Williams wrote: > In article <77de40$v79$1@nnrp1.dejanews.com>, christophd@bigfoot.com > writes > > > >I couldn't find a reason, why ORDER BY is not allowed in SELECT ... FOR > >UPDATE, but I haven't my Informix manuals at hand. It seems at the 1st sight An ORDER BY will often require a temp table, thus the cursor is actually running against the temp table. Therefore there is no "WHERE CURRENT OF" for the cursor, because updating the temp table would not accomplish anything. > I would have though that it would lock all the rows which match the > select before it could return the first row of the results set > (Since you have ot fetch every row to order them). Hence you hold a > lot of locks. Only if you were using Repeatable Read. I don't know what would happen if you used Cursor Stability on a cursor with an Order By that required a temp table. (Hmmm, I feel a test case coming on...) June -- june_t@hotmail.com Grounded in Palo Alto, living on KitKat bars
christophd@bigfoot.com wrote: > In article <77cg3u$5rf4$1@news-inn.inet.tele.dk>, > "Lars Johansson" <lajo@q8.dk> wrote: > > Hi all > > > > I'm programming an interface to simulate ISAM access > > to Informix Dynamic Server, Yes - it ain't easy !!! > > > > Now, to simulate an isread( ISGTEQ (Greater than or Equal ), much > > like a ordinary cursor in Informix 4GL, I need to do a > > SELECT .. FOR UPDATE. since ISAM record always come sorted > > using current index, i need to do an ORDER BY on the select. > > > > BUT, NO -- SELECT .. FOR UPDATE cannot contain, among others, ORDER BY. > > > > A solution to the problem could be to use a view, > > but here you also cannot use order by. > > > > What can this poor soul do ??? > > > > Is it STANDARD SQL that you cannot use ORDER BY in SELECT ..FOR UPDATE > > or is it an Informix "feature" ??? > > > > Please respond, as this is an urgent problem !!! > > > > Lars Johansson > > System Consultant > > lajo@Q8.dk > > > > > > I couldn't find a reason, why ORDER BY is not allowed in SELECT ... FOR > UPDATE, but I haven't my Informix manuals at hand. It seems at the 1st sight > to be an Informix "feature", particularly because it will be correct parsed > in SQL2 mode of the Big Brother's precompiler. > I don't know which "Big Brother" we're talking about here (Big O? Irish Business Machines?) but they might (or rather, they DO have) extensions which are not in the SQL standard. Fact is, I don't think ANY SQL database on the market supports only SQL2, no more and no less (Fact is, such a beast would be rather useless. One nice feature which is NOT in SQL2 is indexes.... No kidding). Anyway, to quote my SQL Reference, the section on ""Updating Cursors". Among the requirements for this is: "I may not specify INSENSITIVE, SCROLL or ORDER BY". Which doesn't mean that I don't think this restriction is silly... Karlsson > Regards > > Ch. Danielewicz > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
In article <XAvrPHAYzom2Ewz4@smooth1.demon.co.uk>, David Williams <djw@smooth1.demon.co.uk> wrote: > In article <77de40$v79$1@nnrp1.dejanews.com>, christophd@bigfoot.com > writes > > > >I couldn't find a reason, why ORDER BY is not allowed in SELECT ... FOR > >UPDATE, but I haven't my Informix manuals at hand. It seems at the 1st sight > > I would have though that it would lock all the rows which match the > select before it could return the first row of the results set > (Since you have ot fetch every row to order them). Hence you hold a > lot of locks. > > SELECT...ORDER BY > SELECT...WHERE primary key = value FOR UPDATE > > would only lock one row at a time. > > David Williams > You use the 'for update of' clause to acquire exclusive row locks, because you want to update some values in a row or delete some rows from the current set. By the way: I would expect in this case all rows locked at the open of your cursor, not at the fetch and remain locked until the end of your transaction. It is true: you must know, what you do and the database costs of such operations can be very high, if you do not take care. The problem was, if and why you are not allowed to sort your current result set and in the matter of fact I still can't find any SQL reasons. I checked this problem both in Informix SQL manuals and SQL standards. It is Informix's SQL forbidden to code 'order by clause' in this case and I suppose there are simply implementation details behind. Besides: I wanted to answer a Lars Johansson's question not to lead philosophical discussions about different databases. About Big O, Irish Business Machines, with or without indexes in SQL2 standard and so on ( 'no kidding' ). Regards Ch. -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own