Database transactions edge cases

Transactions look simple. They’re not. Especially when you mix database operations with external I/O like emails, APIs, or payment gateways. This is where things break.

Let me show you the progression from naive to robust — and why you might not need the complex solution.

The naive approach: I/O inside transactions

Consider a bank transfer: debit one account, credit another, send confirmation email.

try {
  begin_transaction()
  debit_source_account()
  credit_destination_account()
  send_email_notification()    // 💣
  commit_transaction()
} catch (error) {
  rollback_transaction()
}

Looks clean. But that email call is a landmine.

What goes wrong

By default, most databases use pessimistic locking — when you read or write a row inside a transaction, that row is locked until commit. Other transactions wait.

Now add a slow email server:

1. Row blocking: Other transactions trying to access those accounts wait for the lock to release.

2. Deadlocks: Transaction A waits for a row locked by Transaction B, while B waits for a row locked by A. Neither proceeds.

3. Connection exhaustion: Each transaction holds a database connection. Slow I/O means connections pile up. Pool hits zero. Everything stops.

One slow email server takes down your whole application. The domino effect is real.

The rollback problem

There’s another issue. Consider an e-commerce checkout:

try {
  begin_transaction()
  update_inventory()
  charge_credit_card()         // 💣
  update_order_status()
  commit_transaction()
} catch (error) {
  rollback_transaction()
}

What if update_order_status fails? The database rolls back — but the credit card was already charged. You can’t rollback an HTTP call. Now you have money taken and no order record. Manual cleanup required.

Better: Optimistic locking

Pessimistic locking: Lock rows during the transaction. Others wait.

Optimistic locking: Don’t lock anything. Check for conflicts at commit time using version numbers.

Here’s how it works:

1. Read records and note their version numbers
2. Do your work (including slow I/O)
3. At update time, check if versions changed
4. If unchanged, commit. If changed, retry.

try {
  begin_transaction()

  // Read with versions
  source = get_account(source_id)        // version: 5
  dest = get_account(dest_id)            // version: 3

  // Slow I/O — but no rows are locked
  send_email_notification()

  // Update with version check
  debit(source, amount, expected_version: 5)
  credit(dest, amount, expected_version: 3)

  commit_transaction()
} catch (version_conflict) {
  rollback_transaction()
  retry()
}

What this solves

• No row blocking: Other transactions read and write freely
• No deadlocks: Nothing to deadlock on
• Better concurrency: Multiple transactions proceed in parallel

What this doesn’t solve

You’re still holding a database connection for the duration of the transaction. If the email server is slow and you have many concurrent requests, you can still exhaust your connection pool.

Optimistic locking works well for most applications. But if you need guaranteed delivery and maximum throughput, there’s one more level.

Full solution: Transactional outbox

The outbox pattern separates “what needs to happen” from “making it happen.”

try {
  begin_transaction()
  update_inventory()
  add_to_outbox("charge_credit_card", order_id)
  add_to_outbox("send_confirmation", order_id)
  commit_transaction()
} catch (error) {
  rollback_transaction()
}

The transaction is fast — just database writes. A separate process handles the I/O:

// Background worker
process_outbox() {
  for each pending_operation {
    execute(operation)
    mark_completed(operation)
  }
}

What this solves

• Fast transactions: No I/O, no waiting
• No connection exhaustion: Transactions complete quickly
• Guaranteed delivery: Outbox persists until processed
• Retry built-in: Failed operations stay in queue
• Dead letter queue: Errors go somewhere visible

Trade-off

Eventual consistency. The email doesn’t send immediately — it sends when the worker gets to it. For most use cases, a few seconds delay is fine. For some, it’s not.

Choose wisely

Most apps: Just move I/O after the commit. Done.

High concurrency: Optimistic locking.

Mission-critical delivery: Outbox pattern.

Don’t reach for the complex solution until you have the complex problem.