Using custom locks to insert rows safely

Question

I basically need an UPSERT functionality where a row is created once a day and updated from then on until the next day. From a business perspective, there must be one or zero rows per day.

My problem obviously being concurrency and two sessions creating that one row simultaneously.

As I understand, SELECT ... WHERE day = today FOR UPDATE does not prevent a second session from instantly also getting a (0 rows) result, if that row does not exist.

I created a table just for the purpose of locking a certain 'resource' e.g. SELECT * FROM my_semaphore WHERE resource = 'daily' FOR UPDATE and then proceed to do my actual business logic on the other tables (that I don't need to lock now). The row with resource = 'daily' definitely exists, so I know every other sessions, that wants this resource needs to wait until the first session commits their transaction.

Is there a smarter approach, am I missing something?

Answer 1

Assuming that you have a primary key for the day-column and the update functionality adds value to your day-row, you can use the ON DUPLICATE KEY UPDATE:

INSERT INTO yourtable (day, value) 
VALUES(current_date(), YOURVALUE) 
ON DUPLICATE KEY UPDATE value = value + YOURVALUE;