If done some tests and first tried: select * from Table where id=1665 for update by lock here no row lock is set and I can still update that row Second try: select * from table with(updlock) where id=1665 works as expected, the row lock is set and I can't update that row until the transaction finishes I thought both syntaxes to be equivalent, or have I done anything wrong in the first statement? I am using SQLA 16.0.0.1761 asked 25 Mar '14, 12:33 Martin |
The SELECT Help says "FOR UPDATE or FOR READ ONLY clause These clauses specify whether updates are allowed through a cursor opened on the query..." If your SELECT isn't used in the context of a cursor, that might explain why the FOR UPDATE is ignored. In a perfect world, it would be a syntax error, but this would not be the only situation in SQL Anywhere where "does not apply" means "silently ignore" rather than "SQLCODE -WHATEVER". OTOH the WITH applies to the table in the FROM clause, unlike the FOR UPDATE which applies to the SELECT as a whole, so that's probably why it works as you desire. It is a good question... many have run afoul of that difference. answered 25 Mar '14, 13:55 Breck Carter Thanks, great explanation!
(26 Mar '14, 04:24)
Martin
2
FWIW, Jason has recently published an older Glenn posting that also deals with these features: From the Archives: Customizing SQL Requests With Hints BTW: I still miss Glenn's great blog, so a big thanks to Jason for the re-publishing effort!
(28 Mar '14, 05:46)
Volker Barth
Replies hidden
1
Jason needs our support and best wishes, which he can share with Ivan...
(28 Mar '14, 15:25)
Breck Carter
|
How are you executing the two statements? The semantics are not equivalent: the SELECT COUNT(*) FROM rowgenerator WITH(UPDLOCK); SELECT * FROM sa_locks(); The If there are cursor flags specified when the cursor is opened that request answered 27 Mar '14, 11:12 Ivan T. Bowman 1
The existence of cursors when no explicit FOR loop or DECLARE or FETCH statements exist, has befuddled many ( well, me :) Does a cursor always exist for every SELECT no matter what the context? Say, a single-row SELECT INTO @local_variables inside a stored procedure? Does a cursor always exist when a result set is returned to a client application?
(27 Mar '14, 15:26)
Breck Carter
Replies hidden
Thanks for this technical insight
(28 Mar '14, 06:46)
Martin
2
There is always something that server considers a "cursor" when executing any DML statement (insert, update, delete, select, merge, load table). These don't necessarily translate into a cursor name that you could use in a positioned update or see in dbo.sa_list_cursors(). As far as I know, any time there are result sets returned to a client application there would be a named cursor associated with it (and I expect that to be listed in sa_list_cursors while it exists).
(28 Mar '14, 13:10)
Ivan T. Bowman
|