Mastering MySQL Locks: Shared, Exclusive, and Pessimistic Locking for High‑Concurrency

MySQL provides two primary lock types: exclusive locks (triggered by SELECT ... FOR UPDATE) and shared locks (triggered by SELECT ... LOCK IN SHARE MODE). In InnoDB, row‑level locks are only applied when the query uses an indexed condition; otherwise, the engine falls back to a table lock.

Shared vs. Exclusive Locks

Shared lock : multiple sessions can read the same row simultaneously, but none can modify it until all shared locks are released. Syntax: SELECT * FROM table LOCK IN SHARE MODE Exclusive lock : a single session can read and write the locked row, while all other sessions are blocked from any operation on that row. Syntax:

SELECT * FROM table FOR UPDATE

Note: An exclusive lock does not merely prevent other sessions from reading the row; it also prevents them from acquiring any other lock on that row.

Pessimistic Locking for Preventing Oversell

When a query does not use an indexed condition, MySQL may lock the entire table, causing severe contention. In high‑read scenarios, optimistic locking (using a version or timestamp column) is often preferred, but for critical sections like inventory deduction, pessimistic locking is recommended.

Example implementation (pseudo‑code):

// Program A
SELECT * FROM good WHERE id=10 FOR UPDATE; // acquire exclusive lock
-- update inventory, commit transaction

Other programs attempting the same query will wait until Program A releases the lock.

Optimistic Lock Example

Using a version column:

UPDATE good SET num=num-1 WHERE id=10 AND version=23;

If the version has changed, the update fails, indicating a concurrent modification.

Practical Scenario: Preventing Product Oversell

Table good stores product information (id, status, total, sell, price). Table order records orders. The naive flow—checking stock, then updating—fails under high concurrency, leading to negative inventory.

Using pessimistic locking:

START TRANSACTION;
SELECT * FROM good WHERE id=? AND status=1 FOR UPDATE;
-- verify stock, update good, insert order, COMMIT;

Key points:

Disable autocommit; manage transactions manually.

In ThinkORM, use Db::startTrans(), Db::commit(), and Db::rollback().

Concurrency Testing with Apache JMeter

JMeter, an open‑source Java tool, can simulate thousands of concurrent requests. The test creates 1,000 virtual users hitting the purchase endpoint within one second.

Results:

Without locking: 100 items, 1,000 orders → 944 oversold items, only 44 left.

With locking: No oversell; inventory remains consistent.

Handling Lock Wait Timeouts

If a transaction waits too long, MySQL returns ERROR 1205 (HY000): Lock wait timeout exceeded. The timeout is controlled by innodb_lock_wait_timeout (default 50 s). It can be increased, e.g., to 120 s, by editing mysqld.cnf and restarting MySQL.

Diagnosing Lock Contention

Use SHOW PROCESSLIST to view active sessions and identify those stuck in FOR UPDATE. Check INNODB_TRX for active transactions. If a session is sleeping while holding a lock, manually kill it with KILL <em>id</em>.

Overall, applying proper row‑level locking and monitoring tools ensures data integrity during high‑traffic operations.