并发更新到同一行 [英] Concurrent updates to the same row

问题描述

如果要同时从不同客户端发出以下两个查询，我想弄清楚在MySQL/InnoDB中会发生什么:

I'm trying to figure out what is supposed to happen in MySQL/InnoDB if I issue the following 2 queries from different clients at the same time:

UPDATE tbl SET a=a+1 WHERE id=123;
UPDATE tbl SET b=b+1 WHERE id=123;

提供的查询是在没有显式事务或没有较早地建立较早的锁的情况下完成的，就像

Provided the queries are done without an explicit transaction or explicit earlier locks set up earlier, the way the documentation explains it, each query will try to get a next-key lock, so effectively a record lock on the updated rows.

我是否理解正确，最糟糕的结果是一个查询在锁上等待，直到另一个查询完成?这里不可能有冲突的例外，对吧?

Do I understand it right, that the worst outcome would be one query waiting on the lock until the other one finishes? There's no chance of an exception on conflict here, right?

推荐答案

您是对的.这两个更新操作将一个接一个地序列化.他们的顺序很难预测，因此您不应尝试.

You're right. The two update operations will be serialized one after the other. Their order is very hard to predict, so you should not try.

如果第三个查询或多或少地同时询问SELECT a, b FROM tbl WHERE id=123，则该查询将与其他查询不可预测地序列化.因此，它可能在其他两个之前，之间或之后发生.

If a third query asks SELECT a, b FROM tbl WHERE id=123 at more or less the same time, that query will be serialized, unpredictably, with the others. So it may happen before, between, or after the other two.

两个更新查询最终都将完成.导致死锁所需的时间比这些查询还多.

Both update queries will eventually complete. It takes more than these queries to cause a deadlock.

这篇关于并发更新到同一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！