I've interviewed hundreds of developers. When I ask about database transactions, most answer "ACID" and stop there. When I ask what READ COMMITTED allows that REPEATABLE READ doesn't, silence.
Transactions are more nuanced than "BEGIN...COMMIT." The defaults most developers use allow surprising behaviors.
ACID stands for Atomicity, Consistency, Isolation, Durability. But here's what textbooks don't emphasize:
Isolation has levels. And the default level in most databases allows things that violate intuitive expectations.
Most developers have never set an isolation level explicitly. That means you're using READ COMMITTED—the default in PostgreSQL and Oracle. MySQL InnoDB defaults to REPEATABLE READ, which is slightly stricter, but still allows phantom reads in some scenarios. SERIALIZABLE is the only level that prevents all the weird stuff, but it's also the slowest and you have to opt into it explicitly.
If you've never thought about isolation levels, you're probably getting surprised by your database without knowing it.
In READ COMMITTED, if you read the same row twice in a transaction, you might get different results:
Transaction A ran two identical queries. Got two different answers. Both queries were inside the same transaction.
This matters when your business logic depends on consistent reads:
Even in REPEATABLE READ (MySQL's default), you might see new rows appear:
The count changed. A "phantom" row appeared. The first query said 10 pending orders, so you allocated 10 workers. But now there are 11.
For financial or inventory operations, use SERIALIZABLE or explicit locking:
Or use explicit locks:
For read-heavy reporting, use REPEATABLE READ or snapshot isolation:
Long transactions are dangerous:
Long transactions cause lock contention—other queries wait or timeout. They cause transaction log growth, because the database must track all changes for potential rollback. They exhaust your connection pool, because this connection is occupied for 5 minutes. And they cause replication lag, because replicas can't apply changes until commit.
Better: batch in smaller transactions:
Each batch is its own transaction. Locks are held briefly. Progress is committed incrementally.
You can roll back part of a transaction without aborting the whole thing:
Savepoints are useful for "try this, if it fails, try something else" patterns within a single transaction.
Two transactions waiting for each other means a deadlock. Transaction A locks row 1 and tries to lock row 2. Meanwhile, Transaction B locked row 2 and is trying to lock row 1. Both wait forever. The database eventually detects this and aborts one.
Prevention strategies:
Lock in consistent order. Always lock rows in the same sequence—by ID ascending, for example:
Keep transactions short. Less time holding locks means less chance of deadlock.
Use row-level locks. Avoid table-level locks when possible.
Mistake 1: Auto-commit without knowing it. Many drivers auto-commit each statement by default. You think you're in a transaction, but you're not.
Always be explicit:
Mistake 2: Transaction spans network calls. Never call external services inside a database transaction:
Use the saga pattern or outbox pattern instead.
Mistake 3: Catching exceptions and continuing. If any statement fails, you should typically rollback the entire transaction—not log and continue.
Understand your isolation level. READ COMMITTED is probably not what you expect.
Keep transactions short. Long transactions cause lock contention and log bloat.
Lock in consistent order. Prevents deadlocks.
Don't mix transactions with external calls. Use patterns designed for distributed operations.
Be explicit. Don't rely on auto-commit behavior.
Transactions seem simple until they aren't. Know the edge cases before they bite you in production.
UUIDs aren't always the answer. Here's when they hurt more than help.
You optimized the wrong thing. Again. Here's why guessing at performance never works.
Two servers, one resource, zero coordination. Here's how it breaks.
-- Transaction A
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
-- Transaction B (commits between A's reads)
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;
-- Back to Transaction A
SELECT balance FROM accounts WHERE id = 1; -- Returns 500!
COMMIT;-- Transaction A
BEGIN;
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- Returns 10
-- Transaction B
INSERT INTO orders (status) VALUES ('pending');
COMMIT;
-- Back to Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending'; -- Returns 11!
COMMIT;BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT balance FROM accounts WHERE id = 1;
-- Any concurrent modification will cause this transaction to abort
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT; -- Fails if another transaction touched this dataBEGIN;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- Row is locked. Other transactions wait.
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;BEGIN ISOLATION LEVEL REPEATABLE READ;
-- All reads see a consistent snapshot from transaction start
SELECT * FROM orders WHERE created_at > NOW() - INTERVAL '1 day';
SELECT * FROM order_items WHERE order_id IN (...);
-- Guaranteed consistent, even if data changes during query
COMMIT;BEGIN;
-- 10,000 updates over 5 minutes
UPDATE products SET price = price * 1.1 WHERE category = 'electronics';
-- This holds locks on every touched row for 5 minutes
COMMIT;BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 100);
-- order created
SAVEPOINT before_items;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 999, 1);
-- Oops, product 999 doesn't exist, this might fail
ROLLBACK TO SAVEPOINT before_items;
-- Only the order_items insert is rolled back
-- The orders insert is still pending
INSERT INTO order_items (order_id, product_id, quantity) VALUES (1, 1, 1);
-- Try with a valid product
COMMIT;
-- Both the order and the corrected item are committed// Danger: balance might change between check and update
var balance = await db.QuerySingleAsync<decimal>("SELECT balance FROM accounts WHERE id = @id", new { id });
if (balance >= amount)
{
await db.ExecuteAsync("UPDATE accounts SET balance = balance - @amount WHERE id = @id", new { id, amount });
}
// Race condition: balance could have changed between SELECT and UPDATEvar ids = await db.QueryAsync<int>("SELECT id FROM products WHERE category = 'electronics'");
foreach (var batch in ids.Chunk(1000))
{
await using var tx = await connection.BeginTransactionAsync();
await db.ExecuteAsync(
"UPDATE products SET price = price * 1.1 WHERE id = ANY(@ids)",
new { ids = batch.ToArray() }
);
await tx.CommitAsync();
}// Bad: Unpredictable lock order
foreach (var item in items.OrderBy(i => i.Price))
{
await db.ExecuteAsync("UPDATE products SET stock = stock - 1 WHERE id = @id", new { id = item.Id });
}
// Good: Predictable lock order
foreach (var item in items.OrderBy(i => i.Id))
{
await db.ExecuteAsync("UPDATE products SET stock = stock - 1 WHERE id = @id", new { id = item.Id });
}// This is NOT a transaction in most drivers
await db.ExecuteAsync("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
await db.ExecuteAsync("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
// If second statement fails, first is already committedawait using var tx = await connection.BeginTransactionAsync();
// Now it's a real transaction
await tx.CommitAsync();await using var tx = await connection.BeginTransactionAsync();
var order = await CreateOrder(tx);
var paymentResult = await paymentApi.Charge(order.Total); // HTTP call to Stripe!
// If Stripe is slow, transaction is open for seconds
// If Stripe times out, you might commit or rollback?
await tx.CommitAsync();