The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

Hi,

I am trying to use intent locks within a transaction over multiple sessions. The documentation seems to say that one intent lock does allow other sessions to read the record but not to create another intent lock. However when I try that, all intent locks seem to be successful.

In session1 (within a transaction) I create an intent lock by either:

select id, c from tab holdlock where id = 1

or

select id, c from tab where id = 1 for update by lock

This does create a lock, updating the record does block, but when I issue the same select statements in session2 (also in a transaction) to get (or wait for) an intent lock, the lock also seems to be created for session2, the select returns immediately, i would expect the second select to block until the first session transaction is ended.

I am using sybase ianywhere 11

Any idea what I am doing wrong or how to get a blocking state for the second session?

Rob Gansevles

asked 13 Oct '10, 12:18

Rob%20Gansevles's gravatar image

Rob Gansevles
21112
accept rate: 0%

edited 05 Jul '13, 21:56

Mark%20Culp's gravatar image

Mark Culp
23.2k9132273

1

Ahhhh, locking... almost as complicated as cursors, and changing almost as fast :)

(13 Oct '10, 14:22) Breck Carter

What version and build number of SQL Anywhere 11 are you running?

(13 Oct '10, 14:32) Glenn Paulley

HOLDLOCK does not acquire an intent row lock - the WITH( UPDLOCK ) table hint does. HOLDLOCK is equivalent to running the SELECT statement at isolation level 3.

permanent link

answered 13 Oct '10, 15:14

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

By the way, the HOLDLOCK syntax you are using is Transact-SQL. You will need to use WITH() syntax for the table hint if you are attempting to specify UPDLOCK.

(13 Oct '10, 18:56) Glenn Paulley

Thanks, with(updlock) is what I needed.

Rob

(15 Oct '10, 09:31) Rob Gansevles
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:

×137
×2

question asked: 13 Oct '10, 12:18

question was seen: 1,082 times

last updated: 05 Jul '13, 21:56