Database locks are fundamental mechanisms designed to ensure data consistency and integrity during concurrent access. In the context of MySQL, specifically the InnoDB storage engine, locks are categorized by their duration, purpose, and granularity.

Latch vs. Lock

In the MySQL ecosystem, it is essential to distinguish between a latch and a lock.

Latch (Physical Lock)

A latch is a lightweight synchronization primitive used by the database engine to manage access to internal memory structures. Latches are typically held for a very short duration and do not have a deadlock detection mechanism. They are divided into:

• Mutex: Mutual exclusion for specific memory resources.
• RW-Lock: Read-write locks allowing multiple readers or a single writer to access internal structures like buffer pools.

Lock (Logical/Transaction Lock)

A lock is associated with transactions and operates on higher-level database objects like rows, tables, or pages. Unlike latches, locks are usually released upon transaction completion (Commit or Rollback) and support deadlock detection. These are the locks developers typically interact with when optimizing SQL queries.

Functional Categories: Shared and Exclusive Locks

From a functional perspective, locks define how transactions interact with data sets:

• Shared Lock (S): Often called a Read Lock. Multiple transactions can hold an S-lock on the same resource, allowing concurrent reads but preventing any transaction from modifying the data.
• Exclusive Lock (X): Often called a Write Lock. Only one transaction can hold an X-lock. It prevents any other transaction from acquiring any lock type (S or X) on the same resource until it is released.

Locking Granularity

MySQL categorizes locks based on the amount of data they protect, ranging from entire databases to single records.

1. Global Locks

A global lock affects the entire database instance, putting all tables into a read-only state. This is primarily used for performing consistant physical backups of the entire database.

-- Applying a global read lock
FLUSH TABLES WITH READ LOCK;

-- Releasing the global lock
UNLOCK TABLES;

While active, any Data Definition Language (DDL) or Data Manipulation Language (DML) statements from other sessions will be blocked. For InnoDB, using the --single-transaction flag with backup tools is generally preferred to avoid this heavy operation.

2. Table-level Locks

Table-level locks manage access at the table hierarchy. They have lower overhead but offer less concurrency than row-level locks.

Table Locks (READ/WRITE)

• Table Read Lock: Allows all sessions to read the table but prevents all sessions (including the owner) from writing to it.
• Table Write Lock: Allows the owning session to read and write, but prevents all other sessions from any access.

Metadata Lock (MDL)

MDL is managed automatically to maintain the integrity of table structures. If a transaction is actively reading from a table, another transaction cannot perform a DROP TABLE or ALTER TABLE until the first transaction concludes. This prevents conflicts between structure changes and data operations.

Intention Locks

Intention locks are table-level locks that indicate which type of lock (Shared or Exclusive) a transaction intends to acquire on rows with in that table. They help the engine quickly determine if a table-level lock can be granted without scanning every row.

• Intention Shared (IS): Indicates a transaction intends to set S-locks on individual rows.
• Intention Exclusive (IX): Indicates a transaction intends to set X-locks on individual rows.

3. Row-level Locks

Specific to the InnoDB engine, row-level locks provide the highest level of concurrency by locking only specific records. These are implemented on index entries rather than the actual data records.

Record Lock

This locks a single index record. For example, executing SELECT * FROM inventory WHERE id = 10 FOR UPDATE; prevents other transactions from updating or deleting the row with ID 10.

Gap Lock

A Gap Lock protects the space between index records (but not the records themselves). Its primary purpose is to prevent "phantom reads" by stopping other transactions from inserting new data into these gaps during a transaction.

Next-Key Lock

This is a combination of a Record Lock and a Gap Lock. It locks the index record and the gap preceding it. This is the default locking behavior for InnoDB in Repeatable Read (RR) isolation mode.

Lock Escalation and Indexing

InnoDB's locking behavior is heavily influenced by how indexes are utilized in a query:

• Unique Index Exact Match: When searching for a specific record using a primary key or unique index, InnoDB optimizes the lock to a simple Record Lock.
• Non-Unique Index Match: When using a non-unique index, InnoDB may employ Next-Key locks and Gap locks to protect the range.
• Full Table Scans: If a DML statement (like UPDATE) does not use an index, InnoDB must scan every record, which can result in every row being locked, effectively behaving like a table-level lock and severely impacting performance.

Understanding these mechanisms is crucial for preventing deadlocks and ensuring high-performance database applications under heavy concurrent loads.