Chapter 19 · Application Integration, Connectors, Pools, ORMs, and Reliability Patterns
Transaction Boundaries in Application Code, Retries, Idempotency, and Lost Connections
Own transaction boundaries in service code, guarantee rollback and pool return on failures, classify retryable database errors, and use idempotency or reconciliation for ambiguous commit outcomes.
Learning outcomes
ServiceHub creates a work order and an initial note in two SQL statements. A deadlock or dropped connection between them can leave the application uncertain about what happened. “Retry the function” is not a correctness policy. The application must define who owns the transaction, which failures are safely retryable, and how duplicate business requests are detected.
Make service-level transaction ownership explicit and guarantee rollback plus connection return on exceptions.
Differentiate deadlocks, lock wait timeouts, pre-commit disconnects, and ambiguous post-COMMIT connection loss.
Reproduce a small lock conflict with two sessions and observe current InnoDB/Performance Schema evidence.
Design an idempotency key so repeating a request can return the original result instead of creating duplicate work.
Use bounded retry/reconciliation rules instead of blindly replaying every database exception.
Connector/Python autocommit is disabled by default. That makes explicit transactions natural, but it also means forgetting commit() can hold locks/state longer than expected. Never return a connection to the pool with an application transaction still logically owned by a request.
One service operation owns one transaction boundary
The repository layer should not independently commit fragments of a business operation. The service function below owns the boundary: acquire a connection, initialize state, execute all required statements, commit once, roll back on failure, and always return/close resources.
import mysql.connectorfrom mysql.connector import errorcodeRETRYABLE_TX_ERRORS = {1213, 1205} # deadlock, lock wait timeout; policy differsdef create_work_order(pool, request_key, customer_id, summary): cnx = pool.get_connection() cur = cnx.cursor(dictionary=True) try: cnx.rollback() cur.execute("SET SESSION time_zone = '+00:00'") # Fast idempotency check inside the same logical operation. cur.execute( "SELECT work_order_id FROM work_orders WHERE idempotency_key=%s", (request_key,), ) existing = cur.fetchone() if existing: cnx.rollback() return existing["work_order_id"] cur.execute(""" INSERT INTO work_orders(customer_id,idempotency_key,summary,status,priority) VALUES (%s,%s,%s,'open',3) """, (customer_id, request_key, summary)) work_order_id = cur.lastrowid cur.execute(""" INSERT INTO work_order_notes(work_order_id,note_text) VALUES (%s,%s) """, (work_order_id, "Created by ServiceHub API")) cnx.commit() return work_order_id except mysql.connector.Error: cnx.rollback() raise finally: cur.close() cnx.close()The unique constraint on idempotency_key is the final concurrency guard. Two simultaneous requests can both miss the preliminary SELECT; only one can insert that key. The loser must interpret duplicate-key evidence and fetch/reconcile the winner rather than inventing another order.
Deadlocks and lock waits: retry the transaction, not a random statement
InnoDB detects deadlocks and rolls back a victim transaction. A lock wait timeout is different: its rollback scope depends on operation/configuration and application policy should conservatively restore a known transaction boundary. For either case, retrying only the failed UPDATE can violate assumptions made by earlier statements in the same transaction.
-- Session ASTART TRANSACTION;SELECT work_order_id, statusFROM servicehub_app_lab.work_ordersWHERE work_order_id=1 FOR UPDATE;UPDATE servicehub_app_lab.work_ordersSET priority=1 WHERE work_order_id=1;-- Keep Session A open briefly.-- Session B (separate connection)SET SESSION innodb_lock_wait_timeout=3;START TRANSACTION;UPDATE servicehub_app_lab.work_ordersSET status='assigned' WHERE work_order_id=1;-- Expected after timeout if A still holds the row: lock wait timeout.ROLLBACK;-- Session A cleanupROLLBACK;This experiment is disposable and bounded. Do not lower global lock timeouts or hold production locks simply to make monitoring examples visible.
Observe the wait rather than guessing
SELECT waiting_pid, blocking_pid, locked_table, locked_index, wait_age, waiting_query, blocking_queryFROM sys.innodb_lock_waits;SELECT THREAD_ID, EVENT_ID, STATE, TRX_ID, ACCESS_MODE, ISOLATION_LEVEL, AUTOCOMMITFROM performance_schema.events_transactions_currentWHERE STATE='ACTIVE';SHOW ENGINE INNODB STATUS\GThe sys view correlates waiters/blockers when instrumentation is available. SHOW ENGINE INNODB STATUS can provide recent deadlock/transaction diagnostics. Evidence proves a particular blocking relationship at that moment; it does not establish that increasing timeouts is the right fix.
Bounded retry policy with jitter and whole-operation restart
import random, time, mysql.connectorTRANSIENT_TX = {1205, 1213}def run_with_tx_retry(fn, max_attempts=3): for attempt in range(1, max_attempts + 1): try: return fn() except mysql.connector.Error as exc: if exc.errno not in TRANSIENT_TX or attempt == max_attempts: raise # The function being retried must own/restart the whole transaction. time.sleep(random.uniform(0.05, 0.20) * attempt)A retry budget is an overload control, not a magic reliability setting. If the database is saturated, aggressive synchronized retries amplify the incident. Monitor retry rate and exhausted retries as symptoms.
Lost connection and ambiguous commit: the hardest case
If the connection drops before the server receives the transaction, retry may be straightforward. If it drops after COMMIT reaches the server but before the client receives success, the application cannot infer whether the commit happened merely from the socket error. This is an ambiguous outcome. Blindly repeating a non-idempotent INSERT can create duplicates.
def reconcile_create(pool, request_key): cnx = pool.get_connection() cur = cnx.cursor(dictionary=True) try: cur.execute( "SELECT work_order_id, status, created_at " "FROM work_orders WHERE idempotency_key=%s", (request_key,), ) return cur.fetchone() # row means the logical create exists finally: cur.close(); cnx.close()# After a disconnect around COMMIT:# 1) reconnect# 2) query by the durable idempotency/business key# 3) return existing outcome or retry only if policy proves it absentFor payment-like or externally visible side effects, reconciliation may need a durable outbox/state machine rather than one table lookup. The principle is the same: uncertainty is resolved from durable business identity, not by assuming failure.
Deliberately wrong approach: reconnect and replay every exception
A catch-all retry loop mixes syntax errors, permission failures, deadlocks, lock timeouts, duplicate keys, data validation errors, and unknown commit outcomes into one behavior. Some failures will never succeed; others can duplicate work. Repair the design by classifying errors, bounding retries, restarting complete transactions, and reconciling ambiguous outcomes.
SELECT idempotency_key, COUNT(*) AS copies, MIN(work_order_id) AS work_order_idFROM servicehub_app_lab.work_ordersGROUP BY idempotency_keyHAVING COUNT(*) > 1;-- Expected: zero rows because the UNIQUE key enforces the invariant.SHOW CREATE TABLE servicehub_app_lab.work_orders\GProduction judgment and bridge to Lesson 4
Track deadlock/timeout rates, transaction duration, pool hold time, rollback rate, retry attempts, duplicate-key reconciliation, and disconnects around writes. Do not hide these signals behind an ORM. The next lesson uses an ORM deliberately—but first makes every generated query visible so abstraction cannot conceal N+1 amplification or bad pagination.
Knowledge check
- Why should a service layer own the transaction rather than each repository method committing independently?
- What should be retried after a deadlock?
- Why is a connection loss around COMMIT special?
- How does a unique idempotency key help?
- Why can retries worsen an outage?
Reveal answers
- The business operation may span several statements that must succeed or fail together; fragment commits destroy atomicity.
- The complete transaction/business operation from a known boundary, subject to a bounded retry policy.
- The server may have committed even though the client never received success, so the outcome is ambiguous.
- It gives repeated logical requests one durable identity and prevents concurrent duplicate creates.
- They add load and contention when the underlying database is already saturated or unavailable, creating a retry storm.