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.

asked 12 Jan '10, 15:34

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

edited 05 Feb '10, 17:16

Volker%20Barth's gravatar image

Volker Barth
31.5k318461676

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?

(12 Jan '10, 20:44) Breck Carter

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.

(13 Jan '10, 14:48) RobertDD

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

(13 Jan '10, 20:04) RobertDD

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

(14 Jan '10, 05:12) 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.

(04 Feb '10, 10:24) Breck Carter

@Breck: Couldn't stop the retagging:)

(05 Feb '10, 17:17) Volker Barth
More comments hidden
showing 5 of 6 show all flat view

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.

permanent link

answered 04 Feb '10, 17:43

RobertDD's gravatar image

RobertDD
489161719
accept rate: 42%

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

(04 Feb '10, 18:52) Glenn Paulley

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

(11 Feb '10, 19:50) RobertDD
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:

×22
×3

question asked: 12 Jan '10, 15:34

question was seen: 1,202 times

last updated: 05 Feb '10, 17:16