MySQL插入：竞态条件 [英] MySQL Insertion: Race condition

问题描述

我想知道是否存在一个真实情况下，其中种族条件问题实际上可能发生在插入查询。所以我有一个表用户与以下字段：

I would like to know if there is a real case scenario in which race condition problems can actually occur on an insertion query. So I have a table "User" with the following fields:

User:
iduser | idcompany | name | email

我对此表使用复合主键（iduser，idcompany）。这些字段都没有设置为AUTO_INCREMENT。我通过一个会话变量获得字段idcompany的值，所以这里没有一个真正的问题。但是，我使用一个getNextUserId（）函数通过如下的选择查询获得下一个有效的iduser值：

I use a composite primary key for this table that is (iduser, idcompany). None of these fields is set to AUTO_INCREMENT. I get the value of the field "idcompany" through a session variable, so there's is not a real problem in this. However, I use a getNextUserId() function to get the next valid iduser value through a select query like this:

SELECT MAX(iduser) + 1 AS next_iduser FROM User WHERE idcompany = {myCompanyId};

我不知道是否有可能插入（iduser，idcompany）的重复组合在数据库中由于竞争条件，如果是这样，这种情况如何可能。 MySQL不锁定表插入？不会的（iduser，idcompany）的重复组合简单被拒绝，或者我真的可以有一个重复的主键在我的表中？我知道完全防止竞争条件的策略，如使用AUTO_INCREMENT主键，使用SQL事务或手动锁定用户表，但我想了解这种情况下可能的竞争条件的背后的机制，什么是真正的问题，这个实现。现在，没有一个字段需要在我的表中是唯一的明显的原因，但我想知道，如果一个UNIQUE约束iduser例如会改变我面对的情况，为什么会发生这种情况。

I wonder if there is any case in which a duplicate combination of (iduser, idcompany) could be inserted in the database because of race condition and if so how is this scenario possible. Doesn't MySQL lock the table on insertion? Wouldn't a duplicate combination of (iduser, idcompany) simple be rejected or I could really have a duplicate primary key in my table? I am aware of strategies to totally prevent race condition like using AUTO_INCREMENT primary key, using SQL transactions or manually lock the "User" table but I would like to understand the mechanism behind a possible race condition in this case and what are the real problems in this implementation. Right now, none of these fields is required to be unique in my table for obvious reasons, but I would like to know if a UNIQUE constraint on iduser for example would alter the scenario I am facing and why does this happen.

推荐答案

您在iduser，idcompany字段上写了一个复合主键。此约束将阻止表具有重复的iduser，idcompany对。没有锁定可能发生的最糟糕的事情是，用户的创建将通过违反主键来防止。

You wrote you had a composite primary key on iduser, idcompany fields. This constraint will prevent the table from having duplicate iduser, idcompany pairs. The worst that could happen without locking is that the a creation of a user will be prevented by the violation of the primary key.

这篇关于MySQL插入：竞态条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！