Understanding MySQL concurrency/isolation levels

Question

I am working on the backend of an application that needs to protect an external API from too many requests per user per month. So I need to keep track of number of requests from each user. I have a lot of experience with concurrent programming but almost no experience with db management or MySQL,

So, suppose I want to execute the equivalent of the following pseudocode, where I mix SQL statements with application-level logic, and where lookups is a table:

mutex mtx;

set @userid = 'usrid1';
set @date = CURDATE();
set @month = CONCAT_WS('-', YEAR(@date), MONTH(@date));

mtx.lock()

select counter from lookups where userid=@userid and month=@month;

if returned rows == 0:
    insert into lookups set month=@month, userid=@userid, counter=1;
else:
    update lookups set counter=counter+1;

mtx.unlock()

Except, of course, I don't have access to that mutex. At first I thought it would be enough to just wrap the whole thing inside a transaction, but upon closer inspection of the MySQL reference it seems that may not be enough to avoid possible race conditions, such as two threads/processes reading the same counter value. Is it good enough then, in mysql with default settings, to do the following:

set @userid = 'usrid1';
set @date = CURDATE();
set @month = CONCAT_WS('-', YEAR(@date), MONTH(@date));

start transaction;

select counter from lookups where userid=@userid and month=@month for update;

if returned rows == 0:
    insert into lookups set month=@month, userid=@userid, counter=1;
else:
    update lookups set counter=counter+1;

commit;

From what I can glean from the reference, it looks like it should be enough, and it should cause neither race conditions nor deadlocks, but the reference is long winded and complex, so I wanted to ask here to be sure. Performance isn't important. The reference states that MySQL's default isolation level is REPEATABLE READ.

Answer 1

I suggest this solution:

create table lookups (userid varchar(20), yearmonth date, counter int, primary key (userid, yearmonth));

insert into lookups set userid = 'usrid1',
  yearmonth = date_format(curdate(), '%Y-%m-01'),
  counter = last_insert_id(1) 
on duplicate key update 
  counter = last_insert_id(counter + 1);

select last_insert_id(); -- returns the new value, whether 1 or the updated value.

This means you don't have to check if a row exists, it will either insert it or update it atomically.

The last_insert_id(<expression>) trick is documented at the end of the entry for that function: https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_last-insert-id