Chào mọi người, trong bài viết này chúng ta sẽ cùng tìm hiểu về các loại khóa trong MySQL, chủ yếu dưới dạng câu hỏi và trả lời để giúp việc học tập trở nên dễ hiểu hơn.
Không dài dòng nữa, chúng ta bắt đầu ngay!
Trong MySQL, dựa trên phạm vi áp dụng, khóa có thể được chia thành ba loại chính: khóa toàn cầu, khóa cấp bảng và khóa cấp hàng.
Khóa Toàn Cầu
Khóa toàn cầu được sử dụng như thế nào?
Để sử dụng khóa toàn cầu, bạn cần thực thi lệnh sau:
FLUSH TABLES WITH READ LOCK
Sau khi thực thi, toàn bộ cơ sở dữ liệu sẽ ở trạng thái chỉ đọc. Lúc này, các luồng khác thực hiện các thao tác sau sẽ bị chặn:
- Các thao tác CRUD trên dữ liệu, như select, insert, delete, update;
- Các thao tác thay đổi cấu trúc bảng, như alter table, drop table.
Để giải phóng khóa toàn cầu, sử dụng lệnh:
UNLOCK TABLES
Tất nhiên, khi phiên kết thúc, khóa toàn cầu sẽ được tự động giải phóng.
Ứng dụng của khóa toàn cầu là gì?
Khóa toàn cầu chủ yếu được sử dụng cho sao lưu logic toàn bộ cơ sở dữ liệu, đảm bảo trong quá trình sao lưu, dữ liệu hoặc cấu trúc bảng không bị thay đổi, tránh sao lưu không nhất quán.
Ví dụ: Trong quá trình sao lưu toàn bộ cơ sở dữ liệu, nếu không có khóa toàn cầu, có thể xảy ra tình huống không mong muốn.
Giả sử có người dùng mua một sản phẩm. Logic mua hàng thường liên quan đến việc cập nhật nhiều bảng: cập nhật số dư người dùng trong bảng user, và cập nhật tồn kho sản phẩm trong bảng product.
Có thể xảy ra trình tự như sau:
- Đầu tiên, sao lưu dữ liệu bảng user;
- Sau đó, có người dùng thực hiện mua hàng;
- Tiếp theo, sao lưu dữ liệu bảng product.
Tức là giữa khi sao lưu bảng user và bảng product, có giao dịch mua hàng diễn ra.
Trong trường hợp này, kết quả sao lưu là số dư của người dùng trong bảng user không bị trừ đi, nhưng tồn kho sản phẩm trong bảng product lại bị giảm. Nếu khôi phục dữ liệu từ bản sao lưu này, người dùng không mất tiền nhưng sản phẩm lại bị giảm tồn kho, tương tự như người dùng đã nhận được sản phẩm mà không trả tiền.
Vì vậy, trong quá trình sao lưu logic toàn bộ cơ sở dữ liệu, việc sử dụng khóa toàn cầu sẽ ngăn chặn tình huống trên.
Sử dụng khóa toàn cầu có những nhược điểm gì?
Khi sử dụng khóa toàn cầu, toàn bộ cơ sở dữ liệu ở trạng thái chỉ đọc. Nếu cơ sở dữ liệu có nhiều dữ liệu, quá trình sao lưu sẽ tốn nhiều thời gian. Đặc biệt, trong thời gian sao lưu, ứng dụng chỉ có thể đọc dữ liệu mà không thể cập nhật, gây ra tình trạng tắc nghẽn hoạt động kinh doanh.
Vì sao sao lưu dữ liệu bằng khóa toàn cầu ảnh hưởng đến hoạt động kinh doanh, có cách nào khác để tránh không?
Có, nếu cơ sở dữ liệu sử dụng bộ nhớ hỗ trợ giao dịch ở cấp độ cô lập có thể đọc lại được (repeatable read), bạn có thể bắt đầu một giao dịch trước khi sao lưu. Giao dịch này sẽ tạo một Read View, và trong suốt quá trình thực hiện giao dịch, nó sử dụng Read View này. Nhờ hỗ trợ MVCC, hoạt động kinh doanh vẫn có thể cập nhật dữ liệu trong quá trình sao lưu.
Ở mức độ cô lập có thể đọc lại, ngay cả khi các giao dịch khác cập nhật dữ liệu bảng, nó cũng không ảnh hưởng đến Read View khi sao lưu, đây là tính cô lập trong bốn đặc tính của giao dịch. Như vậy, dữ liệu được sao lưu trong suốt quá trình giao dịch luôn là dữ liệu tại thời điểm bắt đầu giao dịch.
Công cụ sao lưu cơ sở dữ liệu là mysqldump, khi sử dụng mysqldump với tham số –single-transaction, nó sẽ tự động bắt đầu một giao dịch trước khi sao lưu. Phương pháp này chỉ áp dụng cho các bộ nhớ lưu trữ hỗ trợ giao dịch ở "cấp độ cô lập có thể đọc lại".
Bộ nhớ lưu trữ InnoDB mặc định sử dụng mức độ cô lập có thể đọc lại, vì vậy có thể sử dụng phương pháp này để sao lưu.
Tuy nhiên, với các bộ nhớ như MyISAM không hỗ trợ giao dịch, bạn vẫn cần sử dụng phương pháp khóa toàn cầu khi sao lưu.
Khóa Cấp Bảng
MySQL có những khóa cấp bảng nào? Cách sử dụng cụ thể như thế nào?
MySQL có các khóa cấp bảng sau:
- Khóa bảng (Table lock);
- Khóa metadata (MDL);
- Khóa ý định (Intention lock);
- Khóa AUTO-INC;
Khóa Bảng
Trước hết, chúng ta hãy tìm hiểu về khóa bảng.
Nếu muốn khóa bảng sinh viên (tb_sinhvien), bạn có thể sử dụng lệnh sau:
// Khóa chia sẻ cấp bảng, hay còn gọi là khóa đọc;
LOCK TABLES tb_sinhvien READ;
// Khóa độc quyền cấp bảng, hay còn gọi là khóa viết;
LOCK TABLES tb_sinhvien WRITE;
Lưu ý rằng khóa bảng không chỉ hạn chế thao tác đọc/viết của các luồng khác, mà còn hạn chế các thao tác đọc/viết tiếp theo của chính luồng đó.
Tức là, nếu luồng hiện tại áp dụng "khóa bảng chia sẻ" lên bảng sinh viên, thì các câu lệnh thao tác ghi tiếp theo lên bảng này sẽ bị chặn. Tất nhiên, các luồng khác khi thực hiện thao tác ghi lên bảng cũng sẽ bị chặn cho đến khi khóa được giải phóng.
Để giải phóng khóa bảng, có thể sử dụng lệnh sau, giải phóng tất cả khóa bảng của phiên hiện tại:
UNLOCK TABLES
Ngoài ra, khi phiên kết thúc, tất cả khóa bảng cũng sẽ được giải phóng.
Tuy nhiên, nên tránh sử dụng khóa bảng trên các bảng sử dụng bộ nhớ InnoDB, vì khóa bảng có phạm vi quá lớn, ảnh hưởng đến hiệu suất đồng thời. Điểm mạnh của InnoDB nằm ở việc triển khai khóa cấp hàng có phạm vi chi tiết hơn.
Khóa Metadata
Tiếp theo, hãy tìm hiểu về khóa metadata (MDL).
Chúng ta không cần sử dụng rõ ràng MDL, vì khi thao tác với bảng cơ sở dữ liệu, hệ thống sẽ tự động áp dụng MDL:
- Khi thực hiện CRUD trên bảng, sẽ áp dụng khóa đọc MDL;
- Khi thay đổi cấu trúc bảng, sẽ áp dụng khóa viết MDL;
MDL được đảm bảo để khi người dùng thực hiện thao tác CRUD trên bảng, các luồng khác không thể thay đổi cấu trúc bảng đó.
Khi có luồng đang thực thi câu lệnh select (áp dụng khóa đọc MDL), nếu có luồng khác muốn thay đổi cấu trúc bảng (yêu cầu khóa viết MDL), luồng đó sẽ bị chặn cho đến khi câu lệnh select hoàn thành (giải phóng khóa đọc MDL).
Ngược lại, khi có luồng đang thay đổi cấu trúc bảng (áp dụng khóa viết MDL), nếu có luồng khác thực hiện thao tác CRUD (yêu cầu khóa đọc MDL), luồng đó sẽ bị chặn cho đến khi thay đổi cấu trúc bảng hoàn tất (giải phóng khóa viết MDL).
MDL không cần gọi rõ ràng, vậy nó được giải phóng khi nào?
MDL được giải phóng sau khi giao dịch được commit, điều này có nghĩa là trong quá trình thực hiện giao dịch, MDL luôn được giữ.
Nếu cơ sở dữ liệu có một giao dịch dài (giao dịch được mở nhưng chưa được commit), khi thay đổi cấu trúc bảng, có thể xảy ra các sự cố không mong muốn, ví dụ như trình tự sau:
- Đầu tiên, luồng A bắt đầu giao dịch (nhưng không commit), sau đó thực thi một câu lệnh select, lúc này bảng sẽ được áp dụng khóa đọc MDL;
- Sau đó, luồng B cũng thực hiện câu lệnh select tương tự, lúc này sẽ không bị chặn vì "đọc-đọc" không xung đột;
- Tiếp theo, luồng C thay đổi trường của bảng, do luồng A chưa commit giao dịch, tức khóa đọc MDL vẫn đang được sử dụng, lúc này luồng C không thể yêu cầu khóa viết MDL và sẽ bị chặn,
Khi luồng C bị chặn, các câu lệnh select tiếp theo đối với bảng cũng sẽ bị chặn. Nếu tại thời điểm này có nhiều yêu cầu select đến bảng, sẽ có nhiều luồng bị chặn, và số lượng luồng của cơ sở dữ liệu sẽ nhanh chóng bị đầy.
Tại sao luồng C không thể yêu cầu khóa viết MDL lại dẫn đến các thao tác đọc tiếp theo cũng bị chặn?
Đây là vì thao tác yêu cầu khóa MDL sẽ tạo thành một hàng đợi, trong đó ưu tiên khóa viết cao hơn khóa đọc. Một khi có khóa viết MDL đang chờ, nó sẽ chặn tất cả các thao tác CRUD tiếp theo trên bảng.
Vì vậy, để an toàn khi thay đổi cấu trúc bảng, trước tiên cần kiểm tra xem cơ sở dữ liệu có giao dịch dài nào đã áp dụng khóa đọc MDL lên bảng không, nếu có có thể cân nhắc kết thúc giao dịch dài đó trước khi thay đổi cấu trúc bảng.
Khóa Ý Định
Tiếp theo, hãy tìm hiểu về khóa ý định.
- Khi sử dụng bảng có bộ nhớ InnoDB để áp dụng "khóa chia sẻ" lên một số bản ghi, trước tiên cần áp dụng "khóa chia sẻ ý định" cấp bảng;
- Khi sử dụng bảng có bộ nhớ InnoDB để áp dụng "khóa độc quyền" lên một số bản ghi, trước tiên cần áp dụng "khóa độc quyền ý định" cấp bảng;
Tức là, khi thực hiện thao tác chèn, cập nhật, xóa, trước tiên cần áp dụng "khóa chia sẻ ý định" lên bảng, sau đó áp dụng khóa độc quyền lên bản ghi.
Còn câu lệnh select thông thường sẽ không áp dụng khóa cấp hàng. Câu lệnh select thông thường sử dụng MVCC để đọc nhất quán, là không khóa.
Tuy nhiên, select cũng có thể áp dụng khóa chia sẻ và khóa độc quyền lên bản ghi, cụ thể như sau:
// Trước tiên áp dụng khóa chia sẻ ý định lên bảng, sau đó áp dụng khóa độc quyền lên bản ghi được đọc
SELECT ... LOCK IN SHARE MODE;
// Trước tiên áp dụng khóa độc quyền ý định lên bảng, sau đó áp dụng khóa độc quyền lên bản ghi được đọc
SELECT ... FOR UPDATE;
Khóa chia sẻ ý định và khóa độc quyền ý định là khóa cấp bảng, không xung đột với khóa chia sẻ và khóa độc quyền cấp hàng, và các khóa ý định cũng không xung đột với nhau, chỉ xung đột với khóa chia sẻ bảng (LOCK TABLES ... READ) và khóa độc quyền bảng (LOCK TABLES ... WRITE).
Khóa bảng và khóa hàng đáp ứng nguyên tắc: đọc-chia sẻ, đọc-viết-xung đột, viết-viết-xung đột.
Nếu không có "khóa ý định", khi áp dụng "khóa độc quyền bảng", cần duyệt qua tất cả bản ghi trong bảng để xem có bản ghi nào đang bị khóa độc quyền không, điều này sẽ rất kém hiệu quả.
Với "khóa ý định", do trước khi áp dụng khóa độc quyền lên bản ghi, đã áp dụng khóa độc quyền ý định cấp bảng, nên khi áp dụng "khóa độc quyền bảng", chỉ cần kiểm tra xem bảng có khóa độc quyền ý định không, nếu có có nghĩa là đã có bản ghi trong bảng bị khóa độc quyền, không cần duyệt qua các bản ghi trong bảng.
Vì vậy, mục đích của khóa ý định là để nhanh chóng xác định xem trong bảng có bản ghi nào bị khóa không.
Khóa AUTO-INC
Cuối cùng, hãy tìm hiểu về khóa AUTO-INC.
Khi khai báo thuộc tính AUTO_INCREMENT cho một trường, sau này khi chèn dữ liệu, bạn không cần chỉ định giá trị cho trường đó, cơ sở dữ liệu sẽ tự động gán giá trị tăng dần cho trường, chủ yếu thông qua cơ chế khóa AUTO-INC.
Khóa AUTO-INC là cơ chế khóa bảng đặc biệt, khóa không được giải phóng sau khi giao dịch được commit, mà được giải phóng ngay sau khi câu lệnh chèn thực thi xong.
Khi chèn dữ liệu, sẽ áp dụng khóa AUTO-INC cấp bảng, sau đó gán giá trị tăng dần cho trường được修饰 bởi AUTO_INCREMENT, khi câu lệnh chèn hoàn tất, khóa AUTO-INC mới được giải phóng.
Trong quá trình một giao dịch giữ khóa AUTO-INC, các giao dịch khác nếu muốn chèn dữ liệu vào bảng đó sẽ bị chặn, đảm bảo khi chèn dữ liệu, giá trị của trường được AUTO_INCREMENT修饰 sẽ tăng dần liên tục.
Tuy nhiên, khóa AUTO-INC khi chèn lượng lớn dữ liệu sẽ ảnh hưởng đến hiệu suất chèn, vì các giao dịch khác sẽ bị chặn.
Vì vậy, từ phiên bản MySQL 5.1.22, bộ nhớ lưu trữ InnoDB cung cấp một loại khóa nhẹ để thực hiện tự động tăng.
Cũng khi chèn dữ liệu, sẽ áp dụng khóa nhẹ cho trường được AUTO_INCREMENT修饰, sau đó gán giá trị tự tăng cho trường và ngay lập tức giải phóng khóa nhẹ, không cần chờ đến khi câu lệnh chèn hoàn tất mới giải phóng khóa.
Bộ nhớ lưu trữ InnoDB cung cấp biến hệ thống innodb_autoinc_lock_mode để kiểm soát việc chọn khóa AUTO-INC hay khóa nhẹ.
- Khi innodb_autoinc_lock_mode = 0, sử dụng khóa AUTO-INC;
- Khi innodb_autoinc_lock_mode = 2, sử dụng khóa nhẹ;
- Khi innodb_autoinc_lock_mode = 1, đây là giá trị mặc định, sử dụng cả hai loại khóa. Nếu có thể xác định số lượng bản ghi chèn thì sử dụng khóa nhẹ, không xác định thì sử dụng khóa AUTO-INC.
Tuy nhiên, khi innodb_autoinc_lock_mode = 2 là phương thức hiệu suất cao nhất, nhưng sẽ mang lại một số vấn đề. Do có chèn đồng thời, giá trị tự tăng mỗi lần chèn có thể không liên tục, điều này không an toàn trong có có sao chép chủ-từ.
Khóa cấp hàng có những loại nào?
Bộ nhớ lưu trữ InnoDB hỗ trợ khóa cấp hàng, trong khi MyISAM không hỗ trợ.
Khóa cấp hàng chủ yếu có ba loại:
- Record Lock: khóa bản ghi, chỉ khóa một bản ghi;
- Gap Lock: khóa khoảng, khóa một khoảng nhưng không bao gồm bản ghi chính;
- Next-Key Lock: kết hợp Record Lock và Gap Lock, khóa một khoảng và khóa cả bản ghi chính.
Như đã đề cập, câu lệnh select thông thường sẽ không khóa bản ghi. Nếu muốn khóa bản ghi khi truy vấn, có thể sử dụng hai cách sau:
// Áp dụng khóa chia sẻ cho bản ghi được đọc
SELECT ... LOCK IN SHARE MODE;
// Áp dụng khóa độc quyền cho bản ghi được đọc
SELECT ... FOR UPDATE;
Hai câu lệnh trên phải nằm trong một giao dịch, khi giao dịch được commit, khóa sẽ được giải phóng. Vì vậy khi sử dụng hai câu lệnh này, cần thêm begin, start transaction hoặc set autocommit = 0.
Cụ thể khóa vào những bản ghi nào phụ thuộc vào câu lệnh select, khá phức tạp, chủ đề này sẽ được đề cập trong bài viết tiếp theo.
Tài liệu tham khảo: "MySQL技术内幕：innodb", "MySQL实战45讲", "从根儿上理解MySQL".