Wstępne blokowanie tabel w transakcji MS SQL

Załóżmy, że program ma wykonać pewien proces w jednej transakcji, czyli program uruchamia "Begin transaction", potem kilka różnych poleceń, a na końcu "Commit" lub ewentualnie "rollback"

Załóżmy, że są dwie sesje, i dwa procesy w aplikacji, gdzie w jednym jest modyfikowana tabela A, a później jest modyfikowana tabela B, a w drugim procesie jest najpierw modyfikowana tabela B, a później jest modyfikowana tabela A.

O ile się nie mylę, w transakcji tabele są blokowanie sukcesywnie w miarę przebiegu transakcji i uruchomienie obu powyższych procesów naraz może spowodować wzajemne zablokowanie tych procesów (deadlock).

W jaki sposób (jakie polecenia SQL) powodują samo blokowanie tabeli lub rekordów w tabelach?

Gdyby obie transakcje rozpoczynały się od polecenia zablokowania tabeli A i B naraz lub blokowane zawsze w kolejności najpierw A, a potem B, wtedy nie ma możliwości wywołania zakleszczenia, bo nie zostanie stworzona sytuacja, w której jedna transakcja ma zablokowaną tabelę B i próbuje zablokować tabelę A podczas, gdy inna transakcja ma zablokowaną tabelę A i próbuje zablokować tabelę B, co jest jednym z warunków koniecznych wystąpienia zakleszczenia. Dodatkowo, obie transakcje się wykonają za każdym razem, bo wykrycie deadlock przez serwer zrywa (rollback) obie transakcje.

andrzejlisek napisał(a):

Załóżmy, że są dwie sesje, i dwa procesy w aplikacji, gdzie w jednym jest modyfikowana tabela A, a później jest modyfikowana tabela B, a w drugim procesie jest najpierw modyfikowana tabela A, a później jest modyfikowana tabela B.

Czyli procesy działają tak samo?

O ile się nie mylę, w transakcji tabele są blokowanie sukcesywnie w miarę przebiegu transakcji i uruchomienie obu powyższych procesów naraz może spowodować wzajemne zablokowanie tych procesów (deadlock).

Rozpoczynasz transakcję.
Robisz modyfikacje na wszystkich tabelach.
Kończysz transakcję.

Robisz to dla obu procesów. I generalnie wszystko powinno być dobrze.
Bardzo małe jest prawdopodobieństwo, że oba procesy rozpoczną transakcję w idealnie w tym samym momencie. Jest tak niskie, że pomijalne.

Juhas napisał(a):

andrzejlisek napisał(a):

Załóżmy, że są dwie sesje, i dwa procesy w aplikacji, gdzie w jednym jest modyfikowana tabela A, a później jest modyfikowana tabela B, a w drugim procesie jest najpierw modyfikowana tabela A, a później jest modyfikowana tabela B.

Czyli procesy działają tak samo?

Procesy są różne, nieistotne, jakie konkretnie czynności wykonują, ale istotne, że są to modyfikacje danych w takiej kolejności, że mogą spowodować zakleszczenie. Chodzi o przykład, że jeden najpierw blokuje tabelę A, a potem tabelę B, a drugi proces dokładnie odwrotnie. Jest to najprostszy przypadek spełnienia jednego z warunków wystąpienia zakleszczenia.

Juhas napisał(a):

O ile się nie mylę, w transakcji tabele są blokowanie sukcesywnie w miarę przebiegu transakcji i uruchomienie obu powyższych procesów naraz może spowodować wzajemne zablokowanie tych procesów (deadlock).

Rozpoczynasz transakcję.
Robisz modyfikacje na wszystkich tabelach.
Kończysz transakcję.

Robisz to dla obu procesów. I generalnie wszystko powinno być dobrze.
Bardzo małe jest prawdopodobieństwo, że oba procesy rozpoczną transakcję w idealnie w tym samym momencie. Jest tak niskie, że pomijalne.

Rozumiem, że w dobrze przemyślanym systemie to nie powinno się zdarzać, a jak się zdarza, to należy przemyśleć procesy i od nowa je zaimplementować uwzględniając kolejność blokowania tabel. Ale pytanie jest nie na temat projektowania przebiegu procesu i działania systemu, tylko na temat blokowania tabel.

W takim razie załóżmy, że proces "1" wysyła polecenia, istotne jest, żeby cały proces był w jednej transakcji:

begin transaction
update table A set xxx = xxx --Blokuje tabelę A
-- Jakaś logika biznesowa, która robi dużo select i obliczeń i trochę trwa
update table B set xxx = xxx --Blokuje tabelę B
commit --Odblokowuje najpierw tabelę B, a potem tabelę A

Proces "2" wysyła polecenia:

begin transaction
update table B set xxx = xxx --Blokuje tabelę B
-- Jakaś logika biznesowa, która robi dużo select i obliczeń i trochę trwa
update table A set xxx = xxx --Blokuje tabelę A
commit --Odblokowuje najpierw tabelę A, a potem tabelę B

Odpalamy proces "1" i w czasie jego trwania odpalamy proces "2". Zablokowanie jednej tabeli nastąpi w chwili wykonania pierwszego update, a zakleszczenie nastąpi w chwili dojścia do ostatniego update obu procesów.

Załóżmy, że proces "2" się lekko zmodyfikuje:

begin transaction
-- "Zablokuj tabelę A" -- Polecenie, o które się pytam, blokuje tabelę A
update table B set xxx = xxx --Blokuje tabelę B
-- Jakaś logika biznesowa, która robi dużo select i obliczeń i trochę trwa
update table A set xxx = xxx --Tabela A została wcześniej zablokowana, więc niczego nie blokuje
commit --Odblokowuje najpierw tabelę A, a potem tabelę B

Wtedy, jak się odpali "1", a później "2", to w chwili odpalenia 2 jest zablokowana tabela A, więc proces poczeka na odblokowanie tabeli "A", co nastąpi dopiero po zakończeniu procesu "1". W takich warunkach wystąpienie deadlock nie jest możliwe. W takim razie, jakie poleceniem SQL dokonuje się wstępnego blokowania tabeli? Jak widać, teoretycznie jedno polecenie może rozwiązać problem w istniejącym systemie bez konieczności przerabiania i testowania.

blokowane są REKORDY, które są zmieniane. Jeśli zrobisz UPDATE tabela SET pole=wartosc bez warunków brzegowych to zablokujesz wszystkie rekordy. Jeśli dodasz np. WHERE id=1 to zablokowany zostanie tylko rekord z id=1
"Wstępne" blokowanie rekordu to albo UPDATE tabela SET id = id WHERE id = 1 albo SELECT id FROM tabela WHERE id = 1 FOR UPDATE. Zalecany jest ten drugi sposób bo mimo iż pierwszy nic nie robi to np. postgres przy takim zapytaniu usunie cały rekord i wstawi go jeszcze raz z takimi samymi danymi

Właśnie dzisiaj wróciłem do tego tematu, bo pierwszy raz testuję to rozwiązanie w praktyce w MS SQL.

Wariant SELECT id FROM tabela WHERE id = 1 FOR UPDATE odpada, MS SQL zwraca błąd, że FOR UPDATE może być tylko przy deklarowaniu kursora w SQL, więc zostaje wariant UPDATE tabela SET id = id WHERE id = 1.

Polecenie (po zmianie nazwy tabeli i pola na odpowiednie) się wykonało, a jedna i ta sama transakcja trwa dalej. Jednakże w Management Studio, jak zrobię w tym samym czasie select * from tabela where id = 1, to pokazują się dane. Czy to jest normalne? Skoro SQL zablokował wszystkie pozycje z id = 1 na modyfikacje, to jak to jest, że na innym połączeniu pokazuje te dane zamiast czekać, aż transakcja się zamknie (commit lub rollback)? Czy może SQL jest na tyle mądry, ze interpretował polecenie, że ono tak naprawdę nie zmienia danych i nie założył blokady na wyłączność?

Juhas napisał(a):

andrzejlisek napisał(a):

Załóżmy, że są dwie sesje, i dwa procesy w aplikacji, gdzie w jednym jest modyfikowana tabela A, a później jest modyfikowana tabela B, a w drugim procesie jest najpierw modyfikowana tabela A, a później jest modyfikowana tabela B.

Czyli procesy działają tak samo?

O ile się nie mylę, w transakcji tabele są blokowanie sukcesywnie w miarę przebiegu transakcji i uruchomienie obu powyższych procesów naraz może spowodować wzajemne zablokowanie tych procesów (deadlock).

Rozpoczynasz transakcję.
Robisz modyfikacje na wszystkich tabelach.
Kończysz transakcję.

Robisz to dla obu procesów. I generalnie wszystko powinno być dobrze.
Bardzo małe jest prawdopodobieństwo, że oba procesy rozpoczną transakcję w idealnie w tym samym momencie. Jest tak niskie, że pomijalne.

Sądzę, ze ważniejszą kategorią od "procesu" jest "połączenie SQL". To w tym wymiarze zachodzi.
Oczywiście, ze inny proces --> inne połączenie, to jasne.

andrzejlisek napisał(a):

Czy to jest normalne?

Tak, tak to właśnie działa. Dostałbyś zakleszczenie (dead lock) albo exception jakbyś z INNEJ transakcji spróbował zrobić update takiego rekordu

Skoro SQL zablokował wszystkie pozycje z id = 1 na modyfikacje, to jak to jest, że na innym połączeniu pokazuje te dane zamiast czekać, aż transakcja się zamknie (commit lub rollback)? Czy może SQL jest na tyle mądry, ze interpretował polecenie, że ono tak naprawdę nie zmienia danych i nie założył blokady na wyłączność?

Tak, jest na tyle "mądry" :). W zależności od poziomu izolacji transakcji (poczytać co to) select może zwrócić różne dane (w sensie mniej lub bardziej "aktualne")

Dowiedziałem się, że możliwe jest wymuszenie zablokowanie także poprzez polecenie SELECT id FROM tabela WITH (tablockx) WHERE id = 1.

Modyfikacja była w aplikacji na poziomie read uncommited, a odczyt był w innej aplikaji (inne połączenie) na poziomie read commited. W międzyczasie stwierdziłem, że ten MS SQL działa tak, że jak trwa transakcja, która zmieniła dane, ale trwa nadal (nie było commit), to jestem w stanie odczytać dane selectem bez żadnych hintów, tyle, że serwer podaje dane sprzed modyfikacji. Jak w tej transakcji modyfikujacej puszczę commit, co kończy transakcję, to widzę dane po modyfikacji. Spodziewałem się, ze za pierwszym razem nie zobaczę żadnych danych dopóki transakcja nie będzie zakończona ani cofnięta.

andrzejlisek napisał(a):

Spodziewałem się, ze za pierwszym razem nie zobaczę żadnych danych dopóki transakcja nie będzie zakończona ani cofnięta.

Niby czemu? Dopóki transakcja nie została zatwierdzona, nic nie zostało zmienione w bazie, czyli zwracane są obecne dane. Po to właśnie są transakcje.