Ví dụ bế tắc của MySQL

Làm việc với cơ sở dữ liệu, kiểm soát tương tranh là khái niệm đảm bảo rằng các giao dịch cơ sở dữ liệu được thực hiện đồng thời mà không vi phạm tính toàn vẹn của dữ liệu

Có rất nhiều lý thuyết và các cách tiếp cận khác nhau xung quanh khái niệm này và cách thực hiện nó, nhưng chúng tôi sẽ đề cập ngắn gọn đến cách PostgreSQL và MySQL (khi sử dụng InnoDB) xử lý nó và một vấn đề phổ biến có thể phát sinh trong các hệ thống đồng thời cao. bế tắc

Các công cụ này thực hiện kiểm soát đồng thời bằng cách sử dụng một phương pháp có tên là MVCC (Kiểm soát đồng thời đa phiên bản). Trong phương pháp này, khi một mặt hàng đang được cập nhật, các thay đổi sẽ không ghi đè lên dữ liệu gốc mà thay vào đó, một phiên bản mới của mặt hàng (có các thay đổi) sẽ được tạo. Vì vậy, chúng tôi sẽ có một số phiên bản của mục được lưu trữ

Một trong những ưu điểm chính của mô hình này là các khóa có được để truy vấn (đọc) dữ liệu không xung đột với các khóa có được để ghi dữ liệu và do đó, việc đọc không bao giờ chặn việc ghi và việc ghi không bao giờ chặn việc đọc

Tuy nhiên, nếu một số phiên bản của cùng một mặt hàng được lưu trữ, giao dịch sẽ thấy phiên bản nào của mặt hàng đó? . Các giao dịch chỉ định một mức cách ly, xác định mức độ mà một giao dịch phải được cách ly khỏi các sửa đổi tài nguyên hoặc dữ liệu được thực hiện bởi các giao dịch khác. Mức độ này liên quan trực tiếp đến việc khóa do một giao dịch tạo ra và vì vậy, vì nó có thể được chỉ định ở cấp độ giao dịch, nên nó có thể xác định tác động mà một giao dịch đang chạy có thể có đối với các giao dịch đang chạy khác

Đây là một chủ đề rất thú vị và dài, mặc dù chúng tôi sẽ không đi vào quá nhiều chi tiết trong blog này. Chúng tôi muốn giới thiệu tài liệu chính thức của PostgreSQL và MySQL để đọc thêm về chủ đề này

Vì vậy, tại sao chúng ta lại đi sâu vào các chủ đề trên khi xử lý bế tắc?

Có một số loại khóa (lại là một chủ đề dài và thú vị khác để xem xét cho PostgreSQL và MySQL), nhưng điều quan trọng về chúng là cách chúng tương tác (chính xác nhất là cách chúng xung đột) với nhau. Tại sao vậy? . Và một chi tiết không nhỏ, sau khi có được, khóa thường được giữ cho đến khi kết thúc giao dịch

Đây là một ví dụ PostgreSQL về cách các loại khóa xung đột với nhau

Xung đột các loại khóa PostgreSQL

Và cho MySQL

Các loại khóa MySQL xung đột

X= khóa độc quyền         IX= khóa độc quyền có ý định
S= khóa chia sẻ         IS= khóa chia sẻ ý định

Vậy điều gì sẽ xảy ra khi tôi có hai giao dịch đang chạy muốn giữ các khóa xung đột trên cùng một đối tượng cùng một lúc?

Vì vậy, bây giờ chúng tôi đang ở một vị trí để thực sự hiểu những gì đang xảy ra trong thời gian bế tắc

Bế tắc là gì sau đó?

Bế tắc cơ sở dữ liệu là tình huống trong đó hai hoặc nhiều giao dịch đang chờ nhau từ bỏ khóa

Vì vậy, ví dụ, tình huống sau đây sẽ dẫn chúng ta đến bế tắc

ví dụ bế tắc

Tại đây, ứng dụng A nhận khóa trên bảng 1 hàng 1 để thực hiện cập nhật

Đồng thời ứng dụng B bị khóa ở bàn 2 hàng 2

Lúc này ứng dụng A cần lấy khóa ở bàn 2 hàng 2 để tiếp tục thực hiện và kết thúc giao dịch nhưng không lấy được khóa do bị ứng dụng B giữ. Ứng dụng A cần đợi ứng dụng B giải phóng nó

Nhưng ứng dụng B cần lấy khóa trên bàn 1 hàng 1 để tiếp tục thực hiện và kết thúc giao dịch nhưng không lấy được khóa do bị ứng dụng A nắm giữ

Vì vậy, ở đây chúng ta đang ở trong một tình huống bế tắc. Ứng dụng A đang đợi tài nguyên do ứng dụng B nắm giữ để kết thúc và ứng dụng B đang đợi tài nguyên do ứng dụng A nắm giữ. Vì vậy, làm thế nào để tiếp tục?

Hãy kiểm tra một số ví dụ bế tắc PostgreSQL và MySQL

PostgreSQL

Giả sử chúng ta có một thông tin từ các quốc gia trên thế giới

world=# SELECT code,region,population FROM country WHERE code IN ('NLD','AUS');
code |          region           | population
------+---------------------------+------------
NLD  | Western Europe            |   15864000
AUS  | Australia and New Zealand |   18886000
(2 rows)

Chúng tôi có hai phiên muốn thay đổi cơ sở dữ liệu

Phiên đầu tiên sẽ sửa đổi trường khu vực cho mã NLD và trường dân số cho mã AUS

Phiên thứ hai sẽ sửa đổi trường khu vực cho mã AUS và trường dân số cho mã NLD

Bảng dữ liệu

code: NLD
region: Western Europe
population: 15864000

code: AUS
region: Australia and New Zealand
population: 18886000

Phiên 1

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Europe' WHERE code='NLD';
UPDATE 1

buổi 2

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Oceania' WHERE code='AUS';
UPDATE 1
world=# UPDATE country SET population=15864001 WHERE code='NLD';

Phiên 2 sẽ treo chờ Phiên 1 kết thúc

Phiên 1

world=# UPDATE country SET population=18886001 WHERE code='AUS';

ERROR:  deadlock detected
DETAIL:  Process 1181 waits for ShareLock on transaction 579; blocked by process 1148.
Process 1148 waits for ShareLock on transaction 578; blocked by process 1181.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,15) in relation "country"

Ở đây chúng ta có bế tắc. Hệ thống đã phát hiện bế tắc và giết phiên 1

buổi 2

world=# BEGIN;
BEGIN
world=# UPDATE country SET region='Oceania' WHERE code='AUS';
UPDATE 1
world=# UPDATE country SET population=15864001 WHERE code='NLD';
UPDATE 1

Và chúng tôi có thể kiểm tra xem phiên thứ hai đã kết thúc chính xác chưa sau khi phát hiện bế tắc và Phiên 1 đã bị hủy (do đó, khóa đã được giải phóng)

Để biết thêm chi tiết, chúng ta có thể xem nhật ký trong máy chủ PostgreSQL của mình

2018-05-16 12:56:38.520 -03 [1181] ERROR:  deadlock detected
2018-05-16 12:56:38.520 -03 [1181] DETAIL:  Process 1181 waits for ShareLock on transaction 579; blocked by process 1148.
       Process 1148 waits for ShareLock on transaction 578; blocked by process 1181.
       Process 1181: UPDATE country SET population=18886001 WHERE code='AUS';
       Process 1148: UPDATE country SET population=15864001 WHERE code='NLD';
2018-05-16 12:56:38.520 -03 [1181] HINT:  See server log for query details.
2018-05-16 12:56:38.520 -03 [1181] CONTEXT:  while updating tuple (0,15) in relation "country"
2018-05-16 12:56:38.520 -03 [1181] STATEMENT:  UPDATE country SET population=18886001 WHERE code='AUS';
2018-05-16 12:59:50.568 -03 [1181] ERROR:  current transaction is aborted, commands ignored until end of transaction block

Ở đây chúng ta sẽ có thể xem các lệnh thực tế đã được phát hiện khi bế tắc

Tải xuống báo cáo trắng ngay hôm nay

Quản lý & Tự động hóa PostgreSQL với ClusterControl

Tìm hiểu về những gì bạn cần biết để triển khai, giám sát, quản lý và thay đổi quy mô PostgreSQL

Tải xuống báo cáo trắng

mysql

Để mô phỏng bế tắc trong MySQL, chúng ta có thể làm như sau

Như với PostgreSQL, giả sử chúng ta có một cơ sở dữ liệu thử nghiệm với thông tin về diễn viên và phim trong số những thứ khác

Chúng tôi có hai quy trình muốn thay đổi cơ sở dữ liệu

Quá trình đầu tiên sẽ sửa đổi trường first_name cho actor_id 1 và trường last_name cho actor_id 7

Quy trình thứ hai sẽ sửa đổi trường first_name cho actor_id 7 và trường last_name cho actor_id 1

Bảng dữ liệu

actor_id: 1
first_name: PENELOPE
last_name: GUINESS

code: NLD
region: Western Europe
population: 15864000

Phiên 1

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

buổi 2

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

Phiên 2 sẽ treo chờ Phiên 1 kết thúc

Phiên 1

code: NLD
region: Western Europe
population: 15864000

Ở đây chúng ta có bế tắc. Hệ thống đã phát hiện bế tắc và giết phiên 1

buổi 2

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

code: NLD
region: Western Europe
population: 15864000

code: AUS
region: Australia and New Zealand
population: 18886000

Như chúng ta có thể thấy trong lỗi, như chúng ta đã thấy đối với PostgreSQL, có một bế tắc giữa cả hai quy trình

Để biết thêm chi tiết, chúng ta có thể sử dụng lệnh SHOW ENGINE INNODB STATUSG .

code: AUS
region: Australia and New Zealand
population: 18886000

Dưới tiêu đề “ KHÓA CHẾT ĐƯỢC PHÁT HIỆN MỚI NHẤT “, chúng ta có thể xem chi tiết về bế tắc của mình.

Để xem chi tiết về bế tắc trong nhật ký lỗi mysql, chúng tôi phải bật tùy chọn innodb_print_all_deadlocks trong cơ sở dữ liệu của mình

code: AUS
region: Australia and New Zealand
population: 18886000

Lỗi nhật ký MySQL

code: AUS
region: Australia and New Zealand
population: 18886000

Cân nhắc những gì chúng ta đã tìm hiểu ở trên về lý do xảy ra bế tắc, bạn có thể thấy rằng chúng ta không thể làm gì nhiều về phía cơ sở dữ liệu để tránh bế tắc. Dù sao, với tư cách là DBA, nhiệm vụ của chúng tôi là thực sự nắm bắt chúng, phân tích chúng và cung cấp phản hồi cho các nhà phát triển

Thực tế là những lỗi này là đặc biệt đối với từng ứng dụng, vì vậy bạn sẽ cần kiểm tra từng lỗi một và không có hướng dẫn cho bạn biết cách khắc phục sự cố này. Hãy ghi nhớ điều này, có một số điều bạn có thể tìm kiếm

Mẹo để điều tra và tránh bế tắc

Tìm kiếm các giao dịch dài hạn. Vì các khóa thường được giữ cho đến khi kết thúc một giao dịch, nên giao dịch càng dài thì các khóa trên tài nguyên càng lâu. Nếu có thể, hãy cố gắng chia các giao dịch dài hạn thành các giao dịch nhỏ hơn/nhanh hơn

Đôi khi không thể thực sự chia nhỏ các giao dịch, vì vậy công việc nên tập trung vào việc cố gắng thực hiện các hoạt động đó theo thứ tự nhất quán mỗi lần, để các giao dịch tạo thành các hàng đợi được xác định rõ và không bị bế tắc

Một giải pháp thay thế mà bạn cũng có thể đề xuất là thêm logic thử lại vào ứng dụng (tất nhiên, trước tiên hãy cố gắng giải quyết vấn đề cơ bản) theo cách mà nếu xảy ra bế tắc, ứng dụng sẽ chạy lại các lệnh đó

Kiểm tra mức cô lập được sử dụng, đôi khi bạn thử bằng cách thay đổi chúng. Tìm các lệnh như CHỌN ĐỂ CẬP NHẬT và CHỌN ĐỂ CHIA SẺ , như . Một điều bạn có thể thử nếu không thể xóa các lệnh này là sử dụng mức cô lập thấp hơn, chẳng hạn như READ CAM KẾT .

Tất nhiên, luôn thêm các chỉ mục được lựa chọn tốt vào bảng của bạn. Sau đó, các truy vấn của bạn cần quét ít bản ghi chỉ mục hơn và do đó đặt ít khóa hơn

Ở cấp độ cao hơn, với tư cách là một DBA, bạn có thể thực hiện một số biện pháp phòng ngừa để giảm thiểu việc khóa nói chung. Ví dụ: trong trường hợp này là PostgreSQL, bạn có thể tránh thêm một giá trị mặc định trong cùng một lệnh mà bạn sẽ thêm một cột. Việc thay đổi bảng sẽ nhận được một khóa thực sự mạnh và việc đặt giá trị mặc định cho bảng sẽ thực sự cập nhật các hàng hiện có có giá trị null, khiến thao tác này mất nhiều thời gian. Vì vậy, nếu bạn chia thao tác này thành nhiều lệnh, thêm cột, thêm mặc định, cập nhật các giá trị null, bạn sẽ giảm thiểu tác động của khóa

Tất nhiên, có rất nhiều mẹo như thế này mà các DBA có được khi thực hành (tạo chỉ mục đồng thời, tạo riêng chỉ mục pk trước khi thêm pk, v.v.), nhưng điều quan trọng là phải học và hiểu “cách

Tóm lược

Hy vọng rằng, blog này đã cung cấp cho bạn những thông tin hữu ích về bế tắc cơ sở dữ liệu và cách khắc phục chúng. Vì không có cách nào chắc chắn để tránh bế tắc, nên việc biết cách chúng hoạt động có thể giúp bạn nắm bắt chúng trước khi chúng gây hại cho các phiên bản cơ sở dữ liệu của bạn. Các giải pháp phần mềm như ClusterControl có thể giúp bạn đảm bảo rằng cơ sở dữ liệu của bạn luôn ổn định. ClusterControl đã giúp hàng trăm doanh nghiệp – doanh nghiệp của bạn sẽ là doanh nghiệp tiếp theo chứ?

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

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

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

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