MySQL Deadlock with an an insert that raises a trigger
<p>I found out some rare deadlock errors happening. I understand deadlock happens when two queries job depends on the result of each other so MySQL rollbacks one of them. But in my situation MySQL is in auto commit mode and I am inserting a new record which fires a trigger. So I don't get the reason it produces a dead lock situation.</p>

<p>Here is my tables schema: </p>

<p>---- users Table ----</p>

<pre><code>CREATE TABLE `users` (
`insta_id` bigint(20) unsigned NOT NULL,
`name` varchar(50) NOT NULL,
`password` varchar(60) NOT NULL,
`gem` int(10) unsigned DEFAULT '20',
`coin` int(10) unsigned DEFAULT '20',
 PRIMARY KEY (`insta_id`),
 UNIQUE KEY `insta_id` (`insta_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
</code></pre>

<p>---like_requests Table---</p>

<pre><code>CREATE TABLE `like_requests` (
`req_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`insta_id` bigint(20) unsigned NOT NULL,
`media_id` varchar(50) NOT NULL,
`remaining_like` int(10) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT '1',
`count` int(10) unsigned NOT NULL,
PRIMARY KEY (`req_id`),
KEY `insta_id` (`insta_id`),
KEY `media_id` (`media_id`),
CONSTRAINT `like_requests_ibfk_1` FOREIGN KEY (`insta_id`) REFERENCES `users`(`insta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=103902 DEFAULT CHARSET=latin1
</code></pre>

<p>---likes Table---</p>

<pre><code>CREATE TABLE `likes` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `insta_id` bigint(20) unsigned NOT NULL,
 `media_id` varchar(50) NOT NULL,
 `req_id` bigint(20) unsigned DEFAULT NULL,
 `date` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`),
 UNIQUE KEY `id` (`id`),
 KEY `req_id` (`req_id`),
 KEY `insta_id` (`insta_id`),
 KEY `media_id` (`media_id`),
 CONSTRAINT `likes_ibfk_1` FOREIGN KEY (`req_id`) REFERENCES  `like_requests`(`req_id`),
 CONSTRAINT `likes_ibfk_2` FOREIGN KEY (`insta_id`) REFERENCES `users`(`insta_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1704209 DEFAULT CHARSET=latin1
</code></pre>

<p>I have a Trigger on likes table, defined as following:</p>

<pre><code>CREATE TRIGGER `after_insert_likes` AFTER INSERT ON `likes`
    FOR EACH ROW BEGIN
        UPDATE users SET users.coin=users.coin+1
            WHERE users.insta_id = NEW.insta_id  LIMIT 1;
        IF NEW.req_id IS NOT NULL THEN
            UPDATE like_requests
                SET  like_requests.remaining_like = like_requests.remaining_like-1
                WHERE like_requests.req_id = NEW.req_id
                  AND like_requests.remaining_like &gt; 0
                LIMIT 1;
        END IF;
    END
</code></pre>

<p>With doing some simple inserts:</p>

<pre><code>    $sql = "INSERT INTO likes (insta_id,media_id,req_id) VALUES (?,?,?);";
    $pdo = $this-&gt;db;

    $statement = $pdo-&gt;prepare($sql);
    $statement-&gt;bindValue(1,$data['id'],PDO::PARAM_INT);
    $statement-&gt;bindValue(2,$data['media_id']);
    $statement-&gt;bindValue(3,$data['req_id'],PDO::PARAM_INT);

    try
    {
        $statement-&gt;execute();
        return GetOkResponseWithMessage($response,"Like was submitted");
    }
    catch (PDOException $exc)
    {
        return GetErrorResponseWithMessage($response,$exc-&gt;getMessage(),500);
    }
</code></pre>

<p>I get the following dead lock error log:</p>

<pre><code>*** (1) TRANSACTION:
TRANSACTION 29031910, ACTIVE 1 sec starting index read
mysql tables in use 4, locked 4
LOCK WAIT 7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 264238, OS thread handle 0x7f6522c6eb00, query id 753506        localhost xxxx updating
UPDATE users SET users.coin=users.coin+1 WHERE users.insta_id=NEW.insta_id   LIMIT 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 1560 n bits 128 index `PRIMARY` of table  `insta_star`.`users` trx table locks 4 total table locks 4  trx id 29031910  lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant  0
*** (2) TRANSACTION:
TRANSACTION 29031909, ACTIVE 1 sec starting index read
mysql tables in use 4, locked 4
7 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 264237, OS thread handle 0x7f65209f8b00, query id 753507 localhost xxxx updating
UPDATE users SET users.coin=users.coin+1 WHERE users.insta_id=NEW.insta_id   LIMIT 1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 14 page no 1560 n bits 128 index `PRIMARY` of table  `insta_star`.`users` trx table locks 4 total table locks 4  trx id 29031909 lock   mode S locks rec but not gap lock hold time 0 wait time before grant 0
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 14 page no 1560 n bits 128 index `PRIMARY` of table    `insta_star`.`users` trx table locks 4 total table locks 4  trx id 29031909   lock_mode X locks rec but not gap waiting lock hold time 0 wait time before grant   0
*** WE ROLL BACK TRANSACTION (2)
</code></pre>

<p>Shouldn't this end up in lock wait instead of a dead lock ?</p>

<p>How can I overcome this issue without restarting transaction ?</p>