Microsoft KB Archive/189098

= FIX: Shared Intent Locks Held After Validating Constraint =

Article ID: 189098

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q189098



BUG #: 18118 (SQLBUG_65)



SYMPTOMS
When modifying a table that either contains a foreign key constraint or is referenced by a foreign key constraint on another table, the shared intent locks that are acquired during the constraint validation are not released until the transaction completes. At the default READ COMMITTED transaction isolation level, the locks should be released after the statement completes, not when the transaction completes.



WORKAROUND
To work around this problem, try one of the following:
 * Minimize the length of the transaction.
 * Use triggers to enforce the referential integrity constraint.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.



MORE INFORMATION
The tables on which you will see the shared intent locks will differ for each data modification statement, as follows:
 * INSERT - A shared intent lock will be held on each table referenced by a foreign key constraint.
 * DELETE - A shared intent lock will be held on each table with a constraint referencing the current table.
 * UPDATE - A shared intent lock will be held on any table that contains a foreign key constraint referencing an updated column.

When working at a higher transaction isolation level, such as REPEATABLE READ or SERIALIZABLE, the shared intent locks should be held until the transaction completes. However, at the default READ COMMITTED isolation level, holding the locks can cause undesirable concurrency issues.

If the data modifications are occurring on tables with a 1:1 relationship, the normal mode of operation would require the shared intent lock to be upgraded to an exclusive intent lock as the modification is performed on the other table. The concurrency issue will be most noticeable on systems where much of the modification is being done on the child table in a 1:many relationship, where the lock on the parent table would not normally need to be held for further operations within the transaction.

To see the locks that are held by a process, use the sp_lock system stored procedure, or use the Current Activity window in SQL Enterprise Manager.

Additional query words: sp sp5SH_INT block deadlock

Keywords: kbbug kbfix KB189098

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.