Transactions, increments and MySQL UPDATE

Question

MySQL

Is the increment operation in an UPDATE transaction-safe in MySQL? Is it possible to have a race condition when multiple clients execute a query like "UPDATE mytable SET myfield=myfield+1 WHERE id=myid" at the same time? If a thousand clients execute such a query at the same time on a line with a base value of 0, will the value be equal to a thousand at the end?

It's about InnoDB.

Answer 1

In InnoDB, a row is locked, there should be no ahtung.

Answer 2

Yes, it will be equal to 1000 at the end. regardless of the engine. In different engines, this is provided in different ways. We have an example in innodb by locking a specific entry. In Myisam, locking the entire table for a short time.

Answer 3

I don't see any reason to say "no". Otherwise, the meaning of a relational DBMS is simply lost.

Answer 4

The record you provided is absolutely transactionally safe. The row is locked, modified, unlocked.

Answer 5

See how update works.
The record is blocked, update is performed, and then you can only work with this record. In principle, because of this, in some articles on optimization and reducing the load on the server, it is recommended not to use update often

Answer 6

It is far from certain that the value will be 1000, especially considering the fact that InnoDB uses MVCC . Given this, if 1000 queries are executed simultaneously, each of them will increase the value from zero to one.