在 MySQL 中增加一个字段是原子的吗? [英] Is incrementing a field in MySQL atomic?

问题描述

我正在制作一个网站，我想在标准 MyISAM 表中增加一个计数器.

I'm making a web site where I would like to increment a counter in a standard MyISAM table.

简化示例:

UPDATE votes SET num = num + 1;

如果多个连接执行相同的查询，这会导致问题吗，或者 MySQL 会处理它并锁定表或其他东西以确保没有冲突吗?

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?

推荐答案

MyISAM 表使用表级锁定.这意味着在执行更新查询期间将锁定整个表.所以你的简化用例的答案是:是的，这是线程安全的.但如果您使用其他存储引擎或您的更新包含多个表，则情况可能并非如此.

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.

为了更清楚，这里引用了 MySQL 手册:

Here is a quote from the MySQL manual for more clarity:

表锁定使许多会话能够同时从表中读取，但是如果一个会话想要写一个表，它必须首先获得独占使用权.在更新期间，所有其他想要访问这个的会话特定的表必须等到更新完成.

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.

干杯！

这篇关于在 MySQL 中增加一个字段是原子的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！