मैं MySQL InnoDB तालिकाओं का उपयोग कर रहा हूं और इंडेक्स रेंज स्कैन के मामले में कुछ पंक्ति-स्तरीय लॉकिंग के कारणों को समझने की कोशिश कर रहा हूं। मैंने पाया कि इस्तेमाल किए गए इंडेक्स की विशिष्टता के आधार पर एक अतिरिक्त इंडेक्स रिकॉर्ड (सीमा से बाहर) लॉक किया जा सकता है। नीचे दिया गया उदाहरण देखें (संस्करण 8.0.18 में सत्यापित)।

CREATE TABLE foo (
  a INT NOT NULL,
  b INT NOT NULL,
  c CHAR(1) NOT NULL,
  PRIMARY KEY (a),
  UNIQUE KEY (b)
) ENGINE=InnoDB;

INSERT INTO foo VALUES (1,1,'A'), (3,3,'B'), (5,5,'C'), (7,7,'D'), (9,9,'E');

टेस्ट केस 1

सत्र 1:

START TRANSACTION;
SELECT * FROM foo WHERE a < 2 FOR UPDATE;

सत्र 2:

DELETE FROM foo WHERE a = 3;  -- Success

टेस्ट केस 2

यह हटाए गए रिकॉर्ड के साथ तालिका की मूल पंक्तियों का उपयोग करता है।

सत्र 1:

START TRANSACTION;
SELECT * FROM foo WHERE b < 2 FOR UPDATE;

सत्र 2:

DELETE FROM foo WHERE b = 3;  -- Blocks

दूसरे टेस्ट केस में सेकेंडरी इंडेक्स रिकॉर्ड को b = 3 से लॉक करना अनावश्यक लगता है।

InnoDB सेकेंडरी इंडेक्स के मामले में स्कैन की गई रेंज के दाईं ओर अगली इंडेक्स एंट्री को ब्लॉक क्यों करता है? क्या इसका कोई व्यावहारिक कारण है? क्या कोई ऐसी समस्या का उदाहरण दे सकता है जो दूसरे परीक्षण मामले में b = 3 के साथ रिकॉर्ड अवरुद्ध नहीं होने पर हो सकती है?

0
Sergei Muraviev 31 जिंदा 2020, 19:36

2 जवाब

सबसे बढ़िया उत्तर

अंत में मुझे जवाब मिल गया। संक्षेप में, दूसरे परीक्षण मामले में इस तरह के अतिरिक्त लॉकिंग के कोई महत्वपूर्ण कारण नहीं हैं। जब द्वितीयक इंडेक्स रेंज का लॉकिंग रीड किया जाता है, तो पर्याप्त लॉक सेट किए जाते हैं, लेकिन आवश्यक न्यूनतम नहीं। इस प्रकार, अतिरिक्त ताले केवल इसलिए सेट किए जाते हैं क्योंकि कुछ InnoDB प्रोग्रामर के लिए कोड लिखना आसान था। यदि अधिकांश भाग के लिए सब कुछ काम करता है तो अतिरिक्त ताले की परवाह कौन करता है?

मैंने इस समस्या के बारे में एक बग रिपोर्ट पोस्ट की: https://bugs.mysql.com/bug .php?id=98639 दुर्भाग्य से, उनका कर्मचारी इस बग को पंजीकृत नहीं करना चाहता है। वह मेरे तर्कों को नहीं समझता है और गलत स्पष्टीकरण देता है। उन्होंने मेरे आखिरी तर्कों को निजी बना दिया और जवाब देना बंद कर दिया।

मैंने आधिकारिक फोरम में इस मुद्दे के बारे में भी पूछा और निम्नलिखित उत्तर प्राप्त किया: https: //forums.mysql.com/read.php?22,684356,684482 संक्षेप में, इस बग को ठीक करने के लिए महत्वपूर्ण प्रयासों की आवश्यकता है। लेकिन चूंकि यह एक बहुत छोटा और महत्वहीन बग है (अधिक सटीक रूप से, एक प्रदर्शन समस्या), वे इसे अभी तक ठीक नहीं करना चाहते हैं। हालांकि, संस्करण 8.0.18 में, उन्होंने क्लस्टर इंडेक्स के लिए एक समान समस्या तय की, और उन्हें एक महीने से अधिक समय लगा।

मुझे बहुत आश्चर्य है कि इस तरह के एक साधारण सिंगल-लेवल स्कैनिंग एल्गोरिदम को अनुकूलित करने में इतना समय लगता है और MySQL टीम के लिए इतना मुश्किल है।

2
Sergei Muraviev 6 पद 2020, 13:20

जैसा कि @Barmar ने पहले ही उल्लेख किया है, ऐसा इसलिए है क्योंकि MySQL GAP या नेक्स्ट-की लॉक सेट कर रहा है। मुझे लगता है कि आप डिफ़ॉल्ट innodb आइसोलेशन स्तर REPEATABLE READ का उपयोग कर रहे हैं

MySQL दस्तावेज़ कहता है:

लॉकिंग रीडिंग के लिए (अपडेट के लिए चुनें या शेयर मोड में लॉक करें), अपडेट करें और स्टेटमेंट हटाएं, लॉकिंग इस बात पर निर्भर करता है कि स्टेटमेंट एक अद्वितीय इंडेक्स का उपयोग करता है या नहीं एक अद्वितीय खोज स्थिति, या एक श्रेणी-प्रकार की खोज स्थिति।

  • एक अद्वितीय खोज स्थिति के साथ एक अद्वितीय अनुक्रमणिका के लिए, InnoDB केवल मिले अनुक्रमणिका रिकॉर्ड को लॉक करता है, न कि इससे पहले का अंतर।
  • अन्य खोज स्थितियों के लिए, InnoDB रेंज द्वारा कवर किए गए अंतराल में अन्य सत्रों द्वारा सम्मिलन को ब्लॉक करने के लिए गैप लॉक या नेक्स्ट-की लॉक का उपयोग करके स्कैन की गई इंडेक्स रेंज को लॉक करता है। गैप लॉक और नेक्स्ट-की लॉक के बारे में जानकारी के लिए, खंड 14.7.1, "इनो डीबी लॉकिंग" देखें।

देखें:https:// dev.mysql.com/doc/refman/5.6/hi/innodb-transaction-isolation-levels.html#isolevel_repeatable-read

फिलहाल मैं इस बारे में जानकारी ढूंढ रहा हूं कि गैप लॉक कैसे सेट किया जाता है, लेकिन मुझे यह जानकारी भी मिली है, और मुझे लगता है कि यह उपयोगी जानकारी है:

ध्यान रखें कि लॉक एक आंतरिक अनुक्रमणिका पर आधारित है क्योंकि आप अपने मानदंड में जिस स्तंभ का उपयोग कर रहे हैं वह विशिष्ट रूप से अनुक्रमित नहीं है।

गैप लॉक इंडेक्स रिकॉर्ड के बीच, या गैप पर लॉक पहले से पहले या आखिरी इंडेक्स के बाद रिकॉर्ड करें

InnoDB में गैप लॉक "विशुद्ध रूप से अवरोधक" हैं, जिसका अर्थ है कि उनका एकमात्र उद्देश्य अन्य लेनदेन को अंतराल में डालने से रोकना है। गैप लॉक सह-अस्तित्व में हो सकते हैं। एक लेन-देन द्वारा लिया गया गैप लॉक दूसरे लेन-देन को उसी गैप पर गैप लॉक लेने से नहीं रोकता है। साझा और अनन्य अंतराल ताले के बीच कोई अंतर नहीं है। वे एक दूसरे के साथ संघर्ष नहीं करते हैं, और वे समान कार्य करते हैं।

गैप लॉकिंग को स्पष्ट रूप से अक्षम किया जा सकता है। यह तब होता है जब आप लेन-देन अलगाव स्तर को कमिटेड पढ़ें या innodb_locks_unsafe_for_binlog सिस्टम चर सक्षम करते हैं (जिसे अब बहिष्कृत कर दिया गया है)। इन परिस्थितियों में, खोज और अनुक्रमणिका स्कैन के लिए गैप लॉकिंग अक्षम है और इसका उपयोग केवल विदेशी-कुंजी बाधा जाँच और डुप्लिकेट-कुंजी जाँच के लिए किया जाता है।

रीड कमिटेड आइसोलेशन स्तर का उपयोग करने या innodb_locks_unsafe_for_binlog को सक्षम करने के अन्य प्रभाव भी हैं। MySQL द्वारा WHERE स्थिति का मूल्यांकन करने के बाद गैर-मिलान पंक्तियों के लिए रिकॉर्ड लॉक जारी किए जाते हैं। अद्यतन विवरण के लिए, InnoDB एक "अर्ध-संगत" पढ़ता है, जैसे कि यह MySQL को नवीनतम प्रतिबद्ध संस्करण लौटाता है ताकि MySQL यह निर्धारित कर सके कि पंक्ति अद्यतन की WHERE स्थिति से मेल खाती है या नहीं।

देखें https://dev.mysql .com/doc/refman/5.6/hi/innodb-locking.html#innodb-gap-locks

0
Beach Chicken 1 फरवरी 2020, 15:16