Bế tắc trong MySQL là gì?

Bế tắc trong MySQL xảy ra khi hai hoặc nhiều giao dịch cùng nắm giữ và yêu cầu khóa, tạo ra một chu kỳ phụ thuộc. Trong một hệ thống giao dịch, bế tắc là một thực tế của cuộc sống và không thể tránh khỏi hoàn toàn. InnoDB tự động phát hiện các bế tắc giao dịch, khôi phục giao dịch ngay lập tức và trả về lỗi. Nó sử dụng một số liệu để chọn giao dịch dễ dàng nhất để khôi phục. Mặc dù bế tắc không thường xuyên không phải là điều đáng lo ngại, nhưng những sự cố thường xuyên xảy ra cần được chú ý

Trước MySQL 5. 6, chỉ có thể xem lại bế tắc mới nhất bằng cách sử dụng lệnh SHOW ENGINE INNODB STATUS. Nhưng với pt-deadlock-logger của Percona Toolkit, bạn có thể truy xuất thông tin bế tắc từ SHOW ENGINE INNODB STATUS tại một khoảng thời gian nhất định và được lưu vào một tệp hoặc bảng để chẩn đoán muộn. Để biết thêm thông tin về cách sử dụng pt-deadlock-logger, hãy xem bài đăng này. Với MySQL5. 6, bạn có thể kích hoạt một biến mới innodb_print_all_deadlocks để tất cả các bế tắc trong InnoDB được ghi lại trong nhật ký lỗi mysqld

Trước và trên hết là chẩn đoán, việc các ứng dụng gặp lỗi bế tắc (lỗi MySQL số. 1213) và xử lý nó bằng cách thử lại giao dịch

Cách chẩn đoán bế tắc MySQL

Bế tắc MySQL có thể liên quan đến nhiều hơn hai giao dịch, nhưng phần KHÓA BẤT NGỜ ĐƯỢC PHÁT HIỆN MỚI NHẤT chỉ hiển thị hai giao dịch cuối cùng. Ngoài ra, nó chỉ hiển thị câu lệnh cuối cùng được thực hiện trong hai giao dịch và khóa từ hai giao dịch đã tạo chu trình. Điều bị bỏ sót là những câu lệnh trước đó có thể đã thực sự có được ổ khóa. Tôi sẽ chỉ một số mẹo về cách thu thập các báo cáo bị bỏ lỡ

Hãy xem xét hai ví dụ để xem thông tin nào được cung cấp. ví dụ 1

1 141013 6:06:22
2 *** (1) TRANSACTION:
3 TRANSACTION 876726B90, ACTIVE 7 sec setting auto-inc lock
4 mysql tables in use 1, locked 1
5 LOCK WAIT 9 lock struct(s), heap size 1248, 4 row lock(s), undo log entries 4
6 MySQL thread id 155118366, OS thread handle 0x7f59e638a700, query id 87987781416 localhost msandbox update
7 INSERT INTO t1 (col1, col2, col3, col4) values (10, 20, 30, 'hello')
8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9 TABLE LOCK table `mydb`.`t1` trx id 876726B90 lock mode AUTO-INC waiting
10 *** (2) TRANSACTION:
11 TRANSACTION 876725B2D, ACTIVE 9 sec inserting
12 mysql tables in use 1, locked 1
13 876 lock struct(s), heap size 80312, 1022 row lock(s), undo log entries 1002
14 MySQL thread id 155097580, OS thread handle 0x7f585be79700, query id 87987761732 localhost msandbox update
15 INSERT INTO t1 (col1, col2, col3, col4) values (7, 86, 62, "a lot of things"), (7, 76, 62, "many more")
16 *** (2) HOLDS THE LOCK(S):
17 TABLE LOCK table `mydb`.`t1` trx id 876725B2D lock mode AUTO-INC
18 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
19 RECORD LOCKS space id 44917 page no 529635 n bits 112 index `PRIMARY` of table `mydb`.`t2` trx id 876725B2D lock mode S locks rec but not gap waiting
20 *** WE ROLL BACK TRANSACTION (1)

Dòng 1 cho biết thời điểm bế tắc xảy ra. Nếu mã ứng dụng của bạn bắt và ghi lại lỗi bế tắc, đúng như vậy, thì bạn có thể khớp dấu thời gian này với dấu thời gian của lỗi bế tắc trong nhật ký ứng dụng. Bạn sẽ có giao dịch đã được khôi phục. Từ đó, truy xuất tất cả các báo cáo từ giao dịch đó

Dòng 3 & 11, lưu ý Số giao dịch và thời gian HOẠT ĐỘNG. Nếu bạn ghi đầu ra SHOW ENGINE INNODB STATUS theo định kỳ (đây là một phương pháp hay), thì bạn có thể tìm kiếm các đầu ra trước đó với số Giao dịch để hy vọng thấy nhiều báo cáo hơn từ cùng một giao dịch. Giây ACTIVE đưa ra gợi ý về việc giao dịch là một câu lệnh đơn hay nhiều câu lệnh

Dòng 4 & 12, các bảng đang được sử dụng và bị khóa chỉ liên quan đến câu lệnh hiện tại. Vì vậy, có 1 bảng đang được sử dụng không nhất thiết có nghĩa là giao dịch chỉ liên quan đến 1 bảng

Dòng 5 & 13, điều này đáng được chú ý vì nó cho biết có bao nhiêu thay đổi mà giao dịch đã thực hiện, đó là “các mục nhật ký hoàn tác” và có bao nhiêu khóa hàng được giữ, đó là “(các) khóa hàng”. Những thông tin này gợi ý sự phức tạp của giao dịch

Dòng 6 & 14, lưu ý id luồng, máy chủ kết nối và người dùng kết nối. Nếu bạn sử dụng những người dùng MySQL khác nhau cho các chức năng ứng dụng khác nhau, đây là một phương pháp hay khác, thì bạn có thể biết giao dịch đến từ khu vực ứng dụng nào dựa trên máy chủ và người dùng kết nối

Dòng 9, đối với giao dịch đầu tiên, nó chỉ hiển thị khóa mà nó đang chờ, trong trường hợp này là khóa AUTO-INC trên bảng t1. Các giá trị có thể khác là S cho khóa dùng chung và X cho loại trừ có hoặc không có khóa khoảng cách

Dòng 16 & 17, đối với giao dịch thứ hai, nó hiển thị (các) khóa được giữ, trong trường hợp này là khóa AUTO-INC mà GIAO DỊCH (1) đang chờ

Dòng 18 & 19 hiển thị khóa GIAO DỊCH (2) nào đang chờ. Trong trường hợp này, đó là khóa bản ghi không được chia sẻ trên khóa chính của bảng khác. Chỉ có một số nguồn để khóa bản ghi được chia sẻ trong InnoDB.
1) sử dụng CHỌN … KHÓA TRONG CHẾ ĐỘ CHIA SẺ
2) trên (các) bản ghi tham chiếu khóa ngoại
3) với . Bằng cách kiểm tra SHOW CREATE TABLE t1, bạn có thể xác nhận rằng khóa S là do ràng buộc khóa ngoại đối với bảng cha t2.
The current statement of trx(2) is a simple insert to table t1, so 1 and 3 are eliminated. By checking SHOW CREATE TABLE t1, you could confirm that the S lock was due to a foreign key constraint to the parent table t2.

ví dụ 2. Với phiên bản cộng đồng MySQL, mỗi khóa bản ghi có nội dung bản ghi được in

1 2014-10-11 10:41:12 7f6f912d7700
2 *** (1) TRANSACTION:
3 TRANSACTION 2164000, ACTIVE 27 sec starting index read
4 mysql tables in use 1, locked 1
5 LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
6 MySQL thread id 9, OS thread handle 0x7f6f91296700, query id 87 localhost ro ot updating
7 update t1 set name = 'b' where id = 3
8 *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
9 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164000 lock_mode X locks rec but not gap waiting
10 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
11 0: len 4; hex 80000003; asc ;;
12 1: len 6; hex 000000210521; asc ! !;;
13 2: len 7; hex 180000122117cb; asc ! ;;
14 3: len 4; hex 80000008; asc ;;
15 4: len 1; hex 63; asc c;;
16
17 *** (2) TRANSACTION:
18 TRANSACTION 2164001, ACTIVE 18 sec starting index read
19 mysql tables in use 1, locked 1
20 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
21 MySQL thread id 10, OS thread handle 0x7f6f912d7700, query id 88 localhost r oot updating
22 update t1 set name = 'c' where id = 2
23 *** (2) HOLDS THE LOCK(S):
24 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap
25 Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
26 0: len 4; hex 80000003; asc ;;
27 1: len 6; hex 000000210521; asc ! !;;
28 2: len 7; hex 180000122117cb; asc ! ;;
29 3: len 4; hex 80000008; asc ;;
30 4: len 1; hex 63; asc c;;
31
32 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
33 RECORD LOCKS space id 1704 page no 3 n bits 72 index `PRIMARY` of table `tes t`.`t1` trx id 2164001 lock_mode X locks rec but not gap waiting
34 Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bit s 0
35 0: len 4; hex 80000002; asc ;;
36 1: len 6; hex 000000210520; asc ! ;;
37 2: len 7; hex 17000001c510f5; asc ;;
38 3: len 4; hex 80000009; asc ;;
39 4: len 1; hex 62; asc b;;

Dòng 9 & 10. 'id không gian' là id không gian bảng, 'trang không' cho biết trang nào khóa bản ghi bên trong không gian bảng. 'n bit' không phải là phần bù trang, thay vào đó là số bit trong bitmap khóa. Độ lệch trang là 'heap no' trên dòng 10,

Dòng 11~15. Nó hiển thị dữ liệu bản ghi ở dạng số hex. Trường 0 là chỉ mục cụm (khóa chính). Bỏ qua bit cao nhất, giá trị là 3. Trường 1 là id giao dịch của giao dịch đã sửa đổi bản ghi này lần cuối, giá trị thập phân là 2164001 là GIAO DỊCH (2). Trường 2 là con trỏ rollback. Bắt đầu từ trường 3 là phần còn lại của dữ liệu hàng. Trường 3 là cột số nguyên, giá trị 8. Trường 4 là cột chuỗi có ký tự 'c'. Bằng cách đọc dữ liệu, chúng tôi biết chính xác hàng nào bị khóa và giá trị hiện tại là gì

Những gì khác chúng ta có thể học được từ phân tích? . Trong ví dụ 1, trx(2) đang đợi khóa dùng chung, vì vậy trx (1) giữ khóa dùng chung hoặc khóa độc quyền trên bản ghi khóa chính đó của bảng t2. Giả sử col2 là cột khóa ngoại, bằng cách kiểm tra câu lệnh hiện tại của trx(1), chúng tôi biết rằng nó không yêu cầu khóa bản ghi tương tự, do đó, nó phải là một số câu lệnh trước đó trong trx(1) yêu cầu khóa S hoặc X( . Trx (1) chỉ thực hiện thay đổi 4 hàng trong 7 giây. Sau đó, bạn đã học được một vài đặc điểm của trx(1). nó thực hiện nhiều xử lý nhưng có một vài thay đổi; . Những thông tin này kết hợp với dữ liệu khác có thể giúp các nhà phát triển xác định vị trí giao dịch

Nơi nào khác chúng ta có thể tìm thấy báo cáo trước đây của các giao dịch? . Với binlog, nếu binlog_format=câu lệnh, mỗi sự kiện binlog sẽ có thread_id. Chỉ các giao dịch đã cam kết mới được đăng nhập vào binlog, vì vậy chúng tôi chỉ có thể tìm kiếm Trx(2) trong binlog. Trong trường hợp của Ví dụ 1, chúng tôi biết khi nào bế tắc xảy ra và chúng tôi biết Trx(2) đã bắt đầu 9 giây trước. Chúng ta có thể chạy mysqlbinlog trên tệp binlog bên phải và tìm kiếm các câu lệnh có thread_id = 155097580. Tốt hơn hết là bạn nên tham khảo chéo các câu lệnh với mã ứng dụng để xác nhận

$ mysqlbinlog -vvv --start-datetime=“2014-10-13 6:06:12” --stop-datatime=“2014-10-13 6:06:22” mysql-bin.000010 > binlog_1013_0606.out

Với Máy chủ Percona 5. 5 trở lên, bạn có thể đặt log_slow_verbosity để bao gồm id giao dịch InnoDB trong nhật ký chậm. Sau đó, nếu bạn có long_query_time = 0, bạn sẽ có thể nắm bắt tất cả các câu lệnh bao gồm cả những câu lệnh được đưa trở lại vào tệp nhật ký chậm. Với nhật ký truy vấn chung, id luồng được bao gồm và có thể được sử dụng để tìm kiếm các câu lệnh liên quan

Cách tránh bế tắc MySQL

Có những điều chúng ta có thể làm để loại bỏ bế tắc sau khi chúng ta hiểu nó

– Thực hiện các thay đổi đối với ứng dụng. Trong một số trường hợp, bạn có thể giảm đáng kể tần suất bế tắc bằng cách chia một giao dịch dài thành các giao dịch nhỏ hơn, do đó, các khóa sẽ được giải phóng sớm hơn. Trong các trường hợp khác, bế tắc tăng lên do hai giao dịch chạm vào cùng một bộ dữ liệu, trong một hoặc nhiều bảng, với các thứ tự khác nhau. Sau đó, thay đổi chúng để truy cập dữ liệu theo cùng một thứ tự, nói cách khác, tuần tự hóa quyền truy cập. Bằng cách đó, bạn sẽ có khóa chờ thay vì bế tắc khi các giao dịch diễn ra đồng thời

– Thực hiện các thay đổi đối với lược đồ bảng, chẳng hạn như xóa ràng buộc khóa ngoại để tách hai bảng hoặc thêm chỉ mục để thu nhỏ các hàng được quét và khóa

– Trong trường hợp khóa khoảng cách, bạn có thể thay đổi mức cô lập giao dịch để đọc cam kết cho phiên hoặc giao dịch để tránh điều đó. Nhưng sau đó, định dạng binlog cho phiên hoặc giao dịch sẽ phải là ROW hoặc MIXED

Bế tắc trong cơ sở dữ liệu là gì?

Làm cách nào để sửa khóa chết trong MySQL?

Làm cách nào để tắt bế tắc trong MySQL?

Điều gì gây ra bế tắc trong cơ sở dữ liệu?