Re: UPSERT with non-unique index

On 02/08/2020 19:15, J.O. Aho wrote:
> 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
> 
I don't know how SQL manages it, but if you have two asynchronous 
instances of read-modify-write, where what is written depends on what is 
read, then you MUST make the read-modify-write cycle ATOMIC - that is 
uninterruptible, by another process doing the same.

You need locking, and the attendant danger that if aprocess crashes with 
locks set...

-- 
The biggest threat to humanity comes from socialism, which has utterly 
diverted our attention away from what really matters to our existential 
survival, to indulging in navel gazing and faux moral investigations 
into what the world ought to be, whilst we fail utterly to deal with 
what it actually is.