MySQL Table Locking

Table locking in MySQL is a mechanism used to control access to a table by multiple database clients to ensure data consistency and prevent conflicts or corruption when multiple clients access the same data simultaneously. Locking allows only one client to modify data in a table at a time or ensures that data is not modified while it is being read by another process.

MySQL supports different types of table locks, which can be controlled manually or implicitly depending on the operation being performed.

Types of Table Locks in MySQL

Explicit Locks:
- LOCK TABLES: Used to manually lock one or more tables.
- UNLOCK TABLES: Used to release the table lock.
Implicit Locks: These locks are automatically applied by MySQL when certain operations are performed on a table.
- READ LOCK: Allows multiple clients to read the table but prevents any modifications.
- WRITE LOCK: Allows only the current client to read and write to the table and blocks other clients from accessing the table until the lock is released.

LOCK TABLES

The LOCK TABLES statement is used to lock one or more tables explicitly. When a table is locked, other sessions cannot access the locked table until the lock is released.

Syntax:

LOCK TABLES table_name [READ | WRITE], table_name2 [READ | WRITE];

READ: Locks the table for reading only (other sessions can read but cannot modify the table).
WRITE: Locks the table for both reading and writing (other sessions cannot read or modify the table).

Example:

LOCK TABLES orders WRITE, customers READ;

This locks the orders table for both reading and writing and locks the customers table for reading only. Other clients cannot modify the orders table and cannot modify or even read the customers table until the lock is released.

Releasing Locks

Once you've finished the operations on the locked tables, you should unlock them to release the locks.

UNLOCK TABLES;

This will release all locks on the tables for the current session.

Implicit Table Locks

Certain operations in MySQL implicitly acquire table locks. These operations are handled automatically by MySQL and do not require the user to manually lock or unlock tables.

1. `SELECT` with `LOCK IN SHARE MODE`

When you execute a SELECT query with LOCK IN SHARE MODE, the table is locked for reading, preventing other sessions from modifying the table but allowing them to read it.
```
SELECT * FROM orders LOCK IN SHARE MODE;
```

2. `SELECT FOR UPDATE`

The SELECT FOR UPDATE statement acquires an exclusive lock on the rows returned by the query. Other sessions are blocked from modifying those rows until the lock is released.
```
SELECT * FROM orders WHERE order_id = 1 FOR UPDATE;
```

3. `UPDATE` or `DELETE` Statements

When you execute an UPDATE or DELETE query, MySQL automatically acquires a write lock on the table being updated or deleted.
```
UPDATE orders SET order_status = 'Shipped' WHERE order_id = 1;
```
This will lock the table to ensure that no other operation can modify the table until the update is completed.

InnoDB Locking

InnoDB, MySQL's default storage engine, uses row-level locking instead of table-level locking. This allows multiple clients to modify different rows in the same table simultaneously. However, InnoDB also provides a way to lock the entire table if needed.

1. Row-Level Locking in InnoDB

InnoDB uses row-level locking for SELECT FOR UPDATE, UPDATE, and DELETE queries. This means that only the specific rows involved in the transaction are locked, leaving the rest of the table available for other operations.

2. Table-Level Locking in InnoDB

While InnoDB prefers row-level locking, it can still use table-level locks in some cases, particularly when using explicit locks with LOCK TABLES.

MyISAM Locking

MyISAM, another storage engine in MySQL, uses table-level locking. This means that when a query modifies data in a table, the entire table is locked for the duration of the query, preventing other queries from accessing or modifying the table.

Read Locks: Multiple SELECT queries can read from a MyISAM table simultaneously, but no UPDATE or DELETE operations can occur while a SELECT query is running.
Write Locks: When an UPDATE, DELETE, or INSERT query is run, MySQL locks the entire table, and no other operations can access the table.

Locking Modes

READ Lock: Allows concurrent reads from the table, but blocks any write operations.
WRITE Lock: Exclusively locks the table for the current session, blocking both reads and writes from other sessions.

Deadlocks in MySQL

A deadlock occurs when two or more transactions are waiting for each other to release locks, and none of them can proceed. In MySQL, deadlocks are automatically detected, and one of the transactions is rolled back to break the deadlock.

Deadlock Example:

Transaction 1:

START TRANSACTION;
UPDATE orders SET order_status = 'Shipped' WHERE order_id = 1;
UPDATE customers SET status = 'Active' WHERE customer_id = 1;

Transaction 2:

START TRANSACTION;
UPDATE customers SET status = 'Inactive' WHERE customer_id = 1;
UPDATE orders SET order_status = 'Pending' WHERE order_id = 1;

Both transactions are waiting on each other, causing a deadlock.

How MySQL Handles Deadlocks:

MySQL automatically detects the deadlock and rolls back one of the transactions to resolve the issue, allowing the other to proceed. The rolled-back transaction will receive an error like:

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Locking Granularity

Table-level Locking: Applies the lock to the entire table.
Row-level Locking: Applies the lock only to specific rows (used by InnoDB).

In general, row-level locking (used by InnoDB) is more efficient in high-concurrency environments, whereas table-level locking (used by MyISAM) can be less efficient but simpler to manage.

Conclusion

Table locking in MySQL ensures data integrity by preventing multiple clients from modifying data simultaneously in conflicting ways. By using various locking mechanisms such as LOCK TABLES, implicit locks, and row-level locking in InnoDB, you can control concurrent access to tables and rows in MySQL. Properly managing locks is essential to avoid issues like deadlocks and to optimize database performance under high-concurrency scenarios.

Souy Soeng

MySQL Table Locking