Drop Table Foreign Key Issue Easy Resolution
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.
woodplarriving1938.blogspot.com
Source: https://sqlperformance.com/2021/06/sql-performance/foreign-keys-blocking-update-conflicts
0 Response to "Drop Table Foreign Key Issue Easy Resolution"
Post a Comment