Geek Answers Handbook

Is this an update, choose a secure response chain?

I have a table called places that has such a schema

id taken

For each user, I take a random untaken id and assign it to this user, here for simplicity I will say that I did take = 1. The request that I use

   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 request takes a random place with the accepted null flag, and for this place it makes flag 1. While this request works fine, is this streaming security?

Consider this scenario, I have two users in parallel. For user1, I select the line X, and just before the update request is executed, user2 checks it, and for this user request, the request returns the same line as user1. Therefore, I will finish updating the same line twice.

Is this scenario possible with this query?

+4
source share
2 answers

In mysql there is a simple, almost incredible solution:

update seats set taken = 1
where taken is null
limit 1

This syntax limits the number of lines affected by the update process to 1, reaching your intent using the special mysql extension for the update syntax.

Naturally, this is an officially supported extension, it is an atomic action and completely thread safe.

Although neither your code nor your question allows you to fix which identifier has been updated, you can write it using a custom variable:

update seats set
taken = 1,
id = (@id := id)
where taken is null
limit 1;

select @id id;

The value returned from the selected one will be either the updated identifier or zero if no row has been updated.

+2
source

SQL, , , , , . .

, /, - . , . , ( ) , - . , , . MySQL , .

, . , , .

SQL, , . , , :

UPDATE seats
SET
    taken = 1
WHERE
    taken IS NULL
ORDER BY RAND()
LIMIT 1

, WHERE taken IS NULL - . , NOT NULL, . , 0 NULL, !

+1

More articles:


All Articles