Re: UPSERT with non-unique index

On 02/08/2020 22:46, Lew Pitcher wrote:
> 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;
> 
> 
I don't use SQL often enough to remember that it has transactions these 
days :-(

Note that you appear to need to be using innoDB or another advanced 
engine and not MyISAM if you want to use transactions


-- 
"In our post-modern world, climate science is not powerful because it is 
true: it is true because it is powerful."

Lucas Bergkamp