Foreign Keys, Blocking, and Update Conflicts

Most databases should make use of foreign keys to enforce referential integrity (RI) wherever possible. However, there is more to this decision than simply deciding to use FK constraints and creating them. There are a number of considerations to address to ensure your database works as smoothly as possible.

This article covers one such consideration that does not receive much publicity: To minimize blocking, you should think carefully about the indexes used to enforce uniqueness on the parent side of those foreign key relationships.

This applies whether you are using locking read committed or the versioning-based read committed snapshot isolation (RCSI). Both can experience blocking when foreign key relationships are checked by the SQL Server engine.

Under snapshot isolation (SI), there is an extra caveat. The same essential issue can lead to unexpected (and arguably illogical) transaction failures due to apparent update conflicts.

This article is in two parts. The first part looks at foreign key blocking under locking read committed and read committed snapshot isolation. The second part covers related update conflicts under snapshot isolation.

1. Blocking Foreign Key Checks

Let’s look first at how index design can affect when blocking occurs due to foreign key checks.

The following demo should be run under read committed isolation. For SQL Server the default is locking read committed; Azure SQL Database uses RCSI as the default. Feel free to choose whichever you like, or run the scripts once for each setting to verify for yourself that the behaviour is the same.

-- Use locking read committed
ALTER DATABASE CURRENT
    SET READ_COMMITTED_SNAPSHOT OFF;

-- Or use row-versioning read committed
ALTER DATABASE CURRENT
    SET READ_COMMITTED_SNAPSHOT ON;

Create two tables connected by a foreign key relationship:

CREATE TABLE dbo.Parent
(
    ParentID integer NOT NULL,
    ParentNaturalKey varchar(10) NOT NULL,
    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]
        PRIMARY KEY (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]
        UNIQUE (ParentNaturalKey)
);

CREATE TABLE dbo.Child 
(
    ChildID integer NOT NULL,
    ChildNaturalKey varchar(10) NOT NULL,
    ChildValue integer NOT NULL,
    ParentID integer NULL,
    
    CONSTRAINT [PK dbo.Child ChildID]
        PRIMARY KEY (ChildID),

    CONSTRAINT [AK dbo.Child ChildNaturalKey]
        UNIQUE (ChildNaturalKey),

    CONSTRAINT [FK dbo.Child to dbo.Parent]
        FOREIGN KEY (ParentID)
            REFERENCES dbo.Parent (ParentID)
);

Add a row to the parent table:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 100;

INSERT dbo.Parent 
(
    ParentID, 
    ParentNaturalKey, 
    ParentValue
) 
VALUES 
(
    @ParentID, 
    @ParentNaturalKey, 
    @ParentValue
);

On a second connection, update the non-key parent table attribute ParentValue inside a transaction, but do not commit it just yet:

DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 200;

BEGIN TRANSACTION;
    UPDATE dbo.Parent 
    SET ParentValue = @ParentValue 
    WHERE ParentID = @ParentID;

Feel free to write the update predicate using the natural key if you prefer, it does not make any difference for our present purposes.

Back on the first connection, attempt to add a child record:

DECLARE
    @ChildID integer = 101,
    @ChildNaturalKey varchar(10) = 'CNK1',
    @ChildValue integer = 999,
    @ParentID integer = 1;
 
INSERT dbo.Child 
(
    ChildID, 
    ChildNaturalKey,
    ChildValue, 
    ParentID
) 
VALUES 
(
    @ChildID, 
    @ChildNaturalKey,
    @ChildValue, 
    @ParentID    
);

This insert statement will block, whether you chose locking or versioning read committed isolation for this test.

Explanation

The execution plan for the child record insert is:

After inserting the new row to the child table, the execution plan checks the foreign key constraint. The check is skipped if the inserted parent id is null (achieved via a ‘pass through’ predicate on the left semi join). In the present case, the added parent id is not null, so the foreign key check is performed.

SQL Server verifies the foreign key constraint by looking for a matching row in the parent table. The engine cannot use row-versioning to do this — it must be sure the data it is checking is the latest committed data, not some old version. The engine ensures this by adding an internal READCOMMITTEDLOCK table hint to the foreign key check on the parent table.

The end result is SQL Server tries to acquire a shared lock on the corresponding row in the parent table, which blocks because the other session holds an incompatible exclusive-mode lock due to the as-yet uncommitted update.

To be clear, the internal locking hint only applies to the foreign key check. The remainder of the plan still uses RCSI, if you chose that implementation of the read committed isolation level.

Avoiding the blocking

Commit or rollback the open transaction in the second session, then reset the test environment:

DROP TABLE IF EXISTS
    dbo.Child, dbo.Parent;

Create the test tables again, but this time instead of accepting the defaults, we choose to make the primary key nonclustered and the unique constraint clustered:

CREATE TABLE dbo.Parent
(
    ParentID integer NOT NULL,
    ParentNaturalKey varchar(10) NOT NULL,
    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]
        PRIMARY KEY NONCLUSTERED (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]
        UNIQUE CLUSTERED (ParentNaturalKey)
);

CREATE TABLE dbo.Child 
(
    ChildID integer NOT NULL,
    ChildNaturalKey varchar(10) NOT NULL,
    ChildValue integer NOT NULL,
    ParentID integer NULL,
    
    CONSTRAINT [PK dbo.Child ChildID]
        PRIMARY KEY NONCLUSTERED (ChildID),

    CONSTRAINT [AK dbo.Child ChildNaturalKey]
        UNIQUE CLUSTERED (ChildNaturalKey),

    CONSTRAINT [FK dbo.Child to dbo.Parent]
        FOREIGN KEY (ParentID)
            REFERENCES dbo.Parent (ParentID)
);

Add a row to the parent table as before:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 100;

INSERT dbo.Parent 
(
    ParentID, 
    ParentNaturalKey, 
    ParentValue
) 
VALUES 
(
    @ParentID, 
    @ParentNaturalKey, 
    @ParentValue
);

In the second session, run the update without committing it again. I am using the natural key this time just for variety — it is not important to the outcome. Use the surrogate key again if you prefer.

DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 200;

BEGIN TRANSACTION 
    UPDATE dbo.Parent 
    SET ParentValue = @ParentValue 
    WHERE ParentNaturalKey = @ParentNaturalKey;

Now run the child insert back on the first session:

DECLARE
    @ChildID integer = 101,
    @ChildNaturalKey varchar(10) = 'CNK1',
    @ChildValue integer = 999,
    @ParentID integer = 1;
 
INSERT dbo.Child 
(
    ChildID, 
    ChildNaturalKey,
    ChildValue, 
    ParentID
) 
VALUES 
(
    @ChildID, 
    @ChildNaturalKey,
    @ChildValue, 
    @ParentID    
);

This time the child insert does not block. This is true whether you are running under locking- or versioning-based read committed isolation. That is not a typo or error: RCSI makes no difference here.

Explanation

The execution plan for the child record insert is slightly different this time:

Everything is the same as before (including the invisible READCOMMITTEDLOCK hint) except the foreign key check now uses the nonclustered unique index enforcing the parent table primary key. In the first test, this index was clustered.

So why do we not get blocking this time?

The as-yet uncommitted parent table update in the second session has an exclusive lock on the clustered index row because the base table is being modified. The change to the ParentValue column does not affect the nonclustered primary key on ParentID, so that row of the nonclustered index is not locked.

The foreign key check can therefore acquire the necessary shared lock on the nonclustered primary key index without contention, and the child table insert succeeds immediately.

When the primary was clustered, the foreign key check needed a shared lock on the same resource (clustered index row) that was exclusively locked by the update statement.

The behaviour may be surprising, but it is not a bug. Giving the foreign key check its own optimized access method avoids logically unnecessary lock contention. There is no need to block the foreign key lookup because the ParentID attribute is not affected by the concurrent update.

2. Avoidable Update Conflicts

If you run the previous tests under the Snapshot Isolation (SI) level, the outcome will be the same. The child row insert blocks when the referenced key is enforced by a clustered index, and does not block when key enforcement uses a nonclustered unique index.

There is one important potential difference when using SI though. Under read committed (locking or RCSI) isolation, the child row insert ultimately succeeds after the update in the second session commits or rolls back. Using SI, there is a risk of a transaction abort due to an apparent update conflict.

This is a little trickier to demonstrate because a snapshot transaction doesn’t begin with the BEGIN TRANSACTION statement — it begins with the first user data access after that point.

The following script sets up the SI demonstration, with an extra dummy table used only to ensure the snapshot transaction has truly begun. It uses the test variation where the referenced primary key is enforced using a unique clustered index (the default):

ALTER DATABASE CURRENT SET ALLOW_SNAPSHOT_ISOLATION ON;
GO
DROP TABLE IF EXISTS
    dbo.Dummy, dbo.Child, dbo.Parent;
GO
CREATE TABLE dbo.Dummy
(
    x integer NULL
);

CREATE TABLE dbo.Parent
(
    ParentID integer NOT NULL,
    ParentNaturalKey varchar(10) NOT NULL,
    ParentValue integer NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID]
        PRIMARY KEY (ParentID),

    CONSTRAINT [AK dbo.Parent ParentNaturalKey]
        UNIQUE (ParentNaturalKey)
);

CREATE TABLE dbo.Child 
(
    ChildID integer NOT NULL,
    ChildNaturalKey varchar(10) NOT NULL,
    ChildValue integer NOT NULL,
    ParentID integer NULL,
    
    CONSTRAINT [PK dbo.Child ChildID]
        PRIMARY KEY (ChildID),

    CONSTRAINT [AK dbo.Child ChildNaturalKey]
        UNIQUE (ChildNaturalKey),

    CONSTRAINT [FK dbo.Child to dbo.Parent]
        FOREIGN KEY (ParentID)
            REFERENCES dbo.Parent (ParentID)
);

Inserting the parent row:

DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 100;

INSERT dbo.Parent 
(
    ParentID, 
    ParentNaturalKey, 
    ParentValue
) 
VALUES 
(
    @ParentID, 
    @ParentNaturalKey, 
    @ParentValue
);

Still in the first session, start the snapshot transaction:

-- Session 1
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;

-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

In the second session (running at any isolation level):

-- Session 2
DECLARE
    @ParentID integer = 1,
    @ParentNaturalKey varchar(10) = 'PNK1',
    @ParentValue integer = 200;

BEGIN TRANSACTION;
    UPDATE dbo.Parent 
    SET ParentValue = @ParentValue 
    WHERE ParentID = @ParentID;

Attempting to insert the child row in the first session blocks as expected:

-- Session 1
DECLARE
    @ChildID integer = 101,
    @ChildNaturalKey varchar(10) = 'CNK1',
    @ChildValue integer = 999,
    @ParentID integer = 1;
 
INSERT dbo.Child 
(
    ChildID, 
    ChildNaturalKey,
    ChildValue, 
    ParentID
) 
VALUES 
(
    @ChildID, 
    @ChildNaturalKey,
    @ChildValue, 
    @ParentID    
);

The difference occurs when we end the transaction in the second session. If we roll it back, the first session’s child row insert completes successfully.

If we instead commit the open transaction:

-- Session 2
COMMIT TRANSACTION;

The first session reports an update conflict and rolls back:

Explanation

This update conflict occurs despite the fact the foreign key being validated was not changed by the second session’s update.

The reason is essentially the same as in the first set of tests. When the clustered index is used for referenced key enforcement, the snapshot transaction encounters a row that has been modified since it started. This is not allowed under snapshot isolation.

When the key is enforced using a nonclustered index, the snapshot transaction only sees the unmodified nonclustered index row, so there is no blocking, and no ‘update conflict’ is detected.

There are many other circumstances where snapshot isolation can report unexpected update conflicts, or other errors. See my prior article for examples.

Conclusions

There are many considerations to take into account when choosing the clustered index for a row-store table. The issues described here are just another factor to evaluate.

This is especially true if you will be using snapshot isolation. No one enjoys an aborted transaction, especially one that is arguably illogical. If you will be using RCSI, the blocking when reading to validate foreign keys may be unexpected, and may lead to deadlocks.

The default for a PRIMARY KEY constraint is to create its supporting index as clustered, unless another index or constraint in the table definition is explicit about being clustered instead. It is a good habit to be explicit about your design intent, so I would encourage you to write CLUSTERED or NONCLUSTERED every time.

Duplicate indexes?

There may be times when you seriously consider, for sound reasons, having a clustered index and nonclustered index with the same key(s).

The intent might be to provide optimal read access for user queries via the clustered index (avoiding key lookups), while also enabling minimally-blocking (and update-conflicting) validation for foreign keys via the compact nonclustered index as shown here.

This is achievable, but there are a couple of snags to watch out for:

Given more than one suitable target index, SQL Server does not provide a way to guarantee which index will be used for foreign key enforcement.

Dan Guzman documented his observations in Secrets of Foreign Key Index Binding, but these may be incomplete, and in any case are undocumented, and so could change.

You can work around this by ensuring there is only one target index at the time the foreign key is created, but it does complicate things, and invite future issues if the foreign key constraint is ever dropped and recreated.
If you use the shorthand foreign key syntax, SQL Server will only bind the constraint to the primary key, whether it is nonclustered or clustered.

The following code snippet demonstrates the latter difference:

CREATE TABLE dbo.Parent
(
    ParentID integer NOT NULL UNIQUE CLUSTERED
);

-- Shorthand (implicit) syntax
-- Fails with error 1773
CREATE TABLE dbo.Child
(
    ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
    ParentID integer NOT NULL 
        REFERENCES dbo.Parent
);

-- Explicit syntax succeeds
CREATE TABLE dbo.Child
(
    ChildID integer NOT NULL PRIMARY KEY NONCLUSTERED,
    ParentID integer NOT NULL 
        REFERENCES dbo.Parent (ParentID)
);

People have become used to largely ignoring read-write conflicts under RCSI and SI. Hopefully this article has given you something extra to think about when implementing the physical design for tables related by a foreign key.

9 thoughts on “Foreign Keys, Blocking, and Update Conflicts”

Alex Friedman says:

June 17, 2021 at 12:06 PM

Very interesting, thanks! Yet another consideration for how to choose a clustered index, sheesh. On heaps I'm assuming it behaves the same as the non-clustered ParentID example, right?

P.S. for some reason it bothers me you didn't index ParentID on the child table, even though it's not relevant here :)
Alex Friedman says:

June 17, 2021 at 12:15 PM

One more thought, after reading Dan Guzman's blog post — since managing the index binding is hacky and can break easily, in extreme cases we might want to even create another column with a unique index to bind on? Maybe even a computed column, hmm.
Paul White says:

June 18, 2021 at 9:48 AM

Hey Alex,

Yes heaps behave like the non-clustered example because there is no clustered index in that case, and a foreign key must reference a unique or primary key constraint. There was an old post by Tony Rogerson highlighting the heap blocking scenario, but I couldn't immediately locate it.

Not indexing the child table as you mention was deliberate to avoid clouding the issue. It's not always appropriate anyway.

Ideally, SQL Server would provide syntax so we could specify the physical index we want to enforce the constraint.
George Walkey says:

July 22, 2021 at 11:34 AM

Would be awesome of someone started blogging on the internals of the Polaris Engine. Really want to know of SQL is dead vs Spark Pools on the high end

Ian Hannah says:

January 23, 2022 at 8:07 PM

We get exactly the same issue when removing rows from a parent table (https://stackoverflow.com/questions/70802876/snapshot-isolation-transaction-aborted-due-to-update-conflict-in-sql-server-due).

This is the script:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parent](
	[ParentID] [int] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
 CONSTRAINT [PK dbo.Parent ParentID] PRIMARY KEY CLUSTERED 
(
	[ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child](
	[ChildID] [int] NOT NULL,
	[ParentID] [int] NULL,
	[UpdateTime] [datetime] NULL,
 CONSTRAINT [PK dbo.Child ChildID] PRIMARY KEY CLUSTERED 
(
	[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK dbo.Child to dbo.Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ParentID])
GO

ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK dbo.Child to dbo.Parent]
GO


CREATE TABLE [dbo].[Dummy](
	[x] [int] NULL
) ON [PRIMARY]
GO

delete from dbo.Child
delete from dbo.Parent
go

-- Insert parent rows
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (1, GETUTCDATE());
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (2, GETUTCDATE());
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (3, GETUTCDATE());

-- Insertion a child row
INSERT into dbo.Child select 101, 2, GetUTCDate()
go


-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

-- Session 1 - part two (3rd bit to run)
delete from dbo.Parent where ParentID = 3



-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION;

UPDATE dbo.Child
SET UpdateTime = GETUTCDATE()
WHERE ParentID = 1

INSERT INTO dbo.Child select 201, 2, GetUTCDate()

-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

Even with the trick of making the PK on the Parent table non-clustered and adding a unique constraint the snapshot update error

Msg 3960, Level 16, State 1, Line 10
Snapshot isolation transaction aborted due to update conflict. You cannot use snapshot isolation to access table 'dbo.Child' directly or indirectly in database 'Example Changed' to update, delete, or insert the row that has been modified or deleted by another transaction. Retry the transaction or change the isolation level for the update/delete statement.

Still happens. Why is this?

January 23, 2022 at 8:15 PM

I run this code to create the database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Parent](
	[ParentID] [int] NOT NULL,
	[UpdateTime] [datetime] NOT NULL,
 CONSTRAINT [PK dbo.Parent ParentID] PRIMARY KEY CLUSTERED 
(
	[ParentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Child](
	[ChildID] [int] NOT NULL,
	[ParentID] [int] NULL,
	[UpdateTime] [datetime] NULL,
 CONSTRAINT [PK dbo.Child ChildID] PRIMARY KEY CLUSTERED 
(
	[ChildID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Child]  WITH CHECK ADD  CONSTRAINT [FK dbo.Child to dbo.Parent] FOREIGN KEY([ParentID])
REFERENCES [dbo].[Parent] ([ParentID])
GO

ALTER TABLE [dbo].[Child] CHECK CONSTRAINT [FK dbo.Child to dbo.Parent]
GO

CREATE TABLE [dbo].[Dummy](
	[x] [int] NULL
) ON [PRIMARY]
GO

Populated the database as follows:

delete from dbo.Child
delete from dbo.Parent
go

-- Insert parent rows
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (1, GETUTCDATE());
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (2, GETUTCDATE());
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (3, GETUTCDATE());

-- Insertion a child row
INSERT into dbo.Child select 101, 2, GetUTCDate()
go

Then run these 2 scripts (as directed):

-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;

-- Session 1 - part two (3rd bit to run)
delete from dbo.Parent where ParentID = 3



-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRANSACTION;

UPDATE dbo.Child
SET UpdateTime = GETUTCDATE()
WHERE ParentID = 1

INSERT INTO dbo.Child select 201, 2, GetUTCDate()

-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

and Session 1 generates the update error as expected:

According to the article this should be eliminated by changing the PK to non-clustered and adding a Unique index but the same error is generated. Doing this should get round the FK issue.

Why is this now the case?

Thanks
Ian

Paul White says:

January 24, 2022 at 6:47 AM

Ian,

You didn't give a full reproduction of the issue, either here or on Stack Overflow, particularly showing how you changed the primary key to nonclustered and added a unique constraint. Nevertheless, I do have an answer for you:

There are two parts to solving the issues you mention. The first part about the primary key and unique constraint is explained as well as I can manage in the article, so I will not repeat that. The second part is similar to your previous Stack Overflow question, in that you have not provided the right index for foreign key checks when parent rows are deleted.

The code below shows both elements, and runs without error:

DROP TABLE IF EXISTS 
    dbo.Dummy,
    dbo.Child, 
    dbo.Parent;
GO
CREATE TABLE dbo.Parent
(
	ParentID integer NOT NULL,
	UpdateTime datetime NOT NULL,

    -- ** Changed to nonclustered
    CONSTRAINT [PK dbo.Parent ParentID] 
        PRIMARY KEY NONCLUSTERED (ParentID)
);
GO 
CREATE TABLE dbo.Child
(
	ChildID integer NOT NULL,
	ParentID integer NULL,
	UpdateTime datetime NULL,

    CONSTRAINT [PK dbo.Child ChildID] 
        PRIMARY KEY CLUSTERED (ChildID),

    CONSTRAINT [FK dbo.Child to dbo.Parent] 
        FOREIGN KEY (ParentID)
        REFERENCES dbo.Parent (ParentID),

    -- ** New nonclustered index to support
    -- FK seek when a Parent row is deleted
    INDEX [IX dbo.Child ParentID] 
        NONCLUSTERED (ParentID)
);
GO
-- ** Now that the child FK has been bound,
-- we add the clustered index
ALTER TABLE dbo.Parent
    ADD CONSTRAINT [UQ dbo.Parent ParentID]
    UNIQUE CLUSTERED (ParentID);
GO
CREATE TABLE dbo.Dummy
(
	x integer NULL
);
GO
 -- Insert parent rows
INSERT dbo.Parent 
    (ParentID, UpdateTime) 
VALUES 
    (1, GETUTCDATE()),
    (2, GETUTCDATE()),
    (3, GETUTCDATE());
 
-- Insert a child row
INSERT dbo.Child
    (ChildID, ParentID, UpdateTime) 
VALUES 
    (101, 2, GETUTCDATE());
GO
-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) 
FROM dbo.Dummy AS D;
 
-- Session 1 - part two (3rd bit to run)
DELETE dbo.Parent 
WHERE ParentID = 3;

COMMIT TRANSACTION;

-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
 
UPDATE dbo.Child
SET UpdateTime = GETUTCDATE()
WHERE ParentID = 1;
 
INSERT dbo.Child 
    (ChildID, ParentID, UpdateTime)
VALUES 
    (201, 2, GETUTCDATE());
 
-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

January 24, 2022 at 6:38 PM

Thanks for the update the FK index – I had missed that!

Following on from your example, I cannot understand why the script below now works. This is slightly different to the previous script:

a) The PK on the Parent table is clustered again.
b) The ChildID references the ParentID in the Parent table now.
c) The PK on the Child table is on ParentID AND ChildID

I am trying to replicate an issue that happens on our system (one or twice a day): https://stackoverflow.com/questions/70802876/snapshot-isolation-transaction-aborted-due-to-update-conflict-in-sql-server-due

So, in the script below, the first part of the second session is inserting rows in the Parent and Child table and Updating a row in the Child table. How does this not block the Parent row removal? The indexes must be updated by the first part of the second session so the FK index on the Child table should be locked?

DROP TABLE IF EXISTS 
    dbo.Dummy,
    dbo.Child, 
    dbo.Parent;
GO
CREATE TABLE dbo.Parent
(
    ParentID integer NOT NULL,
    UpdateTime datetime NOT NULL,

    CONSTRAINT [PK dbo.Parent ParentID] PRIMARY KEY CLUSTERED (ParentID)
);
GO 
CREATE TABLE dbo.Child
(
    ParentID integer NOT NULL,
    ChildID integer NOT NULL,
    UpdateTime datetime NULL,

    CONSTRAINT [PK dbo.Child ParentID ChildID] 
        PRIMARY KEY CLUSTERED (ParentID, ChildID),

    CONSTRAINT [FK dbo.Child to dbo.Parent] 
        FOREIGN KEY (ChildID)
        REFERENCES dbo.Parent (ParentID),

    -- FK seek when a Parent row is deleted
    INDEX [IX dbo.Child ParentID] NONCLUSTERED (ChildID)
);
GO

CREATE TABLE dbo.Dummy
(
    x integer NULL
);
GO
delete from child
delete from parent
go
 -- Insert parent rows
INSERT dbo.Parent (ParentID, UpdateTime) VALUES (1, GETUTCDATE()), (2, GETUTCDATE()), (3, GETUTCDATE()), (4, GETUTCDATE()), (5, GETUTCDATE()), (6, GETUTCDATE());

-- Insert child rows
INSERT dbo.Child (ParentID, ChildID, UpdateTime) VALUES (6, 1, GETUTCDATE());
INSERT dbo.Child (ParentID, ChildID, UpdateTime) VALUES (5, 4, GETUTCDATE());
GO

-- Session 1 - part one (1st bit to run)
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
BEGIN TRANSACTION;
 
-- Ensure snapshot transaction is started
SELECT COUNT_BIG(*) FROM dbo.Dummy AS D;
 
-- Session 1 - part two (3rd bit to run)
DELETE dbo.Parent WHERE ParentID = 3;

COMMIT TRANSACTION;

-- Session 2 - part one (2nd bit to run)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;

UPDATE dbo.Child SET UpdateTime = GETUTCDATE() WHERE ParentID = 5;
 
INSERT dbo.Parent (ParentID, UpdateTime) values (7, GetUTCDate())
INSERT dbo.Child (ParentID, ChildID, UpdateTime) VALUES (7, 2, GETUTCDATE());
 
-- Session 2 - part two (4th bit to run)
COMMIT TRANSACTION;

Paul White says:

January 25, 2022 at 3:48 PM

Ian,

There is no reason to expect blocking in your new example.

Removing the parent row itself is not blocked because locating and deleting row 3 is done using an index key seek. None of the operations in session 2 lock parent row 3 since they use seeks with different target key values, and do not attempt to set any parent ids to 3.

When SQL Server checks for child rows related to parent 3, it uses the [IX dbo.Child ParentID] nonclustered index, so the exact scenario described in the article does not arise.

Even so, the FK check is also a seek on that nonclustered index. It does not touch any rows except where ChildID = 3. Session 2 uses seeks to different key values and does not change any ChildID values to 3, so there is no opportunity for blocking or write conflict there either.

I hope that answers your question.

I no longer answer questions on Stack Overflow, but you are welcome to post any further questions on https://dba.stackexchange.com, which is a site I do use. That's not a promise I will answer any questions you post, but I'm also not the only person there that knows enough about these things to answer you. No one will likely want to spend the time needed to solve every aspect of your complex production problem, so try to make any questions you ask focussed on a single clear issue with reproducible code as you did here.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Foreign Keys, Blocking, and Update Conflicts

1. Blocking Foreign Key Checks

Explanation

Avoiding the blocking

Explanation

2. Avoidable Update Conflicts

Explanation

Conclusions

Duplicate indexes?

9 thoughts on “Foreign Keys, Blocking, and Update Conflicts”

Leave a Reply