In SQL Anywhere 11:
I am trying to update a row in our database.
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.
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.
answered 04 Feb '10, 17:43