问题:锁定 mySQL 表/行

有人可以解释在 mysql 中锁定表和/或行的需要吗?

我假设它可以防止多次写入同一字段,这是最佳做法吗?

解答

首先让我们看看一个好的文档 这不是一个与 mysql 相关的文档,它是关于 postgreSQl 的,但它是我读过的关于事务的更简单明了的文档之一。阅读此链接http://www.postgresql.org/docs/8.4/static/mvcc.html后,您会更好地理解 MySQl 事务

当您运行事务时,应用 4 条规则 (ACID):

• 原子性:全有或全无(回滚)

• Coherence : 前连贯,后连贯

• 隔离:不受他人影响?

• 持久性:commit,如果完成了,就真的完成了

在这些规则中,只有一个有问题,那就是隔离。使用事务并不能确保完美的隔离级别。上一个链接将更好地向您解释并发事务之间的幻读和诸如此类的隔离问题。但是为了简单起见,您应该真正使用行级别锁来防止与您同时运行(并且可能在您之前提交)的其他事务来更改相同的记录。但是随着锁而来的是死锁......

然后,当您尝试使用带锁的良好事务时,您需要处理死锁,并且您需要处理事务可能失败并且应该重新启动(简单的 for 或 while 循环)的事实。

**编辑: - - - - - - **

最新版本的 InnoDb 提供了比以前更高级别的隔离。我已经做了一些测试,我必须承认,即使是应该发生的幻读现在也很难重现。

MySQL 默认处于第 3 级,在 PosgtreSQL 文档中解释的 4 级隔离(其中 postgreSQL 默认处于第 2 级)。这是_REPEATABLE READS_。这意味着您不会有 Dirty reads 并且不会有 Non-repeatable reads。因此,修改您在事务中进行选择的行的人将获得隐式锁定(例如,如果您执行了选择更新)。

警告:如果您使用旧版本的 MySQL,例如 5.0,您可能处于第 2 级,您需要使用“FOR UPDATE”字样执行行锁定!

我们总能找到一些不错的竞争条件,使用聚合查询,如果您不希望人们在您添加行时处于第 4 级隔离(通过在查询末尾使用 LOCK IN SHARE MODE)可能会更安全重新执行一些任务。我已经能够重现一个可序列化级别的问题,但我不会在这里解释这个复杂的例子,非常棘手的竞争条件。有一个非常好的竞争条件示例,即使可序列化级别也无法修复:http://www.postgresql.org/docs/8.4/static/transaction-iso.html#MVCC-SERIALIZABILITY

在处理事务时,更重要的事情是:

• 事务中使用的数据必须始终在 INSIDE 事务中读取(如果您有 BEGIN 之前的数据,则重新读取它)

• 明白为什么高隔离级别会设置隐式锁,可能会阻塞其他一些查询(并使它们超时)

• 尽量避免死锁(尝试以相同的顺序锁定表)但处理它们(重试被 MySQL 中止的事务)

• 当您的应用程序代码假定没有插入或更新应该修改他正在使用的数据集时,尝试_冻结_具有序列化隔离级别(LOCK IN SHARE MODE)的重要源表(如果不是,您不会有问题,但您的结果将忽略并发更改)