Điều gì gây ra khóa bảng trong MySQL?

Tất cả các bảng đang mở trong bộ nhớ cache của bảng đều được liệt kê, nhưng cột IN_USE cho biết bảng đã bị khóa. Khi khóa đầu tiên được thực hiện, giá trị tăng lên 1. Các yêu cầu khóa tiếp theo sẽ bị chặn cho đến khi khóa ban đầu được giải phóng. Mỗi yêu cầu khóa bị chặn sẽ tăng bộ đếm hơn nữa, sao cho số lượng 5 đại diện cho khóa ban đầu và 4 khóa bị chặn

Tất cả các khóa trong MySQL đều không có bế tắc, ngoại trừ các bảng loại InnoDB và BDB. Điều này được quản lý bằng cách luôn yêu cầu tất cả các khóa cần thiết cùng một lúc khi bắt đầu truy vấn và luôn khóa các bảng theo cùng một thứ tự

Các bảng loại InnoDB tự động có được các khóa hàng của chúng và các bảng loại BDB khóa trang của chúng trong quá trình xử lý các câu lệnh SQL, không phải khi bắt đầu giao dịch

Phương thức khóa mà MySQL sử dụng cho các khóa VIẾT hoạt động như sau

• Nếu không có khóa trên bàn, hãy đặt một khóa ghi trên đó

• Nếu không, hãy đặt yêu cầu khóa trong hàng đợi ghi khóa

Phương thức khóa mà MySQL sử dụng cho khóa READ hoạt động như sau

• Nếu không có khóa ghi trên bàn, hãy đặt khóa đọc trên đó

• Nếu không, hãy đặt yêu cầu khóa trong hàng đợi khóa đã đọc

Khi khóa được giải phóng, khóa sẽ được cung cấp cho các luồng trong hàng đợi khóa ghi, sau đó đến các luồng trong hàng đợi khóa đọc

Điều này có nghĩa là nếu bạn có nhiều bản cập nhật trên một bảng, câu lệnh SELECT sẽ đợi cho đến khi không còn bản cập nhật nào nữa

Để giải quyết vấn đề này trong trường hợp bạn muốn thực hiện nhiều thao tác CHÈN và CHỌN trên một bảng, bạn có thể chèn các hàng vào một bảng tạm thời và thỉnh thoảng cập nhật bảng thực với các bản ghi từ bảng tạm thời

Điều này có thể được thực hiện với đoạn mã sau

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;
mysql> INSERT INTO real_table SELECT * FROM insert_table;
mysql> TRUNCATE TABLE insert_table;
mysql> UNLOCK TABLES;

Bạn có thể sử dụng các tùy chọn LOW_PRIORITY với INSERT, UPDATE hoặc DELETE hoặc HIGH_PRIORITY với SELECT nếu bạn muốn ưu tiên truy xuất trong một số trường hợp cụ thể. Bạn cũng có thể bắt đầu mysqld với --low-priority-updates để có hành vi tương tự

Sử dụng SQL_BUFFER_RESULT cũng có thể giúp khóa bảng ngắn hơn. Thấy

Bạn cũng có thể thay đổi mã khóa trong mysys/thr_lock.c để sử dụng một hàng đợi. Trong trường hợp này, khóa ghi và khóa đọc sẽ có cùng mức độ ưu tiên, điều này có thể hữu ích cho một số ứng dụng

Mã khóa bảng trong MySQL không có bế tắc

MySQL sử dụng khóa bảng (thay vì khóa hàng hoặc khóa cột) trên tất cả các loại bảng, ngoại trừ bảng InnoDB và BDB, để đạt được tốc độ khóa rất cao. Đối với các bảng lớn, khóa bảng tốt hơn nhiều so với khóa hàng đối với hầu hết các ứng dụng, nhưng tất nhiên, vẫn có một số cạm bẫy

Đối với các bảng InnoDB và BDB, MySQL chỉ sử dụng khóa bảng nếu bạn khóa bảng một cách rõ ràng bằng LOCK TABLES. Đối với các loại bảng này, chúng tôi khuyên bạn hoàn toàn không nên sử dụng LOCK TABLES vì InnoDB sử dụng khóa cấp độ hàng tự động và BDB sử dụng khóa cấp độ trang để đảm bảo cách ly giao dịch

Trong phiên bản MySQL 3. 23. 7 trở lên, bạn có thể chèn hàng vào bảng MyISAM cùng lúc với các luồng khác đang đọc từ bảng. Lưu ý rằng hiện tại điều này chỉ hoạt động nếu không có lỗ hổng nào sau khi các hàng đã bị xóa trong bảng tại thời điểm chèn được thực hiện. Khi tất cả các lỗ đã được lấp đầy bằng dữ liệu mới, các lần chèn đồng thời sẽ tự động được bật lại

Khóa bảng cho phép nhiều luồng đọc từ một bảng cùng một lúc, nhưng nếu một luồng muốn ghi vào bảng, trước tiên nó phải có quyền truy cập độc quyền. Trong quá trình cập nhật, tất cả các chủ đề khác muốn truy cập vào bảng cụ thể này sẽ đợi cho đến khi bản cập nhật sẵn sàng

Vì các cập nhật trên bảng thường được coi là quan trọng hơn CHỌN, nên tất cả các câu lệnh cập nhật bảng đều có mức độ ưu tiên cao hơn các câu lệnh truy xuất thông tin từ bảng. Điều này sẽ đảm bảo rằng các bản cập nhật không bị 'bỏ đói' vì một bản phát hành rất nhiều truy vấn nặng đối với một bảng cụ thể. (Bạn có thể thay đổi điều này bằng cách sử dụng LOW_PRIORITY với câu lệnh thực hiện cập nhật hoặc HIGH_PRIORITY với câu lệnh SELECT. )

Bắt đầu từ MySQL Phiên bản 3. 23. 7, người ta có thể sử dụng biến max_write_lock_count để buộc MySQL tạm thời cung cấp cho tất cả các câu lệnh CHỌN chờ bảng có mức độ ưu tiên cao hơn sau một số lần chèn cụ thể trên bảng

Tuy nhiên, khóa bảng không tốt lắm trong trường hợp sau

• Một khách hàng đưa ra một CHỌN mất nhiều thời gian để chạy

• Sau đó, một khách hàng khác đưa ra CẬP NHẬT trên một bảng đã sử dụng. Khách hàng này sẽ đợi cho đến khi CHỌN kết thúc

• Một khách hàng khác đưa ra một câu lệnh CHỌN khác trên cùng một bảng. Vì CẬP NHẬT có mức độ ưu tiên cao hơn CHỌN, CHỌN này sẽ đợi CẬP NHẬT kết thúc. Nó cũng sẽ đợi CHỌN đầu tiên kết thúc

• Một luồng đang chờ thứ gì đó như đĩa đầy, trong trường hợp đó, tất cả các luồng muốn truy cập bảng vấn đề cũng sẽ được đặt ở trạng thái chờ cho đến khi có thêm dung lượng đĩa

Một số giải pháp khả thi cho vấn đề này là

• Cố gắng để các câu lệnh SELECT chạy nhanh hơn. Bạn có thể phải tạo một số bảng tóm tắt để làm điều này

• Bắt đầu mysqld với --low-priority-updates. Điều này sẽ cung cấp cho tất cả các câu lệnh cập nhật (sửa đổi) một bảng có mức độ ưu tiên thấp hơn so với câu lệnh CHỌN. Trong trường hợp này, câu lệnh SELECT cuối cùng trong kịch bản trước sẽ thực thi trước câu lệnh INSERT

• Bạn có thể đặt mức độ ưu tiên thấp hơn cho một câu lệnh INSERT, UPDATE hoặc DELETE cụ thể với thuộc tính LOW_PRIORITY

• Bắt đầu mysqld với giá trị thấp cho max_write_lock_count để khóa ĐỌC sau một số lần khóa VIẾT nhất định

• Bạn có thể chỉ định rằng tất cả các cập nhật từ một chuỗi cụ thể phải được thực hiện với mức độ ưu tiên thấp bằng cách sử dụng lệnh SQL SET SQL_LOW_PRIORITY_UPDATES=1. Thấy

• Bạn có thể chỉ định rằng một CHỌN cụ thể là rất quan trọng với thuộc tính CAO_PRIORITY. Thấy

• Nếu bạn gặp sự cố với CHÈN kết hợp với CHỌN, hãy chuyển sang sử dụng các bảng MyISAM mới, vì các bảng này hỗ trợ đồng thời CHỌN và CHÈN

• Nếu bạn chủ yếu kết hợp các câu lệnh INSERT và SELECT, thuộc tính DELAYED thành INSERT có thể sẽ giải quyết vấn đề của bạn. Thấy

  Làm cách nào để xóa khóa bảng trong MySQL?

  Bạn có thể hoàn toàn mở khóa bảng. Nếu kết nối đến máy chủ kết thúc rõ ràng hoặc hoàn toàn, tất cả các khóa sẽ được giải phóng. Bạn có thể giải phóng các khóa của bảng một cách rõ ràng bằng cách sử dụng câu lệnh UNLOCK TABLES .

  Tại sao các bảng bị khóa?

  Khóa là cơ chế liên kết với bảng được sử dụng để hạn chế truy cập trái phép dữ liệu trong bảng . MySQL cho phép phiên máy khách có được khóa bảng một cách rõ ràng để hợp tác với các phiên khác để truy cập dữ liệu của bảng.

  Làm cách nào để tìm các bảng bị khóa trong MySQL?

  Trong MySQL, các bảng bị khóa được xác định bằng cách sử dụng lệnh SHOW OPEN TABLES . Ở dạng đơn giản nhất là hiển thị tất cả các bảng bị khóa. Tất cả các bảng đang mở trong bộ nhớ cache của bảng đều được liệt kê, nhưng cột IN_USE cho biết bảng đã bị khóa. Khi khóa đầu tiên được thực hiện, giá trị tăng lên 1.

  Khóa bảng trong cơ sở dữ liệu là gì?

  Câu lệnh LOCK TABLE cho phép bạn lấy một cách rõ ràng khóa bảng dùng chung hoặc dành riêng trên bảng đã chỉ định . Khóa bảng kéo dài cho đến khi kết thúc giao dịch hiện tại. Để khóa bảng, bạn phải là chủ sở hữu cơ sở dữ liệu hoặc chủ sở hữu bảng.