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
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 126.96.36.1991
asked 25 Mar '14, 12:33
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.
How are you executing the two statements? The semantics are not equivalent: the
SELECT COUNT(*) FROM rowgenerator WITH(UPDLOCK); SELECT * FROM sa_locks();
If there are cursor flags specified when the cursor is opened that request
answered 27 Mar '14, 11:12
Ivan T. Bowman