MySQL में Locks और Deadlocks

InnoDB में Lock क्या है?

Transaction active रहने पर lock database resource का access coordinate करता है। InnoDB सामान्यतः abstract application row नहीं बल्कि index records और ranges lock करता है। Exact footprint statement, indexes, search condition और isolation level पर निर्भर है।

Locks correctness protect करती हैं, जबकि short transactions और good indexes concurrency preserve करते हैं।

Reproducible Lock Laboratory

DROP TABLE IF EXISTS lock_demo;
CREATE TABLE lock_demo (
  resource_id INT PRIMARY KEY,
  resource_name VARCHAR(40) NOT NULL,
  value_no INT NOT NULL
) ENGINE = InnoDB;

INSERT INTO lock_demo VALUES
(1, 'Resource A', 10),
(2, 'Resource B', 20),
(3, 'Resource C', 30);
COMMIT;

Session A और Session B खोलें। Statement wait करे तो उसे running छोड़कर other session में next labelled statement चलाएँ। Experiments के बाद values reset करें।

Shared, Exclusive और Range Lock Types

Unique index पर unique search में InnoDB केवल matching record lock कर सकता है। Range searches scanned ranges lock कर सकती हैं। Useful index न हो तो UPDATE या locking read expected से बहुत अधिक records scan और lock कर सकती है।

Two-Session Blocking Example

-- Session A
START TRANSACTION;
SELECT resource_id, value_no
FROM lock_demo
WHERE resource_id = 1
FOR UPDATE;
-- Resource 1 conflicting operations के लिए locked।

-- Session B
START TRANSACTION;
UPDATE lock_demo
SET value_no = value_no + 1
WHERE resource_id = 1;
-- Session A lock hold करे तब wait।

-- Session A
UPDATE lock_demo SET value_no = 15 WHERE resource_id = 1;
COMMIT;

-- Session B committed row से continue करती है।
COMMIT;

Session B में plain consistent SELECT snapshot version पढ़ सकती है; इसे conflicting update permission न समझें। Waiting के बजाय immediate error चाहिए तो FOR UPDATE NOWAIT लें।

SELECT * FROM lock_demo
WHERE resource_id = 1
FOR UPDATE NOWAIT;
-- Required row lock unavailable हो तो error 3572।

Two Sessions में Real Deadlock

Deadlock cycle है: each transaction one lock hold करती है और दूसरी transaction द्वारा held lock wait करती है।

-- Step 1, Session A
START TRANSACTION;
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 1;

-- Step 2, Session B
START TRANSACTION;
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 2;

-- Step 3, Session A: Session B का wait।
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 2;

-- Step 4, Session B: Session A की row request।
UPDATE lock_demo SET value_no = value_no + 1
WHERE resource_id = 1;

InnoDB cycle detect करके one victim transaction roll back करता है। Victim कौन होगा यह engine decision है; Session B always lose करेगी, ऐसा logic न बनाएँ। Victim rollback के बाद surviving wait proceed कर सकती है और उसे explicitly commit/rollback करें।

Deadlock Error 1213 सही Handle करें

attempt = 0
while attempt retry_limit से कम
    fresh transaction begin करें
    try
        rows deterministic order में acquire करें
        all validated statements execute करें
        commit करें
        success return करें
    catch deadlock error 1213
        victim transaction rolled back है
        small randomized backoff wait करें
        complete logical transaction retry करें
    catch any other error
        active हो तो rollback करें
        failure classify/report करें

• Entire transaction retry करें क्योंकि earlier statements rolled back हैं।
• Small maximum attempt count रखें; endless retry design problems hide करता है।
• Fresh transaction में data re-read करें; stale decisions reuse न करें।
• Externally visible operations idempotent रखें ताकि client retry duplicate न करे।
• Deadlock और lock wait timeout distinguish करें। Default InnoDB setting में timeout normally waiting statement rollback करता है, universally full transaction नहीं।

Locks, Waits और Deadlocks Diagnose करें

SHOW ENGINE INNODB STATUS\G

Involved transactions, statements और index records के लिए LATEST DETECTED DEADLOCK section inspect करें। Exact output diagnostic text है, stable application API नहीं।

SELECT ENGINE_TRANSACTION_ID, OBJECT_SCHEMA,
       OBJECT_NAME, INDEX_NAME, LOCK_TYPE,
       LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks;

SELECT *
FROM performance_schema.data_lock_waits;

• Single process ID नहीं, SQL pattern और transaction order capture करें।
• EXPLAIN से intended indexes confirm करें।
• Long transactions, idle sessions और broad range scans देखें।
• Frequent incidents diagnose करते समय innodb_print_all_deadlocks every deadlock log कर सकता है; finish होने पर extra logging disable करें।
• Current waits monitoring time-sensitive है क्योंकि commit/rollback पर locks disappear हो जाती हैं।

Deadlocks Minimize करें, Hide नहीं

1. Tables और rows हर जगह same deterministic order में access करें।
2. Transactions small रखें और related work के बाद promptly commit करें।
3. Locks hold करके user input या external network call wait न करें।
4. FOR UPDATE, UPDATE और DELETE predicates के appropriate indexes बनाएँ।
5. केवल business unit की rows lock करें।
6. Read-before-write unnecessary हो तो atomic conditional updates लें।
7. Visibility semantics fit हों तो READ COMMITTED evaluate करें ताकि unwanted range locking reduce हो।
8. Busy correct system पर occasional deadlocks expect करके bounded retry रखें।
9. Cyclic lock order fix करने की जगह lock-wait timeout बढ़ाना solution नहीं।

इस lesson के साथ concurrency control और indexes पढ़ें क्योंकि lock footprint access paths से tied है।

Official संदर्भ

• MySQL 8.4: InnoDB Locking
• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: Deadlocks in InnoDB

Lock compatibility, deadlock detection और retry guidance official MySQL 8.4 manual से check की गई है।