Lab 11

back

Table of Contents

0. Transakcje
1. MVCC – Multi-version Concurrency Control
2. Anomalie
3. Tryby izolacji
4. READ COMMITTED
   4.0 Lost update
   4.1 Non-repeatable read
   4.2 Phantom read 👻
5. REPEATABLE READ
   5.0 Fixed – lost update
   5.1 Fixed – non-repeatable read
   5.2 Fixed – phantom read 🚫👻
   5.3 Serialization anomaly
6. SERIALIZABLE
7. Deadlock
8. Postgres > Oracle, ile razy mam to powtarzać also C# > Java
9. Materiały

0. Transakcje

Na Moodle’u są przykłady w MySQL-u i Oraclu. My będziemy bawić się Postgresem, którego model concurrency control jest trochę inny.

Będziemy korzystać z mało ambitnego schematu bazy danych, który zademonstruje wszystko, co nas interesuje.

CREATE TABLE account (
  id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) UNIQUE,
  balance NUMERIC(18, 4)
);

1. MVCC -- Multi-version Concurrency Control

https://www.postgresql.org/docs/7.1/mvcc.html

Unlike most other database systems which use locks for concurrency control, Postgres maintains data consistency by using a multiversion model. This means that while querying a database each transaction sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data. This protects the transaction from viewing inconsistent data that could be caused by (other) concurrent transaction updates on the same data rows, providing transaction isolation for each database session.

The main difference between multiversion and lock models is that in MVCC locks acquired for querying (reading) data don’t conflict with locks acquired for writing data and so reading never blocks writing and writing never blocks reading.

Baza Postgresa działa trochę jak immutable struktura danych znana z programowania funkcyjnego. Wiersze występują w wersjach i zmiany powodują stworzenie nowej wersji, a nie nadpisanie poprzedniej.

2. Anomalie

Rozważamy 5 rodzaji anomalii:

• Dirty read – czytanie zmian, które później zostają zrollbackowane
• Lost update – nadpisywanie zmian innej transakcji, która zakończyła się w trakcie naszej transakcji
• Non-repeatable read – odczytanie tego samego wiersza w trakcie jednej transakcji z dwoma różnymi wartościami w pewnej kolumnie
• Phantom read – to samo zapytanie wykonane drugi raz w trakcie transakcji zwraca inne wyniki
• Serialization anomaly – najsilniejszy warunek, transakcji nie da się uszeregować

3. Tryby izolacji

Tabela dopuszczalnych anomalii przy różnych trybach izolacji:

Jak widać PostgreSQL zapewnia więcej niż wymaga standard.

Po pierwsze, w Postgresie nie ma trybu READ UNCOMMITTED. Znaczy jest, ale jest równoważny z READ COMMITTED. W związku z tym nie da się w Posgresie wyprodukować dirty reada.

4. READ COMMITTED

Tryb READ COMMITTED to domyślny w Postgresie. Każde zapytanie w transakcji dostaje swój snapshot bazy, który widzi tylko zmiany scommittowane do momentu jej rozpoczęcia. Transakcja zachowuje się tak, jakby innych, równoległych transakcji nie było.

Transakcja zakłada zamki na wiersze, które modyfikuje. Są to jednak wyłącznie writer locki, co znaczy, że inna transakcja może swobodnie czytać te wiersze , ale gdy spróbuje je zmodyfikować, to się blokuje i czeka na zakończenie drugiej transakcji.

4.0 Lost update

Dane:

INSERT INTO account(name, balance) VALUES ('Murlak', 1500);

Rozważmy takie dwie transakcje:

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
᠎-- A. Step 1.
UPDATE account
  SET balance = balance + 1000.0
  WHERE name = 'Murlak';
  ᠎
COMMIT;

CREATE PROCEDURE
    charge("name" VARCHAR(255)) AS $$
DECLARE
        b NUMERIC(18, 4);
BEGIN
        -- B. Step 2.
        SELECT balance
          INTO b
          FROM account a
          WHERE a."name" = $1;
᠎
        IF b >= 1000.0 THEN
          -- B. Step 3.
          UPDATE account a
            SET balance = b - 1000.0
            WHERE a."name" = $1;
        END IF;
END;
$$ LANGUAGE plpgsql;
᠎
BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎
-- B. Step 1.
SELECT * FROM account a
WHERE "name" = 'Murlak';
᠎
CALL charge('Murlak');
᠎
COMMIT;

Jeśli zaczynamy od $1500$ to jedyna możliwa wartość balance przy szeregowaniu transakcji dla naszego wiersza to $1500$, ale READ COMMITTED nam tego nie zapewnia.

Lost update mamy dla następujący przeplotu:

$1000$ złotych zniknęło.

Note: Tutaj widać też, dlaczego dirty read jest niemożliwy. Żadna z transakcji nie widzi niescommitowanych zmian drugiej, bo pracują na osobnych snapshotach.

4.1 Non-repeatable read

Ponownie mamy jeden wiersz:

INSERT INTO account(name, balance) VALUES ('Murlak', 1500);

Rozważmy takie dwie transakcje:

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
-- A. Step 1
UPDATE account
  SET balance = balance * 1.05
  WHERE balance <= 1000.0;
᠎᠎
-- A. Step 2
UPDATE account
  SET balance = balance * 1.1
  WHERE balance > 1000.0;
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
-- B. Step 1.
UPDATE account
  SET balance = balance - 1000.0
  WHERE "name" = 'Murlak';
᠎᠎
COMMIT;

Transakcja A wygląda jakby zapewniała, że każdy wiersz dostanie albo $5\%$ albo $10\%$ odsetek. A więc jedyne możliwości dla serializowalnych transakcji to $525$, jeśli najpierw wykona się B, lub $650$, jeśli najpierw wykona się A.

Ponownie, READ COMMITTED nam tego nie zagwarantuje:

Dostaliśmy $500$, czyli niemożliwy do uzyskania sekwencyjnie stan końcowy. Widać tutaj rzeczony “non-repeatable read” – odczytaliśmy wiersz dwa razy, raz z wartością $1500$ w kolumnie balance, a raz $500$.

4.2 Phantom read 👻

Phantom read jest podobny do non-repeatable, ale trochę bardziej zniuansowany. Tym razem nie chodzi o niespójny widok wiersza, tylko danego zapytania.

INSERT INTO account(name, balance) VALUES ('Murlak', 1500);

CREATE OR REPLACE PROCEDURE
    assert_all_above_1000()
AS $$
BEGIN
        IF (
      -- A. Step 1.
          SELECT MIN(balance)
              FROM account
        ) < 1000 THEN
      RAISE EXCEPTION 'Cannot perform operation';
    END IF;
END;
$$ LANGUAGE plpgsql;
᠎᠎
BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
CALL assert_all_above_1000();
᠎᠎
-- A. Step 2.
UPDATE account
  SET balance = balance - 1000.0;
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
-- B. Step 1.
INSERT INTO account("name", balance)
  VALUES ('Stencel', 500);
᠎᠎
COMMIT;

Ujmując tę anomalię inaczej, wykonanie zapytania SELECT balance FROM account w dwóch różnych momentach A daje dwa różne zbiory wyników, gdzie w drugim zbiorze znajdują się jakieś wcześniej niewidziane wartości (fantomy).

5. REPEATABLE READ

Ponownie operujemy na snapshotach, jednak teraz to cała transakcja dostaje jeden snapshot, a nie każde zapytanie w transakcji. Dzięki temu pozbywamy się wszystkich trzech powyższych anomalii za jednym zamachem.

Co jeśli jakaś inna transakcja wykona modyfikację wierszy, które my chcieliśmy zmodyfikować? Takiego konfliktu nie umiemy dobrze rozwiązać, więc transakcja, która czekała, się crashuje.

5.0 Fixed -- lost update

Wróćmy do pierwszego przykładu z lost update:

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
᠎-- A. Step 1.
UPDATE account
  SET balance = balance + 1000.0
  WHERE name = 'Murlak';
  ᠎
COMMIT;

CREATE PROCEDURE
    charge("name" VARCHAR(255)) AS $$
DECLARE
        b NUMERIC(18, 4);
BEGIN
        -- B. Step 2.
        SELECT balance
          INTO b
          FROM account a
          WHERE a."name" = $1;
᠎
        IF b >= 1000.0 THEN
          -- B. Step 3.
          UPDATE account a
            SET balance = b - 1000.0
            WHERE a."name" = $1;
        END IF;
END;
$$ LANGUAGE plpgsql;
᠎
BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎
-- B. Step 1.
SELECT * FROM account a
WHERE "name" = 'Murlak';
᠎
CALL charge('Murlak');
᠎
COMMIT;

i rozważmy ten sam przeplot:

WAŻNE: z perspektywy programisty aplikacji wybór bardziej restrykcyjnego poziomu izolacji ma większe implikacje, niż tylko potencjalnie zmniejszona wydajność (w przypadku MVCC zresztą mało tracimy wydajności). Transakcje modyfikujące wiersze na wysokim poziomie izolacji mogą zawieść i rzucić wyjątkiem. Pisząc logikę musimy być na to gotowi i w jakiś sposób taką ewentualność obsłużyć, np. ponowić transakcję.

5.1 Fixed -- non-repeatable read

INSERT INTO account(name, balance) VALUES ('Murlak', 1500);

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
-- A. Step 1
UPDATE account
  SET balance = balance * 1.05
  WHERE balance <= 1000.0;
᠎᠎
-- A. Step 2
UPDATE account
  SET balance = balance * 1.1
  WHERE balance > 1000.0;
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
-- B. Step 1.
UPDATE account
  SET balance = balance - 1000.0
  WHERE "name" = 'Murlak';
᠎᠎
COMMIT;

Znowu mamy $500$ na koncie, ale teraz cała transakcja zawiodła i nikt nie dostał odsetek. Zapewne będziemy chcieli transakcję A ponawiać do skutku.

5.2 Fixed -- phantom read 🚫👻

INSERT INTO account(name, balance) VALUES ('Murlak', 1500);

CREATE OR REPLACE PROCEDURE
    assert_all_above_1000()
AS $$
BEGIN
        IF (
      -- A. Step 1.
          SELECT MIN(balance)
              FROM account
        ) < 1000 THEN
      RAISE EXCEPTION 'Cannot perform operation';
    END IF;
END;
$$ LANGUAGE plpgsql;
᠎᠎
BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
CALL assert_all_above_1000();
᠎᠎
-- A. Step 2.
UPDATE account
  SET balance = balance - 1000.0;
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
-- B. Step 1.
INSERT INTO account("name", balance)
  VALUES ('Stencel', 500);
᠎᠎
COMMIT;

5.3 Serialization anomaly

Nadal możemy jednak sforsować zachowanie, którego nie da się uzyskać szeregowo.

Będziemy potrzebowali trochę więcej danych, żeby to zademonstrować. Załóżmy, że tabela wygląda tak:

Transakcje są następujące:

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
-- A. Step 1.
INSERT INTO account ("name", balance)
SELECT 'Krzysztof Diks', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Marcin %';
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL REPEATABLE READ;
᠎᠎
-- B. Step 1.
INSERT INTO account ("name", balance)
SELECT 'Marcin Benke', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Krzysztof %';
᠎᠎
COMMIT;

Jakie są możliwe wyniki dla uszeregowanych transakcji?

• A. B – Suma po Marcinach to $300$, dodajemy wiersz ('Krzysztof Diks', 300). B wykonuje się teraz i widzi sumę po Krzysztofach $8300$. W wyniku mamy więc:

• B. A – Suma po Krzysztofach to $8000$, dodajemy wiersz ('Marcin Benke', 8000). A wykonuje się teraz i widzi sumę po Marcinach $8300$. W wyniku mamy więc:

W przypadku REPEATABLE READS nie tylko nie musi tak być, taki wynik jest niemożliwy jeśli transakcje zbiegną się w czasie. Niezależnie od kolejności, jeśli X zacznie się zanim Y scommituje, to dostanie osobny snapshot bez wierszy wstawionych przez Y. A więc jedyny możliwy wynik to:

6. SERIALIZABLE

Tryb SERIALIABLE w Postgresie działa właściwie tak samo jak REPEATABLE READ, ale dodaje dodatkowy poziom analizy zależności na przetwarzaną transakcję. Nie mamy żadnych nowych zamków, ale baza danych bacznie obserwuje zależności między zapisami i odczytami i jeśli wykryje, że nie da się ich wykonać szeregowo (czyli wykryje cykl), to nie pozwala nam scommitować danej transakcji.

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
-- A. Step 1.
INSERT INTO account ("name", balance)
SELECT 'Krzysztof Diks', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Marcin %';
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
-- B. Step 1.
INSERT INTO account ("name", balance)
SELECT 'Marcin Benke', SUM(balance)
  FROM account
  WHERE "name" LIKE 'Krzysztof %';
᠎᠎
COMMIT;

Niezależnie od tego, w jakiej kolejności spróbujemy wykonać te transakcje (o ile zaczniemy jedną przed committem drugiej), to ta druga w kolejności zostanie przerwana z błędem:

SQL Error [40001]: ERROR: could not serialize access due to read/write dependencies among transactions
  Detail: Reason code: Canceled on identification as a pivot, during commit attempt.
  Hint: The transaction might succeed if retried.

7. Deadlock

Korzystając z tego co już wiemy dość łatwo zmusić bazę do deadlocku. Dane:

INSERT INTO account ("name", balance)
  VALUES ('Murlak', 1500.0),
         ('Stencel', 500.0);

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
-- A. Step 1.
UPDATE account
  SET balance = balance - 500.0
  WHERE "name" = 'Murlak';
᠎᠎
-- A. Step 2.
UPDATE account
  SET balance = balance + 500.0
  WHERE "name" = 'Stencel';
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL READ COMMITTED;
᠎᠎
-- B. Step 1.
UPDATE account
  SET balance = balance - 500.0
  WHERE "name" = 'Stencel';
᠎᠎
-- B. Step 2.
UPDATE account
  SET balance = balance + 500.0
  WHERE "name" = 'Murlak';
᠎᠎
COMMIT;

Postgres wykrywa tę sytuację i po chwili wyrzuca jedną z transakcji z komunikatem:

SQL Error [40P01]: ERROR: deadlock detected

8. Postgres > Oracle, ile razy mam to powtarzać also C# > Java

W skrypcie na Moodle’u są jeszcze dwa przykłady transakcji, które się nie serializują a przechodzą w Oraclu.

Na pustej tabeli:

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
SELECT * FROM account;
᠎᠎
INSERT INTO account ("name", balance)
  VALUES ('Alice', 300);
᠎᠎
SELECT * FROM account;
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
SELECT * FROM account;
᠎᠎
INSERT INTO account ("name", balance)
  VALUES ('Bob', 300);
᠎᠎
SELECT * FROM account;
᠎᠎
COMMIT;

Oraz dla danych (trzeba zrzucić UNIQUE constraint):

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
UPDATE account
  SET "name" = 'Alice'
  WHERE "name" = 'Bob';
᠎᠎
COMMIT;

BEGIN TRANSACTION
ISOLATION LEVEL SERIALIZABLE;
᠎᠎
UPDATE account
  SET "name" = 'Bob'
  WHERE "name" = 'Alice';
᠎᠎
COMMIT;

Obie te sytuacje są wykrywane przez PostgreSQL i odrzucane przy commicie drugiej w kolejności transakcji.

9. Materiały

Dokumentacja poziomów izolacji PostgreSQL:
https://www.postgresql.org/docs/current/transaction-iso.html

Polecam, bardzo pouczająca lektura.

Pełen skrypt z wszystkimi transakcjami z tej strony:
isolation-a.sql
isolation-b.sql

back