A lock is a data structure that is acquired by a user and associated to a resource. Until the lock is held, other users will not be able to modify that resource or, depending on the lock type, they would not be able to read it. Typically, concurrent operations will be queued. InnoDB can lock rows and entire tables to prevent the concurrent operations from colliding.

In order to understand how the InnoDB locks work, it is necessary to understand how the concurrent transactions work. Also, this allows us to diagnose and fix problems, such as the transactions that have to wait for too long or the frequent deadlocks between the transactions.

When a transaction needs to access a row that is locked, it is put on hold until the transaction that holds the lock commits or rolls back. The wait has a limit, which is determined by the innodb_lock_wait_timeout server variable, expressed in seconds. The default value is 50 and can usually be decreased. If this timeout exceeds, the transaction...