在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屋！