最小限のロックで大規模なデータセットを更新、挿入、および削除する

SQL Server を使用して、ループを使用して最小限のロックで大規模なデータセットを更新、挿入、および削除する

継続的にロックするシステムでより小さなトランザクションを使用する

特に大規模なデータセットを操作する場合、ロックに問題が発生する可能性がありますが、このようにする必要はありません。可能であれば、トランザクションを細かく分割してください。

以下のコードの一部を見てみましょう。まず、SSMS と 3 つのクエリウィンドウ、およびアクティビティモニターを開き、更新期間を 1 秒に設定します。

最初のウィンドウで、テストに使用するテーブルを作成できます。

これには実行に時間がかかり、いくつかのウィンドウを開く必要があります。

Create test table

CREATE TABLE (ID BIGINT IDENTITY(1,1) CONSTRAINT PK_ID PRIMARY KEY,GUID UNIQUEIDENTIFIER)CREATE INDEX IDX_GUID ON (GUID)

いくつかのレコードを作成する

このコードを実行し、コードを以下に置き換えます。これにより、500 万のランダム GUID が一度に 1 つずつテーブルに挿入されます。これにより、一定の更新と挿入がシミュレートされます。必要に応じて、これをさらに進めることができます。また、1000 行ごとに挿入されたレコード数も表示されるので、追跡できます。

SQL

SET NOCOUNT ONDECLARE @ID BIGINT=1WHILE @ID<5000000 BEGININSERT INTO (GUID) SELECT NEWID()IF @ID%1000=0 PRINT @IDSET @ID=@ID+1END

いくつかのレコードを選択

2 番目のウィンドウを開く場合は、以下のコードを追加します。これにより、20 で始まる 10000 レコードが選択されます。

SQL

SELECT TOP 10000 * FROM WHERE GUID Like '%20'

ループ削除

3 番目のウィンドウを開いて、以下のコードを実行すると、GUID をループして、0 で始まる場所を削除します。

SQL

WHILE EXISTS (SELECT TOP 1 * FROM WHERE GUID LIKE '0%') BEGINDELETE TOP (1000) FROM WHERE GUID LIKE '0%'END

Testing the theory

Once you get above 500000 records in your fist window, run either or both of the statements in windows two and three, and check your activity monitor for what happens.

You should see fewer locks, and the lock that you do get should be for shorter periods of time, not preventing users from carrying on doing the work they need to, although it may slow down to a certain extent.