FIX: Shared Intent Locks Held After Validating Constraint

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.