15.3 How InnoDB Uses Locks

This section describes how InnoDB uses locks internally and some query modifiers you can use to affect locking.

InnoDB's general locking properties are as follows:

• InnoDB does not need to set locks to achieve consistent reads because multi-versioning makes them unnecessary. Transactions that modify rows see their own versions of those rows, and the undo logs allow other transactions to see the original rows. Locking reads may be performed by adding locking modifiers to SELECT statements.

• When locks are necessary, InnoDB uses row-level locking. In conjunction with multi-versioning, this results in good query concurrency because a given table can be read and modified by different clients at the same time. Row-level concurrency properties are as follows:

  • Different clients can read the same rows simultaneously.

  • Different clients can modify different rows simultaneously.

  • Different clients cannot modify the same row at the same time. If one transaction modifies a row, other transactions cannot modify the same row until the first transaction completes. Other transactions cannot read the modified row, either, unless they are using the READ UNCOMMITTED isolation level. That is, they will see the original unmodified row.

• During the course of a transaction, InnoDB may acquire row locks as it discovers them to be necessary. However, it never escalates a lock (for example, by converting it to a page lock or table lock). This keeps lock contention to a minimum and improves concurrency.

• Deadlock can occur. Deadlock is a situation in which each of two transactions is waiting for the release of a lock that the other holds. For example, if two transactions each lock a different row, and then try to modify the row locked by the other, they can deadlock. Deadlock is possible because InnoDB does not acquire locks during a transaction until they are needed. When InnoDB detects a deadlock, it terminates and rolls back one of the deadlocking transactions. It tries to pick the transaction that has modified the smallest number of rows. If InnoDB does not detect deadlock, the deadlocked transactions eventually begin to time out and InnoDB rolls them back as they do.

InnoDB supports two locking modifiers that may be added to the end of SELECT statements. They acquire shared or exclusive locks and convert nonlocking reads into locking reads:

• With LOCK IN SHARE MODE, InnoDB locks each selected row with a shared lock. Other transactions can still read the selected rows, but cannot update or delete them until the first transaction releases the locks, which happens when the transaction finishes. Also, if the SELECT will select rows that have been modified in an uncommitted transaction, IN SHARE MODE will cause the SELECT to block until that transaction commits.

• With FOR UPDATE, InnoDB locks each selected row with an exclusive lock. This is useful if you intend to select and then modify a set of rows, because it prevents other transactions from reading or writing the rows until the first transaction releases the locks, which happens when the transaction finishes.

In the REPEATABLE READ isolation level, you can add LOCK IN SHARE MODE to SELECT operations to force other transactions to wait for your transaction if they want to modify the selected rows. This is similar to operating at the SERIALIZABLE isolation level, for which InnoDB implicitly adds LOCK IN SHARE MODE to SELECT statements that have no explicit locking modifier.