DEV Community

lukman lukman
lukman lukman

Posted on

Database Transactions Are a Boundary, Not a Safety Blanket

The uncomfortable part of a failed payment flow is not always the error.

Sometimes the function returns an error and the database still keeps half of the work.

That is the failure Lab 03 demonstrates. A payment is inserted. The order is marked as paid. Then the flow fails before the wallet transaction is inserted.

The result is not a clean failure:

payment persisted = 1
order.status = paid
wallet_transactions = 0
Enter fullscreen mode Exit fullscreen mode

That is the useful lesson: database transactions are not just a way to call ROLLBACK. They are a boundary. Anything inside the boundary can commit or roll back together. Anything outside it cannot be magically undone by the database.

The Scenario

The lab starts with a local payment operation.

Initial state:

orders.id = 101
orders.status = pending

invoices.order_id = 101
invoices.status = unpaid
Enter fullscreen mode Exit fullscreen mode

The service needs to do three local writes:

INSERT INTO payments (order_id, amount, status)
VALUES ($1, $2, 'completed')
Enter fullscreen mode Exit fullscreen mode
UPDATE orders
SET status = 'paid'
WHERE id = $1
Enter fullscreen mode Exit fullscreen mode
INSERT INTO wallet_transactions (order_id, amount, type)
VALUES ($1, $2, 'credit')
Enter fullscreen mode Exit fullscreen mode

The injected failure happens after the first two statements, but before the wallet transaction insert.

The Problem

PaymentServiceUnsafe executes statements directly with db.ExecContext.

The flow is:

INSERT payment
  ↓
UPDATE order to paid
  ↓
injected failure
  ↓
wallet transaction is not inserted
Enter fullscreen mode Exit fullscreen mode

There is no shared transaction boundary around the related writes. So when the failure happens, the database does not roll the earlier statements back.

The test result is the whole point:

payment persisted = 1
order.status = paid
wallet_transactions = 0
Enter fullscreen mode Exit fullscreen mode

The application failed. The database did not return to the original state.

What Actually Went Wrong

The bug is not that SQL failed.

The bug is that the service treated a multi-step business operation as separate database statements.

From the business side, the payment row, the paid order status, and the wallet transaction belong to the same local invariant. From the unsafe database flow, they are just separate statements executed in order.

Without a transaction, the database has no reason to undo statement one and statement two just because the application never reaches statement three.

Using a Local Database Transaction

PaymentServiceSafe changes the boundary.

It starts a transaction:

tx, err := s.db.BeginTx(ctx, nil)
Enter fullscreen mode Exit fullscreen mode

It keeps rollback as the default if commit does not happen:

committed := false
defer func() {
    if !committed {
        _ = tx.Rollback()
    }
}()
Enter fullscreen mode Exit fullscreen mode

Then the related writes are executed through tx.ExecContext instead of db.ExecContext.

If all local writes succeed, the transaction commits:

if err := tx.Commit(); err != nil {
    return err
}
Enter fullscreen mode Exit fullscreen mode

Successful Flow

The successful local flow is:

BEGIN TRANSACTION
  ↓
INSERT payment
  ↓
UPDATE order status
  ↓
INSERT wallet transaction
  ↓
COMMIT
Enter fullscreen mode Exit fullscreen mode

At commit time, the local database state becomes complete together.

The lab uses the same idea again in the outbox example. InvoiceServiceOutbox updates the invoice and inserts an outbox event in one local transaction:

BEGIN
  ↓
UPDATE invoices SET status = 'paid'
  ↓
INSERT INTO outbox_events (... status = 'pending' ...)
  ↓
COMMIT
Enter fullscreen mode Exit fullscreen mode

After commit, both the business state and the event intent exist locally.

Failure Flow

The failure path is where the difference becomes visible:

BEGIN TRANSACTION
  ↓
INSERT payment
  ↓
UPDATE order status
  ↓
injected failure
  ↓
ROLLBACK
Enter fullscreen mode Exit fullscreen mode

The lab verifies the final state:

payments = 0
order.status = pending
wallet_transactions = 0
Enter fullscreen mode Exit fullscreen mode

This is a clean local failure. The service still returns an error, but the database does not keep half of the payment flow.

Before vs After

Unsafe failure:

payments = 1
order.status = paid
wallet_transactions = 0
Enter fullscreen mode Exit fullscreen mode

Safe local transaction failure:

payments = 0
order.status = pending
wallet_transactions = 0
Enter fullscreen mode Exit fullscreen mode

This is what local atomicity buys you.

But the lab does not stop there, because this is only the easy boundary.

Rollback Does Not Undo WhatsApp

The next example uses DistributedOrderService.

The service opens a database transaction, inserts a payment, updates an invoice, sends a WhatsApp notification, then hits a simulated ERP integration error.

BEGIN TRANSACTION
  ↓
INSERT payment
  ↓
UPDATE invoice
  ↓
Send WhatsApp notification
  ↓
simulated ERP integration error
  ↓
ROLLBACK database transaction
Enter fullscreen mode Exit fullscreen mode

The final state:

WhatsApp sent count = 1
payments = 0
paid invoices = 0
Enter fullscreen mode Exit fullscreen mode

The database rollback worked. The WhatsApp message was still sent.

That is not a contradiction. WhatsApp is outside the database transaction boundary. The database can roll back its own rows. It cannot recall a message that has already been sent through another system.

The lab uses the same boundary idea for email, SMS, ERP APIs, payment gateway APIs, and message broker publishes.

HTTP Inside a Transaction

The lab also shows a blocking external call while a database transaction is open:

BEGIN TRANSACTION
  ↓
UPDATE invoice SET status = 'paid'
  ↓
HTTP call blocks
  ↓
transaction stays open
Enter fullscreen mode Exit fullscreen mode

The test verifies that the transaction remains open during the blocking external call, then closes after commit.

The issue here is not only failure. The lifetime of the database transaction is now tied to an external resource.

The Dual-Write Gap

Another failure appears when database commit and event publish are separate operations.

UPDATE invoice
  ↓
COMMIT succeeds
  ↓
process crashes
  ↓
Publish event never happens
Enter fullscreen mode Exit fullscreen mode

The test shows:

invoice.status = paid
published events = 0
Enter fullscreen mode Exit fullscreen mode

The invoice was paid, but no event was published.

Reversing the order does not make the operation atomic. Publishing before commit can produce an event for a database state that never commits.

Transactional Outbox

The lab uses transactional outbox to keep the business state and event intent in one local transaction.

Local transaction
  ↓
Record business state + event intent
  ↓
COMMIT
  ↓
Dispatcher publishes pending events
Enter fullscreen mode Exit fullscreen mode

InvoiceServiceOutbox inserts an outbox_events row with status pending. The dispatcher later reads pending events, publishes them to the broker, and marks them as published.

The important part is the local atomic step: invoice state and event intent are saved together.

Idempotent Consumer

The lab then shows the consumer side.

The commission worker stores a processed-event marker and the business state in the same transaction:

INSERT INTO processed_events (consumer_name, event_id, processed_at)
VALUES ($1, $2, $3)
ON CONFLICT (consumer_name, event_id) DO NOTHING
Enter fullscreen mode Exit fullscreen mode

If the same consumer receives the same event again, the insert affects zero rows and the event is skipped.

The test result:

same consumer: processed once
same consumer duplicate: skipped
same event, different consumer: processed independently
Enter fullscreen mode Exit fullscreen mode

This matters because the outbox side and the consumer side are connected. Recording an event intent is not enough. The receiver also has to handle repeated delivery safely.

What This Lab Demonstrates

A local transaction is the right tool for local state that must commit or roll back together.

It does not roll back external side effects.

It does not make database commit and broker publish atomic when those are executed as separate operations.

Outbox solves the local database/event-intent part by storing both in one transaction.

Idempotent consumer logic handles repeated processing on the consumer side by storing a dedup marker with the business update.

Key Takeaways

  • First define the transaction boundary.
  • Put related local writes inside the same transaction.
  • Do not treat external calls as rollbackable database work.
  • Do not keep a transaction open longer than necessary while waiting on external systems.
  • Use outbox when database state and event publishing must be coordinated.
  • Make consumers idempotent when the same event can be processed more than once.

Source Code

Repository:

https://github.com/lukman-ss/software-engineering-lab

Lab:

https://github.com/lukman-ss/software-engineering-lab/tree/main/labs/03-database-transaction

Author:

Lukman (lukman-ss)

Top comments (0)