> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-lock-modes.md).

# InnoDB Lock Modes

InnoDB employs Row-Level Locking with Shared (S) and Exclusive (X) locks, along with Intention locks, to manage concurrent transaction access.

Locks are acquired by a transaction to prevent concurrent transactions from modifying, or even reading, some rows or ranges of rows. This is done to make sure that concurrent write operations never collide.

[InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb.md) supports a number of lock modes.

## Shared and Exclusive Locks

The two standard row-level locks are *share locks*(S) and *exclusive locks*(X).

A shared lock is obtained to read a row, and allows other transactions to read the locked row, but not to write to the locked row. Other transactions may also acquire their own shared locks.

An exclusive lock is obtained to write to a row, and stops other transactions from locking the same row. It's specific behavior depends on the [isolation level](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction.md); the default (REPEATABLE READ), allows other transactions to read from the exclusively locked row.

## Locks Apply to Index Records, Not Logical Table Rows

In InnoDB, when using `LOCK IN SHARE MODE` under the `READ COMMITTED` isolation level, it is critical to understand that locking reads are based on index records and not on abstract table rows. This difference is important, when using locking reads like these:

```
SELECT ... LOCK IN SHARE MODE;
SELECT ... FOR UPDATE;
```

InnoDB's locking engine works at the index record level, but a query logically focuses on a row. Therefore, based on the isolation level and data creation order, lock conflicts can be avoided if two simultaneous transactions access the same row through different indexes.

### Index Reads and Lock Behavior

When a secondary index matches a transaction, InnoDB:

* Reads only the secondary index
* locks the secondary index records
* Avoids accessing the primary index record

If another transaction alters a column that is not included in the secondary index, only the primary index record can be locked. In that case, the two transactions lock different index records without blocking each other.

### Example

The following scenario illustrates a case where `SELECT ... LOCK IN SHARE MODE` does not prevent a concurrent update, even though both statements refer to the same logical rows:

```
CREATE TABLE tbl_1 (
  col_1_1 TIMESTAMP,
  col_1_2 VARCHAR(100),
  col_1_3 MEDIUMINT,
  col_1_4 INT,
  col_1_5 DOUBLE,
  col_1_6 NUMERIC,
  pkid_1 INT,
  INDEX idx_1_1 (col_1_4, col_1_2, col_1_3, col_1_5),
  PRIMARY KEY (col_1_4, pkid_1)
) ENGINE=InnoDB;
```

Here:

* `PRIMARY KEY (col_1_4, pkid_1)` -> Clustered Index (Primary Index Record)
* `INDEX idx_1_1 (col_1_4, col_1_2, col_1_3, col_1_5)` -> Secondary Index (Secondary Index Record)

**Sample Data**

```
INSERT INTO tbl_1 VALUES
('2025-12-19 16:46:56','str_002',2,3,1,1,0),
('2025-12-19 16:46:56','str_002',2,3,2,1,1),
('2025-12-19 16:46:57','str_002',1,3,2,1,2),
('2025-12-19 16:46:56','str_002',1,3,2,1,3),
('2025-12-19 16:46:57','str_002',1,3,2,1,4),
('2025-12-19 16:46:56','str_002',1,1,2,1,5),
('2025-12-19 16:46:56','str_002',1,2,2,1,6);
```

**Connection 1**

```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
 
SELECT col_1_4, col_1_2, pkid_1 FROM tbl_1 WHERE col_1_3 > 1 LOCK IN SHARE MODE;
```

**What happens**

* The query makes use of the secondary index `idx_1_1`.
* The query reads `col_1_4`, `col_1_2`, and `pkid_1`, which are part of `idx_1_1`.
* This is a covering or secondary index read.
* InnoDB skips the primary index and only applies shared locks to records in `idx_1_1`.

**Default Connection (Concurrent)**

```
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
 
UPDATE tbl_1
SET col_1_1 = '2025-12-19 16:46:56'
WHERE col_1_4 = 3;
```

**What happens**

* The `UPDATE` changes `col_1_1`, which is not present in `idx_1_1`.
* The PRIMARY KEY record must be locked in InnoDB.
* No changes to `idx_1_1` are required.

### Why No Blocking Occurs

1. Connection 1's `SELECT` performs a "covering read" on the secondary index `idx_1_1`. Since the index contains all required data, InnoDB only uses shared locks on secondary index records.
2. Default Connection's `UPDATE` modifies column `col_1_1`, which is not part of that secondary index. Therefore, an exclusive lock is acquired on the Primary Index record (Clustered Index).
3. The Result: Since these are two different index records, there is no conflict between the shared and exclusive locks.

### How to Ensure Blocking

To cause a lock conflict, the locking read must access the primary key record (Clustered Index). This ensures that the *shared lock* (S) is applied to the location where the update operation (exclusive lock, X) will later be required. This happens when:

* The SELECT contains a column that the secondary index does not cover (e.g., using `SELECT *` or selecting a non‑indexed column), or
* If the secondary index itself is insufficient to satisfy the query, InnoDB must access the full row in the primary index, thereby placing the necessary locks to ensure blocking.

Example:

```
-- The UPDATE is blocked since col_1_1 is not included in the secondary index idx_1_1.
SELECT col_1_1, col_1_4, pkid_1 
FROM tbl_1 
WHERE col_1_3 > 1 
LOCK IN SHARE MODE;
```

The UPDATE will now wait while the clustered index is accessed.

## Locks Are Taken on Every Record That Is Read

`UPDATE`, `DELETE`, and locking reads (`SELECT ... FOR UPDATE` and `SELECT ... LOCK IN SHARE MODE`) lock each index record that InnoDB visits, before the `WHERE` condition is checked for that record. A statement therefore locks, or waits for, records that turn out not to match its `WHERE` condition. Which records are visited depends on the index the statement uses. A condition on a column that isn't indexed doesn't reduce the number of records that are visited and locked.

Unlike a plain `SELECT`, which reads a consistent snapshot of the data, these statements operate on the latest version of each record. If another transaction has inserted or changed a record and hasn't committed yet, a statement that visits the record waits for that transaction, even with the `REPEATABLE READ` isolation level, and even if the record doesn't match the `WHERE` condition.

After a record has been checked:

* With `READ COMMITTED` or `READ UNCOMMITTED`, InnoDB releases the lock on a record that doesn't match the `WHERE` condition. The statement still has to acquire that lock first, so it still waits for a conflicting transaction.
* With `REPEATABLE READ` or `SERIALIZABLE`, the locks on all visited records are kept until the transaction ends.

### Example

The table has an index only on its primary key, `id`:

```sql
CREATE TABLE t (id INT PRIMARY KEY, flag BOOLEAN) ENGINE=InnoDB;
INSERT INTO t VALUES (1, 1), (2, 1);
```

Connection 1 inserts a row and doesn't commit:

```sql
BEGIN;
INSERT INTO t VALUES (3, 0);
```

Connection 2 deletes the rows where `flag` isn't `0`:

```sql
BEGIN;
DELETE FROM t WHERE flag <> 0;
```

The `DELETE` waits, although the new row has `flag = 0` and doesn't match the `WHERE` condition. Because `flag` isn't indexed, the `DELETE` reads all records through the primary key, including the uncommitted row with `id = 3`, and needs to lock it before it can check the condition. What happens next depends on connection 1:

* If connection 1 rolls back, or commits, the `DELETE` continues and deletes the two matching rows.
* If connection 1 doesn't finish within [innodb\_lock\_wait\_timeout](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_lock_wait_timeout) seconds, the `DELETE` fails with error 1205, `Lock wait timeout exceeded`.

There is an exception when connection 1 commits. If connection 2 uses `REPEATABLE READ` and has already read data in its transaction before the `DELETE`, the committed row isn't part of its snapshot. With [innodb\_snapshot\_isolation](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/innodb-system-variables.md#innodb_snapshot_isolation)`=ON`, the default from MariaDB 11.8, the `DELETE` then fails with error 1020, `Record has changed since last read`, and the transaction is rolled back.

### How to Avoid Waiting for Records That Don't Match

* Index the columns of the `WHERE` condition, so that InnoDB visits fewer records.
* For locking reads, use `SKIP LOCKED` to skip records that are locked by other transactions instead of waiting for them. In the example above, `SELECT id FROM t WHERE flag <> 0 FOR UPDATE SKIP LOCKED` returns the rows with `id` `1` and `2` without waiting. See [SELECT](https://mariadb.com/docs/server/reference/sql-statements/data-manipulation/selecting-data/select.md).
* Keep transactions that modify data short, so that other transactions don't wait for them for long.

## Intention Locks

InnoDB also permits table locking, and to allow locking at both table and row level to co-exist gracefully, a series of locks called *intention locks* exist.

An *intention shared lock*(IS) indicates that a transaction intends to set a shared lock.

An *intention exclusive lock*(IX) indicates that a transaction intends to set an exclusive lock.

Whether a lock is granted or not can be summarised as follows:

* An X lock is not granted if any other lock (X, S, IX, IS) is held.
* An S lock is not granted if an X or IX lock is held. It is granted if an S or IS lock is held.
* An IX lock is not granted if in X or S lock is held. It is granted if an IX or IS lock is held.
* An IS lock is not granted if an X lock is held. It is granted if an S, IX or IS lock is held.

## AUTO\_INCREMENT Locks

Locks are also required for auto-increments - see [AUTO\_INCREMENT handling in InnoDB](https://mariadb.com/docs/server/server-usage/storage-engines/innodb/auto_increment-handling-in-innodb.md).

## Gap Locks

With the default [isolation level](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction.md), `REPEATABLE READ`, a method called gap locking is used. When InnoDB sets a shared or exclusive lock on a record, it's actually on the index record. Records will have an internal InnoDB index even if they don't have a unique index defined. At the same time, a lock is held on the gap before the index record, so that another transaction cannot insert a new index record in the gap between the record and the preceding record.

The gap can be a single index value, multiple index values, or not exist at all depending on the contents of the index.

MariaDB does not relax gap locking for unique indexes, so a statement that searches for a single row through all the columns of a unique index still takes a next-key lock.

Similar to the shared and exclusive intention locks described above, there can be a number of types of gap locks. These include the shared gap lock, exclusive gap lock, intention shared gap lock and intention exclusive gap lock.

Gap locks are disabled if the [isolation level](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction.md) is set to [READ COMMITTED](https://mariadb.com/docs/server/reference/sql-statements/administrative-sql-statements/set-commands/set-transaction.md#read-committed) or `READ UNCOMMITTED`. Duplicate-key checking on a unique index is the exception, taking a next-key lock at every isolation level.

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}
