SQL: Operacje na transakcjach

Interakcje transakcji - tryb read committed - użytkownik postgres - część 1 · 3 min

-- umożliwiamy modyfikowanie tabeli equipment drugiemu użytkownikowi
GRANT ALL ON TABLE equipment TO other;
-- rozpoczynamy transakcję
BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają niedużą wartość obrony. 
-- Wartość zmiany obrony zależna jest od WSZYSTKICH rekordów z niepustą obroną
UPDATE equipment SET armor = armor + (SELECT AVG(armor)/2 FROM equipment WHERE armor IS NOT NULL)
                 WHERE armor < 20;

Interakcje transakcji - tryb read committed - użytkownik other - część 1 · 4 min

-- rozpoczynamy transakcję
BEGIN;
-- ustawiamy bazowy poziom izolacji (read commited)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- odpalamy aktualizację rekordów, które mają dużą wartość obrony
-- w tym momencie baza będzie czekała na zakończenie pierwszej transakcji (użytkownika postgres),
-- która modyfikuje te same rekordy co poniższe zapytanie
UPDATE equipment SET armor = armor + 3 WHERE armor > 20;

Interakcje transakcji - tryb read committed - użytkownik postgres - część 2 · 5 min

--   umożliwiamy modyfikowanie tabeli equipment drugiemu użytkownikowi
-- GRANT ALL ON TABLE equipment TO other;
--   rozpoczynamy transakcję
-- BEGIN;
--   ustawiamy bazowy poziom izolacji (read commited)
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--   upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
-- SELECT * FROM equipment WHERE armor IS NOT NULL;
--   odpalamy aktualizację rekordów, które mają niedużą wartość obrony. 
--   Wartość zmiany obrony zależna jest od WSZYSTKICH rekordów z niepustą obroną
-- UPDATE equipment SET armor = armor + (SELECT AVG(armor)/2 FROM equipment WHERE armor IS NOT NULL)
--                 WHERE armor < 20;

-- w tej samej transakcji (z części pierwszej) sprawdzamy zmiany wprowadzone przez UPDATE
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- i zatwierdzamy transakcję (odblokowywując tym samym transakcję użytkownika other)
-- transakcja użytkownika other widzi już zmiany wprowadzone przez UPDATE z obecnej transakcji
COMMIT;

Interakcje transakcji - tryb read committed - użytkownik other - część 2 · 6 min

--   rozpoczynamy transakcję
-- BEGIN;
--   ustawiamy bazowy poziom izolacji (read commited)
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
--   upewniamy się jaki jest stan danych (rekordy z niepustą wartością obrony)
-- SELECT * FROM equipment WHERE armor IS NOT NULL;
--   odpalamy aktualizację rekordów, które mają dużą wartość obrony
--   w tym momencie baza będzie czekała na zakończenie pierwszej transakcji (użytkownika postgres),
--   która modyfikuje te same rekordy co poniższe zapytanie
-- UPDATE equipment SET armor = armor + 3 WHERE armor > 20;

-- w tej samej transakcji (z części pierwszej) weryfikujemy zmiany wprowadzone przez UPDATE 
-- (zmiany z transakcji użytkownika postgres są też tutaj widoczne)
SELECT * FROM equipment WHERE armor IS NOT NULL;
-- cofamy zmiany wprowadzone przez tą transakcję
ROLLBACK;
-- teraz widoczne są tylko zmiany z transakcji użytkownika postgres
SELECT * FROM equipment WHERE armor IS NOT NULL;

Interakcje transakcji - tryb repeatable read - użytkownik postgres - część 1 · 7 min

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- wprowadzamy małą poprawkę do wartości obrony
UPDATE equipment SET armor = armor - 3 WHERE armor IS NOT NULL;
-- sprawdzamy jak się ta zmiana zaaplikowała
SELECT * FROM equipment WHERE armor IS NOT NULL;

Interakcje transakcji - tryb repeatable read - użytkownik other · 7 min

BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- wprowadzamy małą poprawkę do wartości obrony
-- ale tutaj transakcja zatrzyma się, czekając na zakończenie się transakcji użytkownika postgres
UPDATE equipment SET armor = armor - 5 WHERE armor IS NOT NULL;

--po zakończeniu się transakcji użytkownika postgres tutaj nastąpi błąd (serializacji)

Interakcje transakcji - tryb serializable - użytkownik postgres - część 1 · 12 min

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- wstawiamy nowy rekord z wartością obrony zależną od modyfikacji w równoległej transakcji użytkownika other 
-- średnia z wartości obrony dla bohaterów z hero_id > 4 
-- (a w równoległej transakcji wstawiamy rekord powiązany z hero_id = 5)
INSERT INTO equipment (equipment_name, equipment_type, damage, amount, hero_id)
       SELECT 'topór', 'broń', CAST(AVG(damage) AS integer), 1, 2 FROM equipment WHERE hero_id > 4;
-- sprawdzamy jaka wartość została wyliczona
SELECT * FROM equipment WHERE damage IS NOT NULL;

Interakcje transakcji - tryb serializable - użytkownik other - część 1 · 13 min

BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- wstawiamy nowy rekord z wartością obrony zależną od modyfikacji w równoległej transakcji użytkownika postgres 
-- średnia z wartości obrony dla bohaterów z hero_id <= 4 
-- (a w równoległej transakcji wstawiamy rekord powiązany z hero_id = 2)
INSERT INTO equipment (equipment_name, equipment_type, damage, amount, hero_id)
       SELECT 'łuk', 'broń', CAST(AVG(damage) AS integer), 1, 5 FROM equipment WHERE hero_id <= 4;
-- sprawdzamy jaka wartość została wyliczona
SELECT * FROM equipment WHERE damage IS NOT NULL;