on 2014 Mar 25 12:33 PM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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!
Jason needs our support and best wishes, which he can share with Ivan...
How are you executing the two statements? The semantics are not equivalent: the with(updlock)
will lock every row that is scanned from the table that meets local predicates, whether or not the cursor can be updated. For example, you could specify the following and it would happily acquire locks even though there is no way to do a positioned update through the cursor:
SELECT COUNT(*) FROM rowgenerator WITH(UPDLOCK); SELECT * FROM sa_locks();
The for update by lock
clause indicates that the cursor should be updateable (UPDATE table SET x=1 WHERE CURRENT OF crsrname
). This might not include all of the rows fetched from the table if there are joins or subqueries involved. Further, making the cursor updateable may cause a different cursor type to be used (for example, a keyset cursor is needed to make a query containing a join updateable).
If there are cursor flags specified when the cursor is opened that request READ ONLY
access, then the READ ONLY
"trumps" the FOR UPDATE BY LOCK
. As @Breck said, ideally, there would be an error if there is a mismatch between the statement text and the cursor flags, but it turns out that breaks too many clients that have a mismatch.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
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).
User | Count |
---|---|
52 | |
8 | |
5 | |
5 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.