Practical MySQL & MariaDB

อธิบายการล็อกของ MySQL: ตรวจสอบสถานะการล็อก, ปล่อยล็อกอย่างปลอดภัย, และป้องกันการล็อกตาย (5.6–8.0)

การตั้งค่าและการจัดการเซิร์ฟเวอร์
目次

1. บทนำ

MySQL ถูกใช้กันอย่างกว้างขวางในฐานะระบบจัดการฐานข้อมูล แต่เมื่อหลายคำสั่งสอบถามพยายามเข้าถึงข้อมูลเดียวกัน กลไกการล็อกจะทำงานขึ้น การล็อกเป็นสิ่งจำเป็นสำหรับการรักษาความสอดคล้องของข้อมูล; อย่างไรก็ตาม การจัดการที่ไม่เหมาะสมอาจทำให้เกิด deadlock และการลดประสิทธิภาพของระบบ

ในบทความนี้ เราจะอธิบายแนวคิดพื้นฐานของการล็อกใน MySQL และให้คำแนะนำโดยละเอียดเกี่ยวกับ วิธีตรวจสอบสถานะการล็อก, วิธีปลดล็อก, และวิธีป้องกัน deadlock.

สิ่งที่คุณจะได้เรียนรู้ในบทความนี้

  • ประเภทของการล็อกใน MySQL และผลกระทบของมัน
  • วิธีตรวจสอบการล็อกตามเวอร์ชันของ MySQL
  • ขั้นตอนที่ปลอดภัยสำหรับการปลดล็อก
  • คำแนะนำเชิงปฏิบัติเพื่อป้องกัน deadlock

ตอนนี้เรามาเริ่มด้วยการอธิบาย แนวคิดพื้นฐานของการล็อกใน MySQL.

2. แนวคิดพื้นฐานของการล็อกใน MySQL

ในฐานข้อมูล, “การล็อก” คือกลไกที่จำกัดการเข้าถึงเพื่อรักษาความสมบูรณ์ของข้อมูลเมื่อหลายธุรกรรมพยายามแก้ไขข้อมูลพร้อมกัน หากการล็อกไม่ได้รับการจัดการอย่างเหมาะสม, อาจทำให้ประสิทธิภาพลดลงและเกิด deadlock.

2.1 ประเภทหลักของการล็อก

ใน MySQL มี หลายประเภทของการล็อก ขึ้นอยู่กับระดับการปกป้องข้อมูลที่ต้องการ.

การล็อกระดับแถว

  • ล็อกเฉพาะแถวที่กำหนด, ลดผลกระทบต่อธุรกรรมอื่น
  • รองรับเฉพาะโดยเครื่องยนต์จัดเก็บข้อมูล InnoDB
  • ถูกเรียกใช้เมื่อใช้ SELECT ... FOR UPDATE หรือ SELECT ... LOCK IN SHARE MODE .

การล็อกระดับตาราง

  • ล็อกตารางทั้งหมด, ป้องกันไม่ให้หลายคำสั่งทำงานพร้อมกัน
  • มักใช้ร่วมกับเครื่องยนต์จัดเก็บข้อมูล MyISAM
  • ถูกเรียกใช้เมื่อใช้คำสั่ง LOCK TABLES .

การล็อกระดับความตั้งใจ

  • การล็อกที่ใช้ประสานการล็อกระดับแถวและระดับตารางเพื่อหลีกเลี่ยงความขัดแย้ง
  • ใช้เฉพาะใน InnoDB และจัดการโดยอัตโนมัติ

Deadlock

  • สถานะที่หลายธุรกรรมรอการล็อกของกันและกัน
  • หากธุรกรรมไม่ได้ออกแบบอย่างเหมาะสม, การประมวลผลอาจหยุดทำงานอย่างสมบูรณ์

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 contention).

ตัวอย่างการล็อกระดับตาราง

เพื่อทำการล็อกตารางทั้งหมด, ใช้คำสั่งต่อไปนี้.

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

จนกว่าการล็อกนี้จะถูกปลด, ผู้ใช้คนอื่นไม่สามารถแก้ไขข้อมูลในตาราง products ได้.

ตัวอย่าง Deadlock

ต่อไปนี้เป็นสถานการณ์ deadlock ที่พบบ่อย.

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

ในสถานการณ์นี้, แต่ละธุรกรรมกำลังรอให้ธุรกรรมอื่นปลดล็อกของมัน, ทำให้เกิด deadlock.

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

เมื่อคุณระบุเซสชันที่ถือการล็อกแล้ว คุณสามารถปลดล็อกได้โดยการบังคับยุติกระบวนการ.

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

แทนที่จะปลดล็อกด้วยตนเอง คุณสามารถกำหนดค่า 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