MySQL: How to hold lock and make other threads wait for an insert that hasn’t happened yet

I’m confronted by a seemingly simple problem that has turned out to be pretty hard to figure out. We want to keep a record of every time we’re presented with a piece of marketing (a lead) so we don’t purchase it more than once in a 90 day period. Many lead providers can present us the same lead many times, often concurrently. We want to return an “accept” to exactly one lead provider.

So let’s talk about the scenario that works: We have seen the material in the last 90 days and have a record on the table and there are 3 providers presenting the lead concurrently:

select count(id) from recent_leads where 
  last_seen_at >= '2019-10-11 00:00:00' 
  and email = 'yes@example.com' for update;

Thread1 arrives first, and acquires the lock. MySQL returns to Thread1:

+-----------+
| count(id) |
+-----------+
|         1 |
+-----------+
1 row in set (0.00 sec)

Thread1 issues a new insert:

insert into recent_leads (email, last_seen_at)
  values ('yes@example.com', '2019-12-12 18:23:35');

Thread2 and Thread3 will blocking trying to execute the same statement until Thread1 commits or issues a rollback on it’s transaction. Then Thread2 and Thread3 compete for the lock and the same process happens.

So that works as expected and we’re happy with it. The wheels come off when there isn’t a record.

Thread1, Thread2, and Thread3 all issue the same SQL as above. MySQL now returns this to all three threads immediately, whereas before, only one Thread would proceed:

+-----------+
| count(id) |
+-----------+
|         0 |
+-----------+
1 row in set (0.00 sec)

All three threads now attempt the insert. Two of them will get an error:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Is there a way we can get MySQL to behave like the first scenario all the time? We want Thread2 and Thread3 to block ideally.

Thank you, -Jonathan

Answer

So we ended up dropping locking. Instead, we commit the row in a separate transaction, then select back all rows for an email minus the last_insert_id(). If we find a row with a lower primary key, we assume another thread is already handling the request. This is nice because it’s lock free which is a bit easier to debug.

Advertisement

Answer