Zatem omówiliśmy kwestie związane z izolacją, a następnie zrobiliśmy dygresję o organizacji danych na niskim poziomie. A teraz przechodzimy do najciekawszego — wersji wierszy.

Nagłówek

Jak już wspomnieliśmy, każdy wiersz może jednocześnie występować w bazie danych w kilku wersjach. Należy jakoś odróżnić jedną wersję od drugiej. W tym celu każda wersja ma dwa znaczniki, które określają „czas” działania danej wersji (xmin i xmax). W cudzysłowie, ponieważ nie chodzi o czas jako taki, ale o specjalny rosnący licznik. A tym licznikiem jest numer transakcji.

(Jak zwykle, w rzeczywistości wszystko jest bardziej skomplikowane: numer transakcji nie może zawsze rosnąć z powodu ograniczonej rozdzielczości licznika. Ale te szczegóły omówimy dokładnie, gdy dojdziemy do zamrożenia.)

Gdy wiersz jest tworzony, wartość xmin jest ustawiana na numer transakcji, która wykonała polecenie INSERT, a wartość xmax nie jest wypełniana.

Gdy wiersz jest usuwany, wartość xmax aktualnej wersji jest oznaczana numerem transakcji, która wykonała DELETE.

Gdy wiersz jest zmieniany przez polecenie UPDATE, w rzeczywistości wykonywane są dwie operacje: DELETE i INSERT. W aktualnej wersji wiersza ustawiane jest xmax równe numerowi transakcji, która wykonała UPDATE. Następnie tworzona jest nowa wersja tego samego wiersza; wartość xmin jest zgodna z wartością xmax poprzedniej wersji.

Pola xmin i xmax są częścią nagłówka wersji wiersza. Oprócz tych pól nagłówek zawiera także inne, na przykład:

• infomask — ciąg bitów określających właściwości danej wersji. Jest ich dość sporo; podstawowe z nich omówimy stopniowo.
• ctid — odniesienie do następnej, nowszej wersji tego samego wiersza. W najnowszej, aktualnej wersji wiersza ctid odnosi się do samej tej wersji. Numer ma postać (x,y), gdzie x to numer strony, a y to numer porządkowy wskaźnika w tablicy.
• mapa bitowa wartości nieokreślonych — oznacza te kolumny danej wersji, które zawierają wartość nieokreśloną (NULL). NULL nie jest jednym z normalnych wartości typów danych, dlatego ten atrybut musi być przechowywany osobno.

W rezultacie nagłówek staje się dość duży — co najmniej 23 bajty na każdą wersję wiersza, a zazwyczaj więcej z powodu mapy bitowej NULL-ów. Jeśli tabela jest „wąska” (to znaczy zawiera mało kolumn), koszty ogólne mogą przekraczać użyteczne informacje.

Wstawka

Przyjrzyjmy się dokładniej, jak operacje na ciągach są wykonywane na niskim poziomie, zaczynając od wstawiania.

Do eksperymentów stwórzmy nową tabelę z dwiema kolumnami i indeksem po jednej z nich:

=> CREATE TABLE t(
  id serial,
  s text
);
=> CREATE INDEX ON t(s);

Wstawimy jeden wiersz, rozpoczynając wcześniej transakcję.

=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');

Oto numer naszej bieżącej transakcji:

=> SELECT txid_current();

 txid_current 
--------------
         3664
(1 row)

Zajrzyjmy do zawartości strony. Funkcja heap_page_items z rozszerzenia pageinspect pozwala uzyskać informacje o wskaźnikach i wersjach wierszy:

=> SELECT * FROM heap_page_items(get_raw_page('t',0)) gx

-[ RECORD 1 ]-------------------
lp          | 1
lp_off      | 8160
lp_flags    | 1
lp_len      | 32
t_xmin      | 3664
t_xmax      | 0
t_field3    | 0
t_ctid      | (0,1)
t_infomask2 | 2
t_infomask  | 2050
t_hoff      | 24
t_bits      | 
t_oid       | 
t_data      | x0100000009464f4f

Zwróćmy uwagę, że w PostgreSQL termin heap (stos) odnosi się do tabel. To kolejne dziwne użycie terminu – stos jest znaną strukturą danych, która nie ma nic wspólnego z tabelą. Tutaj to słowo odnosi się do znaczenia „wszystko wrzucone w stos”, w przeciwieństwie do uporządkowanych indeksów.

Funkcja pokazuje dane „tak jak są”, w formacie trudnym do zrozumienia. Aby to rozgryźć, zostawimy tylko część informacji i zinterpretujemy ją:

=> SELECT '(0,'||lp||')' AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'niewykorzystany'
         WHEN 1 THEN 'normalny'
         WHEN 2 THEN 'przekierowuje do '||lp_off
         WHEN 3 THEN 'nieżywy'
       END AS state,
       t_xmin as xmin,
       t_xmax as xmax,
       (t_infomask & 256) > 0  AS xmin_commited,
       (t_infomask & 512) > 0  AS xmin_aborted,
       (t_infomask & 1024) > 0 AS xmax_commited,
       (t_infomask & 2048) > 0 AS xmax_aborted,
       t_ctid
FROM heap_page_items(get_raw_page('t',0)) gx

-[ RECORD 1 ]-+-------
ctid          | (0,1)
state         | normalny
xmin          | 3664
xmax          | 0
xmin_commited | f
xmin_aborted  | f
xmax_commited | f
xmax_aborted  | t
t_ctid        | (0,1)

Oto co zrobiliśmy:

• Dodaliśmy zerowy wskaźnik do numeru wskaźnika, aby uzyskać na przykład t_ctid: (numer strony, numer wskaźnika).
• Zinterpretowaliśmy stan wskaźnika lp_flags. Tutaj jest on „normalny” – to znaczy, że wskaźnik rzeczywiście odnosi się do wersji wiersza. Inne wartości omówimy później.
• Z wszystkich bitów informacyjnych wydzieliliśmy na razie tylko dwie pary. Bity xmin_committed i xmin_aborted wskazują, czy transakcja o numerze xmin została zatwierdzona (czy została anulowana). Dwa podobne bity odnoszą się do transakcji o numerze xmax.

Co widzimy? Po wstawieniu wiersza na stronie tabeli pojawi się wskaźnik z numerem 1, odnoszący się do pierwszej i jedynej wersji wiersza.

W wersji wiersza pole xmin jest wypełnione numerem bieżącej transakcji. Transakcja jest jeszcze aktywna, więc oba bity xmin_committed i xmin_aborted nie są ustawione.

Pole ctid wersji wiersza odnosi się do tego samego wiersza. Oznacza to, że nie istnieje nowsza wersja.

Pole xmax jest wypełnione fikcyjnym numerem 0, ponieważ ta wersja wiersza nie została usunięta i jest aktualna. Transakcje nie będą zwracać uwagi na ten numer, ponieważ bit xmax_aborted jest ustawiony.

Zróbmy kolejny krok, aby poprawić czytelność, dołączając informacje o bitach do numerów transakcji. Stworzymy funkcję, ponieważ zapytanie będzie nam jeszcze wielokrotnie potrzebne:

=> CREATE FUNCTION heap_page(relname text, pageno integer)
RETURNS TABLE(ctid tid, state text, xmin text, xmax text, t_ctid tid)
AS $$
SELECT (pageno,lp)::text::tid AS ctid,
       CASE lp_flags
         WHEN 0 THEN 'niewykorzystany'
         WHEN 1 THEN 'normalny'
         WHEN 2 THEN 'przekierowanie do '||lp_off
         WHEN 3 THEN 'martwy'
       END AS state,
       t_xmin || CASE
         WHEN (t_infomask & 256) > 0 THEN ' (c)'
         WHEN (t_infomask & 512) > 0 THEN ' (a)'
         ELSE ''
       END AS xmin,
       t_xmax || CASE
         WHEN (t_infomask & 1024) > 0 THEN ' (c)'
         WHEN (t_infomask & 2048) > 0 THEN ' (a)'
         ELSE ''
       END AS xmax,
       t_ctid
FROM heap_page_items(get_raw_page(relname,pageno))
ORDER BY lp;
$$ LANGUAGE SQL;

W tej formie znacznie wyraźniej widać, co dzieje się w nagłówku wersji wiersza:

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normalny | 3664 | 0 (a) | (0,1)
(1 wiersz)

Podobne, ale znacznie mniej szczegółowe informacje można uzyskać z samej tabeli, używając pseudokolumn xmin i xmax:

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3664 |    0 |  1 | FOO
(1 wiersz)

Zatwierdzenie

Po pomyślnym zakończeniu transakcji należy zapamiętać jej status — oznaczyć, że została zatwierdzona. Do tego służy struktura nazywana XACT (a przed wersją 10 nazywana CLOG (commit log) i ta nazwa może jeszcze występować w różnych miejscach).

XACT — to nie tabela katalogu systemowego; to pliki w katalogu PGDATA/pg_xact. Zawierają dla każdej transakcji dwa bity: committed i aborted — dokładnie tak, jak w nagłówku wersji wiersza. Informacje te są rozdzielone na kilka plików wyłącznie dla wygody; jeszcze wrócimy do tego zagadnienia, gdy będziemy omawiać zamrożenie. Praca z tymi plikami odbywa się strona po stronie, tak jak w przypadku wszystkich innych.

Zatem, podczas zatwierdzania transakcji w XACT ustawia się bit committed dla tej transakcji. I to wszystko, co się dzieje podczas zatwierdzania (póki co nie mówimy o dzienniku przedzapisów).

Kiedy inna transakcja odniesie się do strony tabeli, na którą właśnie spojrzeliśmy, będzie musiała odpowiedzieć na kilka pytań.

1. Czy transakcja xmin została zakończona? Jeśli nie, to stworzona wersja wiersza nie powinna być widoczna.
   Taka weryfikacja odbywa się poprzez przeglądanie innej struktury, która znajduje się w pamięci współdzielonej instancji i nazywa się ProcArray. Zawiera ona listę wszystkich aktywnych procesów i dla każdego z nich określa numer jego aktualnej (aktywnej) transakcji.
2. Jeśli została zakończona, to w jaki sposób — zatwierdzeniem czy wycofaniem? Jeśli wycofaniem, to wersja wiersza również nie powinna być widoczna.
   Tam właśnie potrzebne jest XACT. Jednak, chociaż ostatnie strony XACT są przechowywane w buforach w pamięci operacyjnej, sprawdzanie XACT za każdym razem jest kosztowne. Dlatego ustalony raz status transakcji zapisywany jest w bitach xmin_committed i xmin_aborted wersji wiersza. Jeśli jeden z tych bitów jest ustawiony, to stan transakcji xmin uznaje się za znany i następnej transakcji nie będzie już musiała odnosić się do XACT.

Dlaczego te bity nie są ustawiane przez samą transakcję wykonującą wstawienie? Podczas wstawiania transakcja jeszcze nie wie, czy zakończy się ona sukcesem. A w momencie zatwierdzania nie wiadomo, które dokładnie wiersze w których dokładnie stronach zostały zmienione. Może być ich wiele, a zapamiętywanie ich jest nieopłacalne. Ponadto niektóre strony mogą zostać usunięte z bufora na dysk; ponowne ich odczytywanie w celu zmiany bitów znacznie opóźniłoby zatwierdzenie.

Drugą stroną oszczędności jest to, że po zmianach każda transakcja (nawet wykonująca proste odczyty — SELECT) może zacząć zmieniać strony danych w buforze.

Zatem, zatwierdzimy zmianę.

=> COMMIT;

Na stronie nic się nie zmieniło (ale wiemy, że status transakcji już został zapisany w XACT):

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normalny | 3664 | 0 (a) | (0,1)
(1 wiersz)

Teraz transakcja, która jako pierwsza odwołała się do strony, będzie musiała określić status transakcji xmin i zapisze go w informacyjnych bitach:

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | FOO
(1 row)

=> SELECT * FROM heap_page('t',0);

 ctid  | state  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3664 (c) | 0 (a) | (0,1)
(1 row)

Usunięcie

Podczas usuwania wiersza w polu xmax aktualnej wersji zapisywany jest numer bieżącej usuwającej transakcji, a bit xmax_aborted jest resetowany.

Zauważmy, że ustalone wartość xmax odpowiadająca aktywnej transakcji działa jako blokada wiersza. Jeśli inna transakcja będzie chciała zaktualizować lub usunąć ten wiersz, będzie musiała poczekać na zakończenie transakcji xmax. Więcej na temat blokad będziemy mówić później. Na razie zaznaczymy tylko, że liczba blokad wierszy nie jest w żaden sposób ograniczona. Nie zajmują one miejsca w pamięci operacyjnej, a wydajność systemu nie cierpi z powodu ich liczby. Prawda jest, że „długie” transakcje mają inne wady, ale o tym także później.

Usuńmy wiersz.

=> ROZPOCZNIJ;
=> USUŃ Z t;
=> WYBIERZ txid_current();

 txid_current 
--------------
         3665
(1 wiersz)

Widzimy, że numer transakcji został zapisany w polu xmax, ale bity informacyjne nie są ustawione:

=> SELECT * FROM heap_page('t',0);

 ctid  | stan   |   xmin   | xmax | t_ctid 
-------+--------+----------+------+--------
 (0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 wiersz)

Cofnięcie

Cofnięcie zmian działa analogicznie do zatwierdzania, z tą różnicą, że w XACT dla transakcji ustawiany jest bit aborted. Cofnięcie wykonuje się tak samo szybko, jak i zatwierdzenie. Choć polecenie nazywa się ROLLBACK, cofnięcie zmian nie następuje: wszystko, co transakcja zdążyła zmienić na stronach danych, pozostaje bez zmian.

=> ROLLBACK;
=> WYBIERZ * Z heap_page('t',0);

 ctid  | stan   |   xmin   | xmax | t_ctid 
-------+--------+----------+------+--------
 (0,1) | normal | 3664 (c) | 3665 | (0,1)
(1 wiersz)

Podczas odwołania do strony sprawdzany będzie status, a bit wskazujący xmax_aborted zostanie ustawiony w wersji wiersza. Sam numer xmax pozostaje na stronie, ale nikt już na niego nie zwróci uwagi.

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | FOO
(1 row)

=> SELECT * FROM heap_page('t',0);

 ctid  | stan   |   xmin   |   xmax   | t_ctid 
-------+--------+----------+----------+--------
 (0,1) | normal | 3664 (c) | 3665 (a) | (0,1)
(1 wiersz)

Aktualizacja

Aktualizacja działa tak, jakby najpierw wykonano usunięcie bieżącej wersji wiersza, a potem dodano nową.

=> ROZPOCZNIJ;
=> AKTUALIZUJ t USTAW s = 'BAR';
=> WYBIERZ txid_current();

 txid_current 
--------------
         3666
(1 wiersz)

Zapytanie zwraca jeden wiersz (nową wersję):

=> SELECT * FROM t;

 id |  s  
----+-----
  1 | BAR
(1 wiersz)

Ale na stronie widzimy obie wersje:

=> SELECT * FROM heap_page('t',0);

 ctid  | stan   |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3664 (c) | 3666  | (0,2)
 (0,2) | normal | 3666     | 0 (a) | (0,2)
(2 wiersze)

Usunięta wersja jest oznaczona numerem bieżącej transakcji w polu xmax. Co więcej, ta wartość została zapisana na miejscu starej, ponieważ poprzednia transakcja została anulowana. A bit xmax_aborted został zresetowany, ponieważ status bieżącej transakcji jest jeszcze nieznany.

Pierwsza wersja wiersza teraz odnosi się do drugiej (pole t_ctid) jako nowszej.

Na stronie indeksowej pojawia się drugi wskaźnik i drugi wiersz, który odnosi się do drugiej wersji na stronie tabeli.

Tak samo jak w przypadku usuwania, wartość xmax w pierwszej wersji wiersza jest wskaźnikiem, że wiersz jest zablokowany.

Czas na zakończenie transakcji.

=> COMMIT;

Indeksy

Do tej pory rozmawialiśmy tylko o stronach tabeli. Co dzieje się wewnątrz indeksów?

Informacje na stronach indeksowych w dużej mierze zależą od konkretnego typu indeksu. Nawet w obrębie jednego typu indeksu występują różne typy stron. Na przykład, w drzewie B istnieje strona z metadanymi i 'zwykłe' strony.

Jednak zwykle strona zawiera tablicę wskaźników do wierszy oraz same wiersze (podobnie jak na stronie tabeli). Ponadto, na końcu strony rezerwuje się miejsce na specjalne dane.

Wiersze w indeksach również mogą mieć bardzo różną strukturę w zależności od typu indeksu. Na przykład, w przypadku drzewa B, wiersze odnoszące się do stron liściastych zawierają wartość klucza indeksowania oraz wskaźnik (ctid) na odpowiedni wiersz tabeli. W ogólnym przypadku indeks może mieć całkowicie inną strukturę.

Najważniejsza kwestia polega na tym, że w indeksach jakiegokolwiek typu nie ma wersji wierszy. Można uznać, że każdy wiersz jest przedstawiony dokładnie w jednej wersji. Innymi słowy, w nagłówku wiersza indeksu nie ma pól xmin i xmax. Można uznać, że wskaźniki z indeksu prowadzą do wszystkich wersji wierszy w tabeli — więc, aby dowiedzieć się, którą wersję zobaczy transakcja, należy zajrzeć do tabeli. (Jak zwykle, to nie jest cała prawda. W niektórych przypadkach mapa widoczności pozwala na optymalizację procesu, ale omówimy to później.)

Na stronie indeksowej znajdujemy wskaźniki do obu wersji, zarówno aktualnej, jak i starszej:

=> SELECT itemoffset, ctid FROM bt_page_items('t_s_idx',1);

 itemoffset | ctid  
------------+-------
          1 | (0,2)
          2 | (0,1)
(2 wiersze)

Wirtualne transakcje

W praktyce PostgreSQL wykorzystuje optymalizację, która pozwala na 'oszczędzanie' numerów transakcji.

Jeśli transakcja tylko odczytuje dane, nie ma wpływu na widoczność wersji wierszy. Dlatego na początku proces serwisowy wydaje transakcjom wirtualny numer (virtual xid). Numer składa się z identyfikatora procesu oraz liczby sekwencyjnej.

Wydanie tego numeru nie wymaga synchronizacji między wszystkimi procesami, dzięki czemu jest realizowane bardzo szybko. Poznamy inną przyczynę używania wirtualnych numerów, gdy będziemy omawiać zamrażanie.

Wirtualne numery nie są brane pod uwagę w zrzutach danych.

W różnych momentach w systemie mogą występować wirtualne transakcje z numerami, które już były używane, i to jest w porządku. Jednak taki numer nie może być zapisywany na stronach danych, ponieważ przy następnym dostępie do strony może stracić jakikolwiek sens.

=> BEGIN;
=> SELECT txid_current_if_assigned();

 txid_current_if_assigned 
--------------------------
                         
(1 row)

Jeśli transakcja zaczyna zmieniać dane, otrzymuje prawdziwy, unikalny numer transakcji.

=> UPDATE accounts SET amount = amount - 1.00;
=> SELECT txid_current_if_assigned();

 txid_current_if_assigned 
--------------------------
                     3667
(1 row)

=> COMMIT;

Transakcje zagnieżdżone

Punkty przywracania

W SQL zdefiniowane są punkty przywracania (savepoint), które umożliwiają anulowanie części operacji transakcji, nie przerywając jej całkowicie. Lecz to nie mieści się w przedstawionej powyżej schemacie, ponieważ status transakcji jest jeden dla wszystkich jej zmian, a fizycznie żadne dane nie są cofane.

Aby wdrożyć taką funkcjonalność, transakcja z punktem przywracania jest dzielona na kilka oddzielnych transakcji zagnieżdżonych (subtransaction), których status można zarządzać osobno.

Transakcje zagnieżdżone mają swój własny numer (większy niż numer głównej transakcji). Status transakcji zagnieżdżonych jest zapisywany w standardowy sposób w XACT, jednak ostateczny status zależy od statusu głównej transakcji: jeśli jest ona anulowana, anulowane również będą wszystkie transakcje zagnieżdżone.

Informacje o zagnieżdżeniu transakcji są przechowywane w plikach w katalogu PGDATA/pg_subtrans. Dostęp do plików odbywa się za pośrednictwem buforów w pamięci współdzielonej instancji, zorganizowanych w ten sam sposób, co bufory XACT.

Nie myl transakcji zagnieżdżonych i transakcji autonomicznych. Transakcje autonomiczne nie zależą od siebie, a zagnieżdżone – zależą. Transakcje autonomiczne w PostgreSQL nie istnieją i, być może, dobrze się stało: są one potrzebne bardzo rzadko, a ich obecność w innych DBMS prowokuje nadużycia, z których wszyscy potem cierpią.

Wyczyśćmy tabelę, rozpocznijmy transakcję i wstawmy wiersz:

=> TRUNCATE TABLE t;
=> BEGIN;
=> INSERT INTO t(s) VALUES ('FOO');
=> SELECT txid_current();

 txid_current 
--------------
         3669
(1 wiersz)

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
(1 wiersz)

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3669 | 0 (a) | (0,1)
(1 wiersz)

Teraz ustawmy punkt zapisu i wstawmy jeszcze jeden wiersz.

=> SAVEPOINT sp;
=> INSERT INTO t(s) VALUES ('XYZ');
=> SELECT txid_current();

 txid_current 
--------------
         3669
(1 wiersz)

Zauważ, że funkcja txid_current() zwraca numer głównej, a nie zagnieżdżonej, transakcji.

=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3670 |    0 |  3 | XYZ
(2 wiersze)

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  | xmin | xmax  | t_ctid 
-------+--------+------+-------+--------
 (0,1) | normal | 3669 | 0 (a) | (0,1)
 (0,2) | normal | 3670 | 0 (a) | (0,2)
(2 wiersze)

Cofnijmy się do punktu zapisu i wstawmy trzeci wiersz.

=> ROLLBACK TO sp;
=> INSERT INTO t(s) VALUES ('BAR');
=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3671 |    0 |  4 | BAR
(2 wiersze)

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669     | 0 (a) | (0,1)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671     | 0 (a) | (0,3)
(3 wiersze)

Na stronie wciąż widzimy wiersz dodany przez odwołaną zagnieżdżoną transakcję.

Zatwierdzamy zmiany.

=> COMMIT;
=> SELECT xmin, xmax, * FROM t;

 xmin | xmax | id |  s  
------+------+----+-----
 3669 |    0 |  2 | FOO
 3671 |    0 |  4 | BAR
(2 wiersze)

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669 (c) | 0 (a) | (0,1)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671 (c) | 0 (a) | (0,3)
(3 wiersze)

Teraz dobrze widać, że każda zagnieżdżona transakcja ma swój własny status.

Zauważmy, że zagnieżdżonych transakcji nie można używać w SQL jawnie, to znaczy nie można rozpocząć nowej transakcji, nie kończąc obecnej. Ten mechanizm jest używany niejawnie przy użyciu punktów zapisu, a także podczas przetwarzania wyjątków PL/pgSQL i w wielu innych, bardziej egzotycznych przypadkach.

=> BEGIN;

BEGIN

=> BEGIN;

OSTRZEŻENIE:  transakcja już trwa
BEGIN

=> COMMIT;

COMMIT

=> COMMIT;

OSTRZEŻENIE:  nie ma aktywnej transakcji
COMMIT

Błędy i atomowość operacji

Co się stanie, jeśli podczas wykonywania operacji wystąpi błąd? Na przykład tak:

=> BEGIN;
=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 wiersze)

=> UPDATE t SET s = repeat('X', 1/(id-4));

BŁĄD:  dzielenie przez zero

Wystąpił błąd. Transakcja jest teraz traktowana jako przerwana i żadna operacja w niej nie jest dozwolona:

=> SELECT * FROM t;

BŁĄD: bieżąca transakcja została przerwana, polecenia są ignorowane do końca bloku transakcji

I nawet jeśli spróbujemy zatwierdzić zmiany, PostgreSQL zgłosi anulowanie:

=> COMMIT;

ROLLBACK

Dlaczego nie można kontynuować wykonywania transakcji po awarii? Chodzi o to, że błąd mógł wystąpić w taki sposób, że uzyskalibyśmy dostęp do części zmian — naruszyłoby to atomowość nie tylko transakcji, ale także operatora. Jak w naszym przykładzie, gdzie operator przed błędem zdołał zaktualizować jeden wiersz:

=> SELECT * FROM heap_page('t',0);

 ctid  | stan  |   xmin   | xmax  | t_ctid 
-------+--------+----------+-------+--------
 (0,1) | normal | 3669 (c) | 3672  | (0,4)
 (0,2) | normal | 3670 (a) | 0 (a) | (0,2)
 (0,3) | normal | 3671 (c) | 0 (a) | (0,3)
 (0,4) | normal | 3672     | 0 (a) | (0,4)
(4 wiersze)

Należy powiedzieć, że w psql istnieje tryb, który jednak pozwala kontynuować działanie transakcji po błędzie, tak jakby działania błędnego operatora były wycofywane.

=> set ON_ERROR_ROLLBACK on
=> BEGIN;
=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 wiersze)

=> UPDATE t SET s = repeat('X', 1/(id-4));

BŁĄD:  dzielenie przez zero

=> SELECT * FROM t;

 id |  s  
----+-----
  2 | FOO
  4 | BAR
(2 wiersze)

=> COMMIT;

Nie trudno się domyślić, że w takim trybie psql faktycznie ustawia przed każdym poleceniem niejawny punkt przywracania, a w przypadku błędu inicjuje wycofanie do niego. Taki tryb nie jest używany domyślnie, ponieważ ustawianie punktów przywracania (nawet bez wycofywania do nich) wiąże się z istotnymi kosztami.

Kontynuacja.

Źródło: habr.com