mysql 插入竞争条件
你如何在 MySQL 中停止竞争条件?手头的问题是由一个简单的算法引起的:
How do you stop race conditions in MySQL? the problem at hand is caused by a simple algorithm:
- 从表格中选择一行
- 如果它不存在，插入它
然后要么你得到一个重复的行，要么你通过唯一/主键阻止它，一个错误.
and then either you get a duplicate row, or if you prevent it via unique/primary keys, an error.
现在通常我认为事务在这里有帮助，但由于该行不存在，事务实际上并没有帮助(或者我错过了什么?).
Now normally I'd think transactions help here, but because the row doesn't exist, the transaction don't actually help (or am I missing something?).
LOCK TABLE 听起来有点矫枉过正，尤其是当表每秒更新多次时.
LOCK TABLE sounds like an overkill, especially if the table is updated multiple times per second.
我能想到的唯一其他解决方案是针对每个不同的 id 使用 GET_LOCK()，但没有更好的方法吗?这里也没有可扩展性问题吗?而且，对每个表都这样做听起来有点不自然，因为对我来说这听起来像是高并发数据库中的一个非常普遍的问题.
The only other solution I can think of is GET_LOCK() for every different id, but isn't there a better way? Are there no scalability issues here as well? And also, doing it for every table sounds a bit unnatural, as it sounds like a very common problem in high-concurrency databases to me.
推荐答案
你想要的是 锁表
或者如果这看起来太过分了如何INSERT IGNORE检查该行是否实际插入.
or if that seems excessive how about INSERT IGNORE with a check that the row was actually inserted.
如果使用 IGNORE 关键字，则错误执行 INSERT 时发生的声明被视为警告相反.
If you use the IGNORE keyword, errors that occur while executing the INSERT statement are treated as warnings instead.
相关文章