CODEWITHSUNDEEP
mysql
TareK Khoury
TareK Khoury

Reputation: 13021

MySQL block selecting/reading a specific row

I am developing a call center IVR system with heavy traffic. I want to give each caller a random FREE representative to chat with.

What i do now is:

SELECT admin FROM admins WHERE live = 1 AND available = 1 ORDER BY RAND() LIMIT 1

After i get the free representative, i update him to be unavailable as so:

UPDATE admins SET available = 0 WHERE admin = XXX

After testing this, i noticed than caller sometimes end up with the same representative at the same time if they call at almost the same time.

I have tried adding "FOR UPDATE" to the select query, but it's not really helping with locking the row for select.

Any idea how can this problem be approached?

Upvotes: 1

Views: 267

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

This is called a race condition. One method is to lock the table, but that can slow things down. Another idea is to do the update and fetch the id at the same time:

UPDATE admins 
    SET available = if(@id := admin, 0, 0)
    WHERE live = 1 AND available = 1
    ORDER BY RAND()
    LIMIT 1;

SELECT @id;

This will return the id of the affected row.

Upvotes: 1

Related Questions