LEARN. PRACTICE. GROW.Observe work surviving a timeout
B inserts a note in a transaction and attempts to update a row locked by A. With innodb_rollback_on_timeout=OFF, it receives 1205. The same session still reads the note, but the observing connection cannot see it. When B executes COMMIT, the note becomes visible to the observer even though the update failed. This is the risk of automatically committing in a finally block: the engine may have rolled back only the statement, leaving earlier work pending. The lab uses a fictional note to expose the boundary; an actual debit and credit would require preserving the business contract.
Compare explicit rollback and the server option
In another attempt with the option OFF, B responds to timeout with ROLLBACK and the earlier note is not committed. The second disposable server starts with the option ON: after the same error, the note is no longer visible even in its own session, and a later COMMIT does not recover it. This contrast was executed in separate instances without changing a production server. Record the effective value before deciding retry scope. Even when the engine rolls everything back, the application must decide whether to retry, with what limits and how to handle effects outside the SQL transaction.
Canceling a query does not end its connection
After confirming the wait relationship, the observer sends KILL QUERY only to B’s CONNECTION_ID created by the script. The query returns 1317; the connection keeps the same identifier and still reads its previously inserted note. The script executes ROLLBACK and confirms that the note was not persisted. Acceptance is not limited to the KILL command’s return, which signals interruption without representing complete functional recovery. In an actual pool, check state and reuse policy after the error. This experiment implements no pool and validates no permissions to intervene in other parties’ sessions.
Distinguish a duplicate from a deadlock
A transaction inserts id 4 and attempts to insert the same key again without IGNORE. The second statement fails with 1062; COMMIT persists the first note. In a different experiment, two transactions hold opposite rows and request each other’s lock. One receives 1213 and loses its entire transaction, including the note written before the cycle. The survivor commits both updates and its own note. Do not apply identical treatment to every error or assume which participant becomes the victim. Error code, configuration and actual rollback scope guide recovery.
Retry the correct unit and reduce cycles
After deadlock, retrying only the victim’s last UPDATE can omit work the engine already rolled back. A new attempt, where appropriate, must reconstruct the complete unit using current reads, limits and a consistent business identity. The script does not execute this application logic. It does execute an ordering contrast: both participants update row 1 and then row 2. One waits, the other commits, and both succeed in this interleaving. This avoids the specific cycle without proving universal deadlock freedom. Retain error handling after improving order, indexes and transaction duration.
Turn the experiment into acceptance criteria
The report records version 9.7.2, PyMySQL1.2.3, configuration, diagnostics and before/after state. Both temporary servers exit, six connections close and four query threads are joined. There is no TCP, real credentials, replica, crash, load or human workshop. To apply the pattern to the service, rehearse the actual driver and pool, retry budget, external effects and uncertain commit acknowledgment. Define who decides to resume the batch and how incomplete operations are reconciled. Editorial review and local tests are useful evidence, but they do not replace functional approval or independent review.
SELECT @@global.innodb_rollback_on_timeout;
SET SESSION innodb_lock_wait_timeout=1;
START TRANSACTION;
INSERT INTO notes VALUES(1,'earlier work');
-- Another owned connection holds the required row lock.
UPDATE balances SET units=80 WHERE id=1;
-- After error1205, the application decides recovery explicitly.
ROLLBACKAn INSERT preceding timeout1205 remains pending with rollback-on-timeout OFF and disappears with the option ON.
Common pitfalls
Assuming full rollback for every error; COMMIT in finally; retrying only the last statement after deadlock; treating KILL QUERY as pool cleanup.
Related topics: Concurrency and connection identity · Atomicity and operational acceptance
An error does not replace the atomicity contract. Confirm rollback scope, clean up the session and retry the appropriate unit.
Reference: InnoDB error handling · MySQL 9.7 LTS with InnoDB reference semantics