The race condition that breaks your balance
You are building a ticketing system. Two users click "Buy" for the last seat at the exact same millisecond. Your Go code runs on two different goroutines. Both read the seat count, see 1, and both think they have a ticket. Both write 0 back to the database. You sold two tickets for one seat. The database accepted both writes because each transaction looked valid in isolation.
This is a race condition. The database does not know that your Go code read the value, did math, and wrote it back. It sees two independent updates. Pessimistic locking fixes this by telling the database, "I am reading this row. Lock it so nobody else can read or write until I finish." The database blocks other transactions until you commit or roll back. You trade a bit of waiting time for guaranteed correctness.
Pessimistic locking explained
Pessimistic locking assumes conflicts will happen, so you grab the lock before you do any work. The name comes from the mindset: you are pessimistic about other transactions touching your data, so you secure it immediately.
Think of a physical file cabinet. You pull the drawer and take the key. If someone else tries to open that drawer, they find the key is gone and have to wait. You make your changes, put the file back, and return the key. Only then can the next person access the drawer.
In SQL, this happens with SELECT ... FOR UPDATE. A plain SELECT reads the data but does not lock the row. The database returns a snapshot, and other transactions can modify the row while you are still looking at it. Adding FOR UPDATE tells the database to acquire an exclusive lock on the returned rows. Other transactions that try to SELECT ... FOR UPDATE or UPDATE those rows will block until your transaction ends.
The lock lives inside the database, not in your Go code. Go holds the database connection open while the transaction is active. The database tracks which rows are locked by which transaction. When you call Commit, the database saves the changes and releases all locks. If you call Rollback, the database discards the changes and releases the locks.
Pessimistic locking works best when conflicts are common and the work inside the transaction is fast. If you hold a lock while making an HTTP call or processing a large file, you block the entire system. Keep transactions short.
Minimal example
Here is the core pattern: start a transaction, select the row with FOR UPDATE, perform the update, and commit. The lock is held from the SELECT until the COMMIT.
package main
import (
"database/sql"
)
// updateBalance locks a row, updates the balance, and commits the transaction.
func updateBalance(db *sql.DB, accountID int, amount int64) error {
// Begin starts a new transaction. The driver uses the default isolation level.
tx, err := db.Begin()
if err != nil {
return err
}
// Rollback is deferred to ensure the transaction closes even if we return early.
// Calling Rollback on a committed transaction is a safe no-op.
defer tx.Rollback()
var currentBalance int64
// FOR UPDATE acquires an exclusive lock on the row.
// Other transactions trying to lock this row will block until this transaction ends.
err = tx.QueryRow("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", accountID).Scan(¤tBalance)
if err != nil {
return err
}
// Calculate the new balance in Go. The lock prevents other transactions from changing currentBalance.
newBalance := currentBalance + amount
_, err = tx.Exec("UPDATE accounts SET balance = $2 WHERE id = $1", accountID, newBalance)
if err != nil {
return err
}
// Commit saves the changes and releases the lock.
return tx.Commit()
}
What happens under the hood
When db.Begin() runs, the database creates a transaction context. The isolation level determines what other transactions can see. Most drivers default to ReadCommitted, which means you see changes committed by others, but FOR UPDATE still locks rows exclusively.
The SELECT ... FOR UPDATE query hits the database. The database checks the row. If no one else holds a lock, the database grants the lock to your transaction and returns the row data. If another transaction already holds the lock, your query blocks. The Go code pauses at QueryRow until the other transaction commits or rolls back.
Once you have the lock, your Go code runs. The database guarantees that no other transaction can modify the locked row while you are calculating. When you execute the UPDATE, the database applies the change. Finally, Commit writes the changes to disk and releases the lock. Any blocked transactions wake up and proceed.
The defer tx.Rollback() pattern is standard Go convention. Even if the transaction commits successfully, calling Rollback afterward does nothing harmful. The database/sql package checks the transaction state and ignores the rollback if the transaction is already closed. This keeps error handling uniform: you always roll back, and you only commit on the success path.
The lock is on the database, not your code. Keep transactions short.
Realistic example: transferring funds
Transferring money between accounts requires locking two rows. If you lock account A then account B, and another transaction locks B then A, you create a deadlock. Both transactions wait for each other forever. The database detects this and aborts one transaction with a deadlock error.
To prevent deadlocks, always lock rows in a consistent order. Sorting by ID is the simplest strategy. Lock the lower ID first, then the higher ID. Every transaction follows the same order, so circular waits cannot happen.
Here is the locking logic with ordering:
package main
import (
"database/sql"
"fmt"
)
// transferFunds moves money between two accounts.
// It locks both rows in ID order to prevent deadlocks.
func transferFunds(db *sql.DB, fromID, toID int, amount int64) error {
tx, err := db.Begin()
if err != nil {
return err
}
defer tx.Rollback()
// Determine lock order. Always lock the lower ID first.
firstID, secondID := fromID, toID
if fromID > toID {
firstID, secondID = toID, fromID
}
var balance1, balance2 int64
// Lock the first row.
err = tx.QueryRow("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", firstID).Scan(&balance1)
if err != nil {
return err
}
// Lock the second row. This blocks if another transaction holds the lock.
err = tx.QueryRow("SELECT balance FROM accounts WHERE id = $1 FOR UPDATE", secondID).Scan(&balance2)
if err != nil {
return err
}
// Identify which balance belongs to the source account.
var sourceBalance int64
if fromID == firstID {
sourceBalance = balance1
} else {
sourceBalance = balance2
}
// Check if the source account has enough funds.
if sourceBalance < amount {
return fmt.Errorf("insufficient funds")
}
// Update balances using atomic SQL arithmetic.
// This is safer than reading, calculating in Go, and writing back.
_, err = tx.Exec("UPDATE accounts SET balance = balance - $1 WHERE id = $2", amount, fromID)
if err != nil {
return err
}
_, err = tx.Exec("UPDATE accounts SET balance = balance + $1 WHERE id = $2", amount, toID)
if err != nil {
return err
}
return tx.Commit()
}
Using balance - $1 in the SQL update is safer than calculating the new balance in Go. The database applies the arithmetic directly to the stored value. This avoids race conditions if the balance changes between your SELECT and UPDATE, though the lock already prevents that. The atomic update is a defensive habit that works even if you forget the lock.
In production code, pass context.Context as the first argument to transferFunds. Use db.BeginTx(ctx, ...) to propagate the context. The context controls timeouts and cancellation. If the client disconnects, the context cancels, and the transaction rolls back automatically. This prevents goroutine leaks and stuck locks.
Deadlocks kill performance. Lock in a consistent order.
Pitfalls and errors
Pessimistic locking introduces specific failure modes. Understanding these helps you debug issues quickly.
Deadlocks happen when two transactions lock resources in opposite orders. Transaction A locks row 1 and waits for row 2. Transaction B locks row 2 and waits for row 1. Neither can proceed. The database detects the cycle and aborts one transaction. Postgres returns ERROR: deadlock detected. MySQL returns Deadlock found when trying to get lock. The error includes details about the conflicting transactions. Retry the transaction on deadlock. Use exponential backoff to avoid thundering herds.
Long transactions hold locks for too long. If your transaction includes an HTTP call, file I/O, or heavy computation, you block other transactions for the entire duration. The system slows down as queues build up. Keep transactions limited to database operations. Do the work in Go, then start a transaction to update the database.
Forgetting FOR UPDATE is a common mistake. A plain SELECT does not lock the row. You read the data, do math, and write back. Another transaction can modify the row between your read and write. The database accepts both writes, and one update is lost. The compiler does not catch this. The error appears as incorrect data at runtime. Always verify your queries include FOR UPDATE when you need exclusive access.
Lock timeouts occur when a transaction waits too long for a lock. Databases have timeout settings. If the lock is not acquired within the limit, the query fails. Postgres returns ERROR: could not serialize access due to concurrent update in serializable mode, or ERROR: canceling statement due to lock timeout. MySQL returns Lock wait timeout exceeded. Handle these errors by retrying the transaction.
Serializable isolation prevents phantom reads but increases conflict rates. If you use sql.LevelSerializable, the database checks for conflicts at commit time. Even if you hold locks, you might get a serialization failure if another transaction modified a row in your read range. The error message varies by driver. Postgres returns ERROR: could not serialize access due to concurrent update. Retry on serialization failures.
SKIP LOCKED is an option for queue-like workloads. If you are processing jobs and multiple workers compete for rows, SELECT ... FOR UPDATE SKIP LOCKED lets a worker grab an unlocked row without waiting. This avoids contention when many workers try to lock the same rows. Use this for job queues, not for financial data.
The worst goroutine bug is the one that never logs. Always log transaction errors and retries.
When to use pessimistic locking
Choose the right concurrency strategy based on your workload.
Use pessimistic locking when conflicts are frequent and the cost of retrying is high. Use pessimistic locking when the critical section is short and the lock scope is small. Use pessimistic locking when you need to enforce business rules that depend on the current state of the data. Use optimistic locking when reads vastly outnumber writes and conflicts are rare. Use atomic operations when you only need to increment or decrement a single value without complex logic. Use plain sequential code when concurrency is not a factor.