MYSQL unlock and lock Table introduction, mysql unlock lock table
MySQL lock Overview
Compared with other databases, MySQL locks are relatively simple. The most notable feature is that different storage engines support different locks. For example, the MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking ), however, table-level locks are also supported. the InnoDB Storage engine supports both row-level locking and table-level locks, but row-level locks are used by default.
The features of the three MySQL locks are summarized as follows.
Overhead, locking speed, deadlock, granularity, concurrency Performance
L table-level locks: the costs are small and the locks are fast. No deadlocks occur. The lock granularity is large, and the probability of lock conflicts is highest and the concurrency is lowest.
L row-level locks: high overhead and slow locking; deadlocks may occur; the minimum lock granularity, the lowest probability of lock conflicts, and the highest concurrency.
L page lock: the overhead and lock time are between table locks and row locks. Deadlocks may occur. The lock granularity is between table locks and row locks, with a moderate concurrency.
MyISAM Table lock
The MyISAM storage engine only supports table locks, which is the only supported lock type in MySQL versions. With the increasing requirements of applications on transaction integrity and concurrency, MySQL began to develop a transaction-based storage engine, later, we gradually saw the support for page locks for the BDB storage engine and the InnoDB Storage engine that supports row locks (InnoDB is actually a separate company and has now been acquired by Oracle ). However, MyISAM Table locks are still the most widely used lock types. This section describes how to use the MyISAM Table lock.
Query table-Level Lock contention
You can analyze the table lock contention on the system by checking 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 indicates a serious table-Level Lock contention.
Obtain InnoDB row lock contention
You can check the InnoDB_row_lock status variable to analyze the contention of row locks on the system:
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 it is found that the lock contention is serious, such as InnoDB_row_lock_waits and InnoDB_row_lock_time_avg
Unlock
First
Show processlist;
Locate the lock process and kill the id;
Second
Mysql> unlock tables;
Lock table
Lock the data table to avoid updating the table during the backup process.
Mysql> lock tables tbl_name READ;
Add a write lock to the table:
Mysql> lock tables tbl_name WRITE;