MySQL-更新表行而不锁定行 [英] MySQL - Update table rows without locking the rows

问题描述

我有一个要求，我们需要在不更新的同时保持锁的状态下更新行.

I have requirement where we need to update the row without holding the lock for the while updating.

以下是要求的详细信息，我们将每5分钟在表上运行一次批处理.update blogs set is_visible=1 where some conditions此查询将在数百万条记录上运行，因此我们不想阻塞所有要写入的行更新.

Here is the details of the requirements, we will be running a batch processing on a table every 5 mins update blogs set is_visible=1 where some conditions this query as to run on millions of records so we don't want to block all the rows for write during updates.

我完全理解没有写锁的含义，这对我们来说很好，因为is_visible列将仅通过此批处理过程进行更新，而没有其他线程会更新此列.另一方面，我们不想阻止的同一表中其他列的更新很多

I totally understand the implications of not having write locks which is fine for us because is_visible column will be updated only by this batch process no other thread wil update this column. On the other hand there will be lot of updates to other columns of the same table which we don't want to block

推荐答案

首先，如果默认使用MySQL的InnoDB存储引擎，那么除了设置事务隔离级别外，没有行锁就无法更新数据.向下运行以读取未提交

First of all, if you default on the InnoDB storage engine of MySQL, then there is no way you can update data without row locks except setting the transaction isolation level down to READ UNCOMMITTED by running

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

但是，我认为数据库行为不是您所期望的，因为在这种情况下允许脏读. READ UNCOMMITTED在实践中很少有用.

However, I don't think the database behavior is what you expect since the dirty read is allowed in this case. READ UNCOMMITTED is rarely useful in practice.

要补充@Tim的答案，在where子句中使用的列上具有唯一索引确实是个好主意.但是，也请注意，不能绝对保证优化器最终会使用创建的索引来选择这种执行计划.视情况而定，它可能行不通.

To complement the answer from @Tim, it is indeed a good idea to have a unique index on the column used in the where clause. However, please note as well that there is no absolute guarantee that the optimizer will eventually choose such execution plan using the index created. It may work or not work, depending on the case.

对于您的情况，您可以做的是将多头交易拆分为多个空头交易.与其一次更新数百万行，不如一次只扫描数千行.当每个短事务提交或回滚时，X锁将释放，从而使并发更新有继续进行的机会.

For your case, what you could do is to split the long transaction into multiple short transactions. Instead of updating millions of rows in one shot, scanning only thousands of rows each time would be better. The X locks are released when each short transaction commits or rollbacks, giving the concurrent updates the opportunity to go ahead.

顺便说一句，我认为您的批次的优先级低于其他在线流程，因此可以将其安排在高峰时段之外，以进一步减少影响.

By the way, I assume that your batch has lower priority than the other online processes, thus it could be scheduled out of peak hours to further minimize the impact.

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

P.S. The IX lock is not on the record itself, but attached to the higher-granularity table object. And even with REPEATABLE READ transaction isolation level, there is no gap lock when the query uses a unique index.

这篇关于MySQL-更新表行而不锁定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！