Database
5 min read
Database Transaction
Understanding the fundamentals of database transactions, the ACID properties, and transaction examples in SQL.
240 views
Loading...
Understanding the fundamentals of database transactions, the ACID properties, and transaction examples in SQL.
A database transaction is a set of operations—such as reading, inserting, updating, or deleting data—that are executed as a single unit of work.
Transactions follow the “all-or-nothing” principle: either every operation is saved successfully, or if even one operation fails, all changes are automatically rolled back.
When building applications that involve important data—like balance transfers, payments, ticket bookings, product purchases, or game scoring—you must ensure the whole process runs safely and consistently.
Imagine you’re playing a game. After completing a level, the system must do two things:
Both actions must succeed together. If #1 succeeds but #2 fails (for example, the connection drops midway), you end up with a saved score history but no added coins—your data becomes inconsistent (it gets “stuck”).
A transaction is the database’s way of saying: “treat these steps as one package. If everything goes well, save it permanently (COMMIT). If anything fails, cancel everything as if it never happened (ROLLBACK).”
Here is a minimal real example:
START TRANSACTION;
INSERT INTO score_history(player_id, score)
VALUES (1, 500);
UPDATE players
SET coins = coins + 50
WHERE id = 1;
COMMIT;If an error occurs:
ROLLBACK;Start the transaction
tx, err := db.BeginTx(ctx, nil)
if err != nil {
return err
}db.BeginTx(ctx, nil) creates a new transaction.tx, not db.Add an automatic rollback (safety net)
defer tx.Rollback()Commit() has already succeeded.Commit(), calling Rollback() returns an error like “transaction has already been committed”—but this pattern is still safe and commonly used.First query: insert score history (part 1 of the package)
_, err = tx.ExecContext(
ctx,
"INSERT INTO score_history(player_id, score) VALUES (?, ?)",
playerID,
score,
)
if err != nil {
return err
}tx.ExecContext executes the query inside the transaction.? are parameter placeholders (MySQL style). They are filled by playerID and score.defer tx.Rollback() runs → changes are reverted.Second query: update player coins (part 2 of the package)
_, err = tx.ExecContext(
ctx,
"UPDATE players SET coins = coins + ? WHERE id = ?",
50,
playerID,
)
if err != nil {
return err
}tx.ExecContext so this update is in the same transaction as the insert above.Commit (make changes permanent)
return tx.Commit()Commit() marks the transaction as successful → all changes are saved permanently.Commit() fails (for example, connection drops at the very end), the error is returned to the caller.