如何在并发环境中正确锁定MYSQL表？ [英] How do I correctly lock a MYSQL table on a concurrent environment?

问题描述

大家好，我正在努力解决如何在这种情况下授予以下操作的一致性：我们正在开发一个预订门户，您可以在其中签署课程。每个课程都有一些直接预订：当可用的预订结束时，用户仍然可以签名，但是等待名单中有所下降。



所以情况如下：例如，让我们想象一个课程有15个可用的插槽，我可以预订。在一个简单的逻辑中，当我进行预订时，系统必须决定我是否在直接预订列表（15个可用插槽之一）或等待列表中。为了做到这一点，我们必须计算总直接预订是否小于可用预订总量，如伪代码这样：

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:

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

问题是：我如何管理并发性以确保正确管理两个订阅？如果我只剩下一个预订并且两个用户同时申请，我认为COUNT操作的结果可能会给两个请求带​​来相同的结果并插入列表中的用户。



锁定（或类似程序）的正确方法是什么，以确保如果用户启动订阅程序，在请求完成之前没有人可以完成相同的任务？



我的尝试：



目前我们只是如上所述计数和更新，但是在高并发请求的情况下，我认为它可以生成错误的订阅。

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.

推荐答案

好的，似乎我们找到了一个解决方案:: < br $> b $ b

INSERT

Ok, it seems we have found a solution::

INSERT

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)

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;

这篇关于如何在并发环境中正确锁定MYSQL表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！