How do I correctly lock a MYSQL table on a concurrent environment?

Question

0.00/5 (No votes)

See more:

Hello everybody, I am struggling about how to grant the consistency of the following operation in this scenario: we are developing a reservation portal, where you can sign for courses. Every course has a number of direct reservations: when the available reservations are over, users can still sign but the fall in the waiting list.

So this is the situation: for example, let's imagine that a course has 15 slots available and I make my reservation. In a simple logic, as I made my reservation the system has to decide if I am in the direct reservation list (one of the 15 available slots) or in the waiting list. In order to do so, we must count if the total direct reservations is less than the total available reservations, something like this in pseudocode:

SQL

INSERT new_partecipant IN table_partecipants;
(my_id) = SELECT @@IDENTITY;
(total_reservations_already_made) = SELECT COUNT(*) FROM table_partecipants WHERE flag_direct_reservation=1;
if total_reservations_already_made <= total_reservations_available then
UPDATE table_partecipants SET flag_direct_reservation=1 WHERE id=my_id

The question is: how I can manage the concurrency in order to be sure that two subscriptions are managed correctly? If I have only one reservation left and two users apply at the same time, I think it's possible that the result of the COUNT operation can give the same result to both the requests and insert both the users in the list.

What is the correct way of locking (or similar procedure) in order to be sure that if a user starts the subscription procedure no one can finish the same task before the request has been completed?

What I have tried:

At the moment we are simply counting and updating as described, but in case of high concurrent requests I think it can generate wrong subscriptions.

Posted 26-Jun-17 6:08am

PGagliardi

Updated 27-Jun-17 2:49am

Add a Solution

Comments

Michael_Davies 26-Jun-17 12:16pm

Have you read the MySQL manual on locking tables?

https://dev.mysql.com/doc/refman/5.7/en/lock-tables.html

PGagliardi 27-Jun-17 2:55am

Of course, but I have no practical experience in doing so and I don't know the risks, furthermore I have read that locking tables is not a suggested procedure,giving the fact that the best practice is to use transactions (but really I don't imagine how to define a transaction in this case).

ZurdoDev 26-Jun-17 12:25pm

I would not try to lock tables. I would combine into a single statement. I do not do MySql so I can show you what Sql would look like and MySql would be similar.

INSERT INTO table1 (field1, flag_direct_reservation)
SELECT @field1, CASE WHEN (SELECT COUNT(*) FROM sometable) > (SELECT COUNT(*) FROM othertable) THEN 1 ELSE 0 END

PGagliardi 27-Jun-17 8:34am

This sounds good! But can you provide a working example also for an UPDATE statement?

Tried this:

UPDATE mytable SET subscription = (CASE WHEN (SELECT COUNT(*) FROM mytable WHERE course=course_id) > max_reservations THEN 0 ELSE 1 END) WHERE id=id_subscription

but I got the following error:

"You can't specify target table 'mytable' for update in from clause".

ZurdoDev 27-Jun-17 8:53am

You'll have to look it up for MySql, sorry.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

PGagliardi · Answer 1 · 2017-06-27T02:49:00

Ok, it seems we have found a solution::

INSERT

SQL

INSERT INTO table1 (field1, field2, ..., fieldN, flag_direct_reservation)
SELECT @field1, @field2, ..., @fieldN, ,CASE WHEN (SELECT COUNT(*) FROM sometable WHERE course=@course_id) > @max_part THEN 1 ELSE 0 END

UPDATE
(only for determining the subscription status, in case of subscription deletion)

SQL

UPDATE corsi_mytable p1 INNER JOIN 
(
    SELECT COUNT(*) as actual_subscritions
    FROM mytable
    WHERE course=@course_id
)p2 
SET p1.flag_direct_reservation= CASE WHEN p2.actual_subscritions > @max_part THEN 0 ELSE 1 END 
WHERE p1.id =@first_waiting_id;