LEARN. PRACTICE. GROW.Observe version and configuration
The experiment uses MySQL Community Server 9.7.2 for macOS, InnoDB and three owned connections per instance. The course reference remains the 9.7 LTS series; the 9.7.3 note concerns only the Docker image and does not rename this binary. Observed configuration includes REPEATABLE READ and innodb_rollback_on_timeout. Also record autocommit and explicit transaction starts, because a driver can have defaults different from the server. Do not infer application behavior only from the technology name. The script uses synthetic data, a private socket, local lab accounts and no permanent service.
Compare consistent and locking reads
Session A starts a transaction and reads units=100. B updates it to 90 and commits. A repeats plain SELECT and still sees 100; SELECT FOR UPDATE returns 90. Another plain SELECT in the same transaction again returns 100. The contrast shows why every statement should not be described as reading one shared snapshot. An update decision must use the transaction mechanism appropriate to its requirement and should not combine values from different contexts without analysis. During an incident, collect statement and commit order; two queries with different results do not alone demonstrate loss or corruption.
Test the contract in READ COMMITTED
After ending the previous transaction, A switches to READ COMMITTED. Its first read returns 90; B commits 80; A’s next read returns 80 while still in the same transaction. This result separates snapshot timing from the reader’s commit boundary. READ COMMITTED does not mean absence of transactions and does not automatically protect a read-modify-write decision. Before changing isolation to address a symptom, identify repeated reads, invariants and possible concurrent changes. The lab compares one row in a controlled sequence; it neither measures throughput nor demonstrates that this change suits an entire funds service.
Relate waiting transactions and connections
When B attempts to update the row held by A, observation queries performance_schema.data_lock_waits and joins thread IDs to performance_schema.threads. This reaches PROCESSLIST_ID values obtained from owned connections. The relationship is observed while the query is actually waiting, before any intervention. Do not use a lock identifier as the connection identifier accepted by KILL. In an actual environment, correlate the relationship with functional owner, transaction and impact before acting. The lab uses root only inside the disposable instance and does not demonstrate observation privileges of a production account.
Choose waiting, NOWAIT or SKIP LOCKED
With row 1 locked by A, NOWAIT returns error 3572. SKIP LOCKED, in a query over both rows, returns only id 2. The modes express different contracts: reject contention or continue with the available subset. Neither proves that row 1 ceased to exist. These modifiers concern row locks; they do not remove every possibility of waiting on other resources. For a queue, omission can be deliberate. For reconciliation or position totals, temporarily omitting rows can produce an incorrect conclusion. Define expected behavior before choosing syntax.
Separate selection from task execution
After rolling back the selection transactions, a new SKIP LOCKED query returns ids 1 and 2. The experiment shows eligibility returning when locks are released, but it implements no worker and does not confirm exactly-once delivery. An actual queue needs task state, a claim transaction, ownership duration and external-effect recovery. If a worker stops responding after sending an operation, disappearance of its lock does not explain whether the effect occurred. Use stable identity and appropriate reconciliation. Keep these requirements separate from the small concurrent-selection demonstration performed in this lab.
SELECT VERSION, @@session.transaction_isolation;
START TRANSACTION;
SELECT units FROM balances WHERE id=1;
-- The other owned session updates and commits before the next statements.
SELECT units FROM balances WHERE id=1;
SELECT units FROM balances WHERE id=1 FOR UPDATE;
ROLLBACKIn one REPEATABLE READ transaction, plain SELECT retains 100 while FOR UPDATE reads 90 after another session commits.
Common pitfalls
Treating every read as the same snapshot; using SKIP LOCKED as a complete inventory; confusing thread ID with connection ID.
Related topics: Concurrency and connection identity · Atomicity and operational acceptance
Diagnosis should identify read mode, transaction boundary and the concrete relationship between waiter and blocker.
Reference: Consistent nonlocking reads · MySQL 9.7 LTS with InnoDB reference semantics