Is this update, select combination query thread safe?

Question

I have a table called seats, which has a schema like this

id,taken

For each user, I take a random untaken id and assign to that user, here for simplicity say, I make taken =1. The query I am using

   update seats u inner join  (
        SELECT id from seats  
        where taken is null limit 1) s 
   on s.id = u.id set taken = 1;

This query takes a random seat with taken flag null, and for that seat it makes the flag 1. While this query is working fine, is this thread safe?

Consider this scenario, I have two users in parallel. For user1, I select row X, and just before update query is ran user2 checks in, and for that user select query returns the same row as user1. So I will end updating the same row twice.

Is this scenario possible with this query?

Answer 1

ASSESSMENT

Your query is kind of dangerous to the MySQL Query Optimizer.

I have an old post (Problem with MySQL subquery). The question posed involved this query

DELETE FROM test WHERE id =
(SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1);

Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL Documentation implies that some data may disappear during certain optimization phases. In your case, you are accessing just one row to get an id and then update that row. Notwithstanding, the remote possibility of accessing data from a subquery that might not return an id because it can intermittently disappear does not strike me as transaction-safe, let alone thread-safe.

Your query looks similar to the query in that old post

• Non-SELECT query
• SELECT subquery
• Subquery uses LIMIT

This issues goes a limit beyond just being thread-safe or not. Even if it is, you should not trust MySQL's interpretation of grabbing an available seat in the middle of a subquery. You need your query to be transaction-safe.

SUGGESTION #1

My advice to you would be to rewrite this as three queries in a transaction.

Perhaps something like this:

START TRANSACTION;
SELECT id from seats WHERE taken is null limit 1 FOR UPDATE;
SELECT id INTO @available_id from seats WHERE taken is null limit 1;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

or with two queries

START TRANSACTION;
SELECT id INTO @available_id from seats WHERE taken is null limit 1 FOR UPDATE;
UPDATE seats SET taken = 1 WHERE id = @available_id;
COMMIT;

For further info, please read MySQL Documentation on SELECT ... FOR UPDATE.

SUGGESTION #2

I would also make sure that the seats table has an index on the taken column.

If not, add this index

ALTER TABLE seats ADD INDEX (taken);

Do this in Dev/Staging Environment and test your query before doing this in Production.

GIVE IT A TRY !!!

Answer 2

If you are using InnoDB, You can secure your transaction with SELECT ... FOR UPDATE sentence

http://dev.mysql.com/doc/refman/5.7/en/innodb-locking-reads.html

With this, you can perform an exclusive lock on the row while your transaction is not finished. Other sessions will wait until the row lock is released.