ACID and Transaction Isolation: PostgreSQL, MySQL, and SQL Server Compared

ACID describes four aspects of transactional correctness. Isolation is often the hardest to reason about because identical isolation-level names can conceal different implementations.

The supplied laboratory records illustrate several useful differences:

• •REPEATABLE READ did not produce identical behavior across the three engines. Ordinary snapshot reads in PostgreSQL and MySQL kept a stable view in the supplied phantom scenario; SQL Server allowed an additional row to appear.
• •Snapshot-based reads did not automatically preserve a cross-row business invariant. The write-skew example violated the invariant under PostgreSQL and MySQL REPEATABLE READ and SQL Server SNAPSHOT.
• •Continuing to COMMIT after the tested CHECK violation produced different final balances. This is a statement-error-handling example, not proof that an engine violates atomicity.
• •SQL Server RCSI changed the ordinary reader/writer interaction in the supplied test. It provides statement-level snapshots, not a transaction-wide stable snapshot or freedom from all locks.

Evidence scope: Results below come from author-supplied dxlab output records. Databases were not rerun during this editorial review. The JSON files contain reconstructed commands and summary output lines, not complete client transcripts or native dxlab exports. Each scenario has one reported execution. Download the evidence package.

A payment, reservation, or stock operation may require several SQL statements. Grouping those statements correctly is only part of the problem: concurrent operations and failures also matter.

Atomicity: Does the transaction commit its changes as a unit, or roll them back? Applications must define the correct business boundary and handle errors before deciding to commit.

Consistency: Which rules define a valid state? A constraint such as CHECK (balance >= 0) protects an individual balance. It does not automatically encode a rule about the sum of several accounts. Database constraints and application logic have different responsibilities.

Isolation: Which changes may concurrent transactions observe, and how can their reads and writes interact? The selected level defines those boundaries. It does not mean that transactions never affect or delay each other.

Durability: Under the chosen durability settings and storage assumptions, what survives after a successful commit? Local persistence, high availability, and recoverability from a backup are separate properties.

This article focuses on isolation, but isolation is not the only configurable property. PostgreSQL asynchronous commit, for example, changes the relationship between acknowledging commit and flushing WAL. That trade-off was not tested here. PostgreSQL asynchronous commit

• •Dirty read: Reading another transaction's uncommitted change, which might later disappear through rollback.
• •Nonrepeatable read: Reading a row again and observing a different committed value.
• •Phantom read: Repeating a predicate query and observing a changed set of matching rows.
• •Write skew: Transactions read a shared condition, update different rows, and jointly violate a business invariant.

A serialization anomaly is the broader case in which committed results cannot be explained by any serial ordering of those transactions. Write skew is one example.

The standard isolation-level framework defines minimum protections. Implementations may provide stronger protections than a level's name alone requires. PostgreSQL REPEATABLE READ, for example, protects against phantoms while still permitting serialization anomalies. (S1)

This is why a database migration should not mechanically translate an isolation-level name and assume equivalent application behavior.

In a lock-based read path, readers and writers can conflict over shared and exclusive data locks. SQL Server READ COMMITTED with RCSI disabled is the relevant example in this laboratory.

With multiversion reads, an ordinary reader can use an earlier committed version rather than wait for an uncommitted row change. PostgreSQL, InnoDB consistent reads, and SQL Server row-versioned modes use versioning in different ways. Explicit locking reads, DDL, and schema locks can still cause waits. “MVCC means no waiting” is an unsafe generalization.

The reported initial isolation defaults were PostgreSQL READ COMMITTED, MySQL REPEATABLE READ, and SQL Server isolation code 2 / READ COMMITTED. The SQL Server setup explicitly disabled RCSI before the comparison. These are the tested environments, not a claim about every managed database product.

PostgreSQL Serializable Snapshot Isolation uses nonblocking predicate tracking to detect unsafe dependency patterns. That does not make the whole transaction lock-free: ordinary row and table locks can still wait or deadlock. InnoDB serializable reads can become implicit shared locking reads within the documented transaction context; SQL Server serializable reads protect relevant key ranges. (S1–S3)

The scenarios reset an accounts table to two rows: Ali with balance 100 and Veli with balance 100. The conceptual schema is:

Engine-specific setup commands are included in the evidence archive. Tests use psql, mysql, and sqlcmd with two concurrent sessions where required.

Reported versions are PostgreSQL 16.15 and 17.11, MySQL 8.4.11, and SQL Server major version 16. The SQL Server full build/CU was not recorded. Complete effective-session settings and container image digests are also unavailable. No new version claim is inferred from the documentation used for editorial review.

Schedules use sleep commands rather than synchronization barriers. Each experiment has one reported run. Recorded durations use second-resolution clock differences, not precision latency measurements. The PostgreSQL 16 and 17 records report the same behavioral outcomes.

The archive's acid-env-*.yaml files are proposed environment-capture scenarios; no results from them are supplied. A default measured on a separate connection would not, on its own, establish the effective state of the experiment connection.

E1a starts a transaction, subtracts 50 from Ali, attempts to subtract 500 from Veli, and then sends COMMIT. The second update violates the balance CHECK constraint.

These deliberately incorrect application flows illustrate the issue. Prepare the isolated test schema and initial rows first; do not continue to commit a failed business operation in application code.

The supplied E1a records show final balances of 100,100 in PostgreSQL and 50,100 in MySQL and SQL Server. The PostgreSQL client flow does not enable ON_ERROR_STOP; MySQL uses --force to continue after the error. A driver that stops sending statements after an exception can produce a different application flow.

E1b instead rolls back: all engines report 100,100. SQL Server uses TRY/CATCH, while the other scenarios send an explicit rollback in the command stream; these are not identical client implementations.

Committing successful statements after another statement fails does not violate transaction atomicity. It means that the application committed a different set of successful work than its business intent required. Savepoints are another deliberate design option, not a reason to ignore a failed business operation. (S6)

Do not generalize this CHECK example to every error. MySQL deadlocks can roll back the whole transaction. SQL Server error scope depends on the error and XACT_ABORT; OFF does not ensure statement-only rollback for all failures. (S4, S5)

For a failed business unit, roll back the open transaction, return a clean connection to its pool, and decide whether the entire unit can be retried safely. External effects such as payments or notifications need separate handling.

Session B changes Ali's balance to 0 without committing, waits, and rolls back. Session A reads under READ UNCOMMITTED while B is still open.

The records show:

• •PostgreSQL: 100, not the uncommitted value.
• •MySQL: 0, a dirty read.
• •SQL Server: 0, a dirty read.

PostgreSQL maps READ UNCOMMITTED to READ COMMITTED. The other two engines can expose uncommitted data in this mode. (S1–S3)

Microsoft documents the read effect of READ UNCOMMITTED as equivalent to applying NOLOCK to all SELECT tables. That does not eliminate schema locking. NOLOCK itself was not separately tested here and should not be presented as a general reporting fix. (S3)

Session A reads balance 100, waits three seconds, and reads again. Between the reads, session B updates the value to 150.

Under READ COMMITTED, all engines report 100 → 150. Under REPEATABLE READ, all report 100 → 100, but the mechanisms differ.

In the snapshot-read cases, A retains its read view. In SQL Server REPEATABLE READ, the read locks prevent B's update from completing until A releases them. Its summary records report B's update duration as 2 seconds, versus 0 seconds in the READ COMMITTED scenario. Treat these as coarse scenario timings, not engine latency benchmarks.

The same visible read result can therefore carry different concurrency costs.

Session A counts two rows, waits, and counts again. Session B inserts a third row between the reads.

Under REPEATABLE READ:

• •PostgreSQL reports 2 → 2.
• •MySQL reports 2 → 2, with a recorded B insert duration of 0 seconds.
• •SQL Server reports 2 → 3, with a recorded B insert duration of 0 seconds.

The MySQL observation concerns ordinary consistent SELECTs. It should not be generalized to locking/current reads, which have different behavior. (S2)

Under SERIALIZABLE, all records show 2 → 2. MySQL and SQL Server report a B insert duration of about 2 seconds. PostgreSQL does not record B's insert duration in this scenario; no zero-second timing claim can be made for it.

InnoDB's implicit shared-locking read behavior depends on the documented autocommit/transaction context. PostgreSQL predicate tracking is nonblocking; SQL Server protects the relevant ranges. These mechanisms explain why identical row counts need not imply identical schedules. (S1–S3)

The application invariant is total balance ≥ 100. Starting from 200, both transactions read the total, each decides that subtracting 60 is safe, and each updates a different account. If both commit based on that initial view, the final total is 80.

This is a schedule illustration, not a cross-engine runnable SQL script. Complete scenario commands are in the archive.

At REPEATABLE READ, PostgreSQL and MySQL report both decisions taking effect: total 80, invariant violated. SQL Server reports a deadlock and total 140 in this schedule.

At SERIALIZABLE, PostgreSQL reports a serialization error and total 140. MySQL and SQL Server report a deadlock and total 140.

SQL Server SNAPSHOT reports total 80 without a deadlock. PostgreSQL and MySQL do not have a separate isolation-level name SNAPSHOT; that does not mean they lack snapshot-based reads.

The records report that both sessions saw 200. Their exact start times were not recorded, and no synchronization barrier was used. A single observed deadlock does not mean that every execution must deadlock. Serializable protects the outcome; the particular wait or abort pattern depends on execution.

In write skew, the issue is not overwriting the same row: separate writes can jointly invalidate a shared condition. Readers holding locks can nevertheless cause the two transactions to conflict.

E8 holds an uncommitted update open for eight seconds. Another session reads the row at READ COMMITTED with a three-second lock timeout.

• •E8a — RCSI OFF: The summary reports blocking and a three-second duration; the scenario checks for lock timeout error 1222.
• •E8b — RCSI ON: The reader obtains the earlier committed value 100, with a recorded duration of 0 seconds.

The provided record includes the detection result, not the complete error message transcript.

RCSI ordinarily avoids shared row/page data locks for these versioned reads. Schema locks and other blocking interactions remain possible. Most importantly, RCSI creates a view for each statement: a later SELECT in the same transaction may use a newer committed view. Transaction-level SNAPSHOT is a different choice. (S3)

Evaluate RCSI against application semantics, version retention, long-running readers, and available resources. The version-store location can depend on Accelerated Database Recovery configuration; do not assume it is always tempdb. This package contains no resource-cost measurement. Microsoft locking and row versioning guide

The lab uses WITH ROLLBACK IMMEDIATE when changing its database option. Do not copy that operation into production as a harmless toggle: it can disrupt other work.

E6 commits balance 777, invokes a controlled dxlab restart, and reads the value again. Each record reports 777 after restart.

This establishes only the reported persistence across that controlled restart. It is not evidence of survival through power loss or every storage failure. Killing a container process would test a different failure model, but would still not reproduce all host power-loss and storage-cache behaviors.

Durability claims must identify commit settings, storage guarantees, and the failure model. They must not be inferred from a clean restart alone. The supplied package does not include crash-recovery or durability-tuning measurements.

1. 1Write down the condition that must remain true. A single-row CHECK and a cross-row total are different problems.
2. 2Decide whether consistency is required within one statement or across several reads in one transaction. RCSI is not a substitute for transaction-level snapshot semantics.
3. 3Check the engine's actual read/write behavior, including explicit locks, driver settings, and transaction boundaries.
4. 4When correctness requires a serializable outcome, evaluate SERIALIZABLE together with transaction error handling. Alternative locking or data-model designs require their own validation; they were not tested here.
5. 5Do not default to dirty reads merely to reduce blocking. Evaluate the relevant versioned-read option and its operational cost.
6. 6Retry the whole transaction for appropriately classified deadlock/serialization failures, with bounded attempts. Handle external side effects and uncertain commit outcomes separately.

The laboratory does not rank engines by speed and does not identify one isolation level as best for all applications.

• •Treating an isolation-level name as an identical cross-engine contract.
• •Assuming that MVCC prevents write skew or eliminates every lock wait.
• •Continuing to commit after a business operation fails.
• •Enabling serializable behavior without planning for transaction retries or explicit failure handling.
• •Using READ UNCOMMITTED as a universal performance switch.
• •Confusing statement snapshots with transaction snapshots.
• •Enabling RCSI without validating application assumptions and resource use.
• •Presenting controlled restart persistence as a power-loss durability test.

The archive includes four engine scenarios, three unexecuted environment-capture scenarios, four reconstructed result records, the supplied appendix, and the original article versions for provenance.

The YAML uses dxlab placeholders such as {node:...} and {secret}. These files are not standalone terminal scripts. They can create, reset, and delete a fixed acidlab database and restart nodes. Use only disposable, explicitly isolated test resources; do not attach them to existing databases with valuable data.

For a new run, capture the exact build, image digest, relevant effective settings on the experiment connections, and full session outputs. Use synchronization barriers if validating precise interleavings. Repeat scenarios if estimating variability. Keep any new run distinct from the supplied records rather than rewriting them.

Evidence index · Download the original evidence archive

• •MVCC glossary
• •PostgreSQL WAL, Full Page Writes, and Checkpoint
• •Connection lifecycle, capacity, and pooling

• •S1: PostgreSQL 17 — Transaction Isolation.
• •S2: MySQL 8.4 — InnoDB Transaction Isolation Levels.
• •S3: Microsoft — SET TRANSACTION ISOLATION LEVEL.
• •S4: MySQL 8.4 — InnoDB Error Handling.
• •S5: Microsoft — SET XACT_ABORT.
• •S6: PostgreSQL 17 — Transactions and savepoints.
• •S7: PostgreSQL 17 — Asynchronous Commit.
• •S8: Microsoft — Transaction Locking and Row Versioning Guide.

Reported observations come from the supplied evidence. Mechanism explanations were checked against official documentation. Package hashes, YAML/JSON parsing, and command-text correspondence were checked in this editorial review; database experiments were not rerun. Report corrections to contact@dataiotech.com.