MySQL - 更新表格行而不锁定行

Question

我有一个需求，需要在更新时不持有锁。具体要求如下：我们将在每5分钟批处理一个表格，update blogs set is_visible=1 where some conditions这个查询语句需要运行在数百万条记录上，因此我们不希望在更新期间阻止所有行的写入。

我完全理解没有写锁会产生的影响，但对于我们来说这没关系，因为is_visible列只会被此批处理更新，没有其他线程会更新此列。另一方面，我们不希望阻止同一张表的其他列进行大量的更新。

- Lohith MV

Answer 1

首先，如果您使用MySQL的InnoDB存储引擎并且默认情况下未设置行锁，则除了通过运行“SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED”将事务隔离级别降低到READ UNCOMMITTED以外，没有其他方法可以更新数据。

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

然而，我认为数据库的行为并不符合您的期望，因为在这种情况下允许脏读。在实践中，READ UNCOMMITTED 很少有用。

作为 @Tim 的补充回答，将唯一索引创建在 where 子句中使用的列上确实是一个好主意。但是，请注意，优化器最终选择使用所创建的索引的执行计划并没有绝对的保证。这取决于具体情况，可能管用也可能无效。

对于您的情况，您可以将长事务拆分为多个短事务。不要一次性更新数百万行，而是每次仅扫描数千行更好。当每个短事务提交或回滚时，X 锁会被释放，使并发更新有机会继续执行。

顺便说一下，我假设您的批处理比其他在线进程的优先级低，因此可以在非高峰时间进行调度，以进一步减少影响。

P.S. IX 锁不在记录本身上，而是附加到更高粒度的表对象上。即使使用了 REPEATABLE READ 事务隔离级别，当查询使用唯一索引时也不存在间隙锁。

- flyingice

---

这个不会阻塞没有被更新的行，但会阻塞所有被更新的行，直到查询结束。对于较大的表，这可能会导致错误"锁的总数超过了锁表的大小"。您可以通过增加参数innodb_buffer_pool_size来解决这个问题。 - undefined

Answer 2

最佳实践是在可能与其他事务并发更新时始终获取特定锁。如果您的存储引擎是MyISAM，则MySQL将在更新期间锁定整个表，您无法做太多事情。如果存储引擎是InnoDB，则MySQL可能仅对更新目标记录放置独占IX锁，但这种情况存在一些注意事项。要尝试实现这一点的第一件事是使用SELECT ... FOR UPDATE：

SELECT * FROM blogs WHERE <some conditions> FOR UPDATE;

为了确保InnoDB仅锁定正在更新的记录，需要在出现在WHERE子句中的列上建立唯一索引。对于您的查询，假设涉及的列是id，它必须是主键，否则您需要创建一个唯一索引：

CREATE UNIQUE INDEX idx ON blogs (id);

即使有这样的索引，InnoDB仍然可能在索引值之间的记录上应用间隙锁，以确保执行REPEATABLE READ协议。

因此，您可以在涉及WHERE子句的列上添加索引，以优化在InnoDB上的更新。

- Tim Biegeleisen

---

我不确定你是否正确理解了我的问题？我不想在更新期间发生行锁定，因此使用 select ... For update 将锁定表。我的问题是，有没有一种方法可以在不进行行锁定的情况下更新行？ - Lohith MV

这只适用于 MyISAM 的情况。在 InnoDB 上，假设您设置了正确的唯一索引，FOR UPDATE 不会锁定整个表。 - Tim Biegeleisen

Answer 3

这是因为UPDATE希望保持您的表处于一致的状态，所以不允许部分更新。这就是为什么如果在完成之前中断命令，所有已更新的内容都会被还原，这是一个代价高昂的操作。

修复方法： 将UPDATE语句的WHERE部分保存在一个临时表中，用于存储需要更新的行的主键，例如：CREATE TABLE temp AS SELECT id FROM blogs WHERE conditions;。如果在您的上下文中可以接受，可以在开头添加SET ISOLATION LEVEL READ UNCOMMITTED;以避免被阻塞。然后使用SQL FOR LOOP + CURSOR逐行更新行。这样，即使在循环未完成之前中断，您也不会丢失已经完成的更新，并且可以从中断的位置重新开始。根据要更新的数据量，您甚至可以选择使用FOR LOOP遍历整个表，或者使用HANDLER https://dev.mysql.com/doc/refman/5.7/en/handler.html，逐行检查是否需要更新，从而跳过临时表部分。

作为一种替代方案，您可以使用一个小的限制子句进行较小的更新：update blogs set is_visible=1 where some conditions limit 10，也许可以在循环中自动化执行。

- Marco Marsala