刪除問題上的 MySQL 死鎖

我最近使用應該清理孤立記錄的新查詢更新了我的生產程式碼。它作為最後一步在事務中執行。因此，我偶爾會看到來自 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

以下是有問題的 2 個表的 DDL：

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;

如您所見，我沒有在此處或SELECT FOR UPDATE在上面的查詢中進行任何顯式鎖定。我確實SELECT FOR UPDATE在其他事務中使用了機制，但它不直接涉及提到的表。

此外，預設隔離級別是REPEATABLE_READ.

請幫助我理解這裡的問題。謝謝！

==================更新===================

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-04-13 08:34:28 0x2b2a3b191700
*** (1) TRANSACTION:
TRANSACTION 22501318, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
LOCK WAIT 24 lock struct(s), heap size 1136, 881 row lock(s), undo log entries 8
MySQL thread id 2699877, OS thread handle 47459092735744, query id 13624170039 172.25.150.126 [db_name] Sending data
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
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 824 page no 5 n bits 568 index FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl of table [db_name].`endpoint_to_topic_subscription_associations` trx id 22501318 lock mode S waiting
Record lock, heap no 427 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 12; hex 32326672616638696f746770; asc 22fraf8iotgp;;
1: len 11; hex 7638313876336d30307566; asc v818v3m00uf;;

*** (2) TRANSACTION:
TRANSACTION 22501319, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 2
24 lock struct(s), heap size 1136, 21 row lock(s), undo log entries 13
MySQL thread id 2699872, OS thread handle 47460380120832, query id 13624170054 172.25.150.126 [db_name] Sending data
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
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 824 page no 5 n bits 568 index FK_endpoint_to_topic_subscription_ref_topic_subscriptions_tbl of table [db_name].`endpoint_to_topic_subscription_associations` trx id 22501319 lock_mode X locks rec but not gap
Record lock, heap no 427 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 12; hex 32326672616638696f746770; asc 22fraf8iotgp;;
1: len 11; hex 7638313876336d30307566; asc v818v3m00uf;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 861 page no 5 n bits 192 index PRIMARY of table [db_name].`topic_subscriptions` trx id 22501319 lock_mode X waiting
Record lock, heap no 103 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 12; hex 323266723836636b67376572; asc 22fr86ckg7er;;
1: len 6; hex 0000015694e6; asc    V  ;;
2: len 7; hex e200000231014f; asc     1 O;;
3: len 7; hex 6d616173646576; asc maasdev;;
4: len 18; hex 746f706c6576656c2f6e6578746c6576656c; asc toplevel/nextlevel;;
5: len 12; hex 323266723836636b67373666; asc 22fr86ckg76f;;

*** WE ROLL BACK TRANSACTION (1)

這兩個DELETEs都在進行全表掃描，以查找要刪除的某些行。多餘的。一個會被自動殺死（通過 InnoDB）；對方將分配的任務。

只看那些刪除，我看到兩個問題——

• 刪除行的潛在低效方式。如果這是一張大桌子，讓我們談談如何分塊地穿過它。
• 不必要地從單獨的連接重複命令。在後台只有一項任務執行刪除。當它結束時，它重新開始。
• 也許需要其他地方的程式碼來防止刪除的需要。當從 中刪除該行時，從ctsa中刪除相應的行ts。
• 折騰刪除。檢查其他程式碼——即使ctsa. 如果沒有，也許一個小的改變就會讓它“正常”工作。

您想進一步討論哪些？

如果您不需要立即執行刪除，則可以有一個後台任務不斷執行此類刪除——刪除一些行，休眠幾秒鐘，循環。例如，如果表大於 100 行，則搜尋要刪除的行塊。見http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks

引用自：https://dba.stackexchange.com/questions/289889