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

asked 20 Jul '16, 11:28

Niko's gravatar image

Niko
46115
accept rate: 0%

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.

(21 Jul '16, 14:25) Breck Carter

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

permanent link

answered 20 Jul '16, 13:18

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k35107
accept rate: 32%

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.

(21 Jul '16, 02:57) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×21

question asked: 20 Jul '16, 11:28

question was seen: 1,913 times

last updated: 21 Jul '16, 14:31