We have some locking problems with a legacy software that uses Sql Anywhere 18.104.22.16858 with a Delphi Client. In a TQuery there is an sql that is something like this :
The ODBC connection to the database is using Isolation Level 1. The RequestLive property of TQuery is false (so supossedly it aquires read only cursor).
The problem is that sometimes, but not always this query will lock several rows - the result of sa_locks() is the following.
table_name |index_id|lock_class|lock_duration|lock_type|row_identifier Clients | |Row |Position |Read |3976462421.00 Stores | |Row |Position |Read |5606604832.00 DocTypes | |Row |Position |Read |5607915530.00 Invoices | |Row |Position |Read |20767965292.00
Interesting part is that the locked row in Clients table is the one joined as CI in the upper select, while the row in Clients joined as C is not locked.
And this lock prevents anyone from updating the locked rows.
Anyone has an idea what this type of lock is and how it can be avoided
asked 20 Jul '16, 11:28
These are Read locks held to guarantee 'cursor stability', which means you have an open cursor on the position(s) those locks reflect. The key to identifying this as being due to 'cursor stability' is the phantom duration; combined with the knowledge that you are using Isolation level 1 (a.k.a. Repeatable Read).
You will always require cursor stability with any isolation level higher than 0 (Dirty Read) unless you can switch to Snapshot Isolation as an alternative or switch to single user/bulk mode. Using read/only mode might also work but I would have to check on that and that will not fit you actual requirements. Most of which probably won't be of much use to you.
Closing the cursor will clear these; as would a fetch past the end of a forward-only cursor. In many development environments, it is often recommended to commit after each query (assuming your cursors are defined to 'close on commt'); similarily rollbacks can also work. If that is an option I would recommend doing that.
But short of some modification to the application I don't think you will be able to disable these.
Hope something in that is helpful...
answered 20 Jul '16, 13:18
Nick Elson S...