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;

এই টেবিলটি কুয়েরি করে, আপনি কোন লেনদেনগুলি লক অপেক্ষা করছে তা শনাক্ত করতে পারেন।

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 কমান্ড ব্যবহার করে লক মুক্ত করা

একবার আপনি লক ধারণকারী সেশনটি শনাক্ত করলে, আপনি প্রক্রিয়াটি জোরপূর্বক বন্ধ করে লকটি মুক্ত করতে পারেন।

১. লকটি ধারণকারী প্রক্রিয়া পরীক্ষা করুন

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

২. KILL কমান্ড ব্যবহার করে সেশনটি বন্ধ করুন

KILL <process_id>;

উদাহরণস্বরূপ, ID=12345 সহ একটি প্রক্রিয়া বন্ধ করতে, নিম্নলিখিতটি চালান:

KILL 12345;

⚠️ KILL কমান্ডের ঝুঁকি

  • জোরপূর্বক বন্ধ করা লেনদেনগুলি রোলব্যাক হয়
  • উদাহরণস্বরূপ, একটি বাধাগ্রস্ত UPDATE স্টেটমেন্টের পরিবর্তনগুলি বাতিল হতে পারে।
  • এটি অ্যাপ্লিকেশন ত্রুটি সৃষ্টি করতে পারে
  • যদি আপনি প্রায়ই KILL ব্যবহার করতে হয়, তবে আপনার অ্যাপ্লিকেশন ডিজাইন পুনর্বিবেচনা করা উচিত।

4.3 ROLLBACK দিয়ে লক মুক্ত করা (সুরক্ষিত পদ্ধতি)

KILL কমান্ড ব্যবহার করার আগে, সম্ভব হলে, লক সৃষ্টি করা লেনদেনটি ম্যানুয়ালি শেষ করার চেষ্টা করুন।

১. প্রথমে, বর্তমান লেনদেনগুলি পরীক্ষা করুন

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

২. যদি সমস্যাযুক্ত লেনদেনটি পান, ROLLBACK চালান

ROLLBACK;

এই পদ্ধতি ডেটা সামঞ্জস্যতা বজায় রেখে লকটি মুক্ত করতে সহায়তা করে।

4.4 SET innodb_lock_wait_timeout দিয়ে লক হ্যান্ডলিং স্বয়ংক্রিয় করা

ম্যানুয়ালি লক মুক্ত করার পরিবর্তে, আপনি একটি লক ওয়েট টাইমআউট কনফিগার করতে পারেন যাতে নির্দিষ্ট সময়ের মধ্যে লক মুক্ত না হলে লেনদেন স্বয়ংক্রিয়ভাবে টাইমআউট হয়।

SET innodb_lock_wait_timeout = 10;

এই সেটিংয়ের মাধ্যমে, যদি লক ১০ সেকেন্ডের মধ্যে মুক্ত না হয়, MySQL একটি ত্রুটি ফেরত দেয় এবং স্বয়ংক্রিয়ভাবে লেনদেন শেষ করে।

5. MySQL লকের জন্য গুরুত্বপূর্ণ নোট এবং সেরা চর্চা

সঠিক লক ব্যবস্থাপনা ডেডলক এবং পারফরম্যান্স হ্রাসের ঝুঁকি কমাতে সহায়তা করে। নিচে লক কমাতে এবং সেগুলি দক্ষতার সাথে পরিচালনা করার সেরা চর্চা দেওয়া হল।

5.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