Concurrency Control in DBMS

What Is Concurrency Control?

Concurrency means several sessions execute transactions during overlapping time. That parallelism improves throughput, but two correct single-user operations can produce a wrong combined result when they read or modify the same data without coordination.

InnoDB combines transactions, multi-version concurrency control (MVCC), isolation levels, row and range locks, constraints and deadlock detection. Application design adds atomic statements, version checks, idempotency and retry logic.

Verified Inventory Lab

DROP TABLE IF EXISTS inventory;
CREATE TABLE inventory (
  product_id INT PRIMARY KEY,
  product_name VARCHAR(60) NOT NULL,
  stock INT NOT NULL,
  version_no INT NOT NULL DEFAULT 1,
  CONSTRAINT chk_stock CHECK (stock >= 0)
) ENGINE = InnoDB;

INSERT INTO inventory VALUES
(10, 'Notebook', 5, 1),
(20, 'Pen Pack', 2, 1);

Reset product 10 to stock 5 and version 1 before each independent test. Use two MySQL sessions to reproduce races; a one-session test cannot prove concurrency behavior.

Problems Caused by Uncontrolled Concurrency

Choose a technique for the actual invariant. Raising isolation without understanding the query may add contention but still fail to express the intended business rule.

MVCC, Consistent Reads and Isolation

InnoDB MVCC keeps older row versions through undo information so a plain consistent SELECT can read a snapshot. At READ COMMITTED each consistent read gets a fresh snapshot; at REPEATABLE READ consistent reads normally reuse the snapshot created by the first read.

MVCC improves read/write concurrency, but it does not remove write/write conflicts. Long transactions also retain old versions longer and can increase purge and reconstruction work.

Best First Tool: Atomic Conditional Update

If the business decision can be expressed in one DML statement, avoid the read-calculate-write race.

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 10
  AND stock > 0;

SELECT ROW_COUNT() AS sale_succeeded;

Two concurrent executions serialize their conflicting row updates and each subtracts from the current value. When stock reaches zero, the conditional UPDATE affects zero rows and the application reports “out of stock.”

UPDATE inventory
SET stock = stock - 1
WHERE product_id = 10 AND stock > 0;
-- Require ROW_COUNT() = 1 before recording the order.

For an order that also inserts payment and ledger rows, put the atomic stock update and all required writes in one transaction and roll back if any affected-row check fails.

Pessimistic Control with SELECT FOR UPDATE

Pessimistic locking is appropriate when conflict is likely or a multi-step decision must reserve the current row.

-- Session A
START TRANSACTION;
SELECT stock FROM inventory
WHERE product_id = 10
FOR UPDATE;
-- Returns 5 and locks the record.

UPDATE inventory SET stock = stock - 1
WHERE product_id = 10 AND stock > 0;
COMMIT;

-- Session B's conflicting FOR UPDATE or UPDATE waits,
-- then continues from the committed stock of 4.

• Start the transaction before the locking read; locking reads require autocommit disabled or an explicit transaction.
• Lock all required rows in a consistent order.
• Keep the locked section short and perform no user interaction inside it.
• Use NOWAIT when immediate failure is useful.
• Use SKIP LOCKED only for queue-like work because it returns an inconsistent view by design.

Optimistic Control with a Version Column

Optimistic control is useful when conflicts are uncommon and work may happen outside a short database transaction. Each reader keeps the version it saw.

-- Both clients initially read stock=5, version_no=1.

-- Client A
UPDATE inventory
SET stock = 4, version_no = version_no + 1
WHERE product_id = 10 AND version_no = 1;
-- affected rows=1; version is now 2.

-- Client B still submits version 1.
UPDATE inventory
SET stock = 4, version_no = version_no + 1
WHERE product_id = 10 AND version_no = 1;
-- affected rows=0: stale update rejected.

After a zero-row optimistic update, reload current data and ask the business workflow whether to recalculate, merge or reject. Do not silently overwrite the newer row.

Technique Selection and Production Checklist

1. Write the invariant in plain language before choosing a lock.
2. Use InnoDB, explicit transaction boundaries and database constraints.
3. Index predicates so locking statements scan and lock fewer records.
4. Check row counts and unique constraints; never assume an UPDATE succeeded.
5. Access rows in a consistent order and keep transactions short.
6. Handle deadlock error 1213 by retrying the complete transaction with a bounded policy.
7. Treat lock timeout separately because default rollback scope can differ.
8. Load-test concurrent sessions and monitor lock waits.

Continue to locks and deadlocks and MySQL indexes.

Official References

• MySQL 8.4: Consistent Nonlocking Reads
• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: InnoDB Error Handling

MVCC, locking-read and rollback behavior were checked against the official MySQL 8.4 manual.