MySQL implements various lock types to manage concurrency and ensure data consistency. Locks are categorized by granularity: global, table-level, and row-level.

Global Lock

A global lock restricts the entire database instance to read-only mode, blocking DML write statements, DDL operations, and transaction commits. It is primarily used for full database backups to maintain data consistency.

Syntax and Issues

-- Acquire global lock
FLUSH TABLES WITH READ LOCK;

-- Perform operations

-- Release lock via SQL
UNLOCK TABLES;
-- Or close the session

Global locks can halt normal operations. To mitigate this, use repeatable read isolation with MVCC for backups, as supported by InnoDB. For example, with mysqldump:

mysqldump --single-transaction -uroot -p123456 database_name > backup.sql

This approach is not applicable to engines like MyISAM that lack transaction support.

Table-Level Locks

Table-level locks lock entire tables, offering coarse granularity with higher lock contention and lower concurrency. They are used in engines like MyISAM, InnoDB, and BDB, and include table locks, metadata locks, and intention locks.

Table Locks

Table locks come in two forms: shared read locks and exclusive write locks.

-- Acquire table lock
LOCK TABLES table_name READ;   -- Shared read lock
LOCK TABLES table_name WRITE;  -- Exclusive write lock

-- Release lock
UNLOCK TABLES;
-- Or close the session

• Shared Read Lock: Allows other transactions to read but blocks writes.
• Exclusive Write Lock: Blocks both reads and writes by other transactions.

Metadata Locks (MDL)

MDLs are automatically applied to maintain table structure consistency, preventing DDL operations during active transactions. Common MDL types include:

SQL Statement	MDL Type	Compatibility
LOCK TABLES ... READ	SHARED_READ_ONLY	Compatible with other shared locks
LOCK TABLES ... WRITE	SHARED_NO_READ_WRITE	Exclsuive, blocks others
SELECT ... or SELECT ... LOCK IN SHARE MODE	SHARED_READ	Compatible with SHARED_READ and SHARED_WRITE, exclusive with EXCLUSIVE
INSERT, UPDATE, DELETE, SELECT ... FOR UPDATE	SHARED_WRITE	Compatible with SHARED_READ and SHARED_WRITE, exclusive with EXCLUSIVE
ALTER TABLE ...	EXCLUSIVE	Exclusive with all other MDLs

Intention Locks

Intention locks optimize table lock acquisision by indicating row-level lock presence, reducing the need to scan individual rows. Types include:

• Intention Shared Lock (IS): Set with SELECT ... LOCK IN SHARE MODE, compatible with shared table locks but exclusive with exclusive table locks.
• Intention Exclusive Lock (IX): Set with DML operations like INSERT, UPDATE, DELETE, or SELECT ... FOR UPDATE, exclusive with both shared and exclusive table locks.

Intention locks are automatical released on transaction commit and do not conflict with each other.

Row-Level Locks

Row-level locks target individual rows, offering fine granularity with minimal lock contention and high concurrency, supported in InnoDB. They include record locks, gap locks, and next-key locks.

Record Locks

Record locks lock specific rows to prevent updates or deletions by other transactions.

• Shared Lock (S): Allows reads but blocks writes; compatible with other shared locks, exclusive with exclusive locks.
• Exclusive Lock (X): Blocks both reads and writes; exclusive with all other locks.

In InnoDB, record locks are index-based. Operations without indexes may escalate to table locks.

Gap Locks and Next-Key Locks

Gap locks lock index gaps to prevent inserts, addressing phantom reads. Next-key locks combine record and gap loccks, locking both data and gaps. In repeatable read isolation, next-key locks are the default for searches and scans.

Locking rules in repeatable read isolation:

• Basic unit is next-key lock with interval (open, closed].
• Only accessed object are locked.
• On unique index equality queries: if the record exists, next-key lock degrades to record lock; if not, it degrades to gap lock.
• Gap locks can coexist across transactions.
• Next-key locks prevent phantom reads but are disabled in read committed isolation.

Isolation levels affect lock usage:

Isolation Level	Dirty Read	Non-Repeatable Read	Phantom Read	Supported Locks
READ UNCOMMITTED	Yes	Yes	Yes	Record lock
READ COMMITTED	No	Yes	Yes	Record lock
REPEATABLE READ	No	No	Yes (partially)	Record, gap, next-key locks
SERIALIZABLE	No	No	No	Record, gap, next-key locks

Higher isolation levels enhance data safety at the cost of performance.