Jeśli dobrze rozumiem żądanie, celem jest usunięcie partii wierszy, a jednocześnie operacje DML występują w wierszach w całej tabeli. Celem jest usunięcie partii; jeśli jednak wszystkie leżące poniżej wiersze mieszczące się w zakresie określonym przez tę partię są zablokowane, musimy pominąć tę partię i przejść do następnej partii. Następnie musimy powrócić do partii, które nie zostały wcześniej usunięte, i ponowić próbę naszej oryginalnej logiki usuwania. Musimy powtarzać ten cykl, aż wszystkie wymagane partie wierszy zostaną usunięte.
Jak już wspomniano, uzasadnione jest użycie podpowiedzi PRZECZYTAJ WSTĘP i poziomu izolacji PRZECZYTAJ ZAANGAŻOWANY (domyślny), aby pominąć zakresy przeszłości, które mogą zawierać zablokowane wiersze. Pójdę o krok dalej i zalecę użycie SERIALIZABLE poziomu izolacji i skubanie.
SQL Server używa blokad zakresu klucza do ochrony zakresu wierszy domyślnie zawartych w zestawie rekordów odczytywanym przez instrukcję Transact-SQL podczas korzystania z poziomu izolacji transakcji możliwego do serializacji ... więcej informacji tutaj:
https://technet.microsoft.com /en-US/library/ms191272(v=SQL.105).aspx
W przypadku usuwania skubania naszym celem jest wyodrębnienie zakresu wierszy i dopilnowanie, aby żadne wiersze nie wystąpiły podczas ich usuwania, to znaczy, nie chcemy odczytywania fantomów ani wstawiania. Szeregowy poziom izolacji ma rozwiązać ten problem.
Zanim zademonstruję swoje rozwiązanie, chciałbym dodać, że ani nie zalecam przełączania domyślnego poziomu izolacji bazy danych na SERIALIZABLE, ani nie zalecam, aby moje rozwiązanie było najlepsze. Chcę tylko to przedstawić i przekonać się, dokąd możemy się udać.
Kilka notatek dotyczących utrzymania domu:
- Używana przeze mnie wersja SQL Server to Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
- Moja testowa baza danych używa modelu odzyskiwania FULL
Aby rozpocząć eksperyment, utworzę testową bazę danych, przykładową tabelę i wypełnię tabelę 2 000 000 wierszy.
USE [master];
GO
SET NOCOUNT ON;
IF DATABASEPROPERTYEX (N'test', N'Version') > 0
BEGIN
ALTER DATABASE [test] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
DROP DATABASE [test];
END
GO
-- Create the test database
CREATE DATABASE [test];
GO
-- Set the recovery model to FULL
ALTER DATABASE [test] SET RECOVERY FULL;
-- Create a FULL database backup
-- in order to ensure we are in fact using
-- the FULL recovery model
-- I pipe it to dev null for simplicity
BACKUP DATABASE [test]
TO DISK = N'nul';
GO
USE [test];
GO
-- Create our table
IF OBJECT_ID('dbo.tbl','U') IS NOT NULL
BEGIN
DROP TABLE dbo.tbl;
END;
CREATE TABLE dbo.tbl
(
c1 BIGINT IDENTITY (1,1) NOT NULL
, c2 INT NOT NULL
) ON [PRIMARY];
GO
-- Insert 2,000,000 rows
INSERT INTO dbo.tbl
SELECT TOP 2000
number
FROM
master..spt_values
ORDER BY
number
GO 1000
W tym momencie będziemy potrzebować jednego lub więcej indeksów, na które mogą działać mechanizmy blokujące SERIALIZABLE poziom izolacji.
-- Add a clustered index
CREATE UNIQUE CLUSTERED INDEX CIX_tbl_c1
ON dbo.tbl (c1);
GO
-- Add a non-clustered index
CREATE NONCLUSTERED INDEX IX_tbl_c2
ON dbo.tbl (c2);
GO
Teraz sprawdźmy, czy utworzono nasze 2 000 000 wierszy
SELECT
COUNT(*)
FROM
tbl;
Mamy więc naszą bazę danych, tabelę, indeksy i wiersze. Ustawmy więc eksperyment usuwania skubań. Najpierw musimy zdecydować, jak najlepiej stworzyć typowy mechanizm usuwania skubania.
DECLARE
@BatchSize INT = 100
, @LowestValue BIGINT = 20000
, @HighestValue BIGINT = 20010
, @DeletedRowsCount BIGINT = 0
, @RowCount BIGINT = 1;
SET NOCOUNT ON;
GO
WHILE @DeletedRowsCount < ( @HighestValue - @LowestValue )
BEGIN
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
DELETE
FROM
dbo.tbl
WHERE
c1 IN (
SELECT TOP (@BatchSize)
c1
FROM
dbo.tbl
WHERE
c1 BETWEEN @LowestValue AND @HighestValue
ORDER BY
c1
);
SET @RowCount = ROWCOUNT_BIG();
COMMIT TRANSACTION;
SET @DeletedRowsCount += @RowCount;
WAITFOR DELAY '000:00:00.025';
CHECKPOINT;
END;
Jak widać, umieściłem jawną transakcję w pętli while. Jeśli chcesz ograniczyć opróżnianie logów, umieść go poza pętlą. Ponadto, ponieważ jesteśmy w modelu odzyskiwania PEŁNYM, możesz częściej tworzyć kopie zapasowe dziennika transakcji podczas wykonywania operacji usuwania skubania, aby zapobiec nadmiernemu wzrostowi dziennika transakcji.
Tak więc mam kilka celów z tym ustawieniem. Najpierw chcę moje zamki z kluczem; więc staram się, aby partie były jak najmniejsze. Nie chcę również negatywnie wpływać na współbieżność na moim „gigantycznym” stole; więc chcę wziąć moje zamki i zostawić je tak szybko, jak to możliwe. Dlatego zalecam, aby twoje partie były małe.
Teraz chcę podać bardzo krótki przykład tej procedury usuwania. Musimy otworzyć nowe okno w SSMS i usunąć jeden wiersz z naszej tabeli. Zrobię to w ramach transakcji niejawnej przy użyciu domyślnego poziomu izolacji PRZECZYTAJ ZOBOWIĄZANIE.
DELETE FROM
dbo.tbl
WHERE
c1 = 20005;
Czy ten wiersz rzeczywiście został usunięty?
SELECT
c1
FROM
dbo.tbl
WHERE
c1 BETWEEN 20000 AND 20010;
Tak, zostało usunięte.
Teraz, aby zobaczyć nasze zamki, otwórzmy nowe okno w SSMS i dodajmy fragment kodu lub dwa. Używam sp_whoisactive Adama Mechanica, który można znaleźć tutaj: sp_whoisactive
SELECT
DB_NAME(resource_database_id) AS DatabaseName
, resource_type
, request_mode
FROM
sys.dm_tran_locks
WHERE
DB_NAME(resource_database_id) = 'test'
AND resource_type = 'KEY'
ORDER BY
request_mode;
-- Our insert
sp_lock 55;
-- Our deletions
sp_lock 52;
-- Our active sessions
sp_whoisactive;
Teraz jesteśmy gotowi do rozpoczęcia. W nowym oknie SSMS zacznijmy wyraźną transakcję, która spróbuje ponownie wstawić jeden wiersz, który usunęliśmy. W tym samym czasie uruchomimy naszą operację usuwania skubania.
Wstaw kod:
BEGIN TRANSACTION
SET IDENTITY_INSERT dbo.tbl ON;
INSERT INTO dbo.tbl
( c1 , c2 )
VALUES
( 20005 , 1 );
SET IDENTITY_INSERT dbo.tbl OFF;
--COMMIT TRANSACTION;
Rozpocznijmy obie operacje, zaczynając od wstawki, a następnie usuwając. Widzimy zamki z kluczem i zamki ekskluzywne.
Wstawka wygenerowała następujące blokady:
Nibbling delete / select trzyma te blokady:
Nasza wstawka blokuje nasze usuwanie zgodnie z oczekiwaniami:
Teraz zatwierdź transakcję wstawiania i zobacz, co jest grane.
I zgodnie z oczekiwaniami wszystkie transakcje zostały zakończone. Teraz musimy sprawdzić, czy wstawka była fantomem, czy też usunęła ją również operacja usuwania.
SELECT
c1
FROM
dbo.tbl
WHERE
c1 BETWEEN 20000 AND 20015;
W rzeczywistości wstawka została usunięta; więc nie było dozwolone wstawianie fantomów.
Podsumowując, uważam, że prawdziwym celem tego ćwiczenia nie jest próba śledzenia każdego wiersza, strony lub blokady na poziomie tabeli i próba ustalenia, czy element partii jest zablokowany, a zatem wymagałaby naszej operacji usuwania, aby czekać. Taka mogła być intencja pytających; zadanie to jest jednak herkulesowe i zasadniczo niepraktyczne, jeśli nie niemożliwe. Prawdziwym celem jest dopilnowanie, aby nie powstały żadne niepożądane zjawiska, gdy odizolujemy zakres naszej partii za pomocą własnych blokad, a następnie poprzedzimy usunięcie partii. SERIALIZABLE poziom izolacji osiąga ten cel. Kluczem do sukcesu jest utrzymywanie małych skubków, kontrolowanie dziennika transakcji i eliminowanie niepożądanych zjawisk.
Jeśli chcesz prędkości, nie buduj gigantycznie głębokich tabel, których nie można podzielić na partycje, a zatem nie można używać przełączania partycji w celu uzyskania najszybszych wyników. Kluczem do szybkości jest podział i równoległość; kluczem do cierpienia jest gryzienie i blokowanie życia.
Proszę daj mi znać co myślisz.
Stworzyłem kilka dalszych przykładów SERIALIZABLE poziomu izolacji w działaniu. Powinny być dostępne pod poniższymi linkami.
Usuń operację
Wstaw operację
Operacje na rzecz równości - blokady kluczowego zakresu przy kolejnych kluczowych wartościach
Operacje na rzecz równości - pobieranie istniejących danych w singletonie
Operacje na rzecz równości - pobieranie singleton nieistniejących danych
Nierówności Operacje - Blokowanie kluczowych zakresów przy zakresie i kolejnych kluczowych wartościach