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's gravatar image

Martin
9.0k130169257
accept rate: 14%


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.

permanent link

answered 25 Mar '14, 13:55

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 25 Mar '14, 13:57

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...

alt text

(28 Mar '14, 15:25) Breck Carter

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.

permanent link

answered 27 Mar '14, 11:12

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
2.8k22732
accept rate: 39%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21

question asked: 25 Mar '14, 12:33

question was seen: 7,843 times

last updated: 28 Mar '14, 15:25