Transaktionen und Isolation Level in PostgreSQL
1. Übersicht
Wenn mehrere Anfragen gleichzeitig eine Tabelle lesen und schreiben, können die Ergebnisse falsch sein, obwohl jede einzelne SQL-Anweisung korrekt ist. Das Isolation Level bestimmt, wie viele Änderungen von anderen Transaktionen eine Transaktion “sieht”.
In diesem Artikel werden Sie sehen, wie PostgreSQL die Isolation Level implementiert. Sie werden die Fehler Lost Update und Write Skew selbst reproduzieren. Am Ende werden Sie wissen, wie Sie das passende Level wählen, es in Spring Boot konfigurieren und die Retry-Logik richtig implementieren.
2. Transaktionen in PostgreSQL
Eine Transaktion fasst mehrere Befehle in eine Einheit zusammen. Entweder sind alle erfolgreich, oder keine hat Auswirkungen:
CREATE TABLE account (
id BIGINT PRIMARY KEY,
owner TEXT NOT NULL,
balance NUMERIC(12, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO account VALUES (1, 'An', 1000), (2, 'Binh', 500);
BEGIN;
UPDATE account SET balance = balance - 300 WHERE id = 1;
UPDATE account SET balance = balance + 300 WHERE id = 2;
COMMIT;Wenn die zweite UPDATE-Anweisung fehlschlägt, führen Sie ROLLBACK aus und der Kontostand von An bleibt unverändert. Wenn es kein BEGIN gibt, läuft PostgreSQL im Autocommit-Modus: jede Anweisung ist eine separate Transaktion.
PostgreSQL verwendet MVCC (Multi-Version Concurrency Control). Jedes UPDATE erstellt eine neue Version der Zeile, anstatt sie zu überschreiben. Jede Transaktion liest Daten durch einen Snapshot. Daher blockieren Lesevorgänge keine Schreibvorgänge und umgekehrt. Das Isolation Level bestimmt, wann der Snapshot aufgenommen wird: am Anfang jeder Anweisung oder einmal für die gesamte Transaktion.
3. Anomalien und vier Isolation Level
Der SQL-Standard definiert folgende Anomalien:
- Dirty Read: Lesen von Daten, die von einer anderen Transaktion noch nicht committet wurden.
- Non-repeatable Read: Das zweimalige Lesen derselben Zeile ergibt unterschiedliche Werte.
- Phantom Read: Das erneute Ausführen derselben bedingten Abfrage zeigt zusätzliche oder fehlende Zeilen.
- Lost Update: Zwei Transaktionen lesen dieselbe Zeile, und eines der Schreibergebnisse wird überschrieben.
- Write Skew: Zwei Transaktionen lesen gemeinsame Daten und schreiben dann jeweils in verschiedene Zeilen, was eine Geschäftsregel verletzt.
Die folgende Tabelle zeigt, wie PostgreSQL jede Anomalie handhabt:
Level Dirty Read Non-repeatable Phantom Lost Update Write Skew
READ UNCOMMITTED Nein Ja Ja Ja Ja
READ COMMITTED Nein Ja Ja Ja Ja
REPEATABLE READ Nein Nein Nein Nein (*) Ja
SERIALIZABLE Nein Nein Nein Nein (*) Nein (*)
(*) Transaktion wird mit SQLSTATE 40001 abgebrochen; die Anwendung muss erneut versuchen.Es gibt zwei Punkte, die vom SQL-Standard abweichen und die Sie sich merken sollten:
READ UNCOMMITTEDin PostgreSQL verhält sich genau wieREAD COMMITTED. Sie werden niemals Dirty Read erleben.REPEATABLE READblockiert auch Phantom Reads, da es tatsächlich Snapshot Isolation ist.
Das Standardlevel ist READ COMMITTED. Sie können dies mit dem folgenden Befehl überprüfen:
SHOW default_transaction_isolation; default_transaction_isolation
-------------------------------
read committed4. READ COMMITTED
Auf dieser Ebene nimmt jede Anweisung einen neuen Snapshot auf. Öffnen Sie zwei psql-Sitzungen und führen Sie die Befehle in dieser Reihenfolge aus:
-- Sitzung A
BEGIN;
SELECT balance FROM account WHERE id = 1; -- 700.00
-- Sitzung B
UPDATE account SET balance = 900 WHERE id = 1; -- autocommit
-- Sitzung A
SELECT balance FROM account WHERE id = 1; -- 900.00
COMMIT;Innerhalb derselben Transaktion sieht Sitzung A zwei verschiedene Werte. Dies ist ein Non-repeatable Read.
4.1. Lost Update
Der häufigste Fehler in Anwendungen ist das Muster “in Java lesen, berechnen, dann zurückschreiben”:
BigDecimal balance = jdbc.queryForObject(
"SELECT balance FROM account WHERE id = ?", BigDecimal.class, 1L);
jdbc.update("UPDATE account SET balance = ? WHERE id = ?",
balance.subtract(new BigDecimal("100")), 1L);Angenommen, zwei Threads führen diesen Code aus und lesen beide 1000. Beide werden 900 schreiben. Das Ergebnis ist, dass eine Subtraktion verloren geht.
Es gibt zwei Möglichkeiten, dies zu beheben und dabei bei READ COMMITTED zu bleiben.
Lösung 1: Lassen Sie die Datenbank selbst rechnen und schreiben Sie es als ein atomares Statement.
UPDATE account SET balance = balance - 100 WHERE id = 1;Wenn PostgreSQL auf eine Zeile trifft, die von einer anderen Transaktion geändert wird, wartet es, bis diese Transaktion committet. Dann liest es die neue Version der Zeile und wendet die Subtraktion an.
Lösung 2: Sperren Sie die Zeile beim Lesen mit SELECT ... FOR UPDATE.
BEGIN;
SELECT balance FROM account WHERE id = 1 FOR UPDATE;
-- Anwendung prüft Geschäftsregeln, berechnet...
UPDATE account SET balance = 900 WHERE id = 1;
COMMIT;Die zweite Transaktion wird beim SELECT ... FOR UPDATE blockiert, bis die erste committet. So liest sie immer die neue Version.
5. REPEATABLE READ
Auf dieser Ebene wird der Snapshot einmal bei der ersten Anweisung der Transaktion aufgenommen. Alle nachfolgenden Lesevorgänge sehen denselben Zustand der Daten.
Wenn Sie versuchen, eine Zeile zu ändern, die eine andere Transaktion nach dem Snapshot geändert hat, wird PostgreSQL dies ablehnen:
-- Sitzung A
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT balance FROM account WHERE id = 1; -- 1000.00
-- Sitzung B
UPDATE account SET balance = balance - 100 WHERE id = 1; -- commit
-- Sitzung A
UPDATE account SET balance = balance - 200 WHERE id = 1;ERROR: could not serialize access due to concurrent updateLost Update tritt nicht mehr lautlos auf. Stattdessen muss Sitzung A ROLLBACK durchführen und von vorne beginnen. Der Fehlercode 40001 (serialization_failure) signalisiert der Anwendung, dass sie erneut versuchen sollte.
6. SERIALIZABLE und Write Skew
REPEATABLE READ lässt Write Skew immer noch zu. Betrachten Sie dieses klassische Beispiel: Ein Krankenhaus verlangt, dass immer mindestens ein Arzt Bereitschaftsdienst hat.
CREATE TABLE doctor (
id BIGINT PRIMARY KEY,
name TEXT NOT NULL,
on_call BOOLEAN NOT NULL
);
INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true);Beide Ärzte möchten den Dienst beenden, jeder in einer separaten Transaktion:
-- A und B führen gleichzeitig aus:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctor WHERE on_call; -- beide sehen 2
-- A
UPDATE doctor SET on_call = false WHERE id = 1;
-- B
UPDATE doctor SET on_call = false WHERE id = 2;
COMMIT; -- A: OK
COMMIT; -- B: OK -> Niemand mehr im Dienst!Beide Transaktionen schreiben in verschiedene Zeilen, daher wird kein Schreibkonflikt erkannt. Mit SERIALIZABLE nutzt PostgreSQL den Mechanismus Serializable Snapshot Isolation (SSI). SSI verfolgt Lese-Schreib-Abhängigkeiten zwischen Transaktionen und bricht eine davon ab:
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.Der Fehler kann bei UPDATE oder beim COMMIT auftreten. Der Fehlercode ist weiterhin 40001.
Hinweis:
SERIALIZABLEsperrt keine zusätzlichen Zeilen. Es erkennt nur Konflikte und bricht die Transaktion ab. Mit SSI kann PostgreSQL auch Transaktionen abbrechen, die sich nicht wirklich widersprechen. Daher kann die Retry-Rate bei hoher gleichzeitiger Last erheblich ansteigen.
7. Isolation Level in Spring Boot verwenden
Fügen Sie folgende Abhängigkeiten hinzu:
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>Der einfachste Weg ist, das Isolation Level auf @Transactional zu deklarieren:
@Service
public class ReportService {
private final JdbcTemplate jdbc;
public ReportService(JdbcTemplate jdbc) {
this.jdbc = jdbc;
}
@Transactional(isolation = Isolation.REPEATABLE_READ, readOnly = true)
public BigDecimal totalBalance() {
BigDecimal total = jdbc.queryForObject(
"SELECT sum(balance) FROM account", BigDecimal.class);
Long count = jdbc.queryForObject(
"SELECT count(*) FROM account", Long.class);
// Beide Abfragen verwenden denselben Snapshot -> konsistente Daten
return total;
}
}Spring ruft Connection.setTransactionIsolation(...) auf, bevor die Transaktion beginnt. Der PostgreSQL-Treiber konvertiert dies zu SET TRANSACTION ISOLATION LEVEL ....
7.1. Retry bei Serialization Failure
Das Retry muss außerhalb der Transaktion erfolgen. Ein Retry innerhalb ist sinnlos, da die Transaktion bereits beschädigt ist. TransactionTemplate hilft, die Falle des Self-Invocation von @Transactional zu vermeiden:
@Service
public class OnCallService {
private static final int MAX_ATTEMPTS = 3;
private final JdbcTemplate jdbc;
private final TransactionTemplate tx;
public OnCallService(JdbcTemplate jdbc, PlatformTransactionManager tm) {
this.jdbc = jdbc;
this.tx = new TransactionTemplate(tm);
this.tx.setIsolationLevel(TransactionDefinition.ISOLATION_SERIALIZABLE);
}
public void goOffCall(long doctorId) {
for (int attempt = 1; ; attempt++) {
try {
tx.executeWithoutResult(status -> doGoOffCall(doctorId));
return;
} catch (ConcurrencyFailureException e) {
if (attempt >= MAX_ATTEMPTS) {
throw e;
}
}
}
}
private void doGoOffCall(long doctorId) {
Integer onCall = jdbc.queryForObject(
"SELECT count(*) FROM doctor WHERE on_call", Integer.class);
if (onCall == null || onCall < 2) {
throw new IllegalStateException("Es muss mindestens ein Arzt im Dienst sein");
}
jdbc.update("UPDATE doctor SET on_call = false WHERE id = ?", doctorId);
}
}Der Code funktioniert wie folgt:
setIsolationLevel(ISOLATION_SERIALIZABLE)wird auf jeden Aufruf vonexecuteWithoutResultangewendet.- Spring übersetzt SQLSTATE
40001(und Deadlock40P01) in Unterklassen vonConcurrencyFailureException. Daher müssen Sie nicht den Fehlercode des Treibers lesen. - Beim nächsten Versuch wird eine neue Transaktion mit einem neuen Snapshot erstellt. Sie sieht, dass der andere Arzt den Dienst beendet hat, und wirft
IllegalStateException. Die Geschäftsregel wird korrekt eingehalten.
Hinweis: Spring Boot 3 konfiguriert
JdbcTransactionManagerautomatisch. Diese Klasse übersetzt auch Fehler während des Commit inDataAccessException. Wenn SieDataSourceTransactionManagerselbst deklarieren, werden Commit-Fehler zuTransactionSystemExceptionund das Retry-Loop oben fängt sie nicht ab.
8. Write Skew mit Testcontainers testen
Um die korrekte Reihenfolge nachzuahmen, verwenden wir zwei Connection-Objekte im selben Thread. So ist die Ausführungsreihenfolge nicht vom Thread-Scheduling abhängig. Fügen Sie diese Test-Abhängigkeit hinzu:
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>postgresql</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.testcontainers</groupId>
<artifactId>junit-jupiter</artifactId>
<scope>test</scope>
</dependency>Die Test-Klasse:
@Testcontainers
class WriteSkewTest {
@Container
static PostgreSQLContainer<?> postgres = new PostgreSQLContainer<>("postgres:16-alpine");
@BeforeEach
void setUp() throws SQLException {
try (Connection c = connect(); Statement st = c.createStatement()) {
st.execute("DROP TABLE IF EXISTS doctor");
st.execute("CREATE TABLE doctor (id BIGINT PRIMARY KEY, name TEXT, on_call BOOLEAN)");
st.execute("INSERT INTO doctor VALUES (1, 'Lan', true), (2, 'Minh', true)");
}
}
@Test
void repeatableReadAllowsWriteSkew() throws SQLException {
runConcurrently(Connection.TRANSACTION_REPEATABLE_READ);
assertThat(countOnCall()).isZero();
}
@Test
void serializableRejectsWriteSkew() throws SQLException {
assertThatThrownBy(() -> runConcurrently(Connection.TRANSACTION_SERIALIZABLE))
.isInstanceOf(SQLException.class)
.extracting(e -> ((SQLException) e).getSQLState())
.isEqualTo("40001");
assertThat(countOnCall()).isEqualTo(1);
}
private void runConcurrently(int level) throws SQLException {
try (Connection a = begin(level); Connection b = begin(level)) {
assertThat(countOnCall(a)).isEqualTo(2);
assertThat(countOnCall(b)).isEqualTo(2);
offCall(a, 1);
a.commit();
offCall(b, 2);
b.commit();
}
}
private Connection begin(int level) throws SQLException {
Connection c = connect();
c.setAutoCommit(false);
c.setTransactionIsolation(level);
return c;
}
private Connection connect() throws SQLException {
return DriverManager.getConnection(
postgres.getJdbcUrl(), postgres.getUsername(), postgres.getPassword());
}
private void offCall(Connection c, long id) throws SQLException {
try (PreparedStatement ps = c.prepareStatement(
"UPDATE doctor SET on_call = false WHERE id = ?")) {
ps.setLong(1, id);
ps.executeUpdate();
}
}
private int countOnCall(Connection c) throws SQLException {
try (Statement st = c.createStatement();
ResultSet rs = st.executeQuery("SELECT count(*) FROM doctor WHERE on_call")) {
rs.next();
return rs.getInt(1);
}
}
private int countOnCall() throws SQLException {
try (Connection c = connect()) {
return countOnCall(c);
}
}
}Erwartete Ergebnisse der beiden Tests:
repeatableReadAllowsWriteSkew -> PASSED (beide committen, 0 Ärzte im Dienst)
serializableRejectsWriteSkew -> PASSED (B wird mit 40001 abgebrochen, 1 Arzt im Dienst)Der zweite Test versichert nicht, ob der Fehler bei UPDATE oder COMMIT von B auftritt. Er prüft nur auf Fehlercode 40001, da der Zeitpunkt der SSI-Konflikterkennung zwischen PostgreSQL-Versionen variieren kann.
9. Häufige Fehler und Fälle, in denen man Isolation Level nicht erhöhen sollte
- Erhöhen Sie das Level, aber schreiben Sie kein Retry. Bei
REPEATABLE READundSERIALIZABLEist Fehler40001normales Verhalten, kein Bug. Ohne Retry erhalten Benutzer zufällig 500er-Fehler. - Retry innerhalb von
@Transactional. Die Transaktion muss gerollt werden und komplett neu starten. Legen Sie das Retry außerhalb auf einer höheren Ebene an. - Externe APIs innerhalb von Transaktionen mit Retry aufrufen. E-Mails oder Zahlungen können mehrfach gesendet werden. Führen Sie Nebeneffekte erst nach erfolgreichem Commit aus.
SERIALIZABLEsystemweit setzen “um sicherzustellen”. Das verursacht SSI-Tracking-Overhead und viele Retries. Nutzen Sie es nur für Operationen mit Abhängigkeiten über mehrere Zeilen.- Verschiedene Level in derselben Operation mischen. SSI schützt nur, wenn alle beteiligten Transaktionen
SERIALIZABLEnutzen. EineREAD COMMITTED-Transaktion, die in dieselbe Tabelle schreibt, kann die Regel immer noch brechen. REPEATABLE READ-Transaktionen laufen zu lange. Der alte Snapshot verhindert, dassVACUUMtote Zeilenversionen bereinigt, die Tabelle wird aufgebläht. Halten Sie Transaktionen kurz.
Hinweis: Für Einschränkungen, die durch das Schema ausgedrückt werden können, wie
CHECK,UNIQUEoder Exclusion Constraints, nutzen Sie lieber Constraints. Sie sind günstiger und zuverlässiger als das Erhöhen des Isolation Level.
10. Fazit
PostgreSQL läuft standardmäßig mit READ COMMITTED. Dieses Level reicht für die meisten Anwendungen aus, solange Sie atomare Updates schreiben oder SELECT ... FOR UPDATE nutzen, um Lost Update zu vermeiden.
REPEATABLE READ gibt einen konsistenten Snapshot über die gesamte Transaktion, ideal für Berichte. SERIALIZABLE blockiert auch Write Skew. Beide Level erfordern, dass die Anwendung bei SQLSTATE 40001 erneut versucht.
In Spring Boot deklarieren Sie das Level über @Transactional(isolation = ...) oder TransactionTemplate, fangen ConcurrencyFailureException außerhalb der Transaktion ab, und validieren das Verhalten mit Testcontainers und deterministischer Befehlsreihenfolge.
Gefällt dir diese Art von Erklärung? Cris The Dev veröffentlicht jeden Tag ein kurzes Video auf YouTube und TikTok.
Mehr zu diesem Thema
PostgreSQL EXPLAIN ANALYZE für Anfänger verstehen
Anleitung zum Lesen der EXPLAIN ANALYZE-Ausgabe in PostgreSQL: Verstehen Sie Seq Scan, Index Scan, Cost, Actual Time und wie Sie langsame Abfragen erkennen.
Index in PostgreSQL: B-tree, GIN und wann man sie einsetzt
Unterscheide B-tree und GIN Index in PostgreSQL, erstelle sie, lese EXPLAIN-Ausgaben und wähle den richtigen Index-Typ für jede Situation.
KI-basiertes Debugging für PostgreSQL auf Databricks: Ein Detail Leitfaden
Erfahren Sie, wie Databricks KI zur Diagnose von Datenbankfehlern nutzt und wenden Sie dies auf PostgreSQL mit Spring-Boot-, HikariCP- und konkreten langsamen Abfragen an.