Schema and performance
Use InnoDB transactions
Keep related changes atomic, release locks quickly, and handle deadlocks and lock-wait timeouts with bounded transaction retries.
8 minute lesson
InnoDB is the normal storage engine for application tables. It supports transactions, row-level locking, crash recovery, and foreign keys.
Use START TRANSACTION, COMMIT, and ROLLBACK. Keep the transaction focused and do not wait for an external API while it holds locks and a connection.
Concurrent transactions can deadlock or reach a lock-wait timeout. Roll back the whole transaction, then retry it a small number of times with a short delay. Retry only transient lock errors, not validation or syntax errors.
Access rows in a consistent order when possible. That reduces deadlocks, but your application must still handle them.
Lesson completed