1. Giới thiệu

　　Khóa là cơ chế máy tính phối hợp nhiều tiến trình hoặc luồng để truy cập vào một tài nguyên cùng lúc. Trong cơ sở dữ liệu, ngoài việc sử dụng các tài nguyên tính toán truyền thống (CPU, RAM, I/O), dữ liệu còn là tài nguyên được nhiều người dùng chia sẻ. Làm thế nào để đảm bảo tính nhất quán và hiệu quả của việc truy cập dữ liệu đồng thời là một vấn đề mà tất cả các cơ sở dữ liệu phải giải quyết. Xung đột khóa cũng là một yếu tố quan trọng ảnh hưởng đến hiệu suất truy cập đồng thời của cơ sở dữ liệu. Theo quan điểm này, khóa đặc biệt quan trọng và phức tạp hơn đối với cơ sở dữ liệu.

  .

2. Phân loại

　　Khóa trong MySQL được chia thành ba loại sau theo mức độ chi tiết của khóa.

• Khóa toàn cục: khóa tất cả các bảng của khối lượng dữ liệu
• Khóa cấp độ bảng: khóa toàn bộ bảng cho mỗi thao tác
• Khóa cấp hàng: Mỗi thao tác khóa dữ liệu hàng tương ứng

  .

3. Khóa toàn cục

　　3.1 Giới thiệu

　　　　Khóa toàn cục khóa toàn bộ phiên bản cơ sở dữ liệu. Sau khi khóa, toàn bộ phiên bản ở trạng thái chỉ đọc. Các câu lệnh ghi DML, câu lệnh DDL và câu lệnh cam kết giao dịch tiếp theo cho các hoạt động cập nhật sẽ bị chặn. Kịch bản sử dụng thông thường của nó là tạo bản sao lưu logic của toàn bộ cơ sở dữ liệu và khóa tất cả các bảng để có được chế độ xem nhất quán và đảm bảo tính toàn vẹn của dữ liệu.

　　3.2 Thêm cú pháp khóa toàn cục 

                          xả bảng với khóa đọc;

                        

　　3.3 Mở cú pháp khóa toàn cục

                          mở khóa bảng;

                        

　　3.4 Trường hợp 1

　　　　Lưu ý 1: Khóa toàn cục được thiết lập trong máy khách 1.

　　　　Lưu ý 2: Trên máy khách 2, câu lệnh truy vấn được thực thi bình thường, nhưng thao tác cập nhật trong câu lệnh DML bị chặn.

　　3.5 Trường hợp 2

　　　　Lưu ý 1: Khóa toàn cục được thiết lập trong máy khách 1.

　　　　Lưu ý 2: Máy khách 3 sử dụng các câu lệnh để sao lưu cơ sở dữ liệu, trong đó mysqldump là lệnh sao lưu cơ sở dữ liệu do máy chủ MySQL cung cấp, giống như mysql.

　　　　Lưu ý 3: Khi khóa toàn cục được thiết lập cho cơ sở dữ liệu, nó không ảnh hưởng đến việc sao lưu cơ sở dữ liệu.

　　　　Lưu ý 4: Sao lưu MySQL được thực hiện ở chế độ dòng lệnh terminal, không phải ở chế độ lệnh cơ sở dữ liệu. Xin lưu ý! Để ý! Để ý! .

　　3.6 Trường hợp 3

　　　　Lưu ý 1: Máy khách 1 giải phóng khóa toàn cục.

　　　　Lưu ý 2: Câu lệnh cập nhật của máy khách 2 được thực thi thành công ngay lập tức.

　　　　Lưu ý 3: Thời gian thực hiện câu lệnh cập nhật của máy khách 2 là 37 phút, điều này có nghĩa là câu lệnh đã bị khóa toàn cục của máy khách 1 chặn trong 37 phút.

　　3.7 Tính năng khóa toàn cục

　　　　Việc thêm khóa toàn cục vào cơ sở dữ liệu là một hoạt động tương đối nặng nề và có những vấn đề sau.

• • Nếu sao lưu trên cơ sở dữ liệu chính, không thể thực hiện bất kỳ cập nhật nào trong thời gian sao lưu và về cơ bản, hoạt động kinh doanh sẽ bị đình chỉ.
  • Nếu quá trình sao lưu được thực hiện trên máy phụ, máy phụ không thể thực thi nhật ký nhị phân (binlog) được đồng bộ hóa từ máy chủ trong quá trình sao lưu, điều này sẽ gây ra độ trễ giữa máy chủ và máy phụ.

　　　　Trong công cụ innoDB, chúng ta có thể thêm tham số --single-transaction trong quá trình sao lưu để hoàn tất quá trình sao lưu dữ liệu nhất quán mà không bị khóa.

  .

4. Khóa ở mức bàn

　　4.1 Giới thiệu

　　　　Khóa cấp bảng khóa toàn bộ bảng mỗi khi thực hiện một thao tác. Độ chi tiết của khóa lớn, khả năng xung đột khóa là cao nhất và đồng thời là thấp nhất. Khóa này được sử dụng trong các công cụ lưu trữ như MyISAM và InnoDB.

　　4.2 Phân loại khóa cấp bảng

• • Khóa bảng
  • Khóa siêu dữ liệu (MDL)
  • Khóa ý định

5. Khóa bàn

　　5.1 Phân loại khóa bàn

• • Bảng chia sẻ khóa đọc (khóa đọc)
  • Khóa ghi độc quyền trên bàn (write lock)

　　5.2 Cú pháp khóa bảng

　　　　Khóa: khóa tên bảng... đọc/ghi.

　　　　Mở khóa: mở khóa bảng / Máy khách ngắt kết nối.

　　　　Lưu ý 1: Khi khóa, nhiều bảng có thể bị khóa cùng lúc.

　　　　Lưu ý 2: Khóa bảng vẫn có thể được mở ngay cả khi máy khách ngắt kết nối.

　　5.3 Đọc Vỏ Khóa

　　   .

    Lưu ý 1: Khi khóa đọc được thêm vào một bảng, nó sẽ không ảnh hưởng đến việc đọc dữ liệu của bảng đó, nhưng sẽ ảnh hưởng đến các câu lệnh thêm, sửa và xóa.

　　　　Lưu ý 2: Khi thêm khóa đọc vào một bảng, nó sẽ không ảnh hưởng đến việc đọc dữ liệu của các máy khách khác, nhưng nó sẽ chặn các câu lệnh thêm, sửa đổi, xóa và các câu lệnh thao tác khác của các máy khách khác.

　　　　Lưu ý 3: Khi khóa được mở, các câu lệnh thêm, xóa, sửa của máy khách và các máy khách khác sẽ ngừng chặn.

　　5.4 Viết Khóa Trường Hợp

　　　　Lưu ý 1: Máy khách thêm khóa ghi có thể truy vấn, thêm, xóa và sửa đổi bảng một cách bình thường.

　　　　Lưu ý 2: Tất cả các truy vấn, sửa đổi, bổ sung và xóa của các máy khách khác đều bị chặn khi có khóa ghi cho đến khi máy khách đã thêm khóa ghi giải phóng khóa ghi.

　　　　Lưu ý 3: Các sửa đổi trong hình trên phải là các câu lệnh DML (Ngôn ngữ thao tác dữ liệu, được sử dụng để thêm, xóa và sửa đổi dữ liệu trong các bảng cơ sở dữ liệu), chứ không phải các câu lệnh DDL (DDL: Ngôn ngữ định nghĩa dữ liệu, được sử dụng để xác định các đối tượng cơ sở dữ liệu, bảng và trường).

  .

6. Khóa siêu dữ liệu

　　6.1 Giới thiệu về khóa siêu dữ liệu:

　　　　Khóa siêu dữ liệu (meta data lock): viết tắt là MDL.

　　　　Quá trình khóa MDL được hệ thống tự động kiểm soát và không cần phải sử dụng rõ ràng. Nó được tự động thêm vào khi truy cập vào bảng. Chức năng chính của khóa MDL là duy trì tính nhất quán dữ liệu của siêu dữ liệu bảng. Khi có các giao dịch đang hoạt động trên bảng, siêu dữ liệu không thể được ghi để tránh xung đột DML và DDL và đảm bảo tính chính xác của việc đọc và ghi.

　　6.2 Các loại khóa siêu dữ liệu

　　　　MDL được giới thiệu trong MySQL 5.5. Khi thêm, xóa, sửa đổi hoặc kiểm tra bảng, khóa đọc MDL (chia sẻ) sẽ được thêm vào; khi thay đổi cấu trúc bảng, khóa ghi MDL (độc quyền) sẽ được thêm vào.

　　6.3 Ví dụ

　　　　Lưu ý 1: lệnh select và update sẽ lần lượt thêm SHARED_READ và SHARED_WRITE, nhưng SHARED_READ và SHARED_WRITE tương thích với nhau, do đó các máy khách khác có thể thực hiện cập nhật, xóa, sửa đổi và truy vấn.

  Lưu ý 2: lệnh alter table sẽ tạo ra khóa ĐỘC QUYỀN. Khóa này loại trừ lẫn nhau với các MDL khác. ß.

　　6.4 Xem Khóa Siêu dữ liệu

                          
                            lựa chọn
                          
                           kiểu_đối_tượng, lược_đồ_đối_tượng,
                          
                            tên_đối_tượng
                          
                          ,kiểu_khóa,thời_gian_định_vị 
                          
                            từ
                          
                            performance_schema.metadata_locks;
                        

  .

7. Khóa ý định

　　7.1 Giới thiệu về Khóa ý định

　　　　Để tránh xung đột giữa khóa hàng và khóa bảng khi DML được thực thi, khóa ý định được đưa vào InnoDB, do đó khóa bảng không cần phải kiểm tra xem từng hàng dữ liệu có bị khóa hay không. Khóa ý định được sử dụng để giảm kiểm tra khóa bảng.

　　　　Trường hợp 1: Không có khóa ý định.

　　　　Lưu ý 1: Luồng A bắt đầu một giao dịch và sử dụng câu lệnh DML để cập nhật dữ liệu. Lúc này, một khóa hàng được thêm vào dữ liệu đã cập nhật.

　　　　Lưu ý 2: Lúc này, giao dịch của luồng A chưa được cam kết, và luồng B thêm khóa bảng vào bảng. Khi thêm khóa bảng, nó sẽ kiểm tra từ dữ liệu đầu tiên đến dữ liệu cuối cùng theo trình tự để xem có khóa nào khác không. Nếu có khóa khác, cần phải đợi cho đến khi các khóa khác được giải phóng trước khi thêm khóa bảng.

　　　　Lưu ý 3: Hiệu quả của việc thêm khóa bảng hiện tại rất thấp.

　　　　Trường hợp 2: Thêm khóa ý định.

　　  .

　　　　Lưu ý 4: Khi luồng A bắt đầu giao dịch và thực thi câu lệnh DML để cập nhật dữ liệu, ngoài việc thêm khóa hàng vào dữ liệu hàng, khóa ý định dựa trên toàn bộ bảng cũng được thêm vào.

　　　　Lưu ý 5: Khi luồng B thêm khóa bảng một lần nữa, luồng B không cần phải kiểm tra từng hàng để xem có khóa hàng hay không. Thay vào đó, luồng B trực tiếp kiểm tra xem toàn bộ bảng có khóa ý định hay không. Nếu khóa ý định và khóa bảng tương thích, khóa bảng sẽ được thêm trực tiếp.

　　　　Lưu ý 6: Nếu khóa bảng và khóa ý định không tương thích, khóa bảng chỉ có thể được thêm thành công sau khi khóa ý định được nhả.

　　7.2 Các loại khóa ý định

• • Khóa chia sẻ ý định (IS): Được thêm vào bởi câu lệnh select ... khóa ở chế độ chia sẻ. Tương thích với khóa chia sẻ bảng (đọc) và loại trừ lẫn nhau với khóa độc quyền bảng (ghi).
  • Khóa độc quyền ý định (IX): được thêm bằng lệnh chèn, cập nhật, xóa, chọn ... để cập nhật. Khóa chia sẻ (đọc) và khóa độc quyền (ghi) loại trừ lẫn nhau với khóa bảng, nhưng khóa ý định không loại trừ lẫn nhau.

　　7.3 Truy vấn khóa ý định

                          
                            lựa chọn
                          
                           lược đồ đối tượng, 
                          
                            tên_đối_tượng
                          
                          , tên_chỉ_mục, loại_khóa, chế_độ_khóa, dữ_liệu_khóa 
                          
                            từ
                          
                           performance_schema.data_locks;
                        

　　7.4 Trường hợp khóa ý định

　　　　Lưu ý 1: Máy khách 1 bắt đầu giao dịch và đặt khóa chia sẻ ý định (IS) khi truy vấn một câu lệnh.

　　　　Lưu ý 2: Máy khách 2 tạo thành công khóa đọc khi giao dịch của máy khách 1 chưa được xác nhận.

　　　　Lưu ý 3: Khóa chia sẻ ý định (IS) và khóa chia sẻ đọc là tương thích.

　　　　Lưu ý 4: Khi thực hiện các câu lệnh DML như cập nhật, MySQL sẽ tự động thêm khóa hàng và khóa ý định độc quyền.

　　　　Lưu ý 5: Khóa ý định độc quyền loại trừ lẫn nhau với khóa đọc chia sẻ và khóa ghi độc quyền.

　　　　Lưu ý 6: Mục đích của khóa ý định là giải quyết xung đột giữa khóa hàng và khóa bảng.

  .

8. Khóa cấp hàng

　　8.1 Giới thiệu

　　　　Khóa cấp hàng khóa dữ liệu hàng tương ứng trong mỗi thao tác. Độ chi tiết kết quả là tối thiểu, xác suất xung đột khóa là thấp nhất và tính đồng thời là cao nhất. Nó được sử dụng trong công cụ lưu trữ InnoDB.

　　　　Dữ liệu InnoDB được tổ chức dựa trên các chỉ mục. Khóa hàng được triển khai bằng cách khóa các mục chỉ mục trên chỉ mục thay vì khóa các bản ghi. Khóa cấp hàng chủ yếu được chia thành ba loại sau.

　　　　1. Khóa bản ghi: khóa một bản ghi hàng đơn để ngăn các giao dịch khác cập nhật hoặc xóa hàng này. Nó được hỗ trợ ở cả mức độ cô lập đọc đã cam kết và đọc lặp lại.

  2. Khóa khoảng cách: khóa khoảng cách bản ghi chỉ mục (trừ bản ghi) để đảm bảo khoảng cách bản ghi chỉ mục không thay đổi và ngăn các giao dịch khác chèn vào khoảng cách này, gây ra các lần đọc ảo. Nó được hỗ trợ ở mức cô lập đọc có thể lặp lại.

  3. Khóa Next-Key: Sự kết hợp giữa khóa hàng và khóa khoảng trống khóa dữ liệu và khoảng trống trước dữ liệu cùng lúc. Được hỗ trợ ở mức cô lập đọc có thể lặp lại.

  8.2 Khóa hàng

　　　　InnoDB triển khai hai loại khóa hàng sau.

　　　　1. Khóa chia sẻ (S): cho phép một giao dịch đọc một hàng, ngăn chặn các giao dịch khác có được khóa độc quyền trên cùng một tập dữ liệu.

　　　　2. Khóa độc quyền (X): Cho phép các giao dịch có khóa độc quyền cập nhật dữ liệu, ngăn chặn các giao dịch khác có được khóa chia sẻ và khóa độc quyền trên cùng một tập dữ liệu.

  3. Sự ràng buộc của các câu khác nhau.

  4. Theo mặc định, InnoDB chạy ở mức cô lập đọc có thể lặp lại. InnoDB sử dụng khóa next-key cho tìm kiếm hàng và quét chỉ mục để ngăn chặn việc đọc ảo.

• • Khi tìm kiếm một chỉ mục duy nhất, khi khớp các bản ghi hiện có có giá trị bằng nhau, nó sẽ tự động được tối ưu hóa thành khóa hàng.
  • Khóa hàng của InnoDB là khóa được thêm vào chỉ mục. Nếu dữ liệu không được truy xuất thông qua chỉ mục, InnoDB sẽ khóa tất cả các bản ghi trong bảng, sẽ được nâng cấp thành khóa bảng.

　　 8.3 Vỏ khóa hàng

　　　　Lưu ý 1: Máy khách 1 bắt đầu một giao dịch và thực hiện một câu lệnh truy vấn. Máy khách 2 khóa bảng, được hiển thị dưới dạng một bảng trống.

　　　　Lưu ý 2: Máy khách thực hiện một câu lệnh truy vấn và thêm thủ công một chia sẻ. Trong máy khách 2, trường lock_mode chứa một S, biểu thị một khóa chia sẻ: .

　　　　Lưu ý 3: Máy khách 2 cũng đã mở thủ công khóa chia sẻ và thành công, điều này cho thấy các khóa chia sẻ tương thích với nhau.

　　　　Lưu ý 5: Việc cập nhật dữ liệu có id=2 thành công trên máy khách 2 và truy vấn cho thấy khóa độc quyền (X) được tự động thêm vào.

　　　　Lưu ý 6: Khi cập nhật dữ liệu với id=2, không có khóa nào khác trên hàng có id=2, do đó thao tác có thể được thực hiện thành công.

　　　　Lưu ý 7: Vì khóa chia sẻ (S) đã được thêm vào hàng có id=1 trên điểm máy khách 1, khi máy khách 2 cập nhật dữ liệu có id=1, nó sẽ tự động thêm khóa độc quyền (X) vào dữ liệu có id=1. Điều này có nghĩa là sẽ có khóa chia sẻ (S) và khóa độc quyền (X) trên dữ liệu có id=1. Vì khóa chia sẻ và khóa độc quyền không tương thích, nên bản cập nhật id=1 sẽ bị chặn và sẽ cần phải đợi cho đến khi khóa chia sẻ (S) được giải phóng trước khi thao tác có thể được thực hiện thành công.

  Lưu ý 8: Khóa độc quyền X cũng không tương thích với khóa độc quyền X.

  Lưu ý 9: Khóa hàng của InnoDB là khóa trên chỉ mục. Nếu dữ liệu không được truy xuất thông qua chỉ mục, InnoDB sẽ khóa tất cả các bản ghi trong bảng, sẽ được nâng cấp thành khóa bảng.

  Lưu ý 10: Tùy theo nhu cầu kinh doanh, hãy thử sử dụng chỉ mục làm điều kiện truy vấn, cách này nhanh hơn và giảm tình trạng tắc nghẽn.

  .

9. Khóa khe hở/khóa tạm thời

　　9.1 Theo mặc định, InnoDB chạy ở mức cô lập giao dịch đọc có thể lặp lại. InnoDB sử dụng khóa next-key để tìm kiếm và quét chỉ mục để ngăn chặn việc đọc ảo.

　　9.2 Khi khóa các bản ghi không tồn tại, các truy vấn bằng nhau trên các chỉ mục (chỉ mục duy nhất) được tối ưu hóa để khóa khoảng cách.

　　9.3 Đối với các truy vấn có giá trị bằng nhau trên các chỉ mục (chỉ mục bình thường), khi duyệt sang phải và giá trị cuối cùng không đáp ứng các yêu cầu truy vấn, khóa khóa tiếp theo sẽ trở thành khóa khoảng trống.

　　　　Lưu ý 1: Đối với chỉ mục không duy nhất, tức là chỉ mục chung, ba khóa sẽ được thêm vào khi thực hiện truy vấn bằng nhau.

　　　　Lưu ý 2: Thêm khóa chia sẻ vào bản ghi đang được truy vấn, cho phép các giao dịch khác tiếp tục truy vấn bản ghi.

　　　　Lưu ý 3: Bằng cách đánh dấu vị trí 6, lock_type là S, REC_NOT_GAP, cho biết rằng khóa hàng được thêm vào hàng này.

　　　　Lưu ý 4: Bằng cách đánh dấu vị trí 7, lock_type là S, GAP, điều này có nghĩa là khóa khoảng cách sẽ được thêm vào trước dữ liệu đầu tiên không thỏa mãn truy vấn. Mục đích là ngăn chặn các giao dịch khác chèn hoặc sửa đổi dữ liệu trước khi đạt đến chỉ mục trong truy vấn chọn, điều này sẽ gây ra các lần đọc ảo trong truy vấn.

　　9.4 Truy vấn phạm vi trên chỉ mục (chỉ mục duy nhất) sẽ truy cập giá trị đầu tiên không đáp ứng điều kiện.

　　Lưu ý: Mục đích duy nhất của khóa khoảng trống là ngăn các giao dịch khác chèn vào khoảng trống. Khóa khoảng trống có thể cùng tồn tại và khóa khoảng trống do một giao dịch thực hiện không ngăn giao dịch khác thực hiện khóa khoảng trống trên cùng một khoảng trống.

Cuối cùng, bài viết này về MySQL Advanced 9-Lock kết thúc tại đây. Nếu bạn muốn biết thêm về MySQL Advanced 9-Lock, vui lòng tìm kiếm các bài viết CFSDN hoặc tiếp tục duyệt các bài viết liên quan. Tôi hy vọng bạn sẽ ủng hộ blog của tôi trong tương lai! .