Transactions, Incrementing, and MySQL UPDATE

Asked bykasia k cik z ksi k

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

Speech about InnoDB.


A line is blocked in InnoDB; there should be no ahtung.
Yes, it will be equal to 1000 at the end. regardless of the engine. In different engines it is provided in different ways. For example, innodb blocking a specific entry. In Myisam, locking the entire table for a short time.
george marzen
I do not see any reason to say "will not be." Otherwise, the meaning of the relational DBMS is simply lost.
you probably wanted to say "the meaning of transactions is lost" - art rs
Yes, not only transactions. In MyISAM transactions for example, no. But there are locks, ipc and all that.

Why all this garden if such a simple thing would not work. - ms bayer
The record you provided is completely transaction-safe. The line is locked, changed, unlocked.
See the update action principle.
The record is blocked, update is executed and then you can only continue to work with this record. In principle, because of this, in some articles on optimization and reduction of server load, it is recommended not to use the update frequently
And if in one transaction you first get a value through SELECT ... FOR UPDATE, and then make a direct UPDATE to the next value? - diana mendez
Oh, it's not here :) - slynne
barry benteman
It is far from a fact that the value will be 1000, especially considering the fact that InnoDB uses MVCC Given this while executing 1000 queries , each of them will increase the value from zero to one.
And if within one transaction you first get a value through SELECT ... FOR UPDATE, and then make a direct UPDATE to the next value? - tyler wilson
Each transaction creates its own "copy" of the table. If we run one transaction, which increases the value from zero to one and in parallel with it, it will return 0. - lindsay hunter
Why write something that you don’t know? If your transaction writes some data to the record, no other transaction in innodb can write there. Record locked. You can read from there (depending on the isolation level) and write - it will not work, the new transaction will wait for the record to be released. If the record is not released for a long time, the new transaction will simply be aborted with the error ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction - dila hanim
Advise books on IT marketing :: Creating a device based on electronic paper :: Advise a book on functional programming :: Checking Forms in html5 (firefox 4 beta x) :: Why does the q & a rating of the article appear immediately, but in posts you need to “merge” a voice?
Leave Repply forTransactions, Incrementing, and MySQL UPDATE
Useful Links