The bugs in this post all pass code review. Each one is correct for a single request, which is the only way you ever run it on your laptop. Traffic is what turns them into incidents, and by then the code has been in production for months.
One example carries the whole post. A bank transfer. Debit one account, credit another, tell the customer, all inside one transaction so the money can’t half-move.
try {
begin_transaction()
debit_source_account()
credit_destination_account()
send_email_notification()
commit_transaction()
} catch (error) {
rollback_transaction()
}Seven bugs. The last one is the most common and the slowest to explain, so it comes last.
The payment you can’t roll back
Say the transfer leaves your system, so the money moves through a payment provider.
try {
begin_transaction()
debit_source_account()
call_payment_provider()
record_transfer()
commit_transaction()
} catch (error) {
rollback_transaction()
}If record_transfer() fails, the database rolls back and the provider has already moved the money.
0 ms begin_transaction()
1 ms debit_source_account() row updated, not committed
2 ms call_payment_provider() money moves, outside your database
400 ms record_transfer() fails
401 ms rollback_transaction() debit undone
payment stays doneYou cannot roll back an HTTP call. At low volume this is a support ticket. At high volume it’s a reconciliation job.
I wrote this code. The provider took the money, the commit failed, and the row saying it happened never existed. The database was perfectly consistent with itself and wrong about the world.
The job that runs before the row exists
You want to notify the customer, so instead of sending the email inside the transaction you enqueue a job and let a worker send it. Good instinct, wrong line.
try {
begin_transaction()
debit_source_account()
credit_destination_account()
transfer_id = record_transfer()
enqueue_job("notify_customer", transfer_id) // goes to Redis right now
commit_transaction()
} catch (error) {
rollback_transaction()
}Redis is not your database. It doesn’t know a transaction is open, so the job is visible to workers the instant you enqueue it, while the transfer row is still invisible to everyone but you. A fast worker picks up the job, looks for the transfer, and finds nothing, because you haven’t committed yet.
0 ms begin_transaction()
1 ms record_transfer() row exists only inside this transaction
2 ms enqueue_job(transfer_id) a worker can see this immediately
4 ms worker: load_transfer(id) finds nothing, job fails
50 ms commit_transaction() now everyone can see the rowWhether it fails depends on who gets there first, the worker or your commit. On your laptop the commit always wins. Under load the transaction takes longer, the worker is idle and quick, and the job fails maybe one time in fifty, which is when you’re least able to reproduce it.
The commit that succeeded and looked like a failure
Your app sends the commit. The database writes it, and the transfer is now permanent. Then, before the database can answer “done”, the network connection between them drops. Your app never gets the answer. It gets an error instead, and that error looks the same as a commit that failed.
0 ms your app sends commit
1 ms database writes it, the transfer is permanent
2 ms network connection drops before the reply
3 ms your app gets "connection lost", which looks like a failed commitSo your app does the sensible thing and retries the transfer. The first one already went through, and now the customer has paid twice. Or your app gives up and shows “transfer failed” for a transfer that succeeded.
From inside your code, a commit that failed and a commit that succeeded but never answered are the same error. The database knows which one happened. You don’t.
The only safe retry is one the database can recognise as a repeat. Give the transfer an ID before you start, and make a second attempt with the same ID do nothing. That’s all an idempotency key is.
Any single commit almost never hits this. Add up every commit your app makes in a year, and one of them will.
The deadlock you wrote yourself
Alice sends Bob 50 while Bob sends Alice 20. Both transfers run the same code, debit first, then credit.
transfer 1, Alice to Bob transfer 2, Bob to Alice
0 ms debit(alice) locks alice
0 ms debit(bob) locks bob
1 ms credit(bob) waits for bob
1 ms credit(alice) waits for alice
... both wait, forever
1000 ms Postgres notices, kills one, the other proceedsEach transaction holds one row and needs the other’s. Neither can finish. After a second Postgres gives up on one of them, and that transfer fails with an error your code has never seen in development, because it needs two transfers between the same two accounts in the same moment.
The fix is a rule, not a lock. Take rows in the same order everywhere, lowest account id first, and two transfers can never wait on each other.
for (account of sorted([source, destination])) lock_row(account)The check that passes twice
Postgres runs at Read Committed by default, and under it every statement in a transaction gets its own snapshot. Reads don’t take a lock, only writes do. So this is a race:
balance = read_balance(account) // 100
if (balance >= 80) {
update_balance(account, -80)
}Two requests run it at the same moment, which never happens on your laptop and happens constantly under load.
request A request B
read_balance() 100
read_balance() 100
update_balance(-80)
commit() balance 20
update_balance(-80) waits for A, then applies to 20
commit() balance -60Both checks passed. Both updates ran. Nothing deadlocked, nothing errored, and the transaction did exactly what it was told. The Postgres docs say it plainly. Two successive reads can see different data inside one transaction.
There are three fixes, and all three make the second request notice the first.
// pessimistic (SELECT ... FOR UPDATE): lock the row, the second request waits
balance = read_balance(account, lock: true)
// optimistic: read a version with the balance, the second write fails and retries
balance, version = read_balance(account)
update_balance(account, -80, if_version: version)
// cheapest: put the check inside the update, the second update changes zero rows
update_balance(account, -80, where: balance >= 80)All three turn a silent overdraft into something you can see. A wait, a failed write, or an update that changed no rows.
The rollback that never happened
Rails-specific, wrong from the first request, and it bites teams that nest transaction blocks without noticing. A nested transaction joins the outer one by default. Raise ActiveRecord::Rollback inside the inner block and the inner block swallows it, no rollback is issued, and the outer transaction commits everything, including the rows you thought you had undone.
transaction {
create_account("outer")
transaction {
create_account("inner")
rollback() // swallowed by the inner block
}
}
// both accounts existStep by step, no rollback ever reaches the database.
0 ms outer_transaction begin
1 ms create_account("outer")
2 ms inner_transaction joins the outer one, no savepoint
3 ms create_account("inner")
4 ms rollback() caught by the inner block, nothing reaches the database
5 ms outer_transaction commit, both rows landThe Rails docs describe this. requires_new: true on the inner block turns it into a savepoint, so the rollback undoes only the inner work.
Slow I/O inside the transaction
Back to the transfer at the top, with a stopwatch on each line:
begin_transaction()
debit_source_account() // 1 ms
credit_destination_account() // 1 ms
send_email_notification() // 3000 ms both rows stay locked
commit_transaction()Two milliseconds of database work, three seconds of lock. At ten transfers a day nobody notices. At a few hundred at once, the same three seconds hurt in three places.
The connection pool
Each open transaction holds a connection for its whole life. A few hundred transfers each waiting three seconds on email, and the pool is empty.
pool of 10 connections, each transfer waits 3000 ms on email
0 ms transfer_1 begin_transaction() holds connection 1
10 ms transfer_2 begin_transaction() holds connection 2
...
90 ms transfer_10 begin_transaction() holds connection 10, pool empty
100 ms page_view get_connection() waits, no transfer involved
110 ms health_check get_connection() waits
2000 ms load_balancer health check timed out, instance removed
3000 ms transfer_1 commit_transaction() connection 1 releasedPages that never touch a transfer fail too, because they need a connection. So does the health check, so the load balancer pulls the instance. A slow email server took the site down, and nothing in the logs says “email”.
Other transfers
The rows you wrote stay locked until you commit, so a second transfer on either account waits three seconds for an email it never sends.
0 ms transfer_A debit_source_account() row locked
1 ms transfer_A credit_destination_account() row locked
2 ms transfer_A send_email_notification() email in flight, locks still held
50 ms transfer_B begin_transaction() starts fine
51 ms transfer_B debit_source_account() waits on the locked row
... transfer_B waiting
3000 ms transfer_A commit_transaction() locks released
3001 ms transfer_B debit_source_account() proceedsLonger locks also give the deadlock above three seconds to happen instead of one millisecond.
The next deploy
The migration needs the whole table, so it waits behind your row lock. Every query that arrives after it waits behind the migration, reads included.
0 ms transfer debit_source_account() row locked, email in flight
500 ms deploy alter_table(accounts) waits for an exclusive lock
600 ms any_page read_account() waits behind the migration
... every query on accounts queues here
3000 ms transfer commit_transaction() lock released, migration runs, queue drainsOne slow third party plus one routine migration, and the table stops answering. The deploy gets the blame, and the deploy is innocent.
One bug, three ways to get paged.
Traffic multiplies all of it
None of these seven needs a big system to exist. The code is wrong at one request a day, and nothing happens. Most of them are races, and a race needs two requests close together. At ten transfers a day the second request is hours away. At a few hundred a second, every race in this post runs thousands of times an hour, and the losers pile up in the same place.
They also feed each other. The slow email holds locks longer, so every window above gets wider. The check has more time to pass twice, the worker more time to beat the commit, the deadlock seconds instead of milliseconds. A retry after a lost commit adds a transfer to the queue. The pool empties, requests slow down, transactions get longer, and every race above gets easier to lose.
The failures that were rare stop being rare, and they arrive together, in the busiest hour, which is the hour you have the least time to look.
What to do about it
Move the I/O after the commit. The email, the payment provider, the job enqueue, all of it goes after commit_transaction(). That one move fixes the slow I/O, the payment you can’t roll back, and the job that runs before the row exists, and it costs nothing. The transaction goes back to holding its locks for two milliseconds.
It opens one gap. The commit succeeds, then the process dies before the email goes out. When the side effect has to happen, write it down as a row inside the transaction and let a worker do the sending afterwards. That’s the transactional outbox. The database records the intent, and something else does the talking. Because the worker reads that row from the same database, after the commit, it can never run before the row exists.
Give the payment provider call an idempotency key, an ID you choose before the call, so a retry with the same ID does nothing twice. That’s the fix for the commit that looked like a failure, and it’s what makes the outbox worker safe to retry.
For the check that passes twice, put the check inside the update statement, or lock the row first, or carry a version number. Pick one and use it everywhere that check happens.
For the deadlock, take rows in the same order everywhere. A rule costs nothing and needs no lock.
In Rails, pass requires_new: true to a nested transaction, or don’t nest.
Don’t reach for the complex solution until you have the complex problem.