W jaki sposób SQL Server określa kolejność, w jakiej przyjmuje blokady podczas wybierania tabeli?

Mam dwie procedury składowane, które są zakleszczone, gdy system jest obciążony. Proc A wybiera z tabeli, podczas gdy Proc B wstawia do tej samej tabeli. Wykres blokady pokazuje, że Proc A ma blokadę strony w trybie S, dla której Proc B chce blokady w trybie IX, Proc A czeka jednak na blokadę strony w trybie S dla innej strony, na której Proc B ma już blokadę strony w trybie IX .

Oczywiście można to rozwiązać, upewniając się, że oba zapytania blokują strony w tabeli w tej samej kolejności, ale nie wiem, jak to zrobić.

Moje pytanie brzmi: w jaki sposób SQL Server określa, w jakiej kolejności blokować strony podczas wykonywania INSERT i SELECT oraz jak można zmodyfikować to zachowanie?

W jaki sposób SQL Server określa kolejność blokowania stron podczas wykonywania operacji INSERT i SELECTs a

Nieokreślone - wykonywane przez przetwarzanie wewnętrzne i zależne od danych wyjściowych optymalizatora zapytań.

i jak możesz zmodyfikować to zachowanie?

Kontroluj swoją izolację. Jeśli czytasz, aby pisać, powiedz SQL Serverowi, aby natychmiast uzyskał blokadę zapisu. Punkt zamknięty.

Obecnie proc A ma wspólną blokadę.

„Żadne inne transakcje nie mogą modyfikować danych, gdy w zasobie istnieją blokady udostępnione (S)”. http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx

Jeśli korzystasz z wersji SQL 2005 lub nowszej, spróbuj użyć izolacji migawki.

„Poziom izolacji migawki używa wersjonowania wierszy, aby zapewnić spójność odczytu na poziomie transakcji. Operacje odczytu nie blokują stron ani bloków wierszy; nabywane są tylko blokady tabel SCH-S. Podczas odczytu wierszy zmodyfikowanych przez inną transakcję, pobierają wersję wiersza, który istniała w momencie rozpoczęcia transakcji. Izolacji migawki można użyć tylko w przypadku bazy danych, gdy opcja bazy danych ALLOW_SNAPSHOT_ISOLATION jest ustawiona na WŁ. Domyślnie ta opcja jest wyłączona dla baz danych użytkowników. ” http://msdn.microsoft.com/en-us/library/ms189122.aspx

Ponieważ pobierane są tylko blokady Sch-S, twoje „czytanie nie powinno być w stanie zablokować twojego” zapisu.

„Blokady stabilności schematu (Sch-S) nie blokują żadnych blokad transakcyjnych, w tym blokad wyłącznych (X)”. http://msdn.microsoft.com/en-us/library/ms189122.aspx

Należy pamiętać, że poziom izolacji migawki intensywnie wykorzystuje tempdb do wersjonowania wierszy, więc odpowiednio go dopasuj i trzymaj się najlepszych praktyk dotyczących strategii dysku tempdb.