Database locks serve as a critical mechanism for coordinating concurrent access to shared resources. Beyond traditional resource contention involving CPU, memory, and disk I/O, data itself represents a shared resource accessible to multiple users simultaneously. Ensuring consistency and validity during concurrent data access is a fundamental challenge that all database systems must address. Lock conflicts significantly impact database performance and concurrency capabilities, making lock management particularly crucial and intricate in MySQL.

MySQL implements locks at three distinct granularity levels:

• Global locks: Lock all tables across the entire database instance
• Table-level locks: Lock an entire table for each operation
• Row-level locks: Lock specific rows affected by an operation

Global Locks

A global lock applies to the complete database instance, placing the entire system into read-only mode (allowing only SELECT queries). All data modification statements (INSERT, UPDATE, DELETE), schema changes (CREATE, ALTER, DROP), and transaction commit operations become blocked while a global lock is active.

The primary use case involves performing full database logical backups. By acquiring a global lock, consistent point-in-time snapshots of all tables can be obtained, ensuring data integrity throughout the backup process.

Problems without global locking:

Consider a scenario with three tables: inventory, orders, and order_logs. During a backup operation that captures tables sequentially, an order placement occurs between inventory and orders table backups. This creates an inconsistent snapshot where the orders table reflects the new transaction but the inventory table hasn't been updated, resulting in data corruption.

With global locking applied:

Once the global lock is acquired before backup begins, all write operations from other sessions become blocked. The backup process then captures a consistent view of the database since no modifications can occur during the backup window.

Acquiring a global lock:

FLUSH TABLES WITH READ LOCK;

Releasing the global lock:

UNLOCK TABLES;

Table-Level Locks

Table-level locks hold the entire table for each operation. This approach offers maximum lock granularity but results in the highest probability of lock conflicts and lowest concurrency. These locks are supported in storage engines including MyISAM, InnoDB, and BDB.

MySQL categorizes table-level locks into three types:

• Table locks
• Metadata locks (MDL)
• Intention locks

Table Locks

Table locks split into two categories:

• Table shared read lock: Allows concurrent reads but blocks writes
• Table exclusive write lock: Blocks both reads and writes from other sessions

Shared read lock behavior:

When session A acquires a read lock on a table, session B can still read from that table. However, session B's write operations become blocked. All sessions maintain read access to the table.

Exclusive write lock behavior:

When session A holds a write lock, session B cannot read or write to that table. Session A maintains full read and write capabilities.

Read locks permit other sessions to read but block writes. Write locks prevent both read and write operations from other sessions.

Metadata Locks (MDL)

Metadata locks (MDL) protect table schema consistency. The system automatically acquires MDL locks when accessing any table, requiring no explicit commands. These locks prevent schema modifications while active transactions reference table structures.

Metadata represents the structural definition of a table—its columns, indexes, and constraints. When uncommitted transactions exist against a table, structural changes become prohibited.

Introduced in MySQL 5.5, MDL locks follow these rules:

• DML operations (SELECT, INSERT, UPDATE, DELETE) acquire shared metadata locks
• DDL operations (ALTER, CREATE, DROP) acquire exclusive metadata locks

Shared locks are mutually compatible with other shared locks but incompatible with exclusive locks. Exclusive locks are incompatible with all other lock types.

Intention Locks

Intention locks enable efficient lock verification at the table level. Without intention locks, when determining whether a table lock can be acquired, the system would need to scan every row to check for existing row locks, resulting in expensive full-table scans.

Scenario without intention locks:

Session A starts a transaction and modifies certain rows, acquiring row locks on those records. When session B attempts to acquire a table lock, it must examine each row in the table to verify no row locks exist. This approach requires substantial overhead and full-table scanning.

With intention locks:

When session A performs row-level modifications, it also sets an intention lock on the table. Other sessions checking whether a table lock can be granted simply examine the intention lock status without inspecting individual rows. If an intention lock exists, table-level locking becomes blocked until the transaction completes.

Intention locks automatically release when transactions commit.

Row-Level Locks

Row-level locks target individual table rows, offering the finest granularity with lowest conflict probability and highest concurrency. These locks apply only to the InnoDB storage engine.

InnoDB organizes data based on indexes, and row locks are implemented by locking index entries rather than physical records. Three distinct row lock types exist:

• Record Lock: Locks a single row record, preventing other transactions from updating or deleting that row. Supported in both READ COMMITTED and REPEATABLE READ isolation levels.
• Gap Lock: Locks the index range between records (excluding the record itself), preventing other transactions from inserting new records within that gap. Prevents phantom reads. Supported only in REPEATABLE READ isolation level.
• Next-Key Lock: Combines record lock and gap lock, simultaneously locking a record and the gap preceding it. Supported only in REPEATABLE READ isolation level.

InnoDB implements two row lock modes:

• Shared Lock (S): Permits one transaction to read a row while blocking other transactions from acquiring exclusive locks on the same data.
• Exclusive Lock (X): Permits the holder to modify data while blocking all other transactions from acquiring any lock (shared or exclusive) on the same data.

Lock compatibility matrix:

Lock Combination	Compatible?
Shared + Shared	Yes
Shared + Exclusive	No
Exclusive + Exclusive	No

Lock status can be examined through the performance schema:

SELECT object_schema, object_name, index_name, lock_type, lock_mode, lock_data
FROM performance_schema.data_locks;

Regular SELECT statements do not acquire locks.

SELECT ... FOR SHARE acquires shared locks.

When session A holds a shared lock on row ID 1, session B can acquire an exclusive lock on row ID 3 since these are different records. However, session B attempting to acquire an exclusive lock on row ID 1 would block because shared and exclusive locks conflict.

When session A executes an UPDATE on row ID 1, acquiring an exclusive lock, session B's attempt to UPDATE the same row would block because exclusive locks are mutually exclusive. Session B unblocks once session A commits and releases its lock.

Row Locks Escalating to Table Locks

Consider a students table without an index on the name column. When session A updates the row where name equals 'Alice' within a transaction, InnoDB cannot efficiently identify the affected rows due to the missing index. Consequently, InnoDB escalates the row lock to a table lock, blocking session B's attempt to update an unrelated row (ID 3).

After creating an index on the name column:

CREATE INDEX idx_student_name ON students(name);

Session A's UPDATE can now target specific rows using the index. Session B's update on row ID 3 succeeds without blocking since row-level locking operates precisely on the affected records.

Gap Locks and Next-Key Locks

By default, InnoDB operates in REPEATABLE READ isolation level, using next-key locks to prevent phantom reads during index scans and searches.

Behavior patterns:

• Unique index equality queries on non-existent records lock the gap rather than specific rows
• Non-unique index equality queries convert next-key locks to gap locks when the rightmost value fails the condition
• Range queries on unique indexes lock until the first value that fails the condition

Important: Gap locks exist solely to prevent other transactions from inserting records into the gap. Multiple transactions can hold gap locks on the same range simultaneously—a transaction's gap lock does not prevent another transaction from acquiring a gap lock on the same region.

Example 1: Unique index query on non-existent record

When querying for a non-existent record (ID 7) using a unique index, InnoDB creates a gap lock instead of a record lock. Other transactions cannot insert records into that gap until the holding transaction commits.

Example 2: Non-unique index query

InnoDB's B+ tree indexes store leaf nodes in sorted bidirectional linked lists. When searching for a non-unique index value (such as 18), the query might match multiple rows. The lock extends beyond the matching record to include subsequent gaps until a value fails the condition (in this case, 29). This ensures all potential matching records within the range remain locked.

Example 3: Range query on unique index

For a range query WHERE id >= 19 with shared locks, InnoDB divides the index into segments: the exact record at 19 receives a record lock, records between 19 and 25 receive a next-key lock, and records beyond 25 receive next-key locks extending to positive infinity.