Bế tắc MySQL

Gần đây tôi đã cập nhật mã sản xuất của mình bằng truy vấn mới được cho là để dọn sạch các bản ghi mồ côi. Nó chạy trong giao dịch như là bước cuối cùng. Và kết quả là tôi thỉnh thoảng thấy các ngoại lệ khóa chết đến từ MySQL

org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback;
SQL [
DELETE ts FROM topic_subscriptions AS ts LEFT JOIN 
endpoint_to_topic_subscription_associations AS ctsa 
ON ts.id=topic_subscription_id WHERE ctsa.topic_subscription_id IS NULL
]; (conn=2699877) Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.SQLTransactionRollbackException: (conn=2699877) Deadlock found when trying to get lock; try restarting transaction

Dưới đây là các DDL cho 2 bảng được đề cập

CREATE TABLE `topic_subscriptions` (
  `id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  `topic_subscription` varchar(255) NOT NULL,
  `virtual_broker_id` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ts_vbid_on_topic_subscriptions` (`topic_subscription`,`virtual_broker_id`),
  KEY `FK_topic_subscriptions_references_organizations_table` (`org_id`),
  KEY `FK_topic_subs_refs_virtual_brokers_table` (`virtual_broker_id`),
  CONSTRAINT `FK_topic_subs_refs_virtual_brokers_table` FOREIGN KEY (`virtual_broker_id`) REFERENCES `virtual_brokers` (`id`),
  CONSTRAINT `FK_topic_subscriptions_references_organizations_table` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `endpoint_to_topic_subscription_associations` (
  `endpoint_id` varchar(16) NOT NULL,
  `topic_subscription_id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  PRIMARY KEY (`endpoint_id`,`topic_subscription_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_org_tbl` (`org_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` (`topic_subscription_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_endpoints` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoints` (`id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_org_tbl` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` FOREIGN KEY (`topic_subscription_id`) REFERENCES `topic_subscriptions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Như bạn có thể thấy tôi không thực hiện bất kỳ khóa rõ ràng nào ở đây hoặc

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)

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)

Nếu bạn không may mắn, mỗi giao dịch sẽ thực hiện truy vấn đầu tiên của nó và cập nhật một hàng dữ liệu, khóa nó trong quá trình. Mỗi giao dịch sẽ cố gắng cập nhật hàng thứ hai của nó, chỉ để thấy rằng nó đã bị khóa. Hai giao dịch sẽ đợi nhau mãi mãi để hoàn thành, trừ khi có gì đó can thiệp để phá vỡ bế tắc

để giải quyết vấn đề này, các hệ thống cơ sở dữ liệu triển khai nhiều hình thức phát hiện bế tắc và hết thời gian chờ. công cụ lưu trữ InnoDB sẽ nhận thấy các phụ thuộc vòng tròn và trả về lỗi ngay lập tức. Đây có thể là một điều tốt nếu không, bế tắc sẽ tự biểu hiện dưới dạng các truy vấn rất chậm. những người khác sẽ bỏ cuộc sau khi truy vấn vượt quá thời gian chờ khóa, điều này không phải lúc nào cũng tốt. Cách InnoDB hiện xử lý các bế tắc là khôi phục giao dịch có ít khóa hàng độc quyền nhất

Hành vi và thứ tự khóa là dành riêng cho công cụ lưu trữ, vì vậy một số công cụ lưu trữ có thể bế tắc trên một chuỗi câu lệnh nhất định mặc dù những công cụ khác thì không.
Bế tắc có tính chất kép.

• một số là không thể tránh khỏi do xung đột dữ liệu thực sự
• một số là do cách thức hoạt động của công cụ lưu trữ

Bế tắc không thể bị phá vỡ mà không khôi phục một trong các giao dịch, một phần hoặc toàn bộ. Chúng là một thực tế của cuộc sống trong các hệ thống giao dịch và các ứng dụng của bạn phải được thiết kế để xử lý chúng. Nhiều ứng dụng có thể chỉ cần thử lại giao dịch của họ từ đầu

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ó 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 dành riêng         IX= khóa dành riêng cho mục đích
S= khóa dùng chung         IS= khóa dùng chung theo mục đích

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

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

CREATE TABLE `topic_subscriptions` (
  `id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  `topic_subscription` varchar(255) NOT NULL,
  `virtual_broker_id` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ts_vbid_on_topic_subscriptions` (`topic_subscription`,`virtual_broker_id`),
  KEY `FK_topic_subscriptions_references_organizations_table` (`org_id`),
  KEY `FK_topic_subs_refs_virtual_brokers_table` (`virtual_broker_id`),
  CONSTRAINT `FK_topic_subs_refs_virtual_brokers_table` FOREIGN KEY (`virtual_broker_id`) REFERENCES `virtual_brokers` (`id`),
  CONSTRAINT `FK_topic_subscriptions_references_organizations_table` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `endpoint_to_topic_subscription_associations` (
  `endpoint_id` varchar(16) NOT NULL,
  `topic_subscription_id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  PRIMARY KEY (`endpoint_id`,`topic_subscription_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_org_tbl` (`org_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` (`topic_subscription_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_endpoints` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoints` (`id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_org_tbl` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` FOREIGN KEY (`topic_subscription_id`) REFERENCES `topic_subscriptions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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

CREATE TABLE `topic_subscriptions` (
  `id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  `topic_subscription` varchar(255) NOT NULL,
  `virtual_broker_id` varchar(16) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `UK_ts_vbid_on_topic_subscriptions` (`topic_subscription`,`virtual_broker_id`),
  KEY `FK_topic_subscriptions_references_organizations_table` (`org_id`),
  KEY `FK_topic_subs_refs_virtual_brokers_table` (`virtual_broker_id`),
  CONSTRAINT `FK_topic_subs_refs_virtual_brokers_table` FOREIGN KEY (`virtual_broker_id`) REFERENCES `virtual_brokers` (`id`),
  CONSTRAINT `FK_topic_subscriptions_references_organizations_table` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `endpoint_to_topic_subscription_associations` (
  `endpoint_id` varchar(16) NOT NULL,
  `topic_subscription_id` varchar(16) NOT NULL,
  `org_id` varchar(255) NOT NULL,
  PRIMARY KEY (`endpoint_id`,`topic_subscription_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_org_tbl` (`org_id`),
  KEY `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` (`topic_subscription_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_endpoints` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoints` (`id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_org_tbl` FOREIGN KEY (`org_id`) REFERENCES `organizations` (`org_id`),
  CONSTRAINT `FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl` FOREIGN KEY (`topic_subscription_id`) REFERENCES `topic_subscriptions` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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 thử 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 để khắc phục bế tắc trong MySQL?

MySQL có thể phát hiện bế tắc không?

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