What you need to understand about MySQL locks

1. Introduction

MySQL locks can be divided into global locks, table locks, and row locks according to their scope. Row locks are implemented by the database engine. Not all engines provide row locks. MyISAM does not support row locks, so this article will use the InnoDB engine as an example to introduce row locks.

2. Global Lock

MySQL provides a global lock to lock the entire database instance.

grammar:

FLUSH TABLES WITH READ LOCK

This statement is generally used for backup. When this statement is executed, all open tables in the database will be closed and all tables in the database will be locked with a global read lock. At the same time, update statements (additions, deletions, and modifications) of other threads, data definition statements (creating tables, modifying table structures), and update transaction submissions will be blocked.

After MySQL 8.0, MySQL can directly use the backup lock for backup.

Statement:

LOCK INSTANCE FOR BACKUPUNLOCK INSTANCE

This lock has a wider scope. It prevents the creation, renaming, and deletion of files, including REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE operations and account management. Of course, these operations can be performed on memory temporary tables. Why are memory tables not subject to these restrictions? Because memory tables do not need to be backed up, there is no need to meet these conditions.

3. Table lock

Mysql's table-level locks are divided into two categories: one is metadata lock (MDL), and the other is table lock.

Metadata locks (MDL) do not need to be used explicitly and are automatically acquired when a table is accessed. This feature requires MySQL version 5.5 or above to support it. When adding, deleting, modifying, or checking a table, an MDL read lock will be added to the table; when the table structure is changed, an MDL write lock will be added. MDL locks have some rules:

Read locks are not mutually exclusive, so multiple threads can add, delete, modify, and query the same table. Read-write locks and write locks are mutually exclusive. To ensure the security of table structure changes, if multiple threads need to perform table structure operations such as adding fields to the same table, it will become serialized and require lock waiting. The write lock priority of MDL is higher than the read lock priority of MDL, but you can set the max_write_lock_count system variable to change this situation. When the write lock request exceeds the number set by this variable, the MDL read lock priority will be higher than the MDL write lock priority. (By default, this number is large, so there is no need to worry about the write lock priority decreasing.) The MDL lock must be released only after the transaction ends.

Therefore, we must be careful not to use long transactions when operating the database table structure. What does this mean specifically? Let me give you an example:

The above figure shows the execution of statements by four sessions. First, SessionA starts a transaction but does not commit it. Then, SessionB executes a query. Because they obtain the MDL read lock, they do not affect each other and can execute normally. SessionC adds a field. Because MDL write and read are mutually exclusive, SessionC will be blocked. Then SessionD starts to execute a query statement. Because SessionC is blocked, SessionD is also blocked. Therefore, the transaction of SessionA we simulated is a long transaction, and then the table structure is modified, which will make all subsequent read and write operations on the table infeasible. Therefore, in actual scenarios, if business requests are frequent, modifications to the table structure may cause the library's threads to be blocked.

The syntax for table locks is as follows:

LOCK TABLES tbl_name [[AS] alias] lock_type [, tbl_name [[AS] alias] lock_type] ... lock_type: { READ [LOCAL] | [LOW_PRIORITY] WRITE} UNLOCK TABLES

Table locks are divided into read locks and write locks. Read locks are not mutually exclusive, but data cannot be written after obtaining a read lock. Other sessions that have not obtained a read lock can also read the table, so the purpose of a read lock is to restrict the table from being written. If the table is locked by a read lock, an error will be reported when executing the insert statement. The error is as follows:

1099 - Table 'XXXX' was locked with a READ lock and can't be updated

After the write lock is acquired, the table can be read and written. The write lock is mutually exclusive. Once a session acquires the write lock of a table, other sessions cannot access the table until the write lock is released.

The table can be unlocked using unlock tables , or it can be unlocked automatically by the client port. lock tables tables will lock the table exclusively, which not only restricts other threads from reading and writing the table, but also restricts the next operation object of this thread.

4. Row Lock (InnoDB)

MySQL row locks are implemented at the engine level, so here we discuss row locks under the InnoDB engine. The following will introduce several common row locks under InnoDB in detail.

4.1 Shared Locks

Shared locks allow transactions to perform read operations after acquiring the lock. Shared locks are not mutually exclusive. After one transaction acquires a shared lock, another transaction can also acquire the shared lock. Write operations cannot be performed after acquiring a shared lock.

4.2 Exclusive Lock

An exclusive lock allows a transaction to update or delete a row after acquiring the lock. As the name implies, an exclusive lock is mutually exclusive. After a transaction acquires an exclusive lock, other transactions cannot acquire the exclusive lock until the lock is released.

4.3 Intention Lock

InnoDB supports locks of multiple granularities, allowing row locks and table locks to coexist. The intention lock mentioned here is actually a table-level lock, but I put it in the row lock because it will not exist alone. Its appearance will definitely be accompanied by a row lock (shared lock or exclusive lock). Its main purpose is to indicate that the rows in the table will be locked or are being locked.

Intention locks can be divided into the following types according to their combination with row locks:

• Intentional exclusive lock: indicates that exclusive locks will be acquired on certain rows in the table
• Intentional shared lock: Indicates that shared locks will be acquired on certain rows in the table.

The acquisition of intention lock must be before the acquisition of row lock, that is, the shared intention lock must be acquired before the shared lock, and the same is true for the exclusive lock.

So what exactly does this intention lock do?

Before explaining this, let's take a look at the compatibility relationship between intention locks and row locks: