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. |
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?
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.
Mr. Carter, I have taken the liberty of forwarding you a test application + instructions to replicate this issue. I hope you can.
Mr. Carter was my grandfather, I'm Breck, or "hey you!" :)
@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.
@Breck: Couldn't stop the retagging:)