Dlaczego UPDLOCK powoduje zawieszanie się SELECTs (blokowanie)?

Mam wybór w SQL SERVER, który blokuje całą tabelę.

Oto skrypt instalacyjny (upewnij się, że niczego nie zastąpisz)

USE [master]
GO

IF EXISTS(SELECT 1 FROM sys.databases d WHERE d.name = 'LockingTestDB')
DROP DATABASE LockingTestDB
GO

CREATE DATABASE LockingTestDB
GO

USE [LockingTestDB]
GO
IF EXISTS(SELECT 1 FROM sys.tables t WHERE t.name = 'LockingTestTable')
  DROP TABLE LockingTestTable
GO

CREATE TABLE LockingTestTable (
  Id int IDENTITY(1, 1),
  Name varchar(100),
  PRIMARY KEY CLUSTERED (Id)
)
GO

INSERT INTO LockingTestTable(Name) VALUES ('1')
INSERT INTO LockingTestTable(Name) VALUES ('2')
GO

Otwórz nowe okno zapytania i uruchom następującą transakcję (w której jest oczekiwanie):

USE [LockingTestDB]
GO

BEGIN TRANSACTION
  SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '1'
  WAITFOR DELAY '00:01:00'

COMMIT TRANSACTION
--ROLLBACK
GO

USE [master]
GO

I kolejny, który będzie działał (upewnij się, że działają w tym samym czasie):

USE [LockingTestDB]
GO

SELECT * FROM LockingTestTable t WITH (UPDLOCK, ROWLOCK) WHERE t.Name = '2'

USE [master]
GO

Zauważysz, że drugie zapytanie zostanie zablokowane przez pierwsze. Zatrzymaj pierwsze zapytanie i uruchom ROLLBACK, a drugie zakończy się.

Dlaczego to się dzieje?

PS: Dodanie nieklastrowanego indeksu (z pełnym pokryciem) nad nazwą to naprawi:

USE [LockingTestDB]
GO

CREATE NONCLUSTERED INDEX [IX_Name] ON [dbo].[LockingTestTable] 
(
  [Name] ASC
)
INCLUDE ( [Id]) WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Znowu dlaczego?

Jak udokumentowano w Books Online , UPDLOCKpobiera blokady aktualizacji i utrzymuje je do końca transakcji.

Bez indeksu do zlokalizowania wierszy, które mają zostać zablokowane, wszystkie testowane wiersze są zablokowane, a blokady w kwalifikujących się wierszach są wstrzymywane do czasu zakończenia transakcji.

Pierwsza transakcja zawiera blokadę aktualizacji w wierszu, w którym nazwa = 1. Druga transakcja jest blokowana, gdy próbuje uzyskać blokadę aktualizacji w tym samym wierszu (aby sprawdzić, czy nazwa = 2 dla tego wiersza).

Dzięki indeksowi SQL Server może szybko zlokalizować i zablokować tylko te wiersze, które się kwalifikują, więc nie ma konfliktu.

Powinieneś przejrzeć kod z wykwalifikowanym specjalistą bazy danych, aby sprawdzić przyczynę podpowiedzi blokującej i upewnić się, że istnieją odpowiednie indeksy.

Informacje pokrewne: Modyfikacje danych w obszarze Odczyt zatwierdzonej migawki