InnoDB Row Locking – Atomic vs One-by-One Locking

lockingMySQL

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)?

Best Answer

Atomically cannot always be possible. Suppose you have a transaction that does an UPDATE, SELECTs some stuff, then decides on what to UPDATE 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:

  • The locks taken out do not conflict. In this case each transaction proceeds rapidly to a conclusion.
  • One xaction tries to lock some row that is already locked. In this case, it waits to see if the other xaction will finish. Either it will eventually release the lock allowing resumption of activity, or a "timeout" (see innodb_lock_wait_timeout) will cause the blocked xaction to abort.
  • A deadlock is discovered. At this point one of the xactions is picked to be killed.

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.

Related Question