title: Mysql Innodb deadlock problems on REPLACE INTO
body: <p>I want to update the statistic count in mysql.</p>
  
  <p>The SQL is as follow:</p>
  
  <pre><code>REPLACE INTO `record_amount`(`source`,`owner`,`day_time`,`count`) VALUES (?,?,?,?)
  </code></pre>
  
  <p>Schema :</p>
  
  <pre><code>CREATE TABLE `record_amount` (
     `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
     `owner` varchar(50) NOT NULL ,
     `source` varchar(50) NOT NULL ,
     `day_time` varchar(10) NOT NULL,
     `count` int(11) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `src_time` (`owner`,`source`,`day_time`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  </code></pre>
  
  <p>However, it caused a DEADLOCK exception in multi-processes running (i.e. Map-Reduce). </p>
  
  <p>I've read some materials online and confused about those locks. I know innodb uses row-level lock. I can just use the table-lock to solve the business problem but it is a little extreme. I found some possible solutions:</p>
  
  <ol>
  <li>change <code>REPLACE INTO</code> to transaction with <code>SELECT id FOR UPDATE</code> and <code>UPDATE</code></li>
  <li>change <code>REPLACE INTO</code> to <code>INSERT ... ON DUPLICATE KEY UPDATE</code></li>
  </ol>
  
  <p>I have no idea that which is practical and better. Can someone explain it or offer some links for me to read and study? Thank you!</p>