Understanding MySQL Metadata Locks and InnoDB Lock Mechanisms

Locks are mechanisms that coordinate concurrent access to the same data resource in a program; for database systems, ensuring consistency and effectiveness of concurrent data access is essential, and MySQL implements this through metadata locks and InnoDB locks.

1. MySQL Architecture and Locks

MySQL consists of a service layer (handling network communication, parsing, and planning) and a storage‑engine layer (handling actual data storage). Metadata concurrency is managed in the service layer, while data concurrency is handled in the storage‑engine layer, so metadata locks are implemented in the service layer and data locks in the engine layer.

2. Metadata Locks

2.1 Types of Metadata Locks

Metadata Type

Description

GLOBAL

Global lock

TABLESPACE

Tablespace lock

SCHEMA

Database lock

TABLE

Table lock

FUNCTION

Function lock

PROCEDURE

Procedure lock

TRIGGER

Trigger lock

EVENT

Event lock

COMMIT

Transaction lock applied at commit time on the server

USER_LEVEL_LOCK

User lock obtained via GET_LOCK/RELEASE_LOCK

LOCKING_SERVICE

Provided after installing locking_service.so plugin

2.2 Acquisition and Release

When a metadata lock is requested, a release time is also specified. The lock is automatically released when the statement finishes, when the transaction ends, or when an explicit release is issued.

Release Type

Description

MDL_STATEMENT

Released at statement end

MDL_TRANSACTION

Released at transaction end

MDL_EXPLICIT

Explicitly released by user

The abstract acquisition and release process is illustrated in the following flow diagram:

2.3 Relationships

Metadata locks can have dependency relationships; for example, a SCHEMA lock requires a GLOBAL lock. The dependency graph is shown below:

2.4 Levels

Level

Description

MDL_INTENTION_EXCLUSIVE (IX)

Intention exclusive lock, can be upgraded to exclusive; compatible with other IX locks but not with shared locks.

MDL_SHARED (S)

Shared lock

MDL_SHARED_HIGH_PRIO (SH)

High‑priority shared lock

MDL_SHARED_READ (SR)

Shared read lock, indicates intention to read rows

MDL_SHARED_WRITE (SW)

Shared write lock, indicates intention to write rows

MDL_SHARED_WRITE_LOW_PRIO

Low‑priority shared write lock

MDL_SHARED_UPGRADABLE (SU)

Upgradeable shared lock

MDL_SHARED_READ_ONLY (SRO)

Read‑only shared lock that blocks updates to metadata and data

MDL_SHARED_NO_WRITE (SNW)

Upgradeable table lock that blocks data updates but allows reads

MDL_SHARED_NO_READ_WRITE (SNRW)

Upgradeable lock that blocks both reads and updates

MDL_EXCLUSIVE (X)

Exclusive lock

2.5 Source Code

The core source files for metadata locks are mdl.h and mdl.cc , which define the main data structures and functions. Other files such as lock.cc and sql_db.cc use these definitions.

3. InnoDB Locks

3.1 Transaction Isolation Levels

InnoDB implements four isolation levels (Read Uncommitted, Read Committed, Repeatable Read, Serializable). The table below shows which phenomena (dirty read, non‑repeatable read, phantom read) can occur under each level.

Isolation Level

Dirty Read

Non‑repeatable Read

Phantom Read

Read Uncommitted

X

X

X

Read Committed

--

X

X

Repeatable Read

--

--

X

Serializable

--

--

--

3.2 InnoDB Lock Types

Lock Type

Description

Table Lock

Locks the entire table

Row Lock

Locks a single row

Gap Lock

Combined with row lock to lock a range of rows

3.3 InnoDB Lock Levels

Lock Level

Description

Intention Shared (IS)

Used for table locks, not for row locks

Intention Exclusive (IX)

Used for table locks, not for row locks

Shared (S)

Primarily for row locks; also for "LOCK TABLES FOR READ"

Exclusive (X)

Primarily for row locks; also for "LOCK TABLES FOR WRITE"

Auto‑Increment (AI)

Table‑level lock used for MySQL binlog statements

The compatibility matrix of these lock levels is shown below ("O" = compatible, "X" = not compatible).

IS

IX

S

X

AI

IS

O

O

O

X

O

IX

O

O

X

X

O

S

O

X

O

X

X

X

X

X

X

X

X

AI

O

O

X

X

X

3.4 Gap Locks

InnoDB gap (next‑key) locks prevent phantom reads under the REPEATABLE READ isolation level by locking the interval between index keys. For example, with index keys 5, 17, 23, 29, a SELECT ... FOR UPDATE on id=16 creates a row lock on key 17 and a gap lock covering the range (6,17).

3.5 Source Code Structure

The core code contains macro definitions and function implementations for lock handling; application code uses these macros and functions to implement locking behavior.

Recommended Reading

Ctrip Wireless APM Platform: Global End‑to‑End Performance Monitoring

ALLUXIO in Ctrip Big Data Platform: Application and Practice

Ctrip Wireless Incremental Update Package Solution

Ctrip MTP and MCD Platforms: Supporting 100k+ Wireless Integrations and Releases per Year

Ctrip Software SBC Practice

Limited seats, hurry up and register!

Front‑end experts & new influencers sharing the latest tech and best practices.

March 24, click the image to sign up.