I understand that a single update statement is atomic, the row will be locked during update process. But when there is a "where" cause, is it still atomic?
For example:
table: ABC
-------------
|colA | colB|
-------------
|123 | 0 |
--update 1
update ABC set colA=456, colB=1 where colB=0;
--update 2
update ABC set colA=556, colB=2 where colB=0;
Due to the where cause, the update will first check colB value and find that row in the table, then lock the row for update, right?
If update 1 and update 2 is executed at the same time, is there a chance that both get successfully executed and one of them overwrite the value of another?
Is it better to write as below?
START TRANSACTION;
update ABC set colA=456, colB=1 where colB=0;
COMMIT;
Best Answer
For sanity, we just assume inno
No, that's not how MVCC works. And wrapping single statements in transactions doesn't do anything. They're effectively already wrapped in transactions.
The first transaction will read from ABC, lock it with a row-level lock pending update, write a new row with the updated value, and set it as live on the table.
The second transaction will read from ABC, if the row is locked, it'll block. If not, it will lock the row pending update, write a new row with the updated value, and set it to live on the new table. If it is locked, it'll wait until the lock releases and recheck the row.
Interestingly enough, the default isolation level of read committed does not protect from phantom writes. So if both transactions start at the same time, they'll both read from the same snapshot. But, if one txn1 writes the update it'll actually modify the snapshot txn2 is reading from. So while you can't read the modified data (phantom reads), when you try to write the data you're interacting with the same master copy.
Suggestion: Upgrade to a real database, if possible
For more information, read the docs on MariaDB on transaction isolation.