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
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
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.