title: Real-time deduplication strategy for relational DB
body: <p>I have a messages table with some fields like <code>id</code>, <code>version</code>, <code>timestamp</code></p>
  
  <p>I'm trying to insert and retrieve messages with real-time deduplication logic, implemented in stored procedures.</p>
  
  <ul>
  <li>On insert, if a message with given <code>id</code> already exists in the table, I invoke a complex deduplication logic (a number of IF/ELSE statements) and then update messages's <code>version</code>, <code>timestamp</code>, etc. Otherwise I insert a new row.</li>
  <li>On retrieve, I search for the oldest message and not just select it, but also update a few fields (changing state, setting transient_token, etc)</li>
  </ul>
  
  <p>The problem is, <strong>it does not work as fast as I need</strong>: ~ 200 rps on insert and the same on retrieve</p>
  
  <p>Separately my stored procedures work good, but when I execute both of them concurrently, they start fighting for locks under load > 30 rps, performance dramatically decreases and deadlocks appear periodically</p>
  
  <p><strong>Are there some best practices to solve my problem?</strong> I feel that the whole approach sucks, not my concrete implementation.</p>
  
  <p>May be I should use some temp buffer table for all incoming messages and perform dedup logic afterwords? I would appreciate any ideas.</p>
  
  <p>* I can't use nosql DB in my case (management decision), I need to gain max performance with Mysql/MariaDb</p>