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:
Questions: 1. Where did these intent locks come from? I did not ask for an intent lock in my query.
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
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.
answered 08 Jan '10, 01:36
Your sa_locks() output looks normal for the operations you've just performed. You have:
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.
answered 09 Jan '10, 15:44