Sqlサーバー

外部キーのドロップに時間がかかるのはなぜですか？



Why Does Dropping Foreign Keys Take Long



解決：

制約を削除するには、変更中に他のユーザーがテーブルを照会するのをブロックするSch-M（スキーマ変更）ロックが必要です。あなたはおそらくそのロックを取得するのを待っており、そのテーブルに対して現在実行中のすべてのクエリが終了するまで待たなければなりません。
実行中のクエリには、テーブルにSch-S（Schema Stability）ロックがあり、そのロックはSch-Mロックと互換性がありません。

ロックモードから、スキーマロック



データベースエンジンは、列の追加やテーブルの削除など、テーブルデータ定義言語（DDL）操作中にスキーマ変更（Sch-M）ロックを使用します。保持されている間、Sch-Mロックはテーブルへの同時アクセスを防ぎます。これは、Sch-Mロックが、ロックが解除されるまですべての外部操作をブロックすることを意味します。

テーブルの切り捨てなど、一部のデータ操作言語（DML）操作では、Sch-Mロックを使用して、同時操作による影響を受けるテーブルへのアクセスを防止します。



データベースエンジンは、クエリをコンパイルおよび実行するときにスキーマ安定性（Sch-S）ロックを使用します。 Sch-Sロックは、排他（X）ロックを含むトランザクションロックをブロックしません。したがって、テーブルにXロックがあるトランザクションを含む他のトランザクションは、クエリのコンパイル中も引き続き実行されます。ただし、同時DDL操作、およびSch-Mロックを取得する同時DML操作は、テーブルでは実行できません。


例を紹介しますので、なぜ時間がかかったのかがわかります。このテスト用に空のデータベースを作成します。

CREATE DATABASE [TestFK] GO
2つのテーブルを作成します。
 
 
 
USE [TestFK] GO CREATE TABLE dbo。[Address]（ADDRESSID INT NOT NULL IDENTITY（1,1）PRIMARY KEY、Address1 VARCHAR（50）、City VARCHAR（50）、[State] VARCHAR（10）、ZIP VARCHAR（10 ））; GO CREATE TABLE dbo.Person（PersonID INT NOT NULL IDENTITY（1,1）PRIMARY KEY、LastName VARCHAR（50）NOT NULL、FirstName VARCHAR（50）、AddressID INT）;行く
Personテーブルに外部キー制約を作成します。
 
USE [TestFK] GO ALTER TABLE dbo.Person ADD CONSTRAINT FK_Person_AddressID FOREIGN KEY（AddressID）REFERENCES dbo.Address（AddressID）GO
いくつかのデータを両方のテーブルに挿入します。
 
USE [TestFK] GO INSERT dbo.Address（Address1、City、[State]、Zip）SELECT '123 Easy St'、 'Austin'、 'TX'、 '78701' UNION SELECT '456 Lakeview'、 'Sunrise Beach'、 'TX'、 '78643' GO INSERT dbo.Person（LastName、FirstName、AddressID）SELECT'Smith '、' John '、1 UNION SELECT'Smith'、 'Mary'、1 UNION SELECT'Jones '、' Max '、 2 GO
新しいクエリウィンドウを開いてこれを実行します（クエリが完了したらウィンドウを閉じないでください）。
 
USE [TestFK] GO BEGIN TRAN INSERT dbo.Person（LastName、FirstName、AddressID）SELECT'Smith1 '、' John1 '、1 UNION SELECT'Smith1'、 'Mary1'、1 UNION SELECT'Jones1 '、' Max1 '、2
別のクエリウィンドウを開き、これを実行します。
 
USE [TestFK] GO ALTER TABLE dbo.person DROP CONSTRAINT FK_Person_AddressID
ドロップ制約が実行（待機）し続け、クエリを実行して、実行時間が長くなっている理由と、待機しているロックを確認します。
 
SELECT * FROM sys.dm_os_waiting_tasks WHEREblocking_session_idはNULLではありません。
挿入操作をコミットすると、dropステートメントが必要なロックを取得できるため、drop制約はすぐに完了します。
 
あなたの場合、必要なロックを取得するためのドロップ制約を防ぐ互換性のあるロックを保持しているセッションがないことを確認する必要があります。