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
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
The service needs to do three local writes:
INSERT INTO payments (order_id, amount, status)
VALUES ($1, $2, 'completed')
UPDATE orders
SET status = 'paid'
WHERE id = $1
INSERT INTO wallet_transactions (order_id, amount, type)
VALUES ($1, $2, 'credit')
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
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
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)
It keeps rollback as the default if commit does not happen:
committed := false
defer func() {
if !committed {
_ = tx.Rollback()
}
}()
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
}
Successful Flow
The successful local flow is:
BEGIN TRANSACTION
↓
INSERT payment
↓
UPDATE order status
↓
INSERT wallet transaction
↓
COMMIT
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
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
The lab verifies the final state:
payments = 0
order.status = pending
wallet_transactions = 0
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
Safe local transaction failure:
payments = 0
order.status = pending
wallet_transactions = 0
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
The final state:
WhatsApp sent count = 1
payments = 0
paid invoices = 0
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
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
The test shows:
invoice.status = paid
published events = 0
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
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
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
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)