Intent lock bug?

We have recently upgraded our database from SQL Anywhere 5.5 to SQL Anywhere 11. Our users use applications written in Delphi to access the data in the database through ODBC. Needless to say, we are dealing with legacy code.

Since the changeover we have been encountering many locking issues that we did not use to have. These locking issues all revolve around intent locks that somehow get set and then don't disappear.

This recreates/illustrates the problem step by step using only Sybase tools:

Have a table ready with a few records in it. I'll call it "Test" from here on.
Open an Interactive SQL session (I'll call it ISQL1 from here on) under a user_id that only has this connection to the database.
Open a second Interactive SQL session (ISQL2) and run query "select * from sa_locks() where user_id = 'ISQL1_user_id'" (replace the ISQL1_user_id with the user used to connect to ISQL1)
In ISQL1 run "select * from Test". Run the query in ISQL2: 1 schema lock on the table.
In ISQL1, right click a row in the result set and select "edit row". Modify a value in the row. Now browse away from that row (for instance by clicking on the next record)
In ISQL2 run the query: you will see intent locks. I most often will see both a table and a row intent lock, but I've seen just a table one.

Questions: 1. Where did these intent locks come from? I did not ask for an intent lock in my query.

Why are they still here. Shouldn't the lock duration be until the end of a transaction. I should not be in a transaction anymore?
How do I get rid of them? I can actually re-execute the "select * from Test" query and still not loose that intent lock.

EDIT: I followed the exact steps above, except before step 2, in ISQL1 I chekc my isolation level by running: SELECT CONNECTION_PROPERTY('isolation_level'); which returns 0. I also check my locks for user Robert, and there aren't any. After step 5 I again run in ISQL1 SELECT CONNECTION_PROPERTY('isolation_level') to check my isolation level which again returns 0.

Here's the output of sa_locks in step 6 (and remember, I am not even looking at table TestRobert anymore in ISQL1, or anywhere else, as I just checked my isolation level)

conn_name,conn_id,user_id,table_type,creator,table_name,index_id,lock_class,lock_duration,lock_type SQL_DBC_1f738650,20540,robert,BASE,NTR,TestRobert,,Row,Transaction,Intent SQL_DBC_1f738650,20540,robert,BASE,NTR,TestRobert,,Row,Transaction,Write SQL_DBC_1f738650,20540,robert,BASE,NTR,TestRobert,,Schema,Transaction,Shared SQL_DBC_1f738650,20540,robert,BASE,NTR,TestRobert,,Table,Transaction,Intent

bug

asked 07 Jan '10, 18:01

RobertDD
489●16●17●19
accept rate: 42%

edited 08 Jan '10, 20:26

flat view

2 Answers:

active answers oldest answers newest answers popular answers

Intent row locks were introduced in Version 10. An intent lock embodies the intent of an application to modify a row of table, while permitting other read transactions to be able to read the row at any isolation level (0-3). Once an application actually modifies the row, the lock is "upgraded" from an intent lock to a write lock, which will prevent other read transactions at isolation levels greater than 0 from being able to acquire a read lock on that row.

Ordinarily, row locks are given up at the end of a transaction (COMMIT or ROLLBACK). WITH HOLD cursors are an exception.

I'm pretty sure DBISQL uses WITH HOLD cursors which is why you continue to see locks being held even after a COMMIT/ROLLBACK. DBISQL is sometimes a poor choice of tool to use in diagnosing locking issues because of all of the requests that DBISQL issues underneath the covers to maintain the UI. DBISQLC is slightly better, I think, but there remains the possibility of additional requests and/or changes to behaviour that can be difficult to diagnose.

Also - don't confuse a schema lock from a row lock. They are two completely different things. A schema lock (typically shared) is acquired by a connection once a table is referenced - it prevents modifications to the schema of this table while the transaction is still in-doubt. Intent row locks signify update intent; one mechanism to acquire them is using

SELECT ..... FOR UPDATE BY LOCK

which will prevent other connections from being able to modify those rows after this connection has FETCHed them.

permanent link

answered 08 Jan '10, 01:36

Glenn Paulley
10.8k●5●77●106
accept rate: 43%

while I've used ISQL to make the problem clear and easy to reproduce, it is not the way we access data in our production environment. In production we connect through an ODBC connection. I certainly do not use "WITH HOLD" anywhere in our applications, and all users access the database in isolation level 0 (again a legacy decission) but I see the same persistent intent locks as I have described.

I do not know how to try and troubleshoot/reproduce this in DBISQLC as that program does not allow an editable data set.

(08 Jan '10, 14:10) RobertDD

RobertDD, you might use 1 DBISQLC instance to run update statements against you table, and another one to select from the same table. In order to notice locks, you might have to assure that DBISQLC does not commit after each statement. That option (i.e. "auto_commit") can be set under the "options" tab. You may as well check whether your ODBC connections use autocommit or not.

(08 Jan '10, 15:30) Volker Barth

...To add: Then you might call "commit" eventually in the DBISQLC 1 instance to see if the intent locks go away.

(08 Jan '10, 15:32) Volker Barth

Readers at isolation level 0 are never blocked by INTENT or WRITE row locks - only explicit attempts to acquire an INTENT row lock, or an actual modification of an existing row, will cause an isolation level 0 transaction to block.

Perhaps if you posted your sa_locks() output we might be able to comment on what you are seeing.

(08 Jan '10, 15:35) Glenn Paulley

That is what I thought, Glenn (iso level 0 not putting intent locks on). I am working on this right now and will post SA_Locks output shortly.

As a direct result of your answer on how ISQL works, I do want to make sure I am not using what I see in ISQL to describe what I am seeing in my Delphi apps if it is actually not the same phenomenon (sometimes two things that look alike might not actually be the same thing).

(08 Jan '10, 19:42) RobertDD

Volker, the problem I am having is that I am not using update statements but live datasets. This would not be my choice, but the choice was made long ago, and implemented/integrated in too many places to be changing things now. I can actually see that updating records through update statements works just fine, lock-wise.

(08 Jan '10, 19:42) RobertDD

I have edited the issue to include more info

(08 Jan '10, 20:27) RobertDD

More comments hidden

showing 5 of 7 show all flat view

Your sa_locks() output looks normal for the operations you've just performed. You have:

an intent-to-update row lock on the row that was modified;
a write row lock on the (same) modified row;
a shared schema lock on the table to ensure that no other connection can modify the table schema while your transaction is outstanding; and
an intent-to-write table lock, indicating that at least one of the rows of the table have been modified by this connection. This lock prevents any other connection from locking the entire table with a LOCK TABLE IN [ SHARED | EXCLUSIVE ] MODE statement, which is required, for example, for materialized view maintenance in V11 servers.

So - nothing out of the ordinary here. We will need to more closely follow precisely what your application is doing to determine why you are seeing apparent differences in locking behaviour.

permanent link

answered 09 Jan '10, 15:44

Glenn Paulley
10.8k●5●77●106
accept rate: 43%

Are you sure that what I am seeing is correct? I have no transaction going, I am running in isolation level 0 and the last query I executed is "SELECT CONNECTION_PROPERTY('isolation_level')"... Aren't the intent and write locks supposed to be gone by now/never set in the first place?

(09 Jan '10, 19:18) RobertDD

I am also not entirely sure what you mean by the text behind 4. Are you saying that intent locks on tables are set to indicate that a row has been modified? That is what I am reading, but it is not one of the three reasons listed in the help on how you can obtain an intent lock (plus it seems counter intuitive)? However, it is the table intent locks that are plaguing us in our Delphi applications. Is there anywhere where I can read up on this functionality?

(09 Jan '10, 19:25) RobertDD

Yes, an intent table lock is acquired once a connection acquires a write row lock. However, I don't believe intent table locks are the problem - multiple intent table locks do not conflict with each other. Intent table locks conflict with shared and exclusive table locks, which are acquired using LOCK TABLE statements which I doubt you have in your application.

Both intent row locks, and intent table locks, once acquired are not released until COMMIT or ROLLBACK.

(11 Jan '10, 14:46) Glenn Paulley

Thank you all, but especially Mr. Paulley, so much for explaining all this. I still have an issue, but I now believe it has little or nothing to do with what I described in here, so I posted a follow up "Invisible row locks?"

(12 Jan '10, 15:37) RobertDD

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

bug ×43

question asked: 07 Jan '10, 18:01

question was seen: 3,065 times

last updated: 09 Jan '10, 15:44

SAP SQL Anywhere

Intent lock bug?

Follow this question

Related questions