W PostgreSQL transakcja to ciąg poleceń SQL objętych poleceniami BEGIN i COMMIT.
BEGIN; UPDATE Konta SET saldo = saldo + 100.00 WHERE name = 'Ja'; ... COMMIT;
Jeśłi podczas wykonywania transakcji zechcemy z niej zrezygnować, zamiast COMMIT należy wykonać ROLLBACK, co spowoduje odwołanie wszystkich modyfikacji dokonanych w bieżącej transkacji.
Jeśłi nie wystąpiło polecenie BEGIN, to PostgreSQL zwykle używa trybu AUTOCOMMIT, traktując każde polecenie SQL tak, jak gdyby było wykonane w osobnej transakcji.
W Postgresie są tylko dwa poziomy izolacji transakcji.
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
jest domyślny i pozwala obserwować w trakcie transakcji zmiany zatwierdzone przez inne transakcje.
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
całkowicie chroni transakcję przed zmianami dokonywanymi przez inne transakcje.
Niezależnie od poziomu izolacji transakcji odczyt w PostgreSQL nie powoduje automatycznej blokady danych. Dane odczytane przez jedną transakcję mogą być nadpisane przez inną współbieżną transakcję.
Aby zagwarantować sobie niezmiennośc odczytanych danych należy używać SELECT FOR UPDATE wymuszającego nałożenie blokad poziomu wierszy. Można też użyć polecenia LOCK TABLE, jest to jednak zwykle gorsze rozwiązanie. SELECT FOR UPDATE blokuje przed współbieżną modyfikacją tylko wyszukane wiersze, natomiast LOCK TABLE blokuje całą tabelę. Niektóre polecenia SQL, zwłaszcza operujące na schemacie (na przykład ALTER TABLE), także używają niejawnie blokad poziomu tabeli.
PostgreSQL dostarcza rozmaitych trybów do jawnego blokowania całych tabel. Polecenia LOCK należy używać wewnątrz transakcji (BEGIN...COMMIT), ponieważ wszystkie blokady zdejmuje się na zakończenie transakcji. Polecenie LOCK użyte poza transakcją powoduje nałożenie i natychmiastowe zdjęcie blokady.
Listę bieżących aktywnych blokad można obejrzeć używając systemowej
perspektywy pg_locks.
Polecenie LOCK TABLE nakłada blokadę na tabelę, czekając przy tym, aż wszystkie niezgodne z nią blokady zostaną zwolnione. Nałożona blokada jest zwalniana podczas zakończenia transakcji (nie ma polecenia UNLOCK TABLE).
LOCK [ TABLE ] nazwa [, ...] LOCK [ TABLE ] nazwa [, ...] IN tryb MODE
gdzie nazwa określa tabelę, zaś tryb to jedna z wartości:
ACCESS SHARE ROW SHARE ROW EXCLUSIVE SHARE UPDATE EXCLUSIVE SHARE SHARE ROW EXCLUSIVE EXCLUSIVE ACCESS EXCLUSIVE
Definicja trybu określa, z jakimi innymi trybami jest on niezgodny (pewne tryby są nawet niezgodne ze sobą, np. ACCESS EXCLUSIVE, tzn. nie mogą być naraz użyte przez dwie różne transakcje).
Najmniej ograniczający, niezgodny tylko z trybem ACCESS EXCLUSIVE. Chroni przed zmodyfikowaniem tabeli przez polecenia ALTER TABLE, DROP TABLE and VACUUM FULL. Nakładany automatycznie przez polecenie SELECT.
Niezgodny z trybami EXCLUSIVE i ACCESS EXCLUSIVE. Nakładany przez polecenie SELECT FOR UPDATE.
Niezgodny z trybami SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE. Nakładany przez polecenia UPDATE, DELETE i INSERT na modyfikowane tabele.
Niezgodny z trybami SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE. Chroni tabelę przed zmianami schematu i użyciem VACUUM.
Niezgodny z trybami ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE. Chroni przed współbieżnymi zmianami danych. Używany przez CREATE INDEX.
Niezgodny z trybami ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE.
Niezgodny z trybami ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE. Pozwala tylko na odczyty z innych transkacji (czyli mogą one mieć jedynie tryb ACCESS SHARE
Niezgodny z wszystkimi trybami (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE i ACCESS EXCLUSIVE). Gwarantuje pełną wyłącznośc dostępu. Używany przez polecenia ALTER TABLE, DROP TABLE i VACUUM FULL. Tryb domyśłny dla polecenia LOCK TABLE.
Polecenie LOCK TABLE nie występuje w SQL-92, do okreśłania poziomów współbieżności używa się tam jedynie SET TRANSACTION. Przy automatycznym nakładaniu blokad PostgreSQL zawsze używa najmniej ograniczającego trybu, polecenia LOCK TABLE należy używać w przypadkach, gdy są wymagane mocniejsze ograniczenia.
Blokady poziomu wierszy nakłada się automatycznie na dany wiersz w momencie, gdy jest on modyfikowany, usuwany lub zaznaczany do modyfikacji. Blokada jest zwalniana podczas zamykania transakcji. Blokady poziomu wiersza nie mają wpływu na zapytania, blokują jdynie zapisy do danego wiersza. Jedyny sposób nałożenia takiej blokady (bez modyfikacji wiersza) to użyć SELECT FOR UPDATE.
Podczas używania jawnych blokad może dojść do zakleszczenia. PostgreSQL automatycznie wykrywa zakleszczenia i rozwiązuje je wycofując jedną z transakcji.
Zakleszczeniom należy zapobiegać na poziomie aplikacji, stosując np. następujące zasady:
Transakcje powinny zajmować blokady dotyczące obiektów w tej samej kolejności. Jeśli jakaś transakcja modyfikuje wiersz W1, a następnie wiersz W2, to inne transakcje powinny to robić w tej samej kolejności lub zaznaczać wcześniej odpowiednie wiersze.
Blokady na ten sam obiekt należy nakładać zaczynając od najbardziej ograniczającej.