CODEWITHSUNDEEP
mysqltriggers
Antoniossss
Antoniossss

Reputation: 32550

How to synchronize TRIGGER execution for UPDATE of certain row?

Lets get straight to the point. I have application that concurrently inserts new rows into relational database. On the endpoint of one Many-To-One relation I want to track child rows count with triggers for later usage. Unfortunetly I am getting DEADLOCKS in case when new data contains reference to same parent row (applicant). How to aquire concurrent lock for updated row? Here are my triggers:

DROP TRIGGER IF EXISTS `incrementEntryCountTrigger`;

DELIMITER $$
CREATE TRIGGER `incrementEntryCountTrigger` AFTER INSERT ON trademark FOR EACH ROW
BEGIN
    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 ;

Structure of trademark table

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

Upvotes: 0

Views: 1867

Answers (1)

krokodilko
krokodilko

Reputation: 36127

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

The above foreign key is a source of deadlocks.

Each insert into the trademark table places a shared lock on a corresponding record in the applicant table - due the foreign key constraint. This lock is placed by DBMS to prevens other sessions from updating/deleting a row to ensure database integrity.

Imagine a following scenario:
1. A session 1 inserts a new record into trademark table with applicant=2 - this places a shared lock on applicant.id = 2
2. A few miliseconds later a session 2 inserts another record into trademark table with applicant_id = 2 - this also places a shared lock on a corresponding row in the applicant table. Shared locks don't conflict, so at this moment nothing happens.
3. In the session 1 a trigger after insert is fired - the trigger is trying to udate the row id=2 in the applicant table. Since it is locked by session 2 (the shared lock conflicts with the write lock) - thus the transaction is waiting for releasing the shared lock.
4. In the session 2 the trigger after insert is fired - the trigger is trying to update the same row. The database detects, that the session 2 is trying to lock the same row which the session 1 is trying to lock, but the session 1 is actually waiting for the lock placed by session 2 --> so DBMS reports a deadlock error (both sessions are waiting for each other).

What you can do to cope with this problem:
1. Remove the foreign key constraint - but this can casue data integrity problems.
2. Add a before insert trigger (and maybe before delete also) with a command: SELECT 1 FROM applicant WHERE applicant.id = NEW.applicant_id FOR UPDATE - this will place a write lock on the record and prevents from deadlocks, but will slow down all your insert operations.
3. Detect deadlock errors in your application and retry the INSERT operation.

Upvotes: 3

Related Questions