MySQL lock mechanism details and deadlock Handling Methods

MySQL lock mechanism details and deadlock Handling Methods

To optimize MySQL in high concurrency, it is necessary to understand the lock table mechanism when MySQL queries an update.

I. Overview
MySQL has three levels of locks: Page, table, and row.
The MyISAM and MEMORY storage engines use table-level locking. The BDB storage engine uses page-level locking.
The InnoDB Storage engine supports both row-level locking and table-level locking. However, row-level locking is used by default.
The features of the three MySQL locks are summarized as follows:
Table-level locks: low overhead, fast locking, no deadlock, large lock granularity, the highest probability of lock conflict, the lowest concurrency.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock conflict, and the highest concurrency.
Page lock: 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 general concurrency.

Ii. MyISAM Table lock
The MyISAM storage engine only supports table locks and is the most widely used storage engine.
However, table-level locks allow multithreading to read data from the data table at the same time, but if another thread wants to write data, it must first obtain exclusive access. When updating data, you must wait until the update is complete before other threads can access the table. (This mechanism makes concurrent reads and writes prone to table lock contention, leading to blocked access)
1. 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 | 76939364 | (the number of times the lock can be obtained immediately)
| Table_locks_waited | 305089 | (meaning that the lock cannot be obtained immediately and the number of times to wait for the lock ;)
+ -------- + ---- +
2 rows in set (0.00 sec)
Table_locks_waited/(Table_locks_immediate + Table_locks_waited)
The larger the proportion value, the more severe the table-Level Lock contention.
For example, the proportion = 0.01 indicates that one of the 100 processes needs to wait for the lock;

2. MySQL table-Level Lock mode
There are two Table-level locks in MySQL: Table Read Lock and Table Write
Lock ). Before executing a query statement (SELECT), MyISAM automatically locks all involved tables before performing UPDATE operations (UPDATE, DELETE, INSERT, etc, A write lock is automatically applied to the involved table.
Therefore, the MyISAM table may be operated in the following situations:
A. Read operations (read lock) on the MyISAM table will not block read requests from other processes to the same table, but will block write requests to the same table. Write operations of other processes are performed only after the read lock is released.
B. Write operations on the MyISAM table (with write locks) will block read and write operations on the same table by other processes. Only when the write lock is released, to read and write other processes.

3. Concurrent Inserts
In principle, when a data table has a read lock, other processes cannot update the table. However, under certain conditions, the MyISAM table also supports concurrent query and insert operations.
The MyISAM storage engine has a system variable concurrent_insert, which is used to control its Concurrent Insertion behavior. The values can be 0, 1, or 2, respectively.
A. Concurrent inserts are not allowed when concurrent_insert is set to 0.
B. When concurrent_insert is set to 1, if the MyISAM table has no holes (that is, the rows in the middle of the table are not deleted), MyISAM allows a process to read the table at the same time, another process inserts records from the end of the table. This is also the default setting of MySQL.
C. When concurrent_insert is set to 2, records can be inserted concurrently at the end of the table regardless of whether there are holes in the MyISAM table.
4. Lock Scheduling for MyISAM
MySQL considers that write requests are generally more important than read requests. Therefore, if a read/write request is performed simultaneously, MYSQL will give priority to write requests. In this way, when the MyISAM Table performs a large number of update operations (especially when indexes exist in the updated fields), it is difficult for the query operation to obtain the read lock, leading to query blocking.
We can adjust the scheduling behavior of MyISAM through some settings:
A. by specifying the startup parameter low-priority-updates, the MyISAM engine gives the Read Request priority by default.
B. Run the SET LOW_PRIORITY_UPDATES = 1 command to lower the priority of the update request sent by the connection.
C. Reduce the priority of an INSERT, UPDATE, or DELETE Statement by specifying the LOW_PRIORITY attribute.
The above three methods are either Update-first or query-first methods. It should be noted that do not blindly set mysql to read first, because some query operations that require a long time to run will also starve the write process ". You can only decide which operation is preferred based on your actual situation.
These methods do not fundamentally solve the problem of query and update at the same time. In a mysql with a large amount of data and published data, we can use another method for optimization, that is, implementing Load Balancing through mysql master-slave (read/write) separation, in this way, you can avoid the first operation which may lead to congestion of the other operation. The following sections describe the mysql read/write splitting technology.

MyISAM uses a flock function, which is directly used to lock the entire file (called file lock). InnoDB uses a fcntl function, you can lock the local data in the file (called row lock), so the difference is here.
In addition, MyISAM data tables are stored in a single file and can be locked for a single table file. However, InnoDB is an entire file that stores indexes, data, and structures in the ibdata file, therefore, row locking is required.
Deadlock
The so-called DeadLock <DeadLock>: refers to two or more processes in the execution process,
A phenomenon of mutual wait caused by competition for resources, if there is no external force, they will not be able to continue.
It is said that the system is in a deadlock state or the system has a deadlock. These processes that are always waiting for mutual deadlock are called deadlock processes.
Table-level locks do not produce deadlocks. Therefore, the most common InnoDB is to solve deadlocks.
Deadlock Handling Method

Mysql-uxxx-pxxx-h Server ip Address -- port = server port; (if the server is configured with ip address and port access, it must contain ip address and port)

Mysql> show processlist; # view the SQL statement being executed (show full processlist; view all SQL statements)
Mysql> kill id # kill the SQL process;
If too many processes cannot be found, restart mysql.
/Ect/init. d/mysql restart
Or/ect/init. d/mysql stop (kill-9 process id if it cannot be closed) and then/ect/init. d/mysql start
Check whether the mysql Log File saves the deadlock log:
Common directories:/var/log/mysqld. log. (You can check other related log files in this directory)
How to solve the problem depends on the specific problem.

This article permanently updates the link address: