MySQL逻辑优化 [英] MySql Logic Optimization
问题描述
当前，我们拥有一个票务管理系统，并且像所有票务系统一样，它需要以循环方式将案件分配给代理商.同样，代理可以同时应用自己的过滤逻辑并在其队列上工作.
Currently we have a ticket management system and like all ticketing systems it needs to assign cases in a round-robin manner to the agents. Also, at the same time the agent can apply their own filtering logic and work on their queue.
问题
- 现在有票的桌子很大，跨越一千万行.
- 绝对不能将一张票证分配给两个不同的用户.
- 要解决上述问题，这就是我们的流程，
- 使用筛选条件和限制0,1触发选择查询
- 然后根据id选择上面查询返回的行并锁定以进行更新.
- 最后我们触发更新，说用户X选择了此案.
- 在执行第3步时，其他用户无法锁定同一案例，因此他们触发3.一次查询可能要多次才能获取下一个可用案例.
- 随着用户数量的增加，这一次在步骤4中变得越来越高.
我们尝试在步骤4本身中选择查询更新，但这会使整个查询变慢.假定这是因为选择查询中有大量行.
We tried doing a select for update in query at step 4 itself, but it makes the entire query slow. Assuming this is because a huge number of rows in the select query.
问题，
- 我们是否需要采取完全不同的方法?
- 在存储过程中进行选择和更新是否可以确保与选择先更新然后更新的结果相同?
P.S-我问了同样的问题stackexchange.
P.S - I have asked the same question stackexchange.
推荐答案
问题是您正在尝试使用MySQL级别锁定来确保不能将票证分配给一个以上的人.这种方法无法检测到票证是否被用户锁定.
The problem is that you are trying to use MySQL level locking to ensure that a ticket cannot be assigned to more than one person. This way there is no way to detect if a ticket is locked by a user.
我将通过向票证表添加2个与锁相关的字段来实现应用程序级锁:施加锁的时间戳和一个告诉您哪个用户持有锁的用户ID字段.与锁相关的字段可以保存在另一个表中(例如，购物车可用于此目的).
I would implement an application level lock by adding 2 lock related fields to the tickets table: a timestamp when the lock was applied and a user id field telling you which user holds the lock. The lock related fields may be held in another table (shopping cart, for example can be used for this purpose).
当用户选择一个票证时，您尝试使用条件更新语句来更新这些锁定字段:
When a user selects a ticket, then you try to update these lock fields with a conditional update statement:
update tickets
set lock_time=now(), lock_user=...
where ticket_id=... and lock_time is null
值代替...由您的应用程序提供.可以使用lock_time is null条件来确保如果另一个用户已经选择了该票证，则以后的用户不会覆盖该锁.在update语句之后，请检查受影响的行数.如果为1，则当前用户已获取该锁.如果为0，则其他人锁定了票证.
Values in place of ... are supplied by your application. lock_time is null criteria is there to make sure that if the ticket has already been selected by another user, then the later user does not override the lock. After the update statement check out the number of rows affected. If it is one, then the current user acquired the lock. If it is 0, then someone else locked the ticket.
如果另一个表中有锁定数据，请对该表中的票证ID字段设置唯一的限制，并使用insert来获取锁定.如果插入成功，则将获得锁.如果失败，则其他用户已锁定票证.
If you have the locking data in another table, then place a unique restriction on the ticket id field in that table and use insert to acquire a lock. If the insert succeeds, then the lock is acquired. If it fails, then another user has locked the ticket.
该锁通常会保留几分钟，此后，您的应用程序必须释放该锁(将锁定字段设置为null或从其他表中删除锁定记录).
The lock is usually held for a number of minutes, after that your application must release the lock (set locking fields to null or delete the locking record from the other table).
这篇关于MySQL逻辑优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
