Locking problem in SQL Anywhere 11

[Former Member]

Hello,

We have some locking problems with a legacy software that uses Sql Anywhere 11.0.1.3158 with a Delphi Client. In a TQuery there is an sql that is something like this :

select .... from Invoices DI join Clients CI on CI.N = if DI.InvType = 1 then DI.ContactN else DI.Client endif and DI.N= 123456 join Clients C on C.N = if DI.InvType = 0 then DI.ContactN else DI.Client endif join Stores S on DI.Store = S.N join Users U on DI.UserN = U.UN join PayType PTP on DI.PayTypeN = PTP."Type" join DocTypes DT on DI.Kind = DT.N

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

[Breck_Carter]

Can you change the application code?

If the answer is "yes" then proceed with the next question, else exit this comment.

Is it possible for a user to regain control of the mouse and keyboard after starting an application process that obtains those locks?

If the answer is "yes" then a serious application design flaw may exist, wherein one connection can start a long-running (never-ending?) database transaction.

...to be continued, depending on your answers.

[Former Member · Accepted solution]

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

[VolkerBarth · Accepted solution]

Just to add:

You can of course adapt the isolation level a particular query is using via table hints, such as

...
   from Invoices DI join Clients CI with (readuncommitted)
     on ...

Note, I can't tell whether that would fit your requirements.