MySQL lock overview Compared to other databases, MySQL's locking mechanism is relatively simple, and its most notable feature is that different storage engines support different locking mechanisms. For example, the MyISAM and memory storage engines use table-level locks (table-level locking), BDB storage engines use page locks (page-level locking), but table-level locks are also supported The InnoDB storage engine supports both row-level locks (row-level locking) and table-level locks, but row-level locks are used by default. MySQL features of these 3 types of locks can be broadly summarized as follows.overhead, lock speed, deadlock, granularity, concurrency performance
L table-level lock: Low overhead, lock fast, no deadlock, lock granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.
L row-level lock: High overhead, locking slow, deadlock, locking granularity is the least, the probability of lock conflict is the lowest, and the concurrency is the highest.
L page Lock: Overhead and lock time are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common. The MyISAM table lock MyISAM storage engine supports only table locks, which is the only type of lock supported by MySQL in the first few versions. As applications continue to improve transactional integrity and concurrency requirements, MySQL begins to develop a transaction-based storage engine, and then slowly comes the BDB storage engine that supports page locks and the InnoDB storage engine that supports row locks (the actual innodb is a separate company, Has now been acquired by Oracle Corporation). But MyISAM's table lock is still the most widely used lock type. This section describes the use of MyISAM table locks in detail. Querying table-level lock contention can analyze table lock contention on the system by examining table_locks_waited and table_locks_immediate state 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 higher, then there is a more serious table-level lock contention condition. Get InnoDB row lock contention you can analyze the contention for row locks on the system by examining the Innodb_row_lock state variables: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 more severe, such as innodb_row_lock_waits and INNODB_ROW_LOCK_TIME_AVG values are higher, you can also pass
Unlock
First Kind
Show Processlist;
Find the lock process, kill ID;
The second Kind
Mysql>unlock TABLES;
Lock table
Lock data tables to avoid tables being updated during backup
Mysql>lock TABLES tbl_name READ;
Add a write lock to the table:
Mysql>lock TABLES tbl_name WRITE;
MySQL warm so intimate (iii)
