mysql - 如何为特定行的 UPDATE 同步 TRIGGER 执行？

Question

让我们进入正题。我有同时将新行插入关系数据库的应用程序。在一个多对一关系的端点上，我想使用触发器跟踪子行数以供以后使用。不幸的是，当新数据包含对同一父行 (applicant) 的引用时，我会遇到死锁。如何获取更新行的并发锁？这是我的触发器:

DROP TRIGGER IF EXISTS `incrementEntryCountTrigger`;DELIMITER $$CREATE TRIGGER `incrementEntryCountTrigger` AFTER INSERT ON trademark FOR EACH ROWBEGIN    UPDATE applicant        SET entryCount=entryCount+1,            entryCountChanged=1        WHERE applicant.id=NEW.applicant_id;END$$DELIMITER ;DROP TRIGGER IF EXISTS `decrementEntryCountTrigger`;DELIMITER $$CREATE TRIGGER `decrementEntryCountTrigger` AFTER DELETE ON trademark FOR EACH ROW    BEGIN        UPDATE applicant            SET entryCount=entryCount-1,                entryCountChanged=1            WHERE applicant.id=OLD.applicant_id;    END$$    DELIMITER ;

商标表的结构

CREATE TABLE `trademark` (  `id` int(11) NOT NULL AUTO_INCREMENT,  `applicationDate` datetime DEFAULT NULL,  `applicationNumber` varchar(255) DEFAULT NULL,  `class` varchar(255) DEFAULT NULL,  `creationDate` datetime DEFAULT NULL,  `deleted` tinyint(4) DEFAULT NULL,  `imageDownloaded` tinyint(4) DEFAULT NULL,  `modified` datetime DEFAULT NULL,  `name` varchar(255) DEFAULT NULL,  `registrationDate` datetime DEFAULT NULL,  `registrationNumber` varchar(255) DEFAULT NULL,  `trademarkType` varchar(255) DEFAULT NULL,  `applicant_id` int(11) DEFAULT NULL,  `country_id` int(11) DEFAULT NULL,  `service_id` int(11) DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `uniqueApplicationPerServiceContraint` (`applicationNumber`,`service_id`),  KEY `FK_sv7x27shne6cro3hch7who6vr` (`applicant_id`),  KEY `FK_4fuuxl1srjn7svpby7rd6j1er` (`country_id`),  KEY `FK_1g62lp3kjl15f789m7netvlsk` (`service_id`),  CONSTRAINT `FK_1g62lp3kjl15f789m7netvlsk` FOREIGN KEY (`service_id`) REFERENCES `service` (`id`),  CONSTRAINT `FK_4fuuxl1srjn7svpby7rd6j1er` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`),  CONSTRAINT `FK_sv7x27shne6cro3hch7who6vr` FOREIGN KEY (`applicant_id`) REFERENCES `applicant` (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2101 DEFAULT CHARSET=utf8

Answer 1

CREATE TABLE `trademark` (    
.......
CONSTRAINT `FK_sv7x27shne6cro3hch7who6vr` FOREIGN KEY (`applicant_id`) 
    REFERENCES `applicant` (`id`)
.....

上面的外键是死锁的来源。

由于外键约束，每次插入 trademark 表都会在 applicant 表中的相应记录上放置一个共享锁。此锁由 DBMS 放置以防止其他 session 更新/删除行以确保数据库完整性。

想象以下场景:
1. session 1 向 trademark 表中插入一条新记录，applicant=2 - 这会在 applicant.id = 2
上放置一个共享锁2. 几毫秒后， session 2 将另一条记录插入到 trademark 表中，applicant_id = 2 - 这也在 applicant 表中的相应行上放置了一个共享锁。共享锁不冲突，所以此时什么都没有发生。
3. 在 session 1 中，触发器 after insert 被触发 - 触发器试图更新 applicant 表中的行 id=2。由于被session 2锁定(共享锁与写锁冲突)——因此事务正在等待释放共享锁。
4. 在 session 2 中触发触发器 after insert - 触发器试图更新同一行。数据库检测到， session 2 正试图锁定 session 1 试图锁定的同一行，但 session 1 实际上正在等待 session 2 放置的锁 --> 因此 DBMS 报告死锁错误(两个 session 正在等待彼此)。

你可以做什么来解决这个问题:
1. 删除外键约束 - 但这会导致数据完整性问题。
2. 使用命令添加before insert trigger(也许before delete):SELECT 1 FROM applicant WHERE applicant.id = NEW.applicant_id FOR UPDATE - 这将在记录上放置写锁并防止死锁，但会减慢所有插入操作。
3. 检测应用程序中的死锁错误并重试 INSERT 操作。