MySQL lock mechanism detailed and deadlock processing method

In order to better optimize MySQL in high concurrency, it is necessary to understand the locking table mechanism when MySQL query is updated.
I. Overview
MySQL has three levels of Lock: page level, table level, row level.
The MyISAM and memory storage engines use a table-level lock (Table-level locking), and the BDB storage engine uses a page lock (page-level
Locking), but also supports table-level locks; 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:
Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock conflict is the highest probability, concurrency is the lowest.
Row-level locks: high overhead, slow locking, deadlock, minimum lock granularity, the lowest probability of lock collisions, and the highest degree of concurrency.
Page locks: overhead and lock times are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common.

Second, MyISAM table lock
The MyISAM storage engine supports only table locks and is now the most used storage engine.
1, query table level lock contention situation
You can analyze table lock contention on the system by examining the table_locks_waited and table_locks_immediate state variables:
Mysql> Show status like ' table% ';
+ ——————— –+ ———-+
| variable_name | Value |
+ ——————— –+ ———-+
| Table_locks_immediate | 76939364 |
| table_locks_waited | 305089 |
+ ——————— –+ ———-+
2 rows in Set (0.00 sec) table_locks_waited has a higher value, indicating a more serious table-level lock contention situation.

2, the MySQL table-level lock Lock mode
There are two modes of table-level lock for MySQL: Table shared read lock (tables read lock) and table exclusive write lock (table write
Lock). MyISAM will automatically add read locks to all tables involved before executing the query statement (SELECT), and will automatically write locks to the table involved before performing the update operation (update, DELETE, INSERT, etc.).
As a result, the MyISAM table will be operated in the following situations:
A, read operations on the MyISAM table (read-lock), does not block other processes from reading requests to the same table, but blocks write requests to the same table. Write operations for other processes are performed only when the read lock is released.
b, write to the MyISAM table (write lock), will block the other process to the same table read and write operations, only when the write lock is released, the other process will perform read and write operations.

3. Concurrent Insertion
In principle, when a data table has a read lock, other processes cannot update the table, but under certain conditions, the MyISAM table also supports concurrency of query and insert operations.
The MyISAM storage engine has a system variable Concurrent_insert that is specifically designed to control the behavior of its concurrent insertions, with values of 0, 1, or 2, respectively.
A, concurrent insertions are not allowed when Concurrent_insert is set to 0 o'clock.
B, when Concurrent_insert is set to 1 o'clock, if there are no holes in the MyISAM table (that is, rows in the middle of the table are not deleted), MyISAM allows one process to read the table while another process inserts records from the end of the table. This is also the default setting for MySQL.
C, when Concurrent_insert is set to 2 o'clock, the record is allowed to be inserted concurrently at the end of the table, regardless of whether there is an empty hole in the MyISAM table.
4, MyISAM lock scheduling
Because MySQL considers writing requests to be more important than read requests, MySQL will take precedence in writing if there are read and write requests at the same time. This way, when a large number of update operations are performed (especially in the case of an index in the updated field), the MyISAM table makes the query operation difficult to obtain a read lock, which causes the query to block.
There are some settings that we can use to adjust the scheduling behavior of MyISAM:
A, by specifying the startup parameter low-priority-updates, so that the MyISAM engine defaults to the read request to give priority to the right.
b, by executing the command set Low_priority_updates=1, the update request issued by this connection has a lower priority.
C, reduce the priority of the statement by specifying the Low_priority property of the Insert, UPDATE, DELETE statement.
The above 3 methods are either update priority or query first method. Here to illustrate is, do not blindly to the MySQL set to read first, because some long-running query operations, will also make the write process "starved". Only according to your actual situation, to decide which action to set priority.

These methods There is still no fundamental solution to the problem of querying and updating at the same time. in a MySQL with large data volumes and published, we can also use another strategy to optimize the load balancing by MySQL master-slave (read-write) separation, which avoids the priority of which operations can lead to a blockage of another operation. Here is a space to illustrate MySQL's read-write separation technology.

myisam uses the function of the Flock class, which is to lock the whole file directly (called File lock), InnoDB uses the function of the Fcntl class, can lock the local data in the file (called row lock), so the difference is here.

deadlock
so-called deadlock <deadlock>: Refers to two or more than two processes in the course of execution,
at this point the system is in a deadlock state or the system has a deadlock, and these processes, which are always mutually aulympic, are known as deadlock processes.
encounter How deadlock is handled

Mysql-uxxx-pxxx-h Server IP--port= server port; (be sure to bring IP and port if the server has IP and port access set )

mysql> show processlist; View the currently executing SQL (show full processlist; view all SQL)

mysql> Kill ID # Kill SQL process;

If there are too many processes to find, restart MySQL.

/ect/init.d/mysql restart

or /ect/init.d/mysql Stop (kill-9 process ID directly if it is not turned off) and /ect/init.d/mysql start

Check to see if the MySQL log file holds the deadlock log:

Common directory:/var/log/mysqld.log; (This directory also has other related log files to look at)

How to solve or to see the specific question.

MySQL lock mechanism detailed and deadlock processing method