MariaDB / Intermediate: concurrency and reusable SQL / Lesson 7

Transactions, snapshots and locking

Coordinate a two-account transfer and reason about concurrent sessions. Separate snapshot reads from locking reads, and design bounded retries.

About 4 min readContent reviewed 2026-10-23

On this page

Lesson goals

Define atomic transaction boundaries and isolation expectations.
Handle deadlocks and lock timeouts without partial business effects.

One business event, one transaction

A transfer is not two independent successful UPDATE statements. Debit and credit must commit together on transactional tables, with validation of account existence and available funds. Autocommit otherwise makes each write its own transaction. Keep external network calls outside the locked interval: a slow payment gateway should not leave account locks held indefinitely. If the application loses the connection during COMMIT, the outcome may be uncertain; an idempotency record helps resolve that ambiguity safely.

Lock in a predictable order

Assume training accounts 10 and 20 have balances 100 and 40. Lock them in increasing account order, verify both exist and check that account 10 can pay fifteen before applying the writes. The illustrative balance query then shows 85 and 55, preserving a total of 140. ROLLBACK returns the fixture to its original state. The comments are essential application decisions, not an assertion that SQL automatically rejects a missing destination account.

sql Illustrative SQL; expected results are reasoned, not measured.

START TRANSACTION;
SELECT account_id, balance FROM accounts
WHERE account_id = 10 FOR UPDATE;
SELECT account_id, balance FROM accounts
WHERE account_id = 20 FOR UPDATE;
-- Application verifies two rows and sufficient balance before continuing.
UPDATE accounts SET balance = balance - 15 WHERE account_id = 10;
UPDATE accounts SET balance = balance + 15 WHERE account_id = 20;
SELECT account_id, balance FROM accounts
WHERE account_id IN (10, 20) ORDER BY account_id;
ROLLBACK;

Expected output

Expected before rollback: (10, 85), (20, 55).

Both accounts are InnoDB rows; the example assumes successful precondition checks.

Snapshots do not reserve business rights

InnoDB commonly defaults to REPEATABLE READ. Ordinary consistent reads in that level use a transaction snapshot established by the first consistent read, while READ COMMITTED takes a fresh snapshot for each such statement. A snapshot can show an earlier committed balance without blocking another writer. SELECT ... FOR UPDATE requests a locking read inside a transaction and follows different visibility rules. Do not assume that repeating a plain SELECT reserves stock or makes a later decision safe.

Describe a schedule, not a fixed timing

This snippet is for session A in a controlled two-session lab. After A acquires the account lock, a competing UPDATE of account 10 in session B normally waits until A ends its transaction. An ordinary consistent SELECT in B can still read a committed version under suitable isolation. Range predicates may involve next-key or gap locking depending on isolation and access paths. Index-record locks and metadata locks have different scopes, so diagnose the actual wait rather than calling every delay a table lock.

sql Illustrative SQL; expected results are reasoned, not measured.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT account_id, balance FROM accounts
WHERE account_id = 10 FOR UPDATE;
-- A concurrent writer of account 10 waits here.
ROLLBACK;

SET TRANSACTION without SESSION applies to the next transaction. No global setting is changed.

Treat conflicts as expected outcomes

A deadlock occurs when transactions wait in a cycle; InnoDB selects a victim so another can proceed. Retry the entire business transaction with a bound and jitter after rolling back. A lock timeout can have different rollback scope from a deadlock depending on configuration, so explicitly end the failed transaction rather than continuing blindly. Many DDL statements cause implicit commits. A savepoint supports partial rollback of transactional work but does not make schema changes reversibly transactional.

Common errors and fixes

ERROR 1213 (40001): Deadlock found when trying to get lock

Why it happens: Concurrent transactions acquired conflicting locks in a cycle.
How to fix it: Rollback and retry the complete idempotent business operation within a limit.
Prevent it next time: Use consistent lock order, appropriate indexes and short transactions.

ERROR 1205: Lock wait timeout exceeded

Why it happens: A required lock remained held beyond the configured wait.
How to fix it: End the failed transaction, inspect its blocker and decide whether to retry.
Prevent it next time: Monitor transaction age and avoid user interaction inside transactions.

ROLLBACK does not undo ALTER TABLE

Why it happens: DDL has implicit-commit behavior outside ordinary DML rollback.
How to fix it: Use a reviewed schema migration and recovery strategy.
Prevent it next time: Keep schema changes separate from business transaction reasoning.

Check your understanding

1. A locks account 10 then 20; B locks 20 then 10. Explain the failure and redesign it.

Show worked solution

Each can hold the first lock while waiting for the other, creating a deadlock cycle. Make both transactions acquire the account keys in one consistent order, keep their work short and retain bounded full-transaction retries because other conflict patterns can still occur. Consistent order reduces deadlocks but is not a proof that every workload is deadlock-free.

2. Two ordinary REPEATABLE READ selects show the old balance while another session committed an update. Is replication necessarily broken?