1032851 - Re-run UPSERT on duplicate key error

Status: RESOLVED WONTFIX

(Socorro :: Backend, task)

Description

[Selena Deckelmann :selenamarie :selena]

We have a potential race condition in the UPSERT when the following occurs: 

* A transaction including the UPSERT starts and completes the UPDATE portion of the query, and the row does not exist
* A second transaction starts including the UPSERT and completes the UPDATE portion of the query, and the row still does not exist
* The INSERT portion of query 1 completes, creating the row
* The INSERT portion of query 2 attempts, but fails EVEN THOUGH THE WHERE CLAUSE CHECKS FOR THE EXISTANCE FIRST because query 1 completed at exactly the wrong time, and thus triggers a duplicate key error.

Wooo, concurrency!

So, to get around this, we can just run query 2 twice. When it runs the second time, the row will exist and an UPDATE will complete successfully inside the second UPSERT attempt.

There are some alternatives, but they are much more invasive and will cause a lot more failures of queries for no real gain in functionality for Socorro.

Comment 1

[Lonnen :lonnen]

per 1361394, plan to delete or refactor these portions of the db