Practical MySQL & MariaDB

Giải Thích Khóa MySQL: Kiểm Tra Trạng Thái Khóa, Giải Phóng Khóa An Toàn và Ngăn Ngừa Deadlock (5.6–8.0)

Cấu hình & Quản trị máy chủ
目次

1. Giới thiệu

MySQL được sử dụng rộng rãi như một hệ quản trị cơ sở dữ liệu, nhưng khi nhiều truy vấn cố gắng truy cập cùng một dữ liệu, một cơ chế khóa sẽ được kích hoạt. Các khóa là cần thiết để duy trì tính nhất quán của dữ liệu; tuy nhiên, việc quản lý không đúng có thể dẫn đến deadlock và suy giảm hiệu năng.

Trong bài viết này, chúng tôi sẽ giải thích các khái niệm cơ bản về khóa trong MySQL và cung cấp hướng dẫn chi tiết về cách kiểm tra trạng thái khóa, cách giải phóng khóa và cách ngăn ngừa deadlock.

Những Điều Bạn Sẽ Học Trong Bài Viết Này

  • Các loại khóa MySQL và tác động của chúng
  • Cách kiểm tra khóa theo phiên bản MySQL
  • Quy trình an toàn để giải phóng khóa
  • Lời khuyên thực tế để ngăn ngừa deadlock

Hãy bắt đầu bằng cách giải thích các khái niệm cơ bản về khóa MySQL.

2. Các Khái Niệm Cơ Bản Về Khóa MySQL

Trong một cơ sở dữ liệu, “khóa” là một cơ chế hạn chế truy cập nhằm duy trì tính toàn vẹn dữ liệu khi nhiều giao dịch cố gắng sửa đổi dữ liệu đồng thời. Nếu các khóa không được quản lý đúng cách, hiệu năng có thể giảm và deadlock có thể xảy ra.

2.1 Các Loại Khóa Chính

Trong MySQL, có nhiều loại khóa tùy thuộc vào mức độ bảo vệ dữ liệu cần thiết.

Khóa Dòng

  • Chỉ khóa các hàng cụ thể, giảm thiểu ảnh hưởng đến các giao dịch khác.
  • Chỉ được hỗ trợ bởi engine lưu trữ InnoDB.
  • Được kích hoạt khi sử dụng SELECT ... FOR UPDATE hoặc SELECT ... LOCK IN SHARE MODE .

Khóa Bảng

  • Khóa toàn bộ bảng, ngăn chặn nhiều truy vấn thực thi đồng thời.
  • Thường được sử dụng với engine lưu trữ MyISAM.
  • Được kích hoạt khi sử dụng câu lệnh LOCK TABLES .

Khóa Ý Định

  • Một loại khóa được dùng để phối hợp khóa dòng và khóa bảng nhằm tránh xung đột.
  • Chỉ được sử dụng trong InnoDB và được quản lý tự động.

Deadlock

  • Trạng thái mà nhiều giao dịch đang chờ khóa của nhau.
  • Nếu các giao dịch không được thiết kế đúng, quá trình có thể dừng hoàn toàn.

2.2 Ví Dụ Về Sự Xảy Ra Của Khóa

Hãy xem một số truy vấn SQL cụ thể để hiểu cách các khóa xảy ra.

Ví Dụ Khóa Dòng

Thực thi SQL sau sẽ khóa một hàng cụ thể.

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

Nếu một phiên khác cố gắng cập nhật cùng một hàng, nó sẽ vào trạng thái chờ khóa (xung đột khóa).

Ví Dụ Khóa Bảng

Để khóa toàn bộ bảng, sử dụng lệnh sau.

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

Cho đến khi khóa này được giải phóng, người dùng khác không thể sửa đổi dữ liệu trong bảng products.

Ví Dụ Deadlock

Dưới đây là một kịch bản deadlock điển hình.

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

Trong tình huống này, mỗi giao dịch đang chờ giao dịch kia giải phóng khóa, dẫn đến deadlock.

3. Cách Kiểm Tra Trạng Thái Khóa MySQL (Theo Phiên Bản)

Để xác định liệu có khóa đang xảy ra hay không, bạn cần thực thi các lệnh phù hợp với phiên bản MySQL của mình.

3.1 Cách Kiểm Tra Khóa trong MySQL 5.6 và Trước Đó

Trong MySQL 5.6 và các phiên bản trước, bạn có thể kiểm tra thông tin khóa bằng cách sử dụng SHOW ENGINE INNODB STATUS\G;.

SHOW ENGINE INNODB STATUS\G;

Thực thi lệnh này sẽ hiển thị thông tin chi tiết về các khóa đang hoạt động hiện tại.

3.2 Cách Kiểm Tra Khóa trong MySQL 5.7

Trong MySQL 5.7 và các phiên bản sau, phương pháp dễ nhất là sử dụng bảng sys.innodb_lock_waits.

SELECT * FROM sys.innodb_lock_waits;

Bằng cách truy vấn bảng này, bạn có thể xác định các giao dịch nào đang chờ khóa.

3.3 Cách Kiểm Tra Khóa Trong MySQL 8.0 Và Các Phiên Bản Sau

Trong MySQL 8.0 và các phiên bản sau, bạn có thể lấy thông tin khóa chi tiết hơn bằng cách sử dụng performance_schema.data_locks.

SELECT * FROM performance_schema.data_locks;

Để xác định phiên đang giữ khóa, sử dụng SQL sau:

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

Điều này cho phép bạn xác định chính xác quy trình chịu trách nhiệm cho khóa.

4. Cách Giải Phóng Khóa MySQL (Giải Thích Rủi Ro)

Nếu xảy ra khóa trong MySQL và không được xử lý đúng cách, xử lý có thể bị đình trệ và hiệu suất cơ sở dữ liệu có thể suy giảm.
Trong phần này, chúng tôi sẽ giải thích cách giải phóng khóa và các rủi ro liên quan.

4.1 Xác Định Phiên Đang Giữ Khóa

Trước khi giải phóng một khóa, bạn phải xác định phiên nào đang giữ nó. Sử dụng SQL sau để kiểm tra các phiên đang gặp chờ khóa:

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

Truy vấn này liệt kê các phiên đang chờ khóa.

Trong MySQL 8.0 và các phiên bản sau, bạn có thể lấy thông tin khóa chi tiết bằng cách sử dụng:

SELECT * FROM performance_schema.data_locks;

4.2 Giải Phóng Khóa Sử Dụng Lệnh KILL

Một khi bạn xác định phiên đang giữ khóa, bạn có thể giải phóng nó bằng cách buộc chấm dứt quy trình.

1. Kiểm Tra Quy Trình Đang Giữ Khóa

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

2. Chấm Dứt Phiên Sử Dụng Lệnh KILL

KILL <process_id>;

Ví dụ, để chấm dứt quy trình có ID=12345, thực thi:

KILL 12345;

⚠️ Rủi Ro Của Lệnh KILL

  • Các giao dịch bị chấm dứt buộc phải được rollback
  • Ví dụ, các thay đổi được thực hiện bởi câu lệnh UPDATE bị gián đoạn có thể bị loại bỏ.
  • Nó có thể gây ra lỗi ứng dụng
  • Nếu bạn thường xuyên cần sử dụng KILL , bạn nên xem xét lại thiết kế ứng dụng của mình.

4.3 Giải Phóng Khóa Với ROLLBACK (Phương Pháp An Toàn Hơn)

Trước khi sử dụng lệnh KILL, nếu có thể, hãy thử kết thúc thủ công giao dịch gây ra khóa.

1. Đầu tiên, kiểm tra các giao dịch hiện tại

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2. Nếu bạn tìm thấy giao dịch có vấn đề, thực thi ROLLBACK

ROLLBACK;

Phương pháp này cho phép bạn giải phóng khóa trong khi duy trì tính nhất quán dữ liệu.

4.4 Tự Động Hóa Xử Lý Khóa Với SET innodb_lock_wait_timeout

Thay vì giải phóng khóa thủ công, bạn có thể cấu hình thời gian chờ khóa để giao dịch tự động hết hạn nếu khóa không được giải phóng trong thời gian quy định.

SET innodb_lock_wait_timeout = 10;

Với cài đặt này, nếu khóa không được giải phóng trong vòng 10 giây, MySQL trả về lỗi và tự động kết thúc giao dịch.

5. Ghi Chú Quan Trọng Và Các Thực Hành Tốt Nhất Cho Khóa MySQL

Quản lý khóa đúng cách giúp giảm rủi ro deadlock và suy giảm hiệu suất. Dưới đây là các thực hành tốt nhất để giảm thiểu khóa và quản lý chúng hiệu quả.

5.1 Cách Ngăn Chặn Deadlock

Để ngăn chặn deadlock, hãy ghi nhớ các điểm sau:

1. Chuẩn Hóa Thứ Tự Thực Thi Giao Dịch

  • Ví dụ, khi cập nhật nhiều bảng, luôn cập nhật chúng theo cùng thứ tự .
  • Ví dụ:
    -- 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: Các thứ tự thực thi khác nhau có thể gây ra deadlock

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

-- Phiên 2 (có thể xảy ra deadlock nếu thực thi theo thứ tự ngược lại)
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