Testing Transactions and DB Migrations

Transactions and ACID from a tester’s view

A transaction groups several statements so that they take effect all together or not at all. It starts with BEGIN (START TRANSACTION in MySQL is equivalent), ends with COMMIT to keep the changes or ROLLBACK to discard them, and until it commits its changes are invisible to other sessions. The four ACID properties describe what the database promises:

• Atomicity — all the statements or none: an order, its items, the stock reservation and the payment record are written together, or nothing is written.
• Consistency — each committed transaction moves the database from one valid state to another: constraints hold and the totals still reconcile.
• Isolation — concurrent transactions do not see each other’s unfinished work, to the degree the chosen isolation level promises.
• Durability — once COMMIT returns, the change survives a crash or restart.

Testing atomicity: commit, rollback and failure injection

You do not need to re-test the database engine’s guarantees. What you test is whether the application uses transactions correctly: whether the statements that belong together are grouped in one transaction, whether every error path triggers a rollback, and whether anything outside the transaction — an email, a message to another service, a file — is sent before the data is actually committed.

The method is failure injection: make one step fail after earlier steps have run, then look for leftovers. Engines differ here in a way that matters. In PostgreSQL, an error inside a transaction aborts it, and every later statement fails until ROLLBACK. In MySQL InnoDB, most errors roll back only the failing statement and the transaction stays open — so application code that catches the error and then commits keeps the rows written before it. Test the error paths on the engine you ship on.

A transaction also ends at the database boundary. When an order service commits and then calls a payment service that fails, no database rollback can undo the committed order; the design needs a compensating action (for example, a saga that cancels the order) or an outbox that publishes events only after commit. Test that compensation as a feature in its own right.

• Invalid data in the last of several items — the order and the earlier items must not remain.
• A payment provider timeout after the order is written — the order ends in a defined state (pending or cancelled), never “paid” without a payment.
• The application process killed between two steps (in a test environment) — on restart, nothing is half-written, and retries do not duplicate.
• A unique-key violation mid-transaction — the user sees a clear error and no partial rows remain.

-- PostgreSQL test database: the order must not survive a failed item
BEGIN;

INSERT INTO orders (order_id, customer_id, status, total_amount, created_at)
VALUES (9001, 317, 'PENDING', 150.00, '2026-09-28 10:00:00');

INSERT INTO order_items (order_item_id, order_id, product_sku, quantity, unit_price)
VALUES (90011, 9001, 'SKU-100', 0, 150.00);  -- violates CHECK (quantity > 0)

ROLLBACK;

SELECT COUNT(*) AS leftover FROM orders WHERE order_id = 9001;  -- expect 0

Concurrency anomalies you can test for

Isolation is a trade-off between correctness and throughput, so databases let transactions see some effects of others. The SQL standard names three phenomena; PostgreSQL’s documentation adds serialization anomalies; and the one testers meet most often in practice is not in the standard’s list at all: the lost update.

• Dirty read — a transaction reads data written by another transaction that has not committed yet (and may roll back).
• Non-repeatable read — a transaction reads the same row twice and gets different values, because another transaction committed a change in between.
• Phantom read — a transaction runs the same query twice and gets a different set of rows, because another transaction inserted or deleted matching rows and committed.
• Serialization anomaly — the result of committed concurrent transactions matches no order of running them one at a time (write skew is the classic case: two doctors each go off call because each saw the other still on call).
• Lost update — two transactions read the same value, both compute a new value from it, and the second write silently overwrites the first. The script below reproduces it with two SQL sessions.

-- Setup on a test database: one unit left
CREATE TABLE inventory (
  sku   VARCHAR(40) NOT NULL PRIMARY KEY,
  stock INTEGER     NOT NULL CHECK (stock >= 0)
);
INSERT INTO inventory (sku, stock) VALUES ('SKU-100', 1);

-- Step 1, session A
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100';   -- reads 1

-- Step 2, session B
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100';   -- reads 1
UPDATE inventory SET stock = 0 WHERE sku = 'SKU-100'; -- app computed 1 - 1
COMMIT;

-- Step 3, session A
UPDATE inventory SET stock = 0 WHERE sku = 'SKU-100'; -- app computed 1 - 1
COMMIT;

-- Two sales, one unit: B's update is lost.
-- PostgreSQL READ COMMITTED and MySQL InnoDB REPEATABLE READ: both commits succeed.
-- PostgreSQL REPEATABLE READ: step 3 fails with a serialization error; the app must retry.

Isolation levels: what each prevents, and how engines differ

The SQL standard defines four levels by which of the three phenomena they must prevent. These are minimum guarantees, and each engine implements them differently, so always check the documentation of the engine and version you run.

PostgreSQL defaults to READ COMMITTED and never allows dirty reads — its READ UNCOMMITTED behaves like READ COMMITTED. Its REPEATABLE READ gives each transaction a snapshot taken at the start of its first statement other than a transaction-control command such as BEGIN, and does not allow phantom reads either, which is stricter than the standard requires; if such a transaction tries to update a row that another transaction changed and committed after its snapshot, it fails with a serialization error that the application must catch and retry. SERIALIZABLE additionally detects serialization anomalies and aborts one of the transactions involved.

MySQL’s InnoDB engine defaults to REPEATABLE READ. Plain SELECTs in a transaction read a consistent snapshot established by the first read; locking reads (SELECT … FOR UPDATE or FOR SHARE), UPDATE and DELETE work on the latest committed rows and take locks, including next-key (gap) locks that block inserts into the scanned range. Unlike PostgreSQL, an update of a row changed by another committed transaction does not fail — it applies to the latest version — so a read-then-write in application code can lose an update unless it uses a locking read or an atomic statement. SQLite implements isolation in its own way, so check its documentation rather than assuming PostgreSQL or MySQL behaviour.

Isolation level    Dirty read   Non-repeatable read   Phantom read
READ UNCOMMITTED   possible     possible              possible
READ COMMITTED     prevented    possible              possible
REPEATABLE READ    prevented    prevented             possible
SERIALIZABLE       prevented    prevented             prevented

Minimum guarantees of the SQL standard. Engines may prevent more:
PostgreSQL never allows dirty reads, and its REPEATABLE READ also prevents phantoms.
Lost update is not in the standard's table; see the engine notes above.

Concurrency tests you can run by hand and automate

The simplest concurrency test needs two SQL sessions and a written schedule of steps, as in the script above. It reproduces the race deterministically, which makes it perfect for a bug report. Then test the real application: send the competing requests in parallel through the API, with a small script or a load tool such as k6 or JMeter using a handful of virtual users, and repeat the run enough times to hit the window.

The expected result of a concurrency test is never “both succeed” when only one may. Exactly one request wins; the other receives a defined response (a conflict, an out-of-stock message, or a transparent retry); and afterwards the data reconciles — stock is never negative, a coupon is not used beyond its limit, and the sum of balances across a transfer is unchanged. Engines detect deadlocks and abort one of the transactions; the application should retry it, and the user should never see a 500.

• Last item in stock bought by two customers at the same moment.
• Double-clicked Submit, or a client retry after a timeout — with an idempotency key, the second request returns the first result instead of creating a second order.
• Two administrators editing the same record — with optimistic locking, the second save is rejected with a conflict instead of silently overwriting.
• A limited coupon redeemed by many users at once.
• Transfers in opposite directions between the same two accounts — a classic deadlock pattern.

-- Option 1: lock the row while deciding (PostgreSQL, MySQL; not SQLite)
BEGIN;
SELECT stock FROM inventory WHERE sku = 'SKU-100' FOR UPDATE;
UPDATE inventory SET stock = stock - 1 WHERE sku = 'SKU-100';
COMMIT;

-- Option 2: one conditional, atomic statement; the app checks rows affected = 1
UPDATE inventory
SET stock = stock - 1
WHERE sku = 'SKU-100' AND stock >= 1;

-- Option 3: optimistic locking with a version column; 0 rows affected = conflict
UPDATE orders
SET status = 'SHIPPED', version = version + 1
WHERE order_id = 1042 AND version = 7;

Schema and data migrations: what goes wrong

A schema migration changes structure — adds a table, a column, an index or a constraint, renames or drops something. A data migration changes content — backfills a new column, splits a name field, converts currencies. Migration tools such as Flyway, Liquibase or a framework’s own migrations apply numbered scripts in order and record which ran. The risks are the same whichever tool you use:

• Locks and table rewrites — some operations, such as changing a column’s type or building an index without the engine’s online option, lock or rewrite the table. Seconds on a test database can become minutes or hours at production volume. How a given operation behaves depends on the engine and its version.
• Data loss — dropping or narrowing a column, reducing decimal scale, converting text to numbers, or changing the character set can destroy or corrupt values without an error.
• Incomplete backfills — the script misses NULLs, unusual values, or rows created while it was running.
• Compatibility during rollout — in a rolling deployment the old and new application versions run against the same schema at the same time, so renaming a column breaks the old version immediately.
• Partial failure — MySQL commits implicitly before and after DDL statements, so a multi-step migration that fails halfway leaves the schema half-changed. PostgreSQL can run most DDL inside a transaction, but some statements, such as CREATE INDEX CONCURRENTLY, cannot run inside a transaction block.
• One-way steps — a down script can recreate a dropped column but not the data that was in it.

How to test a migration

Rehearse every significant migration on a database with production-like volume and shape — the same engine and version, similar row counts, similar distributions of values. Build it from generated synthetic data, or from production data only through an anonymisation process your data protection officer has approved; Saudi Arabia’s PDPL applies to personal data in test environments too. Measure how long the migration takes and watch for blocked queries while it runs, ideally with test traffic hitting the application at the same time.

Verify with queries you run before and after: row counts, sums of money columns, distributions by status, and checks specific to the change — no NULLs left in a backfilled column, only allowed values, constraints valid. Save the before results so the comparison is exact.

Plan compatibility with the expand-and-contract pattern (also called parallel change): first add the new structure while keeping the old one, write to both, backfill, switch reads to the new structure, and remove the old one only in a later release. Every step is then backward-compatible, and you can test the old application version against the new schema. Finally, test the way back: run the down migration or the restore procedure on the rehearsal database, time it, and write down which steps are one-way.

-- Fingerprint: run before and after the migration and compare the results
SELECT 'orders' AS table_name, COUNT(*) AS row_count, SUM(total_amount) AS amount_sum FROM orders
UNION ALL
SELECT 'order_items', COUNT(*), SUM(quantity * unit_price) FROM order_items
UNION ALL
SELECT 'payments', COUNT(*), SUM(amount) FROM payments;

-- A distribution that must not change
SELECT status, COUNT(*) AS order_count
FROM orders
GROUP BY status
ORDER BY status;

-- Example: after adding orders.currency with a backfill to 'SAR'
SELECT COUNT(*) AS missing_currency FROM orders WHERE currency IS NULL;

SELECT currency, COUNT(*) AS order_count
FROM orders
GROUP BY currency
ORDER BY currency;

Try it: SQL Dataset Generator Try it: SQL Query Validator

Migration test checklist

Copy this checklist into the ticket of every migration that changes data or touches a large table. It is deliberately short; each line is a question someone must be able to answer yes to before the release.

MIGRATION TEST CHECKLIST: <migration id>

Before
[ ] Change classified: schema, data, or both
[ ] Destructive steps identified (DROP, narrower type or length, data rewrite)
[ ] Rehearsal database at production-like volume (synthetic or approved anonymised data)
[ ] Fingerprint queries run and results saved (counts, sums, distributions)
[ ] Backup or point-in-time restore confirmed; restore time known

During the rehearsal
[ ] Duration measured; locks and blocked queries observed
[ ] Application traffic running while the migration runs
[ ] Failure halfway simulated; resulting state understood

After
[ ] Fingerprint queries match, or differ exactly as intended
[ ] New columns populated; no unexpected NULLs; constraints valid
[ ] Old application version works against the new schema
[ ] New application version passes smoke and integrity checks
[ ] Down migration or restore tested; one-way steps documented
[ ] Plans of the main queries unchanged or better

Automating transaction and migration tests in CI

Run database tests on the same engine and major version as production, started fresh in the pipeline — typically in a container. Substituting SQLite or an in-memory database for PostgreSQL or MySQL hides exactly the behaviour this page is about: isolation, locking, DDL in transactions and constraint enforcement all differ.

The pipeline should apply all migrations to an empty database, apply the new migrations to a database seeded at the previously released version, run the integration tests including the concurrency cases, and finish with the read-only integrity queries. Large-volume rehearsals take longer and usually run on a schedule or before release rather than on every commit.

• Migrations from scratch and from the previous release both succeed.
• Down migrations, where the team supports them, run cleanly on seeded data.
• Concurrency tests assert on the final data, not only on response codes.
• Integrity queries (orphans, duplicates, reconciliation) return zero rows after the suite.
• A migration that locks a table beyond the agreed limit in rehearsal blocks the release.

Try it: SQL INSERT Generator Try it: Boundary Number Dataset Generator

Frequently asked questions

References

• Transactions — PostgreSQL DocumentationPostgreSQL Global Development GroupWhat a transaction is: several steps applied all-or-nothing with BEGIN, COMMIT and ROLLBACK, invisible to others until committed.
• Transaction Isolation — PostgreSQL DocumentationPostgreSQL Global Development GroupThe SQL isolation levels and the phenomena they allow — dirty reads, non-repeatable reads, phantoms, serialization anomalies — as implemented by PostgreSQL.
• Transaction Isolation Levels — MySQL 8.4 Reference ManualOracle (MySQL)The four SQL isolation levels as InnoDB implements them, with REPEATABLE READ as its default.
• Transaction — SQLiteSQLiteHow SQLite begins, commits and rolls back transactions, including deferred, immediate and exclusive modes.
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development GroupCheck, not-null, unique, primary-key, foreign-key and exclusion constraints — the rules a database enforces and a tester verifies.
• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023The framework part of the international SQL standard, which the dialects of PostgreSQL, MySQL, SQLite and others implement to different degrees.
• Personal Data Protection Law (English translation)Saudi Data & AI Authority (SDAIA) · 2023The Saudi law governing personal data — why test environments should use synthetic data rather than copies of real customer records.