MYSQL unlock and lock table introduction

MySQL Lock Overview

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and MEMORY storage engines use table-level locking; the BDB storage engine uses page-level locking, but also supports table-level locking; the InnoDB storage engine supports both row-level locking and table-level locking, but uses row-level locking by default.

The characteristics of these three types of MySQL locks can be roughly summarized as follows.

Overhead, locking speed, deadlock, granularity, and concurrency performance

l Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, and the lowest concurrency.
l Row-level lock: high overhead, slow locking; deadlock may occur; the locking granularity is the smallest, the probability of lock conflict is the lowest, and the concurrency is the highest.
l Page lock: The overhead and locking time are between table lock and row lock; deadlock may occur; the locking granularity is between table lock and row lock, and the concurrency is average.

MyISAM table locks

The MyISAM storage engine only supports table locks, which is the only lock type supported in the first few versions of MySQL. As applications' requirements for transaction integrity and concurrency continued to increase, MySQL began to develop a transaction-based storage engine. Later, the BDB storage engine that supports page locks and the InnoDB storage engine that supports row locks gradually appeared (InnoDB is actually a separate company and has now been acquired by Oracle). However, MyISAM table locks are still the most widely used lock type. This section will introduce the use of MyISAM table locks in detail.
Query table-level lock contention

Table lock contention on your system can be analyzed by examining the table_locks_waited and table_locks_immediate status variables:
mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979 |
| Table_locks_waited | 0 |
+-----------------------+-------+
2 rows in set (0.00 sec))
If the value of Table_locks_waited is relatively high, it means that there is serious table-level lock contention.

Get InnoDB row lock contention status

You can analyze the row lock contention on your system by checking the InnoDB_row_lock status variable:
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| InnoDB_row_lock_current_waits | 0 |
| InnoDB_row_lock_time | 0 |
| InnoDB_row_lock_time_avg | 0 |
| InnoDB_row_lock_time_max | 0 |
| InnoDB_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.01 sec)
If lock contention is found to be serious, such as high values ​​of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg, you can also

Unlock

The first

show processlist;

Find the lock process and kill its ID;

The second

mysql>UNLOCK TABLES;

Lock table

Lock the data table to prevent the table from being updated during the backup process

mysql>LOCK TABLES tbl_name READ;

Add a write lock to the table:

mysql>LOCK TABLES tbl_name WRITE;