MySQL query statement conflict

Question

Consider the following MySQL table, let's name it MyTable:

-------------------
| Id | AssignedTo |
-------------------
| 1  | NULL       |
| 2  | NULL       |
| 3  | NULL       |
-------------------

Let's say I have a script, which when accessed, performs the following query:

UPDATE MyTable SET AssignedTo = '@userid' WHERE AssignedTo IS NULL LIMIT 1

The purpose of the script, is to assign an id to a user from the specific table. Is there a possiblity, that if the above mentioned script is called at the exact same time, by two different users, one of the remains without an assigned ID because the two MySQL statements overwrite each other?

Answer 1

Is there a possiblity, that if the above mentioned script is called at the exact same time, by two different users, one of the remains without an assigned ID because the two MySQL statements overwrite each other?

No. Individual queries in SQL are performed by the server as if they were serialized: as if one completes before the next begins.

But beware: when you use LIMIT without ORDER BY, the row chosen by SQL is unpredictable. It chooses any row it wants. Unpredictable is like random but worse: Random generally implies it may choose a different row every time. Unpredictable in the SQL world means it chooses the same row every time, until it doesn't a year after your application goes into production.

Also beware: The SQL in your question will silently do nothing if no rows remain with a NULL value.

Answer 2

Even if it's not run at the exact same time, that query by itself makes no guarantee that all the unassigned rows will get assigned. It explicitly limits itself to updating only a single row.

If, by any circumstances, you have more than one row with AsssignedTo IS NULL at any given moment, you will assign only one of those (chosen arbitrarily).

There is no obvious solution to this based only on the one query you've shown there. Is it appropriate to just remove the LIMIT 1 and assign all unassigned rows? That would mean if two people are updating at the same time, one of them (arbitrarily) would get assigned to all the rows. That's probably not good.

A better solution would take into account what's happening when those NULLs are written into the database in the first place, or would have some other logic for explicitly selecting which unassigned rows to assign, instead of just 'WHERE AssignedTo IS NULL`.