Odpowiedź Justina Granta wyjaśnia, co LOCK_ESCALATIONogólnie robi ustawienie, ale pomija jeden ważny szczegół i nie wyjaśnia, dlaczego SSMS generuje kod, który je ustawia. Szczególnie dziwnie wygląda to na LOCK_ESCALATIONto, że jest to ostatnia instrukcja w skrypcie.
Zrobiłem kilka testów i oto moje zrozumienie tego, co się tutaj dzieje.
Krótka wersja
ALTER TABLEOświadczenie, że dodaje, krople lub zmienia kolumna domyślnie bierze schemat modyfikacji (SCH-M) blokady na stole, który nie ma nic wspólnego z LOCK_ESCALATIONustawieniem tabeli. LOCK_ESCALATIONwpływa na zachowanie podczas blokowania oświadczenia DML ( INSERT, UPDATE, DELETE, etc.), a nie w trakcie DDL ( ALTER). Blokada SCH-M jest zawsze blokadą całego obiektu bazy danych, w tym przykładzie tabeli.
Jest to prawdopodobne, skąd bierze się zamieszanie.
SSMS dodaje ALTER TABLE <TableName> SET (LOCK_ESCALATION = ...)instrukcję do swojego skryptu we wszystkich przypadkach, nawet jeśli nie jest to potrzebne. W przypadkach, gdy ta instrukcja jest potrzebna, jest dodawana w celu zachowania bieżącego ustawienia tabeli, a nie w celu zablokowania tabeli w określony sposób podczas zmiany schematu tabeli, która ma miejsce w tym skrypcie.
Innymi słowy, tabela jest zablokowana blokadą SCH-M na pierwszej ALTER TABLE ALTER COLUMNinstrukcji, podczas gdy cała praca związana ze zmianą schematu tabeli jest zakończona. To ostatnie ALTER TABLE SET LOCK_ESCALATIONstwierdzenie nie ma na to wpływu. Wpływa jedynie przyszłych sprawozdań DML ( INSERT, UPDATE,DELETE , itd.) Dla tej tabeli.
Na pierwszy rzut oka wygląda to tak, jakby SET LOCK_ESCALATION = TABLE miało to coś wspólnego z faktem, że zmieniamy całą tabelę (zmieniamy tutaj jej schemat), ale jest to mylące.
Długa wersja
Podczas zmiany tabeli w niektórych przypadkach program SSMS generuje skrypt, który ponownie tworzy całą tabelę, aw niektórych prostszych przypadkach (takich jak dodanie lub usunięcie kolumny) skrypt nie tworzy ponownie tabeli.
Weźmy tę przykładową tabelę jako przykład:
CREATE TABLE [dbo].[Test](
[ID] [int] NOT NULL,
[Col1] [nvarchar](50) NOT NULL,
[Col2] [int] NOT NULL,
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Każda tabela ma LOCK_ESCALATIONustawienie, które jest ustawione TABLEdomyślnie. Zmieńmy to tutaj:
ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)
Teraz, jeśli spróbuję zmienić Col1typ w projektancie tabel SSMS, SSMS generuje skrypt, który ponownie tworzy całą tabelę:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Test
(
ID int NOT NULL,
Col1 nvarchar(10) NOT NULL,
Col2 int NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_Test SET (LOCK_ESCALATION = DISABLE)
GO
IF EXISTS(SELECT * FROM dbo.Test)
EXEC('INSERT INTO dbo.Tmp_Test (ID, Col1, Col2)
SELECT ID, CONVERT(nvarchar(10), Col1), Col2 FROM dbo.Test WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Test
GO
EXECUTE sp_rename N'dbo.Tmp_Test', N'Test', 'OBJECT'
GO
ALTER TABLE dbo.Test ADD CONSTRAINT
PK_Test PRIMARY KEY CLUSTERED
(
ID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
Możesz zobaczyć powyżej, że ustawia on LOCK_ESCALATIONdla nowo utworzonej tabeli. SSMS robi to, aby zachować bieżące ustawienie tabeli. Program SSMS generuje ten wiersz, nawet jeśli bieżąca wartość ustawienia jest TABLEwartością domyślną . Chyba tylko po to, aby być bezpiecznym i wyraźnym oraz zapobiec ewentualnym przyszłym problemom, jeśli w przyszłości to domyślne się zmieni. To ma sens.
W tym przykładzie konieczne jest wygenerowanie SET LOCK_ESCALATIONinstrukcji, ponieważ tabela jest tworzona od nowa i jej ustawienie musi zostać zachowane.
Jeśli spróbuję dokonać prostej zmiany w tabeli za pomocą projektanta tabel SSMS, takiej jak dodanie nowej kolumny, wówczas SSMS generuje skrypt, który nie odtwarza tabeli:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.Test ADD
NewCol nchar(10) NULL
GO
ALTER TABLE dbo.Test SET (LOCK_ESCALATION = DISABLE)
GO
COMMIT
Jak widać, nadal dodaje ALTER TABLE SET LOCK_ESCALATIONoświadczenie, chociaż w tym przypadku wcale nie jest potrzebne. Pierwsza ALTER TABLE ... ADDnie zmienia aktualnego ustawienia. Chyba programiści SSMS uznali, że nie warto próbować ustalić, w jakich przypadkach to ALTER TABLE SET LOCK_ESCALATIONstwierdzenie jest zbędne i generować je zawsze, tak dla bezpieczeństwa. Nie ma nic złego w dodawaniu tego stwierdzenia za każdym razem.
Ponownie LOCK_ESCALATIONustawienie dotyczące całej tabeli jest nieistotne, gdy schemat tabeli zmienia się za pośrednictwem ALTER TABLEinstrukcji. LOCK_ESCALATIONustawienie wpływa tylko na zachowanie blokowania instrukcji DML, jak np UPDATE.
Na koniec cytat z ALTER TABLE, podkreśl mój:
Zmiany określone w ALTER TABLE są wprowadzane natychmiast. Jeśli zmiany wymagają modyfikacji wierszy w tabeli, ALTER TABLE aktualizuje wiersze. ALTER TABLE uzyskuje blokadę modyfikacji schematu (SCH-M) w tabeli, aby upewnić się, że żadne inne połączenia nie odwołują się nawet do metadanych tabeli podczas zmiany, z wyjątkiem operacji indeksowania online, które wymagają na końcu bardzo krótkiej blokady SCH-M. W operacji ALTER TABLE… SWITCH blokada jest uzyskiwana zarówno w tabeli źródłowej, jak i docelowej. Modyfikacje dokonane w tabeli są rejestrowane iw pełni możliwe do odzyskania. Zmiany, które wpływają na wszystkie wiersze w bardzo dużych tabelach, takie jak upuszczenie kolumny lub, w niektórych wersjach SQL Server, dodanie kolumny NOT NULL z wartością domyślną, mogą zająć dużo czasu i wygenerować wiele rekordów dziennika. Te instrukcje ALTER TABLE powinny być wykonywane z taką samą ostrożnością, jak każda instrukcja INSERT, UPDATE lub DELETE, która ma wpływ na wiele wierszy.