SQL Server - jaki poziom izolacji dla nieblokujących instrukcji select?

Mam długo działającą transakcję (o nazwie powiedzmy T1), która wykonuje niektóre operacje usuwania, aktualizacji i wstawiania w tabeli w SQL Server 2008 R2. W tym samym czasie inny proces okresowo uruchamia instrukcje select z tej tabeli.

W domyślnych ustawieniach izolacji (READ COMMITTED, jak sądzę?) T1 blokuje wykonywanie wszystkich instrukcji select do momentu zatwierdzenia lub wycofania transakcji.

Chciałbym zobaczyć, aby instrukcje select działały na spójnych danych, nawet gdy transakcja jest w toku. Wierzę, że izolacja SNAPSHOT może pomóc, ale nie jestem pewien, czy idę w dobrym kierunku. Czy byłby to najlepszy poziom izolacji dla tej aplikacji?

Po drugie, nie mam żadnej kontroli nad procesem wywoływania instrukcji select, ale mam kontrolę nad aplikacją .NET, która wywołuje T1. Czy wymagane byłyby jakiekolwiek zmiany poziomu izolacji zarówno w instrukcjach select, jak i T1, czy wystarczyłoby zaznaczyć tylko T1 jako inny poziom izolacji?

W idealnym świecie masz dwie możliwości: SNAPSHOT i READ COMMITTED SNAPSHOT (RCSI). Upewnij się, że rozumiesz podstawy poziomów izolacji transakcji, zanim zdecydujesz, który jest odpowiedni dla twojego obciążenia. Zwróć szczególną uwagę na różne wyniki, które możesz zobaczyć w wyniku przejścia na RCSI.

Wygląda na to, że nie jest to idealny świat, ponieważ nie masz żadnej kontroli nad aplikacją, która generuje instrukcje select. W takim przypadku jedyną opcją jest włączenie RCSI dla danej bazy danych, tak aby wybrane automatycznie używały RCSI zamiast CZYTAJ ZOBOWIĄZANIE.

Prawidłowo, użyj izolacji SNAPSHOT, aby uzyskać spójne, zatwierdzone dane sprzed rozpoczęcia transakcji.

Izolacja READ UNCOMMITTED (alias NOLOCK) odczyta brudne, niespójne dane

Gdy włączysz izolację SNAPSHOT, będzie ona obowiązywać dla wszystkich wybieranych dalej instrukcji SELECT. W ALTER DATABASEtym przypadku korzystasz z READ_COMMITTED_SNAPSHOT

Edycja: dodano link + cytat ALTER DATABASE (mój pogrubienie)

Włącza opcję migawki zatwierdzonej do odczytu na poziomie bazy danych. Po włączeniu instrukcje DML zaczynają generować wersje wierszy, nawet jeśli żadna transakcja nie korzysta z funkcji Snapshot Isolation. Po włączeniu tej opcji transakcje określające zatwierdzony poziom odseparowania odczytu używają wersjonowania wierszy zamiast blokowania. Gdy transakcja przebiega na poziomie izolacji zatwierdzonego odczytu, wszystkie instrukcje widzą migawkę danych, jaka istnieje na początku instrukcji.

I od używania Snapshot Isolation (mój pogrubienie)

Opcja bazy danych READ_COMMITTED_SNAPSHOT określa zachowanie domyślnego poziomu izolacji READ COMMITTED, gdy w bazie danych jest włączona izolacja migawek. Jeśli nie określisz jawnie READ_COMMITTED_SNAPSHOT ON, READ COMMITTED zostanie zastosowane do wszystkich transakcji niejawnych. Powoduje to takie samo zachowanie, jak ustawienie READ_COMMITTED_SNAPSHOT OFF (ustawienie domyślne). Gdy działa READ_COMMITTED_SNAPSHOT OFF, aparat bazy danych używa blokad współdzielonych w celu wymuszenia domyślnego poziomu izolacji. Jeśli ustawisz opcję bazy danych READ_COMMITTED_SNAPSHOT na WŁ., Aparat bazy danych używa domyślnie wersjonowania wierszy i izolacji migawek zamiast blokad w celu ochrony danych.

Więc tak.

Włączenie RCSI pozwoli odczytom uzyskać spójne dane i nie zostanie zablokowane przez autorów, którzy będą nadal używać Read Committed

Sugeruję przeczytanie następującego pytania i odpowiedzi: Problemy z blokowaniem bazy danych? .

Znalezienie odpowiedniego poziomu izolacji do użycia na poziomie db jest najszybszą rzeczą, którą możesz teraz zrobić, aby pomóc rozwiązać ten problem, ponieważ trudno jest teraz zmienić wszystkie aplikacje, które dotykają bazy danych i zmienić ich kod. Ponieważ powiedziałeś: „Nie mam żadnej kontroli nad procesem wywołującym instrukcje select”, najszybszą odpowiedzią byłoby przełączenie db na poziom izolacji Read Committed Snapshot, abyś nie dotykał zapytań dotyczących odczytu. W przeciwnym razie należy użyć poziomu izolacji migawki dla sesji, które odczytują dane podczas dużych transakcji.

Więcej informacji na temat wyboru właściwego: Wybór poziomów izolacji na podstawie wersji wiersza .