Transaktionen, Isolation Levels und Race Conditions

Warum Transaktionen und Isolation Levels jeden Entwickler angehen

Race Conditions in Datenbanken sind eine der tückischsten Fehlerklassen in der Webentwicklung. Sie treten selten auf — aber wenn sie es tun, ist der Schaden real: Produkte, die doppelt verkauft werden. Gutscheine, die mehrfach eingelöst werden. Lagermengen, die ins Negative rutschen. Das sind keine theoretischen Szenarien, sondern reale Bugs in produktiven Magento-Shops.

Das Fundament für das Verständnis dieser Probleme ist die Transaktions-Isolation in MySQL. Dieser Artikel erklärt ACID von Grund auf, beleuchtet alle vier Isolation Levels mit ihren spezifischen Anomalien, erklärt wie MVCC Lesevorgänge ohne Locks ermöglicht und zeigt konkrete Lösungspatterns für die häufigsten Race Conditions im E-Commerce.

1. ACID – die vier Grundprinzipien von Datenbanktransaktionen

Das Akronym ACID beschreibt die vier Eigenschaften, die eine zuverlässige Datenbanktransaktion garantieren muss. InnoDB implementiert alle vier — das ist einer der Hauptgründe, warum InnoDB und nicht MyISAM in produktiven Anwendungen verwendet werden sollte.

• Atomicity (Atomarität): Eine Transaktion ist unteilbar. Entweder werden alle Operationen erfolgreich abgeschlossen und mit COMMIT dauerhaft gespeichert, oder keine einzige Änderung bleibt bestehen (ROLLBACK). Es gibt kein "halb committed".
• Consistency (Konsistenz): Die Datenbank befindet sich vor und nach jeder Transaktion in einem konsistenten, validen Zustand. Integritätsbedingungen (Fremdschlüssel, Constraints, Unique-Einschränkungen) werden durchgesetzt.
• Isolation (Isolation): Gleichzeitig ausgeführte Transaktionen sehen sich gegenseitig nicht — zumindest in der Theorie. In der Praxis ist das Maß der Isolation einstellbar über den Isolation Level, weil vollständige Isolation auf Kosten der Performance geht.
• Durability (Dauerhaftigkeit): Committete Transaktionen überstehen Server-Abstürze. InnoDB schreibt Änderungen in den Redo-Log (Write-Ahead-Log), bevor es sie in die Datendateien schreibt — der Recovery-Mechanismus rekonstruiert committete Transaktionen nach einem Crash.

-- Basic transaction syntax in MySQL
START TRANSACTION;  -- or BEGIN;

-- All DML statements here are atomic
UPDATE cataloginventory_stock_item
SET qty = qty - 1
WHERE product_id = 4711 AND stock_id = 1;

INSERT INTO sales_order (customer_id, grand_total, status, created_at)
VALUES (42, 149.95, 'pending', NOW());

-- Either commit all changes:
COMMIT;

-- Or rollback all changes:
-- ROLLBACK;

-- Check autocommit setting (default: ON in MySQL)
SHOW VARIABLES LIKE 'autocommit';

-- Disable autocommit for session
SET autocommit = 0;
    

2. Die vier Isolation Levels im Überblick

SQL definiert vier Isolation Levels, die ein Spektrum von "maximale Performance, wenige Garantien" bis "maximale Sicherheit, hohe Lock-Kosten" abdecken. Jeder Level erlaubt bestimmte Anomalien und verhindert andere.

-- Set isolation level for current session
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- MySQL default
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- Set global default (all new sessions)
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- Verify current isolation level
SELECT @@transaction_isolation;
-- Or:
SHOW VARIABLES LIKE 'transaction_isolation';

-- Anomaly overview per isolation level:
-- Level              | Dirty Read | Non-Repeatable | Phantom Read
-- READ UNCOMMITTED   | possible   | possible        | possible
-- READ COMMITTED     | prevented  | possible        | possible
-- REPEATABLE READ    | prevented  | prevented       | prevented* (InnoDB next-key locks)
-- SERIALIZABLE       | prevented  | prevented       | prevented
    

3. READ UNCOMMITTED: Die Dirty Read-Falle

READ UNCOMMITTED ist der schwächste Isolation Level. Eine Transaktion kann Daten lesen, die eine andere Transaktion geändert hat — aber noch nicht committed hat. Das nennt man einen Dirty Read. Wenn die schreibende Transaktion danach einen ROLLBACK durchführt, hat die lesende Transaktion Daten gesehen, die nie dauerhaft in der Datenbank gespeichert waren.

-- Dirty Read scenario (two concurrent sessions):

-- Session A: starts a transaction, reduces inventory
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE cataloginventory_stock_item
SET qty = 0
WHERE product_id = 100 AND stock_id = 1;
-- (NOT yet committed!)

-- Session B (READ UNCOMMITTED): reads the uncommitted change!
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT qty FROM cataloginventory_stock_item
WHERE product_id = 100;
-- Returns: qty = 0  ← this is the dirty read!

-- Session A then rolls back:
ROLLBACK;
-- Now the actual qty is back to its original value
-- Session B made a decision based on data that never existed

-- READ UNCOMMITTED is almost never appropriate for production use
-- Only useful for extremely rough approximations (e.g., COUNT(*) on huge tables)
-- where locking overhead is unacceptable
    

4. READ COMMITTED: Non-Repeatable Reads

READ COMMITTED verhindert Dirty Reads — eine Transaktion sieht nur committed Daten. Aber es erlaubt Non-Repeatable Reads: Wenn dieselbe Zeile innerhalb einer Transaktion zweimal gelesen wird und eine andere Transaktion in der Zwischenzeit diese Zeile geändert und committed hat, können die beiden Leseoperationen unterschiedliche Ergebnisse liefern.

-- Non-Repeatable Read scenario:

-- Session A (READ COMMITTED):
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;

-- First read: price = 149.95
SELECT price FROM catalog_product_entity_decimal
WHERE entity_id = 100 AND attribute_id = 75;
-- Returns: 149.95

-- Session B: commits a price change
UPDATE catalog_product_entity_decimal
SET value = 99.95
WHERE entity_id = 100 AND attribute_id = 75;
COMMIT;

-- Session A reads again: NOW gets 99.95 (different result in same transaction!)
SELECT price FROM catalog_product_entity_decimal
WHERE entity_id = 100 AND attribute_id = 75;
-- Returns: 99.95  ← Non-Repeatable Read!

COMMIT;

-- READ COMMITTED is the default isolation level for PostgreSQL
-- and is popular for high-concurrency OLTP systems where
-- REPEATABLE READ's snapshot can cause "stale read" issues
    

5. REPEATABLE READ: Der MySQL InnoDB Standard

REPEATABLE READ ist der Standard-Isolation Level von MySQL InnoDB. Er garantiert, dass alle Lesevorgänge innerhalb einer Transaktion denselben konsistenten Snapshot sehen — unabhängig davon, was andere Transaktionen in der Zwischenzeit ändern und committen. Diesen Snapshot nennt man in InnoDB den Consistent Read View.

-- REPEATABLE READ is InnoDB's default: verify it
SELECT @@transaction_isolation;
-- Returns: REPEATABLE-READ

-- Consistent snapshot example:
START TRANSACTION;

-- First read establishes the snapshot timestamp
SELECT qty FROM cataloginventory_stock_item WHERE product_id = 100;
-- Returns: 10

-- Another session commits: UPDATE ... SET qty = 5 WHERE product_id = 100;

-- Second read in our transaction STILL returns the original value!
SELECT qty FROM cataloginventory_stock_item WHERE product_id = 100;
-- Returns: 10  ← consistent snapshot read, NOT 5

COMMIT;

-- Important distinction in InnoDB REPEATABLE READ:
-- Regular SELECT: snapshot read (uses MVCC, no locks)
-- SELECT FOR UPDATE / SELECT LOCK IN SHARE MODE: current read (uses locks!)
-- UPDATE / DELETE: also current reads (see the latest committed data)

-- This is the source of many confusing bugs:
-- Your SELECT sees the old snapshot, but your UPDATE affects current rows!
START TRANSACTION;
SELECT qty FROM cataloginventory_stock_item WHERE product_id = 100;
-- Returns 10 (snapshot)

-- Another session committed: qty is now 1

UPDATE cataloginventory_stock_item
SET qty = qty - 1
WHERE product_id = 100;
-- This UPDATE affects the CURRENT row (qty=1), not the snapshot!
-- Result: qty becomes 0, not 9!
-- This can cause unexpected behavior if you rely on the SELECT value

COMMIT;
    

6. SERIALIZABLE: Striktester Modus

SERIALIZABLE ist der strengste Isolation Level. Er verhindert alle genannten Anomalien, indem er jeden SELECT-Vorgang implizit in ein SELECT ... IN SHARE MODE umwandelt. Das bedeutet: jeder Lesevorgang setzt einen Shared Lock auf die gelesenen Zeilen. Andere Transaktionen können diese Zeilen noch lesen, aber nicht schreiben — bis der Lock freigegeben wird.

-- SERIALIZABLE: every SELECT becomes SELECT ... IN SHARE MODE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

START TRANSACTION;

-- This simple SELECT now acquires a shared lock!
SELECT qty FROM cataloginventory_stock_item WHERE product_id = 100;

-- Another session trying to UPDATE this row will now WAIT
-- until our transaction commits or rolls back

COMMIT;  -- Lock released here

-- SERIALIZABLE is rarely appropriate for high-concurrency web applications
-- It can cause massive lock contention and deadlocks
-- Use cases: financial audit processes, strict sequential processing

-- Deadlock example with SERIALIZABLE:
-- Session A: SELECT product_id = 100 (S lock)
-- Session B: SELECT product_id = 200 (S lock)
-- Session A: tries to UPDATE product_id = 200 → waits for Session B's lock
-- Session B: tries to UPDATE product_id = 100 → waits for Session A's lock
-- Result: DEADLOCK → one transaction is killed by MySQL
    

7. MVCC: Wie InnoDB Lesevorgänge ohne Locks ermöglicht

Der geheime Mechanismus hinter der Performance von InnoDB ist Multi-Version Concurrency Control (MVCC). Anstatt Lesevorgänge zu blockieren, bis alle schreibenden Transaktionen abgeschlossen sind, hält InnoDB mehrere Versionen jeder Zeile gleichzeitig vor — im sogenannten Undo-Log. Lesende Transaktionen greifen auf die Version der Zeile zu, die ihrem Snapshot-Zeitpunkt entspricht.

-- MVCC in action: readers don't block writers, writers don't block readers

-- Session A: long-running read transaction
START TRANSACTION;
SELECT COUNT(*) FROM sales_order WHERE status = 'complete';
-- Establishes snapshot at T1

-- Session B: concurrent writes (don't block Session A's reads!)
INSERT INTO sales_order (status, grand_total, created_at)
VALUES ('complete', 299.95, NOW());
COMMIT;  -- Committed at T2

-- Session A still sees the data from T1, not T2:
SELECT COUNT(*) FROM sales_order WHERE status = 'complete';
-- Returns same count as first SELECT (snapshot at T1)
-- This is consistent and correct behavior!

COMMIT;

-- How InnoDB stores multi-version data:
-- Each row has hidden columns: DB_TRX_ID (transaction that last modified),
-- DB_ROLL_PTR (pointer to undo log for previous versions)
-- When InnoDB needs to show a row as of a past snapshot,
-- it follows the undo log chain to reconstruct the historical version

-- Monitor MVCC history length (high = long-running transactions accumulating versions)
SELECT
    trx_id,
    trx_started,
    trx_state,
    trx_rows_locked,
    trx_rows_modified,
    trx_concurrency_tickets
FROM information_schema.innodb_trx
ORDER BY trx_started ASC;
    

8. Reale Race Conditions in Magento

Magento 2 ist anfällig für bestimmte Race Conditions, die in hochfrequenten Shop-Umgebungen auftreten. Zwei klassische Szenarien:

Race Condition 1: Inventarreduzierung ohne Lock

-- PROBLEM: Two concurrent orders for the last item (qty=1)
-- Both sessions execute these steps "simultaneously":

-- Session A: reads qty = 1
SELECT qty FROM cataloginventory_stock_item
WHERE product_id = 4711 AND stock_id = 1;  -- Returns 1

-- Session B: reads qty = 1 (same value, both see qty=1)
SELECT qty FROM cataloginventory_stock_item
WHERE product_id = 4711 AND stock_id = 1;  -- Returns 1

-- Session A: places order, reduces qty
UPDATE cataloginventory_stock_item
SET qty = qty - 1, is_in_stock = (qty - 1 > 0)
WHERE product_id = 4711 AND stock_id = 1;
-- qty is now 0

-- Session B: also places order, reduces qty
UPDATE cataloginventory_stock_item
SET qty = qty - 1, is_in_stock = (qty - 1 > 0)
WHERE product_id = 4711 AND stock_id = 1;
-- qty is now -1 ← OVERSOLD! Both sessions got the item!

-- This race condition causes overselling in Magento
-- particularly visible during flash sales and high-concurrency events
    

Race Condition 2: Gutschein-Einlösung

-- PROBLEM: Single-use coupon redeemed by two concurrent orders

-- Both sessions simultaneously check if coupon is still valid:
SELECT uses_per_coupon, times_used
FROM salesrule_coupon
WHERE code = 'SAVE10' AND times_used < uses_per_coupon;
-- Both return a row (coupon appears valid)

-- Session A: increments times_used
UPDATE salesrule_coupon SET times_used = times_used + 1
WHERE code = 'SAVE10';

-- Session B: also increments times_used (too late to prevent!)
UPDATE salesrule_coupon SET times_used = times_used + 1
WHERE code = 'SAVE10';
-- times_used is now 2 for a single-use coupon!

-- Both orders receive the discount → revenue loss
    

9. Lösungspatterns: SELECT FOR UPDATE und Optimistic Locking

Für die Race Conditions oben gibt es zwei etablierte Lösungsstrategien, die je nach Anwendungsfall unterschiedlich geeignet sind.

Pessimistisches Locking mit SELECT FOR UPDATE

-- SOLUTION 1: Pessimistic locking with SELECT FOR UPDATE
-- Correct inventory decrement (prevents race condition):

START TRANSACTION;

-- Acquire an exclusive lock on the stock row BEFORE reading
SELECT qty, is_in_stock
FROM cataloginventory_stock_item
WHERE product_id = 4711 AND stock_id = 1
FOR UPDATE;
-- Now: Session B trying the same SELECT FOR UPDATE will WAIT

-- Check availability
-- If qty > 0, proceed with the order
UPDATE cataloginventory_stock_item
SET qty = qty - 1,
    is_in_stock = CASE WHEN qty - 1 > 0 THEN 1 ELSE 0 END
WHERE product_id = 4711 AND stock_id = 1 AND qty > 0;

-- If ROW_COUNT() = 0: qty was 0, abort the order
SELECT ROW_COUNT() AS rows_updated;

COMMIT;  -- Lock released here, Session B can proceed

-- For coupon single-use lock:
START TRANSACTION;
SELECT id, times_used, uses_per_coupon
FROM salesrule_coupon
WHERE code = 'SAVE10'
    AND times_used < uses_per_coupon
FOR UPDATE;

-- If row returned: coupon is valid, claim it
UPDATE salesrule_coupon
SET times_used = times_used + 1
WHERE code = 'SAVE10' AND times_used < uses_per_coupon;

COMMIT;
    

Optimistisches Locking mit Version-Spalte

-- SOLUTION 2: Optimistic locking (better for low-contention scenarios)
-- Uses a version column to detect conflicts without upfront locking

-- Schema addition:
ALTER TABLE cataloginventory_stock_item
    ADD COLUMN version INT NOT NULL DEFAULT 0;

-- Application flow:
-- Step 1: Read current state including version
SELECT qty, is_in_stock, version AS v
FROM cataloginventory_stock_item
WHERE product_id = 4711 AND stock_id = 1;
-- Returns: qty=1, version=42

-- Step 2: Attempt update with version check in WHERE clause
UPDATE cataloginventory_stock_item
SET qty     = qty - 1,
    version = version + 1,
    is_in_stock = CASE WHEN qty - 1 > 0 THEN 1 ELSE 0 END
WHERE product_id = 4711
    AND stock_id = 1
    AND version  = 42       -- ← this prevents the race condition
    AND qty      > 0;       -- ← also ensure stock is still available

-- Step 3: Check if update succeeded
SELECT ROW_COUNT() AS updated;
-- If 0: another transaction modified the row first
-- Application must retry the entire read-modify-write cycle
-- If 1: update succeeded, proceed with order

-- Optimistic locking is ideal when conflicts are rare
-- Pessimistic locking (FOR UPDATE) is better when conflicts are frequent
    

10. SAVEPOINT für partiellen Rollback

SAVEPOINTs ermöglichen einen partiellen Rollback innerhalb einer Transaktion. Anstatt bei einem Fehler die gesamte Transaktion zu verwerfen, kann zu einem definierten Zwischenpunkt zurückgerollt werden. Das ist besonders nützlich bei mehrstufigen Operationen, bei denen ein Teilschritt fehlschlagen kann.

-- SAVEPOINT for partial rollback in multi-step operations
START TRANSACTION;

-- Step 1: Create the order record
INSERT INTO sales_order (customer_id, grand_total, status, created_at)
VALUES (42, 299.95, 'pending', NOW());
SET @order_id = LAST_INSERT_ID();

SAVEPOINT order_created;  -- Mark checkpoint after successful order insert

-- Step 2: Process each order item
INSERT INTO sales_order_item (order_id, product_id, qty_ordered, price)
VALUES (@order_id, 4711, 1, 149.95);

SAVEPOINT items_added;

-- Step 3: Reserve inventory (can fail if another transaction grabbed the last item)
UPDATE cataloginventory_stock_item
SET qty = qty - 1
WHERE product_id = 4711 AND stock_id = 1 AND qty > 0;

IF ROW_COUNT() = 0 THEN
    -- Inventory reservation failed: roll back to before items were added
    -- but keep the transaction alive to handle the error gracefully
    ROLLBACK TO SAVEPOINT order_created;
    -- Now we can update the order status to 'failed' or take other action
    UPDATE sales_order SET status = 'canceled' WHERE entity_id = @order_id;
    COMMIT;
ELSE
    -- Everything succeeded
    COMMIT;
END IF;

-- Release a savepoint (optional, frees resources)
RELEASE SAVEPOINT order_created;
    

11. Zusammenfassung

Die Transaktions-Isolation in MySQL ist kein akademisches Thema — sie entscheidet darüber, ob ein Magento-Shop Produkte doppelt verkauft oder Gutscheine korrekt begrenzt. REPEATABLE READ (InnoDB-Standard) mit MVCC ermöglicht konsistente Lesevorgänge ohne Locks, aber Regular-SELECTs sehen nur den Snapshot — UPDATE und DELETE sehen immer die aktuellen Daten. Für kritische Ressourcen ist SELECT FOR UPDATE die zuverlässigste Absicherung. Optimistic Locking mit einer Version-Spalte ist performanter bei seltenen Konflikten.

12. FAQ: Transaktions-Isolation in MySQL