mysql - 主键会导致mysql死锁吗？

Question

我最近遇到了一个奇怪的 mysql 死锁，我的表看起来像(为简单起见，我删除了不相关的列):

CREATE TABLE Node (
    `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,
    `nodeId` varchar(128) NOT NULL UNIQUE,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE  JobQueue (
    `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,
    `workerManagementNodeId` varchar(32) DEFAULT NULL,
    CONSTRAINT `fkJbqMgmtNodeId` FOREIGN KEY (`workerManagementNodeId`) REFERENCES `Node` (`nodeId`) ON DELETE SET NULL,
    PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

当我的节点宕机时，它会删除节点表中的记录。此时，作业队列可能正在删除 JobQueue 表中具有指向 Node.nodeId 的外键的队列。然后mysql抛出异常:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: 尝试获取锁时发现死锁；尝试重启交易

我检查了数据库，JobQueue 被成功删除，但 Node 没有。我知道外键的顺序可能会导致死锁，但在我的例子中，节点表没有外键只有主键。那怎么会死锁呢？

顺便说一句:我很确定死锁是由 JobQueue 引起的，我花了很多时间缩小这个问题的范围，所以在我的测试中只会使用这两个表。

更新:

CREATE TABLE  JobQueueEntry (
    `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `jobQueueId` bigint unsigned NOT NULL,
    `issuerManagementNodeId` varchar(32) DEFAULT NULL,
    PRIMARY KEY  (`id`),
    CONSTRAINT `fkJbqEtryMgmtNodeId` FOREIGN KEY (`issuerManagementNodeId`) REFERENCES `Node` (`nodeId`) ON DELETE SET NULL,
    CONSTRAINT `fkJobQueueId` FOREIGN KEY (`jobQueueId`) REFERENCES `JobQueue` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我终于注意到它仍然是由外键顺序引起的问题。实际上还有另一个表 JobQueueEntry，它以相反的顺序同时具有指向 Node 和 JobQueue 的外键。所以在删除节点时，它会尝试更新 JobQueue 和 JobQueueEntry。发生死锁是因为 JobQueueEntry 在节点之前有 JobQueue 的外键。

感谢@ctrl 的回答!

Answer 1

首先，这应该是一个评论，但我现在没有足够的代表，所以......我的“评论”基于我的 Oracle 经验，但我认为这是一个常见问题，而且 mysql 可以在同理。

由于您有一个 fk on delete set null，当您从 Node 中删除某些内容时，数据库引擎必须通过 JobQueue 来更新它，并且它可能需要一个表锁来执行此操作(oracle 在您的情况下这样做)。如果您有多个参与者，一些更新/删除 Jobs 表和一些更新/删除 JobsQueue 表，您可能会陷入死锁。

在 Oracle 中，为了解决这个问题(并获得更好的性能)，您通常会在子表的 fk 列上创建一个索引，在您的例子中是 workerManagementNodeId。

如果 mysql 以不同且更智能的方式执行此操作，请原谅 :)