TiDB is a distributed NewSQL database that supports both optimistic and pessimistic transaction models. Pessimistic transactions in TiDB behave similarly to MySQL/InnoDB: when a transaction reads a row with SELECT ... FOR UPDATE, it acquires an exclusive lock on that row immediately, preventing other transactions from modifying it until the lock holder commits or rolls back. This is the default transaction mode in TiDB since version 3.0.8, chosen because it provides behavior familiar to MySQL users and avoids the high abort rates of optimistic transactions under contention.
The pattern of using SELECT FOR UPDATE to lock rows, check invariants, and then update is fundamental to financial and transactional workloads. Consider a marketplace where a buyer purchases goods from a seller: the system must read the buyer’s coin balance and the seller’s goods count, verify both are sufficient, and then atomically debit/credit both accounts. Without pessimistic locking, a concurrent transaction could read the same balances and both succeed, violating the invariant.
In a typical distributed marketplace application, this pattern is used extensively with a helper function util.TiDBSqlBegin that starts transactions in pessimistic mode. The application issues SELECT ... FOR UPDATE reads to pin seller and buyer rows, enforces goods/coin invariants, and atomically updates balances before commit. This ensures that concurrent marketplace transactions are serialized at the row level without application-level locking.
In an optimistic transaction, no locks are acquired during the transaction. Conflict detection happens only at commit time: if another transaction modified a row you read, your commit is rejected and you must retry. This works well under low contention but suffers high abort rates when many transactions compete for the same rows.
In a pessimistic transaction, locks are acquired eagerly—when you read a row with FOR UPDATE or write to it. Other transactions that attempt to lock the same row will block (or timeout) until the lock is released. This eliminates retries at the cost of reduced concurrency and the risk of deadlocks.
SELECT ... FOR UPDATE is not just a read—it is a declaration of intent to modify. The database acquires an exclusive lock on every row returned by the query. Other transactions can still read these rows (under MVCC snapshot isolation), but any attempt to lock or modify them will block. This makes it safe to read a value, make a decision based on it, and update it—all without the possibility of another transaction interleaving.
TiDB implements pessimistic locks by writing lock records to TiKV (the distributed storage layer). When a transaction executes SELECT ... FOR UPDATE, TiDB sends a PessimisticLock request to the TiKV region leader that owns the row. The lock is a special key in TiKV’s lock column family, separate from the data. If the row is already locked by another transaction, the request blocks with a configurable timeout (default: 50 seconds via innodb_lock_wait_timeout).
TiDB locks at the row level, matching MySQL/InnoDB behavior. A SELECT ... FOR UPDATE WHERE id = 5 locks only the row with id = 5. A range query like SELECT ... FOR UPDATE WHERE id BETWEEN 1 AND 100 locks all rows in the range that satisfy the condition. TiDB does not implement gap locking (unlike InnoDB), which means phantom reads are handled differently—through TiDB’s MVCC snapshot isolation rather than next-key locks.
Pessimistic locks are held for the entire duration of the transaction. They are acquired on SELECT FOR UPDATE or UPDATE/DELETE statements and released only when the transaction commits or rolls back. Long-running transactions hold locks for a long time, blocking other transactions. This is why it is critical to keep pessimistic transactions as short as possible—do the locking read, the invariant check, and the update in quick succession, then commit immediately.
When two transactions each hold a lock the other needs, a deadlock occurs. TiDB detects deadlocks through a centralized deadlock detector in the PD (Placement Driver) component. When a lock request blocks, TiKV reports the wait relationship to PD. PD maintains a wait-for graph and aborts one of the transactions in the cycle (typically the one with the smaller transaction ID or less work done). The aborted transaction receives a deadlock error and must retry.
The simplest way to prevent deadlocks is to acquire locks in a consistent global order. For example, if you need to lock both a buyer row and a seller row, always lock the row with the smaller ID first. This ensures that no two transactions can form a cycle in the wait-for graph. When consistent ordering is impractical, implement retry logic with exponential backoff for deadlock errors.
A typical marketplace transaction in a marketplace application follows this flow:
util.TiDBSqlBegin(db) which issues BEGIN PESSIMISTIC.SELECT coins FROM users WHERE id = ? FOR UPDATE — acquires an exclusive lock on the buyer’s row and reads their current coin balance.SELECT goods FROM users WHERE id = ? FOR UPDATE — acquires an exclusive lock on the seller’s row and reads their current goods count.Under the hood, a pessimistic transaction in TiDB proceeds through these phases:
FOR UPDATE read or write sends a PessimisticLock RPC to TiKV. The lock record includes the transaction’s start timestamp and a for-update timestamp.// util.TiDBSqlBegin starts a pessimistic transaction in TiDB.
// This sets the transaction mode before the first statement.
func TiDBSqlBegin(db *sql.DB) (*sql.Tx, error) {
tx, err := db.Begin()
if err != nil {
return nil, err
}
// TiDB-specific: set pessimistic mode for this transaction
_, err = tx.Exec("SET @@tidb_txn_mode = 'pessimistic'")
if err != nil {
tx.Rollback()
return nil, err
}
return tx, nil
}package marketplace
import (
"database/sql"
"fmt"
"example.com/marketplace/util"
)
// ExecuteTrade atomically transfers goods from seller to buyer.
func ExecuteTrade(db *sql.DB, buyerID, sellerID int64, price, quantity int) error {
tx, err := util.TiDBSqlBegin(db)
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
defer tx.Rollback() // no-op if committed
// Lock buyer and seller rows in consistent ID order to avoid deadlocks
firstID, secondID := buyerID, sellerID
if sellerID < buyerID {
firstID, secondID = sellerID, buyerID
}
var firstCoins, firstGoods int
err = tx.QueryRow(
"SELECT coins, goods FROM users WHERE id = ? FOR UPDATE", firstID,
).Scan(&firstCoins, &firstGoods)
if err != nil {
return fmt.Errorf("lock first user %d: %w", firstID, err)
}
var secondCoins, secondGoods int
err = tx.QueryRow(
"SELECT coins, goods FROM users WHERE id = ? FOR UPDATE", secondID,
).Scan(&secondCoins, &secondGoods)
if err != nil {
return fmt.Errorf("lock second user %d: %w", secondID, err)
}
// Resolve which scan result is buyer vs seller
buyerCoins, sellerGoods := firstCoins, firstGoods
if firstID == sellerID {
buyerCoins, sellerGoods = secondCoins, secondGoods
}
// Enforce invariants
totalCost := price * quantity
if buyerCoins < totalCost {
return fmt.Errorf("buyer %d has %d coins, needs %d", buyerID, buyerCoins, totalCost)
}
if sellerGoods < quantity {
return fmt.Errorf("seller %d has %d goods, needs %d", sellerID, sellerGoods, quantity)
}
// Update balances
_, err = tx.Exec(
"UPDATE users SET coins = coins - ?, goods = goods + ? WHERE id = ?",
totalCost, quantity, buyerID,
)
if err != nil {
return fmt.Errorf("update buyer: %w", err)
}
_, err = tx.Exec(
"UPDATE users SET coins = coins + ?, goods = goods - ? WHERE id = ?",
totalCost, quantity, sellerID,
)
if err != nil {
return fmt.Errorf("update seller: %w", err)
}
return tx.Commit()
}A marketplace application models a marketplace where users trade goods for coins. Every trade is a pessimistic transaction that locks both the buyer and seller rows, checks that the buyer has sufficient coins and the seller has sufficient goods, and then atomically transfers both. This is the classic “double-entry bookkeeping” pattern adapted for a distributed database.
E-commerce platforms use the same pattern for inventory reservation: lock the product row with FOR UPDATE, check available quantity, decrement stock, and create an order record—all within one transaction. Without pessimistic locking, two concurrent buyers could both see 1 item in stock and both succeed, creating an oversell.
Rate limiters that use a database counter (e.g., “user X has made N API calls this minute”) require SELECT FOR UPDATE to safely increment the counter. Reading the counter, checking the limit, and incrementing must be atomic. TiDB’s pessimistic mode makes this straightforward even across a distributed cluster.
When checking for duplicate usernames before inserting a new user, an optimistic transaction can fail if two concurrent registrations check at the same time and both find the username available. A pessimistic transaction with SELECT ... FOR UPDATE on the username index row serializes concurrent registrations, ensuring only one succeeds.
Even with consistent lock ordering, deadlocks can still occur in complex applications. Production code should wrap pessimistic transactions in a retry loop that handles deadlock errors:
package util
import (
"database/sql"
"errors"
"fmt"
"math/rand"
"strings"
"time"
)
const maxRetries = 3
// WithRetry executes fn in a pessimistic transaction, retrying on deadlock.
func WithRetry(db *sql.DB, fn func(tx *sql.Tx) error) error {
for attempt := 0; attempt <= maxRetries; attempt++ {
tx, err := TiDBSqlBegin(db)
if err != nil {
return fmt.Errorf("begin tx: %w", err)
}
err = fn(tx)
if err != nil {
tx.Rollback()
if isDeadlockError(err) && attempt < maxRetries {
// Jittered backoff before retry
jitter := time.Duration(rand.Intn(50)) * time.Millisecond
time.Sleep(time.Duration(attempt*100)*time.Millisecond + jitter)
continue
}
return err
}
if err := tx.Commit(); err != nil {
if isDeadlockError(err) && attempt < maxRetries {
continue
}
return fmt.Errorf("commit: %w", err)
}
return nil
}
return fmt.Errorf("exceeded max retries due to deadlocks")
}
func isDeadlockError(err error) bool {
return err != nil && strings.Contains(err.Error(), "Deadlock found")
}A common anti-pattern is opening a transaction, doing non-database work (HTTP calls, file I/O, complex computation), and then committing. This holds locks for the duration of the external work, blocking other transactions. Instead, do all preparatory work before opening the transaction, and keep the transaction body to: lock, check, update, commit.
// ANTI-PATTERN: holds locks while doing HTTP call
func badTrade(db *sql.DB, buyerID, sellerID int64) error {
tx, _ := util.TiDBSqlBegin(db)
defer tx.Rollback()
tx.QueryRow("SELECT ... FOR UPDATE", buyerID) // lock acquired
tx.QueryRow("SELECT ... FOR UPDATE", sellerID) // lock acquired
// BAD: HTTP call while holding locks
price, _ := fetchPriceFromExternalAPI()
tx.Exec("UPDATE ...", price, buyerID)
tx.Exec("UPDATE ...", price, sellerID)
return tx.Commit()
}
// GOOD: fetch external data first, then do a tight transaction
func goodTrade(db *sql.DB, buyerID, sellerID int64) error {
price, err := fetchPriceFromExternalAPI() // no locks held
if err != nil {
return err
}
tx, _ := util.TiDBSqlBegin(db)
defer tx.Rollback()
// Tight transaction: lock, check, update, commit
tx.QueryRow("SELECT ... FOR UPDATE", buyerID)
tx.QueryRow("SELECT ... FOR UPDATE", sellerID)
tx.Exec("UPDATE ...", price, buyerID)
tx.Exec("UPDATE ...", price, sellerID)
return tx.Commit()
}If you read a balance with a plain SELECT (no FOR UPDATE) and then update based on the value, another transaction can modify the row between your read and write. In pessimistic mode, the write will still succeed (no conflict detection on reads without FOR UPDATE), leading to lost updates. Every read that informs a subsequent write must use FOR UPDATE.
When many transactions compete for the same rows, lock wait queues grow long. Transactions that exceed the lock wait timeout (default 50s in TiDB) are aborted with an error. Symptoms include spikes in “lock wait timeout exceeded” errors during peak traffic. Solutions: reduce transaction duration, shard hot rows, or use optimistic mode for read-heavy workloads where contention is rare.
If transaction A locks row 1 then row 2, while transaction B locks row 2 then row 1, both transactions will deadlock. The fix is to always lock rows in a deterministic order (e.g., ascending by primary key). This is especially important in batch operations where the order of rows may vary.
TiDB allows switching between optimistic and pessimistic modes per-transaction. If your application mixes modes, ensure that critical invariant-checking transactions always use pessimistic mode. A common bug is a code path that accidentally uses the default mode (which may be optimistic in older TiDB versions), silently degrading from “always correct” to “usually correct.”
If a transaction is started but never committed or rolled back (e.g., due to an early return or panic without a defer tx.Rollback()), the connection is returned to the pool with an open transaction and held locks. Subsequent uses of that connection may see unexpected lock state. Always use defer tx.Rollback() immediately after beginning the transaction.