mysql is handling one sql query at the time?

Question

if you got 100 000 users, is mysql executing one sql query at the time?

cause in my php code i check if a certain row exists. if it doesn't it creates one. if it does, it just update the row counter.

it crossed my mind that perhaps 100 users are checking if the row exists at the same time, and when it doesn't they all create one row each.

if the mysql is handling them sequentially i know that it won't be an issue. then one user will check if it exists, if not, create it. and the other user will check if it exists, and since that's the case, it just update the counter.

but if they all check if it exists at the same time and let's say it doesn't, then they all create one row and the whole table structure will fail.

would be great if someone could shed a light on this topic.

Answer 1

If you put the check and insert into a transaction then you can avoid this problem. This way, the check and create will be run as one one query and there shouldn't be any confusion

Answer 2

Use a UNIQUE constraint or, if viable, make the primary key one of your data items and the SQL server will prevent duplicate rows from being created. You can even use the "ON DUPLICATE KEY UPDATE ..." syntax to specify the alternate operation if the row already exists.

From your comments, it sounds like you could use the user_id as your primary key, in which case, you'd be able to use something like this:

INSERT INTO usercounts (user_id,usercount)
VALUES (id-goes-here,1)
ON DUPLICATE KEY UPDATE usercount=usercount+1;