SQL atomic increment and locking strategies-czy to bezpieczne?

Mam pytanie dotyczące sql i strategii blokowania. Jako przykład, załóżmy, że mam licznik widoków dla obrazów na mojej stronie internetowej. Jeśli mam sproc lub podobne do wykonania następujących instrukcji: 

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

Załóżmy, że licznik dla określonego image_id ma wartość ' 0 ' w czasie t0. Jeśli dwie sesje aktualizujące ten sam licznik obrazów, s1 i s2, rozpoczynają się jednocześnie od t0, czy jest szansa, że obie te sesje odczytają wartość '0', zwiększą ją do '1' i obie spróbują zaktualizować licznik do '1' , więc licznik otrzyma wartość ' 1 'zamiast ' 2'?

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Wynik końcowy: niepoprawna wartość ' 1 ' dla image_id=15, powinna być 2.

Moje pytania to:

  1. czy taki scenariusz jest możliwy?
  2. Jeśli tak, czy poziom izolacji transakcji ma znaczenie?
  3. czy istnieje rozwiązanie konfliktu, które wykryłoby taki konflikt jako błąd?
  4. Czy można użyć dowolnej specjalnej składni w celu uniknięcia problemu (coś jak Compare And Swap (CAS) lub wyraźne techniki blokowania)?

Interesuje mnie odpowiedź ogólna, ale jeśli jej nie ma, interesują mnie odpowiedzi specyficzne dla MySql i InnoDB, ponieważ próbuję użyć tej techniki do implementacji sekwencji na InnoDB.

Edytuj: Możliwy może być również następujący scenariusz, co skutkuje tym samym zachowaniem. Zakładam, że jesteśmy na poziomie READ_COMMITED lub wyższym, więc s2 pobiera wartość od początku transakcji, chociaż S1 napisał już " 1 " do licznik.

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok
Author: Alexander Torstling, 2010-09-29
Source

2 answers

UPDATE zapytanie umieszcza blokadę aktualizacji na stronach lub rejestruje, które czyta.

Po podjęciu decyzji, czy zaktualizować rekord, blokada jest podnoszona lub promowana do wyłącznej blokady.

Oznacza to, że w tym scenariuszu: 

s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

s2 będzie czekać, aż s1 zdecyduje, czy napisać licznik, czy nie, a ten scenariusz jest w rzeczywistości niemożliwy.

Będzie tak: 

s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

Zauważ, że w InnoDB, DML zapytania nie podnoszą blokady aktualizacji z czytają płyty.

Oznacza to, że w przypadku pełnego skanowania tabeli rekordy, które zostały odczytane, ale zdecydowały się nie aktualizować, pozostają zablokowane do końca transakcji i nie mogą być aktualizowane z innej transakcji.

 28
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-09-29 13:13:30

Jeśli blokada nie jest wykonana prawidłowo, z pewnością jest możliwe uzyskanie tego typu stanu wyścigowego, a domyślny tryb blokowania (read committed) na to pozwala. W tym trybie odczyty tylko umieszczają współdzieloną blokadę na rekordzie, więc mogą zarówno widzieć 0, zwiększać go i zapisywać 1 do bazy danych.

Aby uniknąć tego stanu wyścigowego, należy ustawić wyłączną blokadę operacji odczytu. "Serializable" i "Repeatable Read" tryby współbieżności zrobią to, a dla operacji na jeden rząd są prawie równoważne.

Aby było całkowicie atomowe musisz:

  • ustawić odpowiedni poziom izolacji transakcji , taki jak Serializowalny. Zwykle można to zrobić z biblioteki klienta lub explicilty w SQL.
  • rozpocznij transakcję
  • odczytaj DANE
  • Update it
  • Zatwierdź transakcję.

Możesz również wymusić wyłączną blokadę odczytu za pomocą blokady (T-SQL) lub równoważnej podpowiedzi, w zależności od dialektu SQL.

Pojedyncze zapytanie aktualizacyjne zrobi to atomicznie, ale nie można podzielić operacji (być może odczytać wartość i zwrócić ją klientowi) bez upewnienia się, że odczyty wyjmują wyłączną blokadę. będziesz musiał uzyskać wartość atomicznie, aby zaimplementować sekwencję , więc sama aktualizacja prawdopodobnie nie jest wszystkim, czego potrzebujesz. nawet z atomic update, nadal masz race warunek, aby odczytać wartość po aktualizacja. odczyt będzie nadal musiał odbywać się w ramach transakcji (przechowując to, co dostał w zmiennej) i wydać wyłączną blokadę podczas odczytu.

Zauważ, że aby to zrobić bez tworzenia hot spot twoja baza danych musi mieć odpowiednie wsparcie dla autonomicznych (zagnieżdżonych) transakcji w ramach procedury składowanej. Zauważ, że czasami "zagnieżdżone" jest używane w odniesieniu do transakcji łańcuchowych lub zapisywania punktów, więc termin ten może być nieco mylący. Edytowałem to, aby odnosić się do autonomicznych transakcje.

Bez transakcji autonomicznych Twoje blokady są dziedziczone przez transakcję nadrzędną, która może cofnąć całą partię. Oznacza to, że będą one przechowywane do czasu zatwierdzenia transakcji nadrzędnej, co może zmienić twoją sekwencję w hot spot, który serializuje wszystkie transakcje za pomocą tej sekwencji. Wszelkie inne próby użycia sekwencji będą blokowane, dopóki cała transakcja rodzica się nie zatwierdzi.

IIRC Oracle obsługuje transakcje autonomiczne, ale DB/2 nie Ostatnio i SQL Server nie. z góry Nie wiem, czy InnoDB je obsługuje, ale Grey i Reuter mówią trochę o tym, jak trudne są do wdrożenia. W praktyce wydaje mi się, że jest całkiem prawdopodobne, że nie. YMMV.

 8
Author: ConcernedOfTunbridgeWells,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-12-13 14:33:09