Transactions and Database Errors

Why Some Writes Must Stick Together

Imagine moving 300 reward points from one customer wallet to another. If you subtract from the sender but crash before adding to the receiver, your data no longer describes reality. The transfer was conceptually one action, but your code performed two separate SQL statements. That gap is exactly what transactions exist to close.

A transaction says, 'treat these related changes as one unit.' Either all of them become permanent with Commit, or none of them survive and the database returns to the earlier state with Rollback. The word you will often hear is atomicity: the outside world should never observe the half-finished middle.

Using sql.Tx Correctly

The usual shape is: begin the transaction, do all related reads and writes through the tx object, and only then commit. If any step fails, return early and let the rollback happen. One of the safest habits in Go is defer tx.Rollback() immediately after a successful begin. If Commit succeeds, the later rollback becomes harmless because the transaction is already closed. If you exit early, the rollback is the safety net that still runs.

Tracing Success and Rollback

wallets before success case
- id 1: 900 points
- id 2: 200 points

success case
1. begin transaction
2. read wallet 1 balance -> 900
3. subtract 300 from wallet 1 -> staged value 600
4. add 300 to wallet 2 -> staged value 500
5. commit
6. final stored values: wallet 1 = 600, wallet 2 = 500

wallets before failure case
- id 1: 900 points
- id 2: 200 points

failure case
1. begin transaction
2. read wallet 1 balance -> 900
3. subtract 300 from wallet 1 -> staged value 600
4. add to wallet 2 fails because the row is locked or missing
5. rollback
6. final stored values: wallet 1 = 900, wallet 2 = 200

That all-or-nothing promise is what lets higher-level business rules stay honest. A refund, booking, stock reservation, ledger entry, or password-reset token issue often spans several rows or tables. Transactions do not remove every concurrency problem in the world, but they prevent the especially painful category where a multi-step action leaves the system half-changed.

Distinguishing Not Found from Real Failure

Database errors are not all the same. 'No row matched' is very different from 'the query syntax is broken' or 'the database connection died.' If your data-access layer blurs all of those together, callers cannot respond intelligently. A web handler may want to turn missing data into HTTP 404, while a broken connection should probably become HTTP 500 and an alert.

Quiz and Practice

Hands-On Project

Summary and Key Takeaways

• Use transactions when several statements together represent one business action that must not partially succeed.
• Start work with Begin or BeginTx, do all related operations through tx, then Commit or Rollback.
• defer tx.Rollback() is a practical safety habit for early-return failure paths.
• A rollback preserves the pre-transaction state when something fails partway through.
• Treat sql.ErrNoRows as a distinct not-found case, and translate it into clearer domain errors for callers.