Invisible row locks?

[Former Member]

In SQL Anywhere 11:

I am trying to update a row in our database.

1. In ISQL I "select * from table"
2. Right click
3. Edit Row
4. Change a value in a column.

I get the message: "The row could not be updated. User '[User]' has the row in '[Table]' locked. Do you want to change the column values or cancel the update? [Change] [Cancel]"

I select cancel, then run the query "select * from sa_locks() where user_id = '[USER]'" and all user has is a shared schema lock.

How is this user locking me?

Some additional info that might or might not be relevant: 1. when I try and update another row in the same table I am not being blocked by User. 2. User is looking at (but not updating) the row in the table through a view. 3. User and I are both using transaction level 0. 4. User connects through ODBC, using ODBC98 Delphi components.

(This is a follow up of the "Intent lock bug" question I posted up a few days ago, and which I now believe has little or nothing to do with the behavior I see)

EDIT: Due to how this issue is affecting our production environment we also raised a support issue with Sybase. I just got confirmation from their help desk that they were able to reproduce the invisible lock in their offices.

[Breck_Carter]

Is this a controlled environment, or "in the wild" where other users can update and commit at random times outside of your control? The reason I ask is this, SQL Anywhere 11 is widely used and if locking doesn't work properly then there would be a zillion complaints... Are you sure the other user isn't updating and then committing later on?

[Former Member]

I have been able to reproduce this behavior in a controlled environment (where I am both users). I can send you a test app with a few simple instructions so that you will be able to see this behavior for yourself. It is quite reproducable. I do agree with your comment. Obviously other people are not seeing this, so somewhere we must be doing something somewhere noone else is doing in quite the same way. If we can find out what that is and eliminate it we have the issue fixed, which would be awesome because this is a huge problem in our production environment.

[Former Member]

Mr. Carter, I have taken the liberty of forwarding you a test application + instructions to replicate this issue. I hope you can.

[Breck_Carter]

Mr. Carter was my grandfather, I'm Breck, or "hey you!" 🙂

[Breck_Carter]

@RobertDD: I believe you are happy now... if so, can you post an answer describing the solution? If not, let's open it up again.

[VolkerBarth]

@Breck: Couldn't stop the retagging:)

[Former Member · Accepted solution]

Up until now I did all my testing on our production failover cluster. Since I could not get anyone else (Sybase, Breck) to see (or not see) the invisible locks (my report that Sybase was able to reproduce turned out to be incorrect) I decided to make sure I could reproduce this in a different environment myself. I installed a local SQLA 11, same build as production and I did the same test.

Shockingly I saw a row level read lock on my local database! Note that such a read lock explains all loccking behavior I am experiencing!

I then realized that I must not be running in isolation level 0 after all. At the sugestion of Sybase we ended up changing the DSNs on the client machines to specify the isolation level. This worked to eliminate all locking issues we had been experiencing.

The carefull reader realizes that we did not solve the issue of the invisible locks at all. In isolation level 1, on production, following the steps of the test I outlined in the original post, we do not see a row level read lock. It is there, we experience all consequences of it, but it is not visible. I can't reproduce this behavior anywhere else than in our production environment. In all my test situations with local databases I can see the read lock just fine.

[Former Member · Accepted solution]

What build/version of SQL Anywhere 11 are you testing with?

[Former Member · Accepted solution]

I am sorry, I have been swamped with work and only just saw this. We are running build 2355 of SQL Anywhere 11.