PostgreSQL Serializable隔离级别下两个并发事务的异常问题

Hey there, let's break down this behavior and walk through the root causes and solutions. First, remember that the SQL standard's Serializable guarantee only promises the final result is equivalent to running the transactions one after another—it doesn't force real-time visibility of uncommitted changes between transactions. That's key to understanding what's happening here.

Common Causes #

1. Snapshot Isolation-Based Serializable Implementations #

Most modern databases (like PostgreSQL, SQL Server with SSI) implement Serializable using a variant of Snapshot Isolation (SI) instead of strict two-phase locking (2PL). In this model, each transaction reads from a snapshot of the database taken at the start of the transaction.

If your second transaction starts before the first transaction inserts or modifies data, its snapshot won't include those uncommitted changes. So when the second transaction queries the table, it sees the state of the table as it was when the transaction started—empty, in your case. The database will only enforce serializability at commit time: if the two transactions' operations conflict, one will be rolled back to ensure the final outcome matches some serial order.

2. The SQL Standard's "Final Equivalence" Rule #

The SQL standard doesn't require that a transaction sees uncommitted changes from another concurrent Serializable transaction. It only guarantees that after all transactions finish, the end state is the same as if they ran one after another in some order. So seeing an empty table mid-execution isn't a violation of the standard—it's just how the database enforces serializability without blocking every read.

3. Edge Cases in Lock-Based Implementations #

If you're using a database that uses strict two-phase locking (like MySQL InnoDB's default Serializable mode), regular SELECT statements take shared locks. Normally, this would block your second transaction's read until the first transaction commits (so it would see the data once unlocked). But if you're seeing an empty table here, it might be due to:

• Accidentally using a non-transactional storage engine (like MyISAM in MySQL, which ignores isolation levels).
• Using a hint that bypasses locking (like NOLOCK in SQL Server, though this isn't recommended for Serializable).

Solutions #

1. Align Transaction Start Timing #

If you need the second transaction to see the first's data, start the second transaction after the first one commits. This ensures its snapshot (for SI-based systems) includes the committed changes, or that locks are released for lock-based systems.

2. Force a "Current Read" Instead of Snapshot Read #

To bypass the snapshot and read the latest committed (or uncommitted, lock-held) data, use locking read statements:

• MySQL/InnoDB: Use SELECT * FROM your_table FOR SHARE or SELECT * FROM your_table FOR UPDATE. These take locks and wait for conflicting transactions to commit, so you'll see the latest data once the lock is released.
• PostgreSQL/SQL Server (SSI): Use SELECT * FROM your_table FOR UPDATE to force a current read. This will block until the first transaction commits, then return the updated data.

3. Add Retry Logic for Serialization Conflicts #

In SI-based Serializable systems, the database may roll back one of the transactions if it detects a serialization conflict (error codes like PostgreSQL's 40001 or SQL Server's 1205). Add retry logic in your application to re-run the rolled-back transaction—this time, it'll see the committed changes from the first transaction and execute correctly.

4. Verify Your Database's Serializable Behavior #

Double-check how your database implements Serializable:

• MySQL InnoDB: Ensure you're using InnoDB (not MyISAM) and that the isolation level is set correctly with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE. Regular SELECT statements will block on locks here.
• PostgreSQL: Remember that Serializable uses snapshot reads by default; use locking reads if you need real-time visibility, or handle retries for commit-time conflicts.
• SQL Server: If using SSI (enabled via ALTER DATABASE ... SET ALLOW_SNAPSHOT_ISOLATION ON and ALTER DATABASE ... SET READ_COMMITTED_SNAPSHOT ON), expect snapshot reads and commit-time conflicts. For strict lock-based Serializable, use SET TRANSACTION ISOLATION LEVEL SERIALIZABLE without snapshot isolation enabled.

5. Minimize Transaction Duration #

Shrink the scope of your transactions to reduce the time they hold locks or maintain snapshots. This makes it more likely that the first transaction commits before the second starts or reads the table, reducing conflicts and the chance of seeing empty tables.

内容的提问来源于stack exchange，提问作者Mahdi Ne