Intent lock bug?

[Former Member]

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:

1. Have a table ready with a few records in it. I'll call it "Test" from here on.

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

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

4. In ISQL1 run "select * from Test". Run the query in ISQL2: 1 schema lock on the table.

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

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

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

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

[Former Member]

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.

[Former Member]

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.

[VolkerBarth]

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.

[VolkerBarth]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

I have edited the issue to include more info