FIX: Shared Intent Locks Held After Validating Constraint

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.

To work around this problem, try one of the following:

• Minimize the length of the transaction.
• Use triggers to enforce the referential integrity constraint.

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:For more information, contact your primary support provider.

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.