Sch-M WAIT blokuje Sch-S w SQL Server 2014, ale nie SQL Server 2008 R2?

Niedawno przeprowadziliśmy migrację naszych instancji produkcyjnych z SQL 2008 R2 na zupełnie nowe serwery SQL 2014. Oto ciekawy scenariusz, który odkryliśmy podczas korzystania z Service Broker. Rozważ bazę danych za Broker Enabled = truepomocą MyServicei MyQueue. Obsługa komunikatów o zatruciach jest wyłączona w tej kolejce. Są co najmniej 2 aktywne rozmowy z wiadomościami w kolejce.

W jednym procesie (SPID 100) wykonaj:

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;

Pamiętaj, że transakcję pozostawiamy otwartą. Wyobraź sobie, że jest to program .NET, który długo czeka na jakiś zasób zewnętrzny. Za pośrednictwem sys.dm_tran_lockswidzimy, że ten identyfikator SPID otrzymał IX blokadę w kolejce.

| type   | resource_id | mode | status | spid |
| OBJECT | 277576027   | IX   | GRANT  | 100  |

W osobnym procesie (SPID 101) wykonaj pięć razy :

BEGIN TRANSACTION;
DECLARE @conversation_group_id UNIQUEIDENTIFIER;
RECEIVE TOP (1) @conversation_group_id = conversation_handle FROM MyQueue;
ROLLBACK TRANSACTION;

Kluczem jest tutaj to, że wycofujemy transakcję pięć razy . To uruchamia wbudowaną logikę obsługi komunikatów o zatruciach . Chociaż kolejka nie wyłącza się (ponieważ jest skonfigurowana tak, aby nie wyłączać), zadanie w tle nadal próbuje działać i uruchomić broker_queue_disabledzdarzenie. Więc teraz, jeśli zapytamy sys.dm_tran_locksponownie, zobaczymy inny SPID (powiązany z BRKR TASK) czekający na zamek Sch-M.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |

Jak dotąd wszystko ma sens.

Na koniec, w innym procesie (SPID 102), spróbuj wysłać do usługi przy użyciu tej kolejki:

BEGIN TRANSACTION;
DECLARE @ch uniqueidentifier;
BEGIN DIALOG @ch FROM SERVICE [MyService] TO SERVICE 'MyService';
SEND ON CONVERSATION @ch ('HELLO WORLD');

SENDKomenda jest zablokowana. Jeśli spojrzymy ponownie sys.dm_tran_locks, zobaczymy, że proces ten czeka na zamek Sch-S. Podczas wykonywania sp_who2stwierdzamy, że SPID 102 jest blokowany przez SPID 36.

| type   | resource_id | mode  | status | spid |
| OBJECT | 277576027   | IX    | GRANT  | 100  |
| OBJECT | 277576027   | Sch-M | WAIT   | 36   |
| OBJECT | 277576027   | Sch-S | WAIT   | 102  |

Dlaczego zamek Sch-S czeka na zamek Sch-M, który również czeka?

To zachowanie jest zupełnie inne w SQL 2008 R2! Korzystając z tego samego scenariusza, działającego w naszych instancjach, które mają jeszcze zostać wycofane z eksploatacji w 2008R2, ostatnia partia zawierająca SENDpolecenie nie zostanie zablokowana przez oczekującą blokadę Sch-M.

Czy zachowanie blokowania zmieniło się w SQL 2012 lub 2014? Czy jest może jakieś ustawienie bazy danych lub serwera, które może wpłynąć na to blokowanie?

Zachowanie zmieniło się między SQL Server 2008 R2 a SQL Server 2012. Implementacja 2008 R2 była niespójna z udokumentowaną semantyką „zrelaksowanego FIFO” :

Zamki są przyznawane w sposób swobodny pierwsze wejście, pierwsze wyjście (FIFO). Chociaż kolejność nie jest ścisła FIFO, zachowuje pożądane właściwości, takie jak unikanie głodu i działa w celu zmniejszenia niepotrzebnych impasów i blokowania.

Nowe żądania blokady, w których żądający nie jest jeszcze właścicielem blokady zasobu, zostają zablokowane, jeśli żądany tryb jest niezgodny z połączeniem przyznanych żądań i trybami oczekujących żądań.

Żądanie konwersji zostaje zablokowane tylko wtedy, gdy żądany tryb jest niezgodny z połączeniem wszystkich przyznanych trybów, z wyjątkiem trybu, w którym sam wniosek o konwersję został pierwotnie przyznany.

W 2008 r. R2 otrzymano nowe Sch-Sżądanie blokady, mimo że jest ono niezgodne z połączeniem żądań przyznanych i oczekujących, co może doprowadzić do głodu blokady. W 2012 r. Sch-SŻądanie blokady jest zablokowane.

Poniższy skrypt odtwarzania używa zwykłych tabel, a nie kolejki Service Broker:

-- Session 1
CREATE TABLE dbo.LockTest (col1 integer NULL);

INSERT dbo.LockTest (col1) VALUES (1);

BEGIN TRANSACTION;

-- Will hold row-X, Pag-IX, and Tab-IX
INSERT dbo.LockTest (col1) VALUES (2);

-- Session 2
-- Blocked waiting on Sch-M
TRUNCATE TABLE dbo.LockTest;

-- Session 3
-- Takes Sch-S only
-- Not blocked in 2008 R2
SELECT * FROM dbo.LockTest AS LT WITH (READUNCOMMITTED);

Podsumowując, 2008 R2 nie działał zgodnie z planem. Problem został rozwiązany w SQL Server 2012.