DBMS में Concurrency Control
Concurrency Control क्या है?
Concurrency का अर्थ several sessions overlapping time में transactions execute करें। Parallelism throughput improve करती है, लेकिन coordination न हो तो two correct single-user operations same data read/modify करके wrong combined result बना सकती हैं।
InnoDB transactions, MVCC, isolation levels, row/range locks, constraints और deadlock detection combine करता है। Application design atomic statements, version checks, idempotency और retry logic add करती है।
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);Every independent test से पहले product 10 stock 5 और version 1 reset करें। Races reproduce करने के लिए two MySQL sessions लें; one-session test concurrency behavior prove नहीं कर सकता।
Uncontrolled Concurrency की Problems
| Problem | Sequence | Wrong result |
|---|---|---|
| Lost update | A reads 5; B reads 5; both write 4 | Two sales लेकिन stock केवल once कम |
| Dirty read | B, A का uncommitted value पढ़े; A rollback | B ने unreal data पर action लिया |
| Nonrepeatable read | B row twice पढ़े, बीच में A commit | Same row B के unit में बदल गई |
| Phantom | A, B के predicate matching row insert करे | B repeated result set में new row |
| Write skew या rule race | Transactions related rows separately validate | Combined business rule false |
| Deadlock | A और B each-other-needed locks hold | One transaction rollback करनी पड़ती है |
Actual invariant के लिए technique चुनें। Query समझे बिना isolation raise करना contention बढ़ा सकता है और intended business rule फिर भी express नहीं करेगा।
MVCC, Consistent Reads और Isolation
InnoDB MVCC undo information से older row versions रखता है ताकि plain consistent SELECT snapshot पढ़ सके। READ COMMITTED में each consistent read fresh snapshot लेती है; REPEATABLE READ में consistent reads सामान्यतः first read से created snapshot reuse करती हैं।
| Operation | Typical concurrency behavior |
|---|---|
| Plain SELECT at READ COMMITTED/REPEATABLE READ | MVCC snapshot से consistent nonlocking read |
| SELECT FOR SHARE | Shared locking read; conflicting modifications wait |
| SELECT FOR UPDATE | Change होने वाले records के लिए exclusive-style locking read |
| UPDATE या DELETE | Plan और isolation के अनुसार affected/scanned index records lock |
MVCC read/write concurrency improve करता है, लेकिन write/write conflicts remove नहीं करता। Long transactions old versions longer retain करके purge और reconstruction work बढ़ा सकती हैं।
Best First Tool: Atomic Conditional Update
Business decision one DML statement में express हो सके तो read-calculate-write race avoid करें।
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 10
AND stock > 0;
SELECT ROW_COUNT() AS sale_succeeded;Two concurrent executions conflicting row updates serialize करती हैं और each current value से subtract करती है। Stock zero पर conditional UPDATE zero rows affect करती है और application “out of stock” report करती है।
UPDATE inventory
SET stock = stock - 1
WHERE product_id = 10 AND stock > 0;
-- Order record करने से पहले ROW_COUNT() = 1 require करें।Order payment और ledger rows भी insert करे तो atomic stock update तथा all required writes one transaction में रखें और any affected-row check fail होने पर rollback करें।
SELECT FOR UPDATE से Pessimistic Control
Conflict likely हो या multi-step decision current row reserve करे तो pessimistic locking appropriate है।
-- Session A
START TRANSACTION;
SELECT stock FROM inventory
WHERE product_id = 10
FOR UPDATE;
-- 5 return और record lock।
UPDATE inventory SET stock = stock - 1
WHERE product_id = 10 AND stock > 0;
COMMIT;
-- Session B का conflicting FOR UPDATE या UPDATE wait,
-- फिर committed stock 4 से continue।- Locking read से पहले transaction start करें; उसे autocommit disabled या explicit transaction चाहिए।
- All required rows consistent order में lock करें।
- Locked section short रखें; इसके अंदर user interaction न करें।
- Immediate failure useful हो तो NOWAIT लें।
- SKIP LOCKED केवल queue-like work के लिए क्योंकि यह intentionally inconsistent view देता है।
Version Column से Optimistic Control
Conflicts uncommon और work short database transaction से बाहर हो तो optimistic control useful है। हर reader seen version रखता है।
-- Both clients initially 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 अब 2।
-- Client B अभी version 1 submit करता है।
UPDATE inventory
SET stock = 4, version_no = version_no + 1
WHERE product_id = 10 AND version_no = 1;
-- affected rows=0: stale update rejected।Zero-row optimistic update के बाद current data reload करें और workflow से recalculate, merge या reject का decision लें। Newer row silently overwrite न करें।
Technique Selection और Production Checklist
| Need | Preferred starting technique |
|---|---|
| Single counter/stock decrement | Atomic conditional UPDATE |
| Likely conflict वाला short multi-step decision | One transaction में SELECT FOR UPDATE |
| Rare conflict वाला long editing form | Optimistic version check |
| Read-only snapshot report | Suitable isolation पर consistent read |
| Parallel job queue | Indexed status + FOR UPDATE SKIP LOCKED |
- Lock चुनने से पहले invariant plain language में लिखें।
- InnoDB, explicit boundaries और database constraints लें।
- Predicates index करें ताकि locking statements fewer records scan/lock करें।
- Row counts और unique constraints check करें; UPDATE success assume न करें।
- Rows consistent order में access और transactions short रखें।
- Deadlock error 1213 पर complete transaction bounded policy से retry करें।
- Lock timeout separately handle करें क्योंकि default rollback scope different हो सकती है।
- Concurrent sessions load-test और lock waits monitor करें।
अब locks तथा deadlocks और MySQL indexes पढ़ें।
Official संदर्भ
- MySQL 8.4: Consistent Nonlocking Reads
- MySQL 8.4: InnoDB Locking Reads
- MySQL 8.4: InnoDB Error Handling
MVCC, locking-read और rollback behavior official MySQL 8.4 manual से check किया गया है।