Re: UPSERT with non-unique index

On 02/08/2020 18.55, Stanimir Stamenkov wrote:
> [I've posted this on Stack Overflow: 
> https://stackoverflow.com/questions/63194116/upsert-with-non-unique-index]
> 
> I need to implement concurrent-safe UPSERT using a non-unique key and 
> avoid unnecessary auto-increment of ID.
> 
> Traditional INSERT ... ON DUPLICATE KEY [1] doesn't work for me, so I'm 
> performing:
> 
>      INSERT INTO table (col1, col2, col3, col4, col5)
>      SELECT 1, 2, 'value3', 'value4', 'value5'
>       WHERE NOT EXISTS (SELECT 1
>                           FROM table
>                          WHERE col3 = 'value3'
>                            AND col4 = 'value4'
>                            AND col5 = 'value5')
> 
> then if it results in no row inserted, I'm performing:
> 
>      UPDATE table
>         SET col1 = col1 + 1,
>             col2 = MAX(col2, 2)
>       WHERE col3 = 'value3'
>         AND col4 = 'value4'
>         AND col5 = 'value5'
> 
> There's an index:
> 
>      CREATE INDEX ON table (col3, col4, col5)
> 
> It is non-unique as there are legacy data that does not allow me to 
> declare it unique.  Newer records, however, should not have duplicated 
> (col3, col4, col5) rows.
> 
> Unsurprisingly, using the given INSERT statement I'm getting mixed 
> results trying to execute it concurrently from two sessions.  I can see 
> the second session blocking until the first one commits its transaction, 
> but then the second transaction is also able to insert a new row 
> sometimes (or sometimes it achieves the expected of avoiding to insert a 
> duplicate (col3, col4, col5) row).

Maybe the blocking is more then way you are testing things than what in 
reality happens.

I would suggest you use

LOCK TABLES table WRITE;

<do what you did before>

UNLOCK TABLES;


LOCK TABLES table WRITE;

UNLOCK TABLES;

https://www.mysqltutorial.org/mysql-table-locking/
https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

-- 

  //Aho