LEARN. PRACTICE. GROW.Concept and mechanism
InnoDB defaults to REPEATABLE READ. Plain consistent reads reuse the snapshot established by the first consistent read in the transaction; locking reads and DML should not be treated as having exactly the same view. SELECT FOR UPDATE protects a decision only when reading and modification have the appropriate transaction scope. Short transactions and consistent access order reduce contention. SKIP LOCKED can help queue workers but omits locked rows and does not provide a complete report. The decision includes task resumption, external effects, and waiting limits.
Guided application
Error handling forms part of business atomicity. A deadlock rolls back the entire transaction and requires a complete new attempt where appropriate. A lock wait timeout rolls back only the statement by default; innodb_rollback_on_timeout can change that behavior. In a fictional example, debit completes and credit fails: automatic COMMIT in finally can commit only the debit. Choose rollback or coherent resumption and check state before returning the connection to the pool. Do not use a new business identifier to blindly repeat an operation whose commit became uncertain. Record attempts, outcome, and decision rationale.
Pending debit + credit timeout + COMMIT can produce an incomplete operation despite technical commit success.
Common pitfalls
Every error as full rollback; autocommit as a lasting lock; SKIP LOCKED as a complete read.
Related topics: Types and data contracts · Deterministic queries and plans · Safe integration and effective identity
Test failure paths with the same attention given to success paths.
Reference: Statement versus transaction rollback · MySQL 9.7 LTS with InnoDB reference semantics