SA 10.0.1.3960 / Win64 + mirroring running on Windows 2003 Server.

When trying to create a foreign key, I get the following error message:

User 'another user' has the row in 'mytable' locked

I know it may happen due to the running out of locks. However, 'mytable' has only ~30 000 records - not that much for a database with 8 k pages. Also, it does work on the same database started in standalone mode. What can I do to fix the problem?

asked 03 Dec '09, 13:51

Dmitri's gravatar image

Dmitri
1.5k41132
accept rate: 11%


Without more details, I believe that this issue is related to the background cleaner process that was introduced in Version 10.

The cleaner process is a background task that performs maintenance tasks, such as reorganizing index pages after a significant number of rows have been deleted. The cleaner process runs only when the server is (largely) idle. However, it does run as a connection (rather than only as a thread) and it acquires shared schema locks on the table(s) that it is modifying.

The issue with DDL statements such as ALTER TABLE ADD FOREIGN KEY is that the connection executing the ALTER TABLE must acquire an exclusive lock on the table, and if it is unable to do so the ALTER TABLE statement fails immediately with the lock error (SQLCODE: -210; SQLSTATE: 42W18) that you have seen. If the ALTER TABLE is attempted while the cleaner holds a shared schema lock on the same table, the ALTER will subsequently fail.

Retrying the operation should succeed.

We are currently working on a fix so that the cleaner process will terminate as soon as any other connection requires exclusive access to the table(s) being processed by the cleaner. That particular change is now undergoing testing. A separate, independent change, available with 10.0.1 build 3985, modifies the error message for SQLSTATE -210 so that the offending process holding the lock (in this case, the cleaner process) is properly identified.

permanent link

answered 03 Dec '09, 14:55

Glenn%20Paulley's gravatar image

Glenn Paulley
10.7k571104
accept rate: 43%

edited 03 Dec '09, 18:38

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:

×113
×12

question asked: 03 Dec '09, 13:51

question was seen: 3,782 times

last updated: 03 Dec '09, 18:38