title: Avoid mysql deadlock on simple update query
body: <p>Im getting mysql deadlock error on this simple update query :</p>
  
  <pre><code>UPDATE messages SET seen = 1 WHERE fromId=10 AND toId=20 AND seen = 0
  </code></pre>
  
  <p>This query is for flag received messages as seen when users open their chat with another user.</p>
  
  <p>latest deadlock log obtained from innodb status command:</p>
  
  <pre><code>LATEST DETECTED DEADLOCK
  ------------------------
  2020-01-25 14:19:33 0x7f26cc19c700
  *** (1) TRANSACTION:
  TRANSACTION 11389580, ACTIVE 0 sec fetching rows
  mysql tables in use 3, locked 3
  LOCK WAIT 18 lock struct(s), heap size 1136, 128 row lock(s)
  MySQL thread id 952386, OS thread handle 139804764247808, query id 48510845 localhost 127.0.0.1 root updating
  UPDATE `messages` SET `seen`=1 WHERE `seen` = 0 AND `fromId` = 16746 AND `toId` = 20242
  *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 163 page no 1965 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389580 lock_mode X locks rec but not gap waiting
  Record lock, heap no 57 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  0: len 4; hex 80156f39; asc   o9;;
  1: len 6; hex 000000956b88; asc     k ;;
  2: len 7; hex 60000001fa115f; asc `     _;;
  3: len 4; hex 74657874; asc text;;
  4: len 8; hex d8b3d984d8a7d985; asc         ;;
  5: len 1; hex 81; asc  ;;
  6: len 5; hex 99a56737d7; asc   g7 ;;
  7: len 4; hex 8000416a; asc   Aj;;
  8: len 4; hex 80003e7d; asc   &gt;};;
  
  *** (2) TRANSACTION:
  TRANSACTION 11389578, ACTIVE 0 sec fetching rows
  mysql tables in use 3, locked 3
  1515 lock struct(s), heap size 172240, 14139 row lock(s)
  MySQL thread id 952395, OS thread handle 139804609726208, query id 48510843 localhost 127.0.0.1 root Searching rows for update
  UPDATE `messages` SET `seen`=1 WHERE `seen` = 0 AND `fromId` = 16252 AND `toId` = 15997
  *** (2) HOLDS THE LOCK(S):
  RECORD LOCKS space id 163 page no 1965 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389578 lock_mode X locks rec but not gap
  Record lock, heap no 57 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  0: len 4; hex 80156f39; asc   o9;;
  1: len 6; hex 000000956b88; asc     k ;;
  2: len 7; hex 60000001fa115f; asc `     _;;
  3: len 4; hex 74657874; asc text;;
  4: len 8; hex d8b3d984d8a7d985; asc         ;;
  5: len 1; hex 81; asc  ;;
  6: len 5; hex 99a56737d7; asc   g7 ;;
  7: len 4; hex 8000416a; asc   Aj;;
  8: len 4; hex 80003e7d; asc   &gt;};;
  
  *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  RECORD LOCKS space id 163 page no 1649 n bits 280 index PRIMARY of table `game`.`messages` trx id 11389578 lock_mode X locks rec but not gap waiting
  Record lock, heap no 48 PHYSICAL RECORD: n_fields 9; compact format; info bits 0
  0: len 4; hex 8013206d; asc    m;;
  1: len 6; hex 000000895755; asc     WU;;
  2: len 7; hex 800000010d0110; asc        ;;
  3: len 4; hex 74657874; asc text;;
  4: len 12; hex d8a8d8aed8b4db8cd8afd985; asc             ;;
  5: len 1; hex 80; asc  ;;
  6: len 5; hex 99a5627af5; asc   bz ;;
  7: len 4; hex 8000416a; asc   Aj;;
  8: len 4; hex 80003e66; asc   &gt;f;;
  
  *** WE ROLL BACK TRANSACTION (1)
  </code></pre>
  
  <p>And the table structure :</p>
  
  <pre><code>-- Table structure for table `messages`
  --
  
  CREATE TABLE `messages` (
    `id` int(11) NOT NULL,
    `type` varchar(24) COLLATE utf8mb4_bin NOT NULL DEFAULT 'text',
    `content` varchar(400) COLLATE utf8mb4_bin NOT NULL,
    `seen` tinyint(1) NOT NULL DEFAULT '0',
    `createdAt` datetime NOT NULL,
    `fromId` int(11) DEFAULT NULL,
    `toId` int(11) DEFAULT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
  
  -- Indexes for table `messages`
  
  ALTER TABLE `messages`
    ADD PRIMARY KEY (`id`),
    ADD KEY `fromId` (`fromId`),
    ADD KEY `toId` (`toId`),
    ADD KEY `messages_created_at` (`createdAt`),
    ADD KEY `messages_seen` (`seen`);
  </code></pre>
  
  <p>How can i avoid this mysql deadlocks ?anything wrong with my query ?</p>