InnoDB has two main lock modes: shared and exclusive. A shared lock prevents other connections from writing to a row, but allows them to read it. An exclusive lock prevents other connections from reading a row; the isolation level (explained in the Transactions isolation levels section) determines whether other transactions can write in the row. Shared locks are acquired before reading a row, and exclusive locks are acquired before writing a row. Before a transaction can acquire a shared or exclusive lock, it needs to acquire an intention shared lock or an intention exclusive lock. In other words, an intention lock indicates that a transaction is waiting to acquire a shared or exclusive lock, and prevents two connections from acquiring a lock on the same rows.

Shared and exclusive locks are commonly abbreviated as S and X. Intention shared locks and intention exclusive locks are abbreviated as IS and IX.

A lock on a record can only be acquired if an incompatible lock does not already involve the same record. If such a lock exists, the connection is put on hold. The following table shows which lock modes block the other lock modes:

InnoDB supports both table-level locks and record-level locks.

Record-level locks lock one or more records. InnoDB has the following types of record-level locks: record locks, gap locks, and next-key locks.

A particular case of gap lock is the insertion gap lock. It is acquired before the insertion of a new row. If another connection tries to insert a row with the same index values, it will be put on hold until the current transaction commits or rolls back the changes.

Gap locks are not used with the READ COMMITTED isolation level.

While locks are necessary to guarantee the consistency of data, they can cause performance problems. Each lock can cause one or more sessions to wait, slowing down the applications. If sessions have to wait too long or too often, we have to find out the reason and fix the problem.

The SHOW ENGINE INNODB STATUS statement is usually the fastest way to get human-readable information about locks. Its output is quite long, but fortunately it is human-readable, friendly for regular expressions, and divided into some useful sections. The section which lists existing locks is TRANSACTIONS. Here is an example:

------------ 
TRANSACTIONS 
------------ 
Trx id counter 14488 
Purge done for trx's n:o < 14477 undo n:o < 0 state: running but idle 
History list length 230 
LIST OF TRANSACTIONS FOR EACH SESSION: 
…
---TRANSACTION 14509, ACTIVE 3 sec inserting 
mysql tables in use 1, locked 1 
LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 
MySQL thread id 5, OS thread handle 0x7f5d48554700, query id 161 localhost root update 
INSERT INTO t VALUES (1) 
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED: 
RECORD LOCKS space id 48 page no 4 n bits 72 index `a` of table `test`.`t` trx id 14509 lock mode S waiting 
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 
 0: len 4; hex 80000001; asc     ;; 
 1: len 6; hex 000000000601; asc       ;; 
------------------ 
---TRANSACTION 14486, ACTIVE 9 sec 
2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 
MySQL thread id 4, OS thread handle 0x7f5d4859d700, query id 154 localhost root cleaning up

For each transaction, an ID is shown. The last listed transaction has been active for nine seconds. We know its thread ID is 4, which is important to debug. By executing SHOW PROCESSLIST, we can find out what thread 4 is doing. The operating system thread ID is also shown, in the hexadecimal format. It can be converted to a decimal number by using the UNHEX() function. We know it holds a lock on 1 row.

Another transaction is shown in this example. It has been active for three seconds. The transaction has been blocked for three seconds from inserting a row by the existing lock.

To get more specific information about one or more locks, the INNODB_LOCKS table in the information_schema database can be queried. Note that locks appear in that table only if they are blocking a transaction. The table consists of the following columns:

The INNODB_LOCK_WAITS table shows which transactions are waiting to obtain a lock, and which transactions are holding the locks which are blocking them. Its columns are:

The INNODB_TRX table holds information about a transaction's activities.

The following example shows a JOIN query between INNODB_LOCKS and INNODB_LOCK_WAITS, which shows all the transactions that are blocking another transaction, as well as the detailed information about the blocking lock:

MariaDB [test]> SELECT * FROM information_schema.INNODB_LOCK_WAITS lw LEFT JOIN information_schema.INNODB_LOCKS l ON lw.BLOCKING_LOCK_ID = l.LOCK_ID G 
*************************** 1. row *************************** 
requesting_trx_id: 14512 
requested_lock_id: 14512:48:4:2 
  blocking_trx_id: 14486 
 blocking_lock_id: 14486:48:4:2 
          lock_id: 14486:48:4:2 
      lock_trx_id: 14486 
        lock_mode: X 
        lock_type: RECORD 
       lock_table: `test`.`t` 
       lock_index: a 
       lock_space: 48 
        lock_page: 4 
         lock_rec: 2 
        lock_data: 1 
1 row in set (0.00 sec) 

Note that information_schema related to InnoDB does not show information about the locks obtained using LOCK TABLES. The reason is that those locks are handled by the MariaDB server itself, not by the InnoDB storage engine.

The UPDATE and DELETE statements lock all scanned index records, including the ones that do not satisfy the WHERE condition. Locking reads can free the locks on the index records that do not satisfy a WHERE condition but sometimes this cannot be done.

The UPDATE and DELETE statements acquire an exclusive next-key lock for each scanned record.

The INSERT statement acquires an insertion gap lock on the index values it is going to insert. Transactions that insert different values do not need to wait for each other. After the insertion, an exclusive record lock is placed on all the new rows until the end of the transaction. If a duplicate error occurs, normally the transaction does not rollback and a shared record lock is placed on the existing record. If the ON DUPLICATE KEY clause is present, an exclusive next-key lock is put on the existing record.

The INSERT … SELECT statement is similar to the INSERT statement and is used for the inserted tables, except that no insertion gap lock is set. With the READ COMMITTED isolation level, the SELECT part of the statement performs a consistent read. Otherwise, shared next-key locks are set on the read rows.

Foreign keys need to read some rows to guarantee that the integrity constraints are enforced. On each read record, a shared record lock is acquired.