FIX: Shared Intent Locks Held After Validating Constraint

ID: Q189098

The information in this article applies to:

Microsoft SQL Server version 6.5

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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 : SSrvDRI SSrvLock SSrvTran_SQL kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug