|
|
Opublikowano 20.07.2016 12:37:53
|
|
|
Przegląd śluz
1. Dlaczego wprowadzać zamki
Gdy wielu użytkowników wykonuje równoczesne operacje nad bazą danych, występują następujące niespójności danych:
Brakujące aktualizacje
Dwóch użytkowników, A i B, odczytuje te same dane i je modyfikuje, a wynik jednej modyfikacji niszczy wynik drugiego modyfikacji, na przykład system rezerwacji biletów
Brudne czytanie
Użytkownik A modyfikuje dane, a następnie użytkownik B odczytuje dane, ale użytkownik A z jakiegoś powodu anuluje modyfikację danych i dane wracają do pierwotnej wartości
Nie czytaj wielokrotnie
Użytkownik A odczytuje dane, a następnie użytkownik B odczytuje dane i je modyfikuje
Główną metodą kontroli współbieżności jest blokowanie, czyli zakazywanie użytkownikom wykonywania określonych operacji przez określony czas, aby uniknąć niespójności danych
2. Klasyfikacja śluz
Istnieją dwie kategorie śluz:
1 . Z perspektywy systemu baz danych: dzieli się on na zamki wyłączne (czyli zamki wyłączne), zamki współdzielone oraz blokady aktualizacji
MS - SQL Server wykorzystuje następujące wzorce blokad zasobów.
Opis trybu blokady
Share(y) jest używany do operacji, które nie zmieniają ani nie aktualizują danych (operacje tylko do odczytu), takich jak instrukcje SELECT.
Update (U) jest używany w zasobach do aktualizacji. Zapobiega typowym formom martwym punkcji, gdy wiele sesji jest czytanych, blokowanych i ewentualnie może nastąpić aktualizacja zasobów.
Wyłączność (X) jest używana do operacji modyfikacji danych, takich jak INSERT, UPDATE lub DELETE. Upewnij się, że nie wykonuje się wielu aktualizacji jednocześnie na tym samym zasobie.
Zamki intencyjne służą do ustanowienia hierarchii zamków. Rodzaje blokad intent to: Intent Shared (IS), Intent Exclusive (IX) oraz Intent Exclusive (SIX).
Blokady schematu są stosowane podczas wykonywania operacji zależnych od schematu tabeli. Typy zamków schematu to: modyfikacja schematu (Sch -M) oraz stabilność schematu (Sch -S).
Aktualizacje masowe (BU) są stosowane, gdy duże ilości danych są kopiowane do tabeli i określana jest wskazówka TABLOCK.
Zamki współdzielone
Współdzielona blokada pozwala na jednoczesne transakcje odczytujące (SELECT) zasob. Gdy na zasobach istnieje współdzielony (S) blok, żadna inna transakcja nie może modyfikować danych. Zwolnij współdzieloną (S) blokadę na zasob natychmiast po odczytaniu danych, chyba że poziom izolacji transakcji jest ustawiony na powtarzalny lub wyższy, albo zamek współdzielony (S) jest zachowany z podpowiednieniem blokady przez cały okres życia transakcji.
Blokada aktualizacji
Blokady Update (U) zapobiegają impasom w ich zwykłej formie. Typowy wzorzec aktualizacji polega na transakcji, która odczytuje rekord, otrzymuje wspólną blokadę (S) dla zasobu (strony lub wiersza), a następnie modyfikuje wiersz, co wymaga konwersji blokady na wyłączną (X) blokadę. Jeśli dwie transakcje uzyskują blokadę w trybie współdzielonym na zasobie i próbują jednocześnie zaktualizować dane, jedna z transakcji próbuje przekształcić blokadę w wyłączną (X) blokadę. Przejście z trybu współdzielonego do wyłącznego zamka musi poczekać przez jakiś czas, ponieważ wyłączny zamek jednej transakcji nie jest zgodny z zamkiem trybu współdzielonego innej transakcji; Następuje oczekiwanie na blokadę. Druga transakcja próbuje uzyskać wyłączną blokadę (X) dla aktualizacji. Impas występuje, ponieważ obie transakcje są konwertowane na wyłączne (X) blokady, a każda transakcja czeka, aż druga transakcja zwolni blokadę trybu współdzielonego.
Aby uniknąć potencjalnego problemu z martwym punktem, użyj zaktualizowanej blokady (U). Tylko jedna transakcja naraz może uzyskać zaktualizowaną blokadę (U) dla zasobu. Jeśli transakcja modyfikuje zasób, blokada aktualizacji (U) jest przekształcana w wyłączną blokadę (X). W przeciwnym razie zamek jest przekształcany w zamek współdzielony.
Zamki wyłączne
Wyłączne (X) blokady uniemożliwiają jednoczesnym transakcjom dostęp do zasobów. Inne transakcje nie mogą odczytywać ani modyfikować danych zablokowanych wyłącznym (X) zamknięciem.
Blokada intencji
Zamek intencji oznacza, że SQL Server musi uzyskać wspólną (S) blokadę lub wyłączną (X) blokadę na niektórych podstawowych zasobach w hierarchii. Na przykład blokada zamiaru udziału umieszczona na poziomie tabeli oznacza, że transakcja zamierza nałożyć blokadę udziałów na stronie lub wierszu w tabeli. Ustawienie blokady intencji na poziomie tabeli zapobiega późniejszemu nabyciu wyłącznej blokady (X) na tabeli zawierającej tę stronę. Blokady intencji mogą poprawić wydajność, ponieważ SQL Server sprawdza blokadę intencji tylko na poziomie tabeli, aby sprawdzić, czy transakcja może bezpiecznie uzyskać blokadę na tej tabeli. Zamiast sprawdzać blokady na każdym wierszu lub stronie tabeli, aby sprawdzić, czy transakcja może zablokować całą tabelę.
Blokady intent obejmują Intentne Udostępnianie (IS), Intent Exclusive (IX) oraz Intent Exclusive Sharing (SIX).
Opis trybu blokady
Udostępnianie intent (IS) wskazuje, że intencją transakcji są część, a nie wszystkie zasoby bazowe w hierarchii odczytu, poprzez umieszczenie blokad S na każdym zasobie.
Wyłączność intent (IX) oznacza, że celem transakcji jest modyfikacja częściowych, ale nie wszystkich, zasobów bazowych w hierarchii poprzez nałożenie blokady X na każdy zasób. IX jest nadzbiorem IS.
Wyłączne udostępnianie z zamiarem (SIX) oznacza, że celem transakcji jest odczytanie wszystkich zasobów bazowych w hierarchii i modyfikacja niektórych, ale nie wszystkich, zasobów bazowych poprzez nałożenie blokad IX na każdy zasób. Pozwól na jednoczesne blokady IS na zasobach najwyższego poziomu. Na przykład, blokada SIX w tabeli umieszcza blokadę SIX na tabeli (umożliwiając jednoczesne blokady IS) oraz blokadę IX na aktualnie zmodyfikowanej stronie (blokadę X na zmodyfikowanym wierszu). Podczas gdy każdy zasób może mieć tylko jedną blokadę SIX na określony czas, aby zapobiec aktualizacji innych transakcji, inne transakcje mogą odczytywać zasoby bazowe w hierarchii poprzez pozyskiwanie blokad IS na poziomie tabeli.
Wyłączna blokada: Tylko program, który wykonuje operację blokady, może ją używać, a wszelkie inne operacje na niej nie będą akceptowane. Gdy wykonasz polecenie aktualizacji danych, SQL Server automatycznie używa wyłącznej blokady. Gdy na obiekcie istnieją inne blokady, nie można dodać do niego wyłącznej blokady.
Współdzielona blokada: Zasób zablokowany przez współdzieloną blokadę może być odczytywany przez innych użytkowników, ale inni użytkownicy nie mogą go zmieniać.
Blokada aktualizacji: Gdy SQL Server jest gotowy do aktualizacji danych, najpierw blokuje obiekt danych, aby nie można było ich zmieniać, ale można je było odczytać. Gdy SQL Server zdecyduje, że chce zaktualizować dane, automatycznie zastąpi blokadę aktualizacji zamkiem ekskluzywnym i nie może dodać blokady aktualizacyjnej, gdy na obiekcie istnieją inne blokady.
2 . Z punktu widzenia programisty: dzieli się na blokadę optymistyczną i pesymistyczną.
Optimism Lock: Całkowicie opiera się na bazie danych do zarządzania pracą zamka.
Pesymistyczne blokady: Programiści sami zarządzają obsługą blokad na danych lub obiektach.
MS - SQLSERVER wykorzystuje blokady do implementacji pesymistycznej kontroli współbieżności między wieloma użytkownikami, którzy jednocześnie dokonują modyfikacji bazy danych
3. Rozmiar cząstek zamka
Granularność blokady to rozmiar blokowanego celu, drobna granularność blokująca to wysoka równobieżność, ale narzut jest duży, a duża granularność blokująca to niskie współbieżności, ale narzut jest niewielki
SQL Server obsługuje blokowanie szczegółowości dla wierszy, stron, kluczy, zakresów kluczy, indeksów, tabel lub baz danych
Opis zasobów
Identyfikator wiersza RID. Używa się do indywidualnego blokowania rzędu w stole.
Blokada wiersza klucza w indeksie. Używany do ochrony zakresu kluczy w transakcjach serializowalnych.
8 kilobajtów (KB) stron danych lub stron indeksowych.
Extended Disk Zestaw ośmiu sąsiadujących stron danych lub stron indeksowych.
Tabela Cała tabela wraz ze wszystkimi danymi i indeksami.
Baza danych baz danych.
4. Długość czasu blokady
Czas przechowywania zamka to czas potrzebny do ochrony zasobu na żądanym poziomie.
Czas oczekiwania współdzielonej blokady używanej do ochrony operacji odczytu zależy od poziomu izolacji transakcji. Przy domyślnym poziomie izolacji transakcji READ COMMITTED, współdzielony zamek jest kontrolowany tylko przez czas trwania strony odczytu. Podczas skanowania zamek nie zostaje zdejmowany, dopóki nie zostanie zdobyty na następnej stronie skanu. Jeśli określisz prompt HOLDLOCK lub ustawisz poziom izolacji transakcji na REPEATABLE READ lub SERIALIZABLE, blokada nie zostanie zwolniona, dopóki transakcja się nie zakończy.
W zależności od ustawienia opcji współbieżności kursora, kursor może uzyskać blokadę przewijania w trybie współtworzonym (scroll lock), aby chronić ekstrakt. Gdy wymagany jest zamek przewijania, blokada jest zwalniana dopiero przy kolejnym wyodrębnieniu lub zamknięciu kursora, w zależności od tego, co nastąpi wcześniej. Jednak jeśli określisz HOLDLOCK, blokada przewijania nie zostaje zwolniona aż do końca transakcji.
Wyłączny zamek chroniący aktualizację nie zostanie zwolniony aż do zakończenia transakcji.
Jeśli połączenie próbuje uzyskać blokadę kolidujące z blokadą kontrolowaną przez inne połączenie, połączenie próbujące zdobyć blokadę zostanie zablokowane aż do:
Blokada konfliktowa zostaje zwolniona, a połączenie uzyskuje żądany zamek.
Czas na połączenie wygasł. Domyślnie nie ma przerw czasowych, ale niektóre aplikacje ustawiają je, aby zapobiec nieokreślonym oczekiwaniom
Pięć opcji personalizacji zamków w SQL Server
1 Obsługa martwych blokad i ustalanie priorytetów blokad
Impas to niekończące się oczekiwanie spowodowane przez wielu użytkowników aplikujących na różne bloki, ponieważ wnioskodawca ma część prawa do blokowania i czeka na częściowe zablokowanie należące do innych użytkowników
Możesz użyć DEADLOCK_PRIORITY SET, aby kontrolować reakcję sesji w przypadku martwego zablokowania. Jeśli oba procesy zablokują dane i każdy nie może zwolnić własnego zamka, dopóki drugi proces nie zwolni własnego zamka, dochodzi do sytuacji martwego zablokowania.
2 Zarządzanie timeoutami i ustalanie czasu trwania blokady.
@@LOCK_TIMEOUT Zwraca aktualne ustawienie limitu blokady dla bieżącej sesji w milisekundach
Ustawienie SET LOCK_TIMEOUT pozwala aplikacji ustawić maksymalny czas, przez jaki żądanie czeka na zablokowanie zasobu. Gdy czas oczekiwania na żądanie jest większy niż LOCK_TIMEOUT ustawienie, system automatycznie anuluje polecenie blokujące i zwraca aplikacji komunikat o błędzie 1222, że okres przekroczenia czasu żądania blokady został przekroczony
przykład
W poniższym przykładzie okres wyłączenia blokady ustawiony jest na 1 800 milisekund.
ZESTAW LOCK_TIMEOUT1800
3) Ustaw poziom izolacji transakcji.
4) Używanie tabliczowych wskazówek blokad dla instrukcji SELECT, INSERT, UPDATE i DETETE.
5) Konfiguracja granularności blokowania indeksu
Możesz użyć procedur sp_indexoption systemowych przechowywanych do ustawienia szczegółowości blokady dla indeksowania
6. Zobacz informacje o śluzie
1 Wykonaj EXEC SP_LOCK raportuj informacje o zamku
2 Naciśnij Ctrl + 2 w analizatorze zapytań, aby zobaczyć informacje o blokadzie
7. Środki ostrożności dotyczące stosowania
Jak uniknąć impasu
1. Podczas korzystania z transakcji staraj się skrócić proces logicznego przetwarzania transakcji i przedłużać lub cofać transakcje.
2 Ustaw parametr blokady na rozsądny zakres, na przykład: 3 minuty - 10 minut; Po upływie czasu operacja zostaje automatycznie przerwana, aby uniknąć zawieszenia procesu;
3. Optymalizacja programu, sprawdzenie i unikanie zjawiska martwego punku;
4. Dokładnie przetestować wszystkie skrypty i SP przed dokładną wersją.
5 Wszystkie SP muszą mieć obsługę błędów (przez @error)
6 Nie modyfikuj domyślnego poziomu transakcji SQL SERVER. Nie zaleca się wymuszanego blokowania
Rozwiąż problem Jak zablokować bazę danych wierszowych
8. Kilka pytań dotyczących zamków
1 Jak zamknąć rząd stołu
USTAW POZIOM TRANSACTIONISOLATION READUNCOMMITTED
WYBIERZ *Z TABELI ROWLOCK, gdzie id = 1
2 Blokuj tabelę w bazie danych
WYBIERZ *Z TABELI WITH( HOLDLOCK )
Oświadczenie blokady:
sybase:
aktualizacja tabeli col1 = col1 gdzie 1 = 0 ;
MSSQL:
Wybierz col1z tabeli (tablockx), gdzie 1= 0 ;
oracle:
STÓŁ BLOKUJĄCY STÓŁ W TRYBIE WYŁĄCZNYM ;
Po zablokowaniu blokady nikt inny nie może jej obsługiwać, dopóki zablokowany użytkownik jej nie odblokuje, a blokada zostaje odblokowana przez commit lub rollback
Kilka przykładów pomoże pogłębić twoje wrażenie
Ustaw tabelę1(A,B,C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3
1) Wyłączny zamek
Stwórz dwa nowe połączenia
Wykonaj następujące polecenie w pierwszym połączeniu
Begin tran
Aktualizacja tabeli1
set A= ' aa '
gdzie B= ' b2 '
czekajcie na opóźnienie' 00:00:30' --czekaj 30 sekund
commit tran
Wykonaj następujące polecenie w drugim połączeniu
Begin tran
Wybierz *z tabeli1
gdzie B= ' b2 '
commit tran
Jeśli powyższe dwa polecenia są wykonywane jednocześnie, zapytanie select musi poczekać na wykonanie aktualizacji, czyli poczekać 30 sekund
2) Zamek współdzielony
Wykonaj następujące polecenie w pierwszym połączeniu
Begin tran
wybierz *z tabeli1 Holdlock - Holdlock jest sztucznie dodawany do blokady
gdzie B= ' b2 '
czekajcie na opóźnienie' 00:00:30' --czekaj 30 sekund
commit tran
Wykonaj następujące polecenie w drugim połączeniu
Begin tran
wybierz A,C z tabeli 1
gdzie B= ' b2 '
Aktualizacja tabeli1
set A= ' aa '
gdzie B= ' b2 '
commit tran
Jeśli powyższe dwa polecenia są wykonywane jednocześnie, można wykonać zapytanie select w drugim połączeniu
Aktualizacja musi poczekać na pierwszą transakcję, aby zwolnić blokadę współdzieloną i przekształcić ją w wyłączną, zanim będzie mogła zostać wykonana, czyli odczekać 30 sekund
3) Impas
Dodana tabela2(D,E)
D E
d1 e1
D2 E2
Wykonaj następujące polecenie w pierwszym połączeniu
Begin tran
Aktualizacja tabeli1
set A= ' aa '
gdzie B= ' b2 '
czekajna opóźnienie' 00:00:30'
Tabela aktualizacji 2
zestaw D= ' d5 '
gdzie E= ' e1 '
commit tran
Wykonaj następujące polecenie w drugim połączeniu
Begin tran
Tabela aktualizacji 2
zestaw D= ' d5 '
gdzie E= ' e1 '
czekaj na opóźnienie' 00:00:10'
Aktualizacja tabeli1
set A= ' aa '
gdzie B= ' b2 '
commit tran
Jednocześnie system wykrywa blokadę i przerywa proces
Dodam:
Wskazówki blokujące na poziomie tabeli obsługiwane przez SQL Server 2000
HOLDLOCK przechowuje współdzieloną blokadę do czasu zakończenia całej transakcji i powinien zostać zwolniony, gdy zablokowany obiekt nie jest potrzebny, równy poziomowi izolacji transakcji SERIALIZABLE
Instrukcja NOLOCK jest wykonywana bez wydawania współdzielonej blokady, co pozwala na brudne odczyty, co odpowiada poziomowi izolacji transakcji READ UNCOMMITTED
PAGLOCK używa wielu zamków stron, gdzie używa się jednej blokady stołowej
READPAST pozwala serwerowi sql pomijać zablokowane linie i wykonywać transakcje, a dla poziomów izolacji transakcji READ Noncommitted pomija tylko blokady RID, a nie blokady stron, stref i tabel
ROWLOCK wymusza stosowanie rowlocków
TABLOCKX wymusza stosowanie wyłącznej blokady na poziomie tabeli, która uniemożliwia innym transakcjom korzystanie z tabeli podczas transakcji
UPLOCK wymusza użycie aktualizacji podczas odczytu tabeli bez wspólnego zamknięcia
Blokada aplikacji:
Blokada aplikacji to blokada generowana przez kod klienta, a nie zamek generowany przez SQL Server
Dwa procesy obsługi blokad aplikacji
sp_getapplock Zasoby aplikacji Lock
sp_releaseapplock Odblokuj zasoby aplikacji
Uwaga: Różnica między blokowaniem tabeli w bazie danych
SELECT *FROM table WITH( HOLDLOCK ) Inne transakcje mogą odczytywać tabelę, ale nie mogą aktualizować ani usuwać
WYBIERZ *Z TABELI WITH(TABLOCKX) Inne transakcje nie mogą odczytywać, aktualizować i usuwać tabeli
|
Poprzedni:Nie było żadnego punktu podsłuchowego http://localhost:111/xxx.svc że c...Następny:BLOKADY SQL NOLOCK, HOLDLOCK, UPDLOCK, TABLOCK, TABLOCKX
|
