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]

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

1. an intent-to-update row lock on the row that was modified;
2. a write row lock on the (same) modified row;
3. a shared schema lock on the table to ensure that no other connection can modify the table schema while your transaction is outstanding; and
4. 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.

[Former Member]

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?

[Former Member]

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?

[Former Member]

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.

[Former Member]

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?"