Detailed examples of unlocking and locking tables in MYSQL

Compared with other databases, MySQL's lock mechanism is relatively simple. Its most significant feature is that different storage engines support different lock mechanisms.

MySQL lock overview

Compared with other databases, MySQL's locking mechanism is relatively simple. Its most significant 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 locks; the InnoDB storage engine supports both row-level locks (row-level locking), table-level locking is also supported, but row-level locking is used by default.

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

Overhead, locking speed, deadlock, granularity, concurrency performance

l Table-level lock: low overhead, fast locking; no deadlock; large locking granularity, lock conflict occurs has the highest probability and the lowest concurrency.
L line -level lock: Large expenses, slow locks; dead locks; the minimum locking particle size, the lowest probability of locking conflict, and the highest concurrency.
l                                                                                  
l              The cost and locking time are between table locks and row locks; deadlocks will occur; the locking granularity is between table locks and row locks, and the concurrency is average.
MyISAM table lock

The MyISAM storage engine only supports table locks, which is also the only lock type supported in the first few versions of MySQL. With the continuous improvement of application requirements for transaction integrity and concurrency, MySQL began to develop transaction-based storage engines. Later, the BDB storage engine that supports page locks and the InnoDB storage engine that supports row locks slowly appeared (actually InnoDB is a separate A company that has now been acquired by Oracle). However, MyISAM's table lock is still the most widely used lock type. This section will introduce the use of MyISAM table locks in detail. Query

Table-level lock contention

You can analyze table lock contention on the system by checking table_locks_waited and table_locks_immediatestatusVariables
：
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 that there is a serious table-level lock contention.

Get InnoDB row lock contention

You can analyze the row lock contention on the system by checking the InnoDB_row_lock status variable:
mysql> show status like 'innodb_row_lock%';
+----------------------------------+-------+
| Variable_name                                                    |
+----------------------------------+-------+
| 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 | |
+---------- --------------------+-------+
5 rows in set (0.01 sec)
If lock contention is found to be serious , if the values ​​​​of InnoDB_row_lock_waits and InnoDB_row_lock_time_avg are relatively high, you can also unlock it through

The first

show processlist;

Find the lock process, kill id;

Second type

mysql>UNLOCK TABLES;

Lock table

Lock the data table to avoid During the backup process, the table was updated

##mysql>LOCK TABLES tbl_name READ;

Add a write lock to the table:

mysql>LOCK TABLES tbl_name WRITE;

【Related recommendations】

1. Mysql free video tutorial

2. How to improve the speed of database query for millions of items

3. 5 Frequently Made Database Design Mistakes

4. Detailed explanation of the sql statement that does not change the original content of Mysql and adds content later

5. Summary of how to write commonly used SQL statements in MySQL

The above is the detailed content of Detailed examples of unlocking and locking tables in MYSQL. For more information, please follow other related articles on the PHP Chinese website!