Re: UPSERT with non-unique index

On August 2, 2020 17:28, The Natural Philosopher wrote:

> 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...

I'd roll the whole thing in a transaction

START TRANSACTION;

-- your sql goes here

COMMIT;


-- 
Lew Pitcher
"In Skills, We Trust"