MYSQL unlock and lock Table introduction, mysql unlock lock table

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;