Re: UPSERT with non-unique index

On 03/08/2020 19.45, Stanimir Stamenkov wrote:
> Sun, 2 Aug 2020 20:15:03 +0200, /J.O. Aho/:
> 
>> 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
> 
> Wouldn't this basically block concurrent inserts/updates?  I don't want 
> to block all on the same table trying to insert/update different sets of 
> data.
> 

Yes, it will, the good thing (or sometimes bad) is that the others will 
be waiting for the lock to be released and then do their task.

Unless you do a lot in the transaction, the time the table is locked 
tends to be a lot. If the case is that you do a lot, then I would 
recommend you to break out things, so that the only thing you will do is 
the check for existing values and based on that do the update or insert.
It could be good to make an insert SP that takes care of everything 
(begin transaction (if innodb),locking table, check if key already 
exists and do update else insert, unlock table, commit transaction (if 
innodb)).
See https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

Without the begin transaction/commit transaction, there is a risk for 
deadlocks, which means that the table never gets unlocked (could happen 
if the locking thread unexpectedly dies) and everyone else who wait for 
access the table will keep on waiting forever.

Other alternative is to move the old data that can have more than one 
row with the same key to a history table and in the current table you 
make the key to a primary key. This will complicate things when you need 
to fetch data, you could of course be able to make a view that selects 
with a union from both tables, this will make it easier when you want to 
join things, but keep in mind it may slow things down.
Alternative you have a switch telling you if you need current data or 
historical data, if you need the historical data, then use the history 
table otherwise the current table.
As I don't know the needs, I can't give you a best option, but some 
options you can look at and then decide yourself which works for you.


-- 

  //Aho