on 2009 Dec 03 1:51 PM
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?
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
70 | |
10 | |
9 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.