When one locks rows for Exclusive access using FOR UPDATE
in a SELECT
query, are all the rows one-by-one (potentially leading to a hold-and-wait condition for locks) or the lock is acquired atomically on all the rows (which means all rows are locked or none is locked)?
InnoDB Row Locking – Atomic vs One-by-One Locking
lockingMySQL
Best Answer
Atomically cannot always be possible. Suppose you have a transaction that does an
UPDATE
,SELECTs
some stuff, then decides on what toUPDATE
next. If it had not locked as it went along, the state could change, etc, etc.Suppose you have two transactions proceeding in parallel. Any of these might happen:
innodb_lock_wait_timeout
) will cause the blocked xaction to abort.Any killed transaction will have any actions (update/delete/insert) undone.
Note: The above applies to InnoDB tables in a single server. Galera (and other clustering), NDB, and MyISAM abide by different rules.