Concurrent updates to the same row

Question

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?

Answer 1

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.

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.