在 MySQL 中增加一个字段是原子的吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4358732/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is incrementing a field in MySQL atomic?
提问by Newb
I'm making a web site where I would like to increment a counter in a standard MyISAM table.
我正在制作一个网站，我想在标准 MyISAM 表中增加一个计数器。
Simplified example:
简化示例：
UPDATE votes SET num = num + 1;
Will this cause problems if multiple connections are doing the same query, or will MySQL take care of it and lock the table or something to make sure that there are no conflicts?
如果多个连接执行相同的查询，这会导致问题，还是 MySQL 会处理它并锁定表或其他东西以确保没有冲突？
采纳答案by Lachezar Balev
MyISAM tables use table level locking. This means that the whole table will be locked during the execution of your update query. So the answer for your simplified use case is: yes, this is thread safe. But this may not be the case if you use another storage engine or your update includes multiple tables.
MyISAM 表使用表级锁定。这意味着在执行更新查询期间将锁定整个表。所以你的简化用例的答案是：是的，这是线程安全的。但如果您使用其他存储引擎或您的更新包含多个表，则情况可能并非如此。
Here is a quote from the MySQL manual for more clarity:
为了更清楚，这里引用了 MySQL 手册：
Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done.
表锁定使许多会话可以同时从一个表中读取，但是如果一个会话要写入一个表，它必须首先获得独占访问权限。在更新期间，想要访问此特定表的所有其他会话必须等到更新完成。
You can also consider using auto increment columns, transactions or external synchronization if that fits to your design.
如果适合您的设计，您还可以考虑使用自动增量列、事务或外部同步。
Cheers!
干杯!
回答by TraderJoeChicago
The write is atomic but an increment also requires a read. So the question is: Are you sure the read is safe, in other words, are you sure another thread doing the increment will not end up with the same value to be incremented? I have doubts. The 100% correct way of doing this would be.
写入是原子的，但增量也需要读取。所以问题是：你确定读取是安全的，换句话说，你确定另一个执行增量的线程不会以相同的值被增量？我有疑问。这样做的 100% 正确方法是。
-- begin transaction here
select counter from myCounters where counter_id = 1 FOR UPDATE;
-- now the row is locked and nobody can read or modify its values
update myCounters set counter = ? where id = 1;
-- set ? to counter + 1 programmatically
commit; -- and unlock...
回答by grahamparks
Yes, the table (or rows in InnoDB format databases) is automatically locked when you execute an update query.
是的，当您执行更新查询时，表（或 InnoDB 格式数据库中的行）会自动锁定。
回答by Ignacio Vazquez-Abrams
This form of UPDATEis atomic. Other forms of UPDATEcan be made atomic by using transactions with SELECT ... FOR UPDATE.
这种形式UPDATE是原子的。其他形式的UPDATE可以通过使用事务与 SELECT ... FOR UPDATE.
回答by mavarazy
Had the same issue, although query was more complicated:
有同样的问题，虽然查询更复杂：
UPDATE CLB_SYNC_T SET PENDING_MESSAGES = PENDING_MESSAGES + ? WHERE USER_ID = ?
Using MyISAMas a default engine did not help, so I fallback to SELECT FOR UPDATEuse.
使用MyISAM作为默认引擎没有帮助，所以我回退到SELECT FOR UPDATE使用。
With SELECT FOR UPDATEperformance improved ~ 10 times, since MySQL did not lock whole table, to make a row update.
使用SELECT FOR UPDATE性能提高了约 10 倍，因为 MySQL 没有锁定整个表，以进行行更新。
回答by guykaplan
Another approach when using InnoDB is using unique indexon multiple column as follow:
使用 InnoDB 时的另一种方法是在多列上使用唯一索引，如下所示：
Table 'Sessions' { unique_key(browser_session_id,profile_id) // ensures that inserting 1 entry per session will occur once }
Table 'Sessions' { unique_key(browser_session_id,profile_id) // 确保每个会话插入 1 个条目将发生一次 }
select count(browser_session_id) from Sessions
从会话中选择 count(browser_session_id)
Will guarantee result of unique sessions, as multiple sessions per user is not allowed.
将保证唯一会话的结果，因为不允许每个用户进行多个会话。
Conclusions
结论
Advantage
Each insert does require a pre-select.
Disadvantage
It is not suitable for all cases.
May slow down write performance, and requires extra management
优势
每个插入都需要预选。
坏处
它并不适合所有情况。
可能会降低写入性能，并且需要额外的管理
