Practical MySQL & MariaDB

MySQL लॉकिंग समझाया गया: लॉक स्थिति जांचें, लॉक को सुरक्षित रूप से रिलीज़ करें, और डेडलॉक्स को रोकें (5.6–8.0)

सर्वर कॉन्फ़िगरेशन और प्रबंधन
目次

1. परिचय

MySQL एक व्यापक रूप से उपयोग किया जाने वाला डेटाबेस प्रबंधन प्रणाली है, लेकिन जब कई क्वेरी एक ही डेटा तक पहुँचने की कोशिश करती हैं, तो एक लॉकिंग तंत्र सक्रिय हो जाता है। लॉक डेटा की स्थिरता बनाए रखने के लिए आवश्यक हैं; हालांकि, अनुचित प्रबंधन से डेडलॉक और प्रदर्शन में गिरावट हो सकती है।

इस लेख में, हम MySQL में लॉकिंग के मूलभूत सिद्धांतों को समझाएंगे और लॉक स्थिति कैसे जांचें, लॉक कैसे रिलीज़ करें, और डेडलॉक कैसे रोकें पर विस्तृत मार्गदर्शन प्रदान करेंगे।

इस लेख में आप क्या सीखेंगे

  • MySQL लॉक के प्रकार और उनका प्रभाव
  • MySQL संस्करण के अनुसार लॉक कैसे जांचें
  • लॉक रिलीज़ करने की सुरक्षित प्रक्रियाएँ
  • डेडलॉक रोकने के लिए व्यावहारिक सलाह

आइए MySQL लॉकिंग के बुनियादी सिद्धांत को समझाकर शुरू करते हैं।

2. MySQL लॉकिंग के बुनियादी सिद्धांत

डेटाबेस में, “लॉक” एक ऐसा तंत्र है जो कई लेनदेन एक साथ डेटा संशोधित करने की कोशिश करने पर पहुँच को प्रतिबंधित करता है ताकि डेटा की अखंडता बनी रहे। यदि लॉक को सही ढंग से प्रबंधित नहीं किया गया, तो प्रदर्शन में गिरावट और डेडलॉक हो सकते हैं।

2.1 लॉक के मुख्य प्रकार

MySQL में, आवश्यक डेटा सुरक्षा स्तर के आधार पर कई प्रकार के लॉक होते हैं।

रो लॉक

  • केवल विशिष्ट पंक्तियों को लॉक करता है, जिससे अन्य लेनदेन पर प्रभाव कम हो जाता है।
  • केवल InnoDB स्टोरेज इंजन द्वारा समर्थित।
  • SELECT ... FOR UPDATE या SELECT ... LOCK IN SHARE MODE का उपयोग करने पर सक्रिय होता है।

टेबल लॉक

  • पूरी तालिका को लॉक करता है, जिससे कई क्वेरी एक साथ निष्पादित नहीं हो पातीं।
  • आमतौर पर MyISAM स्टोरेज इंजन के साथ उपयोग किया जाता है।
  • LOCK TABLES स्टेटमेंट का उपयोग करने पर सक्रिय होता है।

इंटेंशन लॉक

  • रो लॉक और टेबल लॉक को समन्वयित करने के लिए उपयोग किया जाने वाला लॉक, जिससे टकराव से बचा जा सके।
  • केवल InnoDB में उपयोग होता है और स्वचालित रूप से प्रबंधित होता है।

डेडलॉक

  • एक ऐसी स्थिति जहाँ कई लेनदेन एक‑दूसरे के लॉक का इंतजार कर रहे होते हैं।
  • यदि लेनदेन सही ढंग से डिज़ाइन नहीं किए गए, तो प्रक्रिया पूरी तरह रुक सकती है।

2.2 लॉक उत्पन्न होने के उदाहरण

आइए विशिष्ट SQL क्वेरीज़ को देखें ताकि समझ सकें कि लॉक कैसे उत्पन्न होते हैं।

रो लॉक उदाहरण

निम्नलिखित SQL को चलाने से एक विशिष्ट पंक्ति लॉक हो जाएगी।

BEGIN;
UPDATE products SET stock = stock - 1 WHERE product_id = 100;
-- Other sessions cannot update this row until this transaction is COMMIT or ROLLBACK

यदि कोई अन्य सत्र उसी पंक्ति को अपडेट करने की कोशिश करता है, तो वह लॉक प्रतीक्षा स्थिति (लॉक संघर्ष) में प्रवेश करेगा।

टेबल लॉक उदाहरण

पूरी तालिका को लॉक करने के लिए, निम्न कमांड का उपयोग करें।

LOCK TABLES products WRITE;
-- Other sessions cannot modify the products table until all read/write operations are complete

जब तक यह लॉक रिलीज़ नहीं होता, अन्य उपयोगकर्ता products तालिका में डेटा संशोधित नहीं कर सकते।

डेडलॉक उदाहरण

निम्नलिखित एक सामान्य डेडलॉक परिदृश्य दर्शाता है।

-- Session 1
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
-- Waiting for Session 2...

-- Session 2
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
-- Waiting for Session 1...

-- Session 1 (executes another statement)
UPDATE customers SET last_order = NOW() WHERE customer_id = 10; -- Deadlock occurs here

इस स्थिति में, प्रत्येक लेनदेन दूसरे के लॉक रिलीज़ होने की प्रतीक्षा कर रहा है, जिससे डेडलॉक उत्पन्न होता है।

3. MySQL लॉक स्थिति कैसे जांचें (संस्करण के अनुसार)

यह पता लगाने के लिए कि लॉक हो रहे हैं या नहीं, आपको अपने MySQL संस्करण के अनुसार उपयुक्त कमांड चलाने की आवश्यकता है।

3.1 MySQL 5.6 और उससे पहले के संस्करण में लॉक कैसे जांचें

MySQL 5.6 और उससे पहले, आप SHOW ENGINE INNODB STATUS\G; का उपयोग करके लॉक जानकारी जांच सकते हैं।

SHOW ENGINE INNODB STATUS\G;

इस कमांड को चलाने से वर्तमान में सक्रिय लॉक की विस्तृत जानकारी प्रदर्शित होती है।

3.2 MySQL 5.7 में लॉक कैसे जांचें

MySQL 5.7 और बाद के संस्करण में, सबसे आसान तरीका sys.innodb_lock_waits तालिका का उपयोग करना है।

SELECT * FROM sys.innodb_lock_waits;

By querying this table, you can identify which transactions are waiting for locks.

3.3 MySQL 8.0 और बाद में लॉक कैसे जांचें

MySQL 8.0 और बाद में, आप performance_schema.data_locks का उपयोग करके अधिक विस्तृत लॉक जानकारी प्राप्त कर सकते हैं।

SELECT * FROM performance_schema.data_locks;

लॉक रखने वाले सत्र की पहचान करने के लिए, निम्नलिखित SQL का उपयोग करें:

SELECT * FROM performance_schema.threads WHERE PROCESSLIST_ID = <process_id>;

यह आपको लॉक के लिए जिम्मेदार प्रक्रिया को सटीक रूप से पहचानने में मदद करता है।

4. MySQL लॉक कैसे रिलीज़ करें (जोखिमों की व्याख्या)

यदि MySQL में लॉक हो जाता है और उसे सही तरीके से संभाला नहीं जाता, तो प्रोसेसिंग रुक सकती है और डेटाबेस प्रदर्शन घट सकता है।
इस अनुभाग में, हम लॉक को रिलीज़ करने के तरीके और जुड़े जोखिमों की व्याख्या करेंगे।

4.1 लॉक रखने वाले सत्र की पहचान

लॉक रिलीज़ करने से पहले, आपको यह पहचानना चाहिए कि कौन सा सत्र इसे रख रहा है। लॉक प्रतीक्षा वाले सत्रों की जाँच के लिए निम्नलिखित SQL का उपयोग करें:

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE STATE='Waiting for table metadata lock';

यह क्वेरी उन सत्रों को सूचीबद्ध करती है जो लॉक की प्रतीक्षा कर रहे हैं।

MySQL 8.0 और बाद में, आप विस्तृत लॉक जानकारी प्राप्त करने के लिए उपयोग कर सकते हैं:

SELECT * FROM performance_schema.data_locks;

4.2 KILL कमांड का उपयोग करके लॉक रिलीज़ करना

एक बार जब आप लॉक रखने वाले सत्र की पहचान कर लेते हैं, तो आप प्रक्रिया को जबरन समाप्त करके इसे रिलीज़ कर सकते हैं।

1. लॉक रखने वाली प्रक्रिया की जाँच करें

SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM INFORMATION_SCHEMA.PROCESSLIST;

2. KILL कमांड का उपयोग करके सत्र को समाप्त करें

KILL <process_id>;

उदाहरण के लिए, ID=12345 वाले प्रक्रिया को समाप्त करने के लिए, निम्नलिखित चलाएँ:

KILL 12345;

⚠️ KILL कमांड के जोखिम

  • जबरन समाप्त किए गए लेनदेन रोल बैक हो जाते हैं
  • उदाहरण के लिए, एक बाधित UPDATE स्टेटमेंट द्वारा किए गए परिवर्तन निरस्त हो सकते हैं।
  • यह एप्लिकेशन त्रुटियों का कारण बन सकता है
  • यदि आपको अक्सर KILL का उपयोग करना पड़ता है, तो आपको अपने एप्लिकेशन डिज़ाइन की समीक्षा करनी चाहिए।

4.3 ROLLBACK के साथ लॉक रिलीज़ करना (सुरक्षित विधि)

KILL कमांड का उपयोग करने से पहले, यदि संभव हो, तो लॉक उत्पन्न करने वाले लेनदेन को मैन्युअल रूप से समाप्त करने का प्रयास करें।

1. पहले, वर्तमान लेनदेन की जाँच करें

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. यदि आप समस्या वाले लेनदेन को पाते हैं, तो ROLLBACK चलाएँ

ROLLBACK;

यह विधि डेटा संगतता बनाए रखते हुए लॉक को रिलीज़ करने की अनुमति देती है।

4.4 SET innodb_lock_wait_timeout के साथ लॉक हैंडलिंग का स्वचालन

मैन्युअल रूप से लॉक रिलीज़ करने के बजाय, आप एक लॉक वेट टाइमआउट कॉन्फ़िगर कर सकते हैं ताकि यदि लॉक निर्दिष्ट समय के भीतर रिलीज़ नहीं होता है तो लेनदेन स्वचालित रूप से टाइमआउट हो जाए।

SET innodb_lock_wait_timeout = 10;

इस सेटिंग के साथ, यदि लॉक 10 सेकंड के भीतर रिलीज़ नहीं होता है, तो MySQL एक त्रुटि लौटाता है और लेनदेन को स्वचालित रूप से समाप्त कर देता है।

5. MySQL लॉक के लिए महत्वपूर्ण नोट्स और सर्वोत्तम प्रथाएँ

उचित लॉक प्रबंधन डेडलॉक और प्रदर्शन गिरावट के जोखिम को कम करने में मदद करता है। नीचे लॉक को न्यूनतम करने और उन्हें प्रभावी ढंग से प्रबंधित करने के लिए सर्वोत्तम प्रथाएँ दी गई हैं।

5.1 डेडलॉक कैसे रोकें

डेडलॉक को रोकने के लिए, निम्न बिंदुओं को ध्यान में रखें:

1. लेनदेन निष्पादन क्रम को मानकीकृत करें

  • उदाहरण के लिए, कई तालिकाओं को अपडेट करते समय, हमेशा उन्हें एक ही क्रम में अपडेट करें ।
  • उदाहरण:
    -- OK: Always update in the order orders → customers
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

× NG: विभिन्न निष्पादन क्रम डेडलॉक का कारण बन सकते हैं

-- Session 1
BEGIN;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
COMMIT;

-- Session 2 (deadlock may occur if executed in reverse order)
BEGIN;
UPDATE orders SET status = 'shipped' WHERE order_id = 1;
UPDATE customers SET last_order = NOW() WHERE customer_id = 10;
COMMIT;

2. Keep Transactions Short

  • Commit or roll back as quickly as possible
  • Avoid long-running transactions, as they can negatively impact other processes.

3. Set Appropriate Indexes

  • Creating proper indexes helps avoid unnecessary locks .
  • Example: Adding an index on customer_id in the orders table ensures that only specific rows are locked .
    CREATE INDEX idx_customer_id ON orders (customer_id);

6. Summary

  • MySQL locks include row locks, table locks, and intention locks . Improper management can lead to deadlocks and performance issues.
  • The method for checking lock status varies depending on the MySQL version , so choose the appropriate approach for your environment.
  • Be cautious when releasing locks!
  • Try ROLLBACK before using the KILL command.
  • Use SET innodb_lock_wait_timeout to automatically handle lock timeouts.
  • To prevent deadlocks, standardize transaction execution order and keep transactions short .

7. FAQ (Frequently Asked Questions)

Q1. What is the easiest command to check MySQL lock status?

  • A1. In MySQL 8.0 and later, use SELECT * FROM performance_schema.data_locks; to easily check lock status.

Q2. What should I do if a deadlock occurs?

  • A2. First, run SHOW ENGINE INNODB STATUS\G; to identify the cause of the deadlock. Then review and standardize the transaction execution order to prevent recurrence.

Q3. Can using the KILL command corrupt data?

  • A3. When forcibly terminating a session, unfinished transactions are rolled back, which may affect data consistency. Use it with caution.

Q4. How can I prevent deadlocks?

  • A4. The following methods are effective:
  • Standardize transaction execution order
  • Keep transactions short
  • Set appropriate indexes

Q5. How can I reduce locks and improve MySQL performance?

  • A5.
  • Design proper indexes to reduce unnecessary locks
  • Keep transactions short to minimize lock duration
  • Avoid full table locks (LOCK TABLES)
  • Use read replicas to distribute read workloads