MySQL Locking

Posted on | In

At its core, a lock is a mechanism that allows a database system (like MySQL) to regulate concurrent access to data. When a transaction or operation needs to read or modify data, it acquires a lock on that data. This lock prevents other transactions from accessing or modifying the same data in a way that would lead to inconsistency or data corruption.

Key Principles of Locking

  • Concurrency Control: Locks ensure that multiple users or processes can access the database simultaneously without interfering with each other’s operations.

  • Data Consistency: They prevent phenomena like dirty reads (reading uncommitted data), non-repeatable reads (reading the same data twice and getting different results because another transaction committed a change in between), and phantom reads (seeing new rows appear or disappear in a range query due to concurrent insertions/deletions).

  • Data Integrity: They protect data from being corrupted by simultaneous updates that could overwrite each other’s changes.

  • Transaction Isolation: Locks are fundamental to implementing ACID properties, specifically “Isolation,” ensuring that concurrent transactions appear to execute serially.

Types of Locks (Simplified for understanding)

MySQL (especially with InnoDB, its primary storage engine) uses a sophisticated locking mechanism. The most common types are:

Shared Locks (S-locks / Read Locks)

  • Acquired when a transaction wants to read data.
  • Multiple transactions can hold shared locks on the same data simultaneously.
  • A shared lock prevents an exclusive lock from being acquired on the same data.
  • Analogy: Multiple people can read the same book at the same time (e.g., in a library reading room), but no one can modify it.

Exclusive Locks (X-locks / Write Locks)

  • Acquired when a transaction wants to modify (insert, update, delete) data.
  • Only one transaction can hold an exclusive lock on particular data at any given time.
  • An exclusive lock prevents any other shared or exclusive locks from being acquired on the same data.
  • Analogy: Only one person can borrow a book and make annotations or changes to it at a time.

Lock Granularity

Locks can be applied at different levels of granularity:

  • Row-level locks: The most common and desirable for high concurrency. Only the specific rows being accessed are locked. InnoDB primarily uses row-level locking.

  • Page-level locks: Less granular than row-level, but more granular than table-level.

  • Table-level locks: Locks the entire table, preventing any other operations (reads or writes) on that table. MyISAM primarily uses table-level locking. InnoDB also uses table-level locks for DDL operations (e.g., ALTER TABLE).

  • Metadata Locks (MDL): Protects database objects (tables, functions, etc.) from concurrent DDL and DML operations that would conflict. For example, an ALTER TABLE cannot proceed if there are active queries on the table, and vice-versa.

Lock Commands

It’s crucial to understand that MySQL’s primary storage engine, InnoDB (the default), handles most locking automatically at the row level within transactions.

You rarely explicitly “lock a row” or “lock a page” yourself in application code. The LOCK TABLES statement is an explicit table-level lock that bypasses InnoDB’s finer-grained control and is generally discouraged for high-concurrency applications using InnoDB.

Instance Wide Lock

For replication, we use instance lock even before dump/load as we want to get the binlog file and position at that point.

1
2
3
4
5
6
# Wait for active transactions to complete or flush caches
mysql> FLUSH TABLES WITH READ LOCK;

# This lock is released automatically when the session that issued the command
disconnects, so if you quit the session it is the same as UNLOCK TABLES;
mysql> UNLOCK TABLES;

I tested that after running FTWRL, from a new terminal login and the insert operation hangs(blocked) until the lock is released.

Table Lock

Read Lock

1
LOCK TABLES products READ;

What it does: Allows the session holding the lock to read from products. Allows other sessions to read from products without explicitly acquiring a lock. Prevents any session (including the one holding the lock) from writing to products.

Behavior for session holding the lock:

  • Can execute SELECT statements on products.
  • Cannot execute INSERT, UPDATE, DELETE on products.

Behavior for other sessions:

  • Can execute SELECT statements on products.
  • INSERT, UPDATE, DELETE statements on products will be blocked (wait) until the lock is released.

Write Lock

1
LOCK TABLES products WRITE;

What it does: Grants exclusive access to the table for the session holding the lock. The session holding the lock can both read and write. All other sessions are blocked from both reading and writing to products.

Behavior for session holding the lock:

  • Can execute SELECT, INSERT, UPDATE, DELETE statements on products.

Behavior for other sessions:

  • All SELECT, INSERT, UPDATE, DELETE statements on products will be blocked (wait) until the lock is released.

Release Lock

1
UNLOCK TABLES;
0%