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.
But table-level locks allow multithreading to read data from a data table at the same time, but if another thread wants to write data, it must first obtain exclusive access. When you are updating data, you must wait until the update is complete before other threads can access the table. (This mechanism causes the table lock contention for concurrent read and write to cause blocking access)
1, query table-level lock contention
to 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 |  (indicates the number of times a lock can be acquired immediately)
  | table_locks_waited | 305089 |  (indicates the number of times a lock cannot be acquired immediately, waits for a lock)        &NBSP,
  + ——————— –+ ———-+
  2 rows in Set ( 0.00 sec)
  table_locks_waited/(table_locks_immediate+table_locks_waited)
  The larger the scale value, the more serious the table-level lock contention is.
  Example: Scale value =0.01 Description 100 processes There is a process that needs to wait for a lock;

2, MySQL table-level lock mode
The table-level lock for MySQL has two modes: table-Shared 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.).
So for the MyISAM table, the following occurs:
A, read operation 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, writes to the MyISAM table (with write locks), blocks read and write operations on the same table by other processes, and reads and writes to other processes only after the write lock is released.

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 for 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, when Concurrent_insert is set to 0 o'clock, concurrent insertions are not allowed.
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, allows a record 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.
We can adjust the scheduling behavior of the MyISAM with some settings:
A, by specifying the startup parameter low-priority-updates, so that the MyISAM engine defaults to the right to read requests.
B, by executing command set Low_priority_updates=1, reduces the priority of update requests made by this connection.
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 first 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 do not fundamentally address queries and updates 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.
In addition, the MYISAM data table is stored according to a single file, can be locked for a single table file, but InnoDB is an entire file, the index, data, structure are all stored in the Ibdata file, so you must use row locking.
Dead lock
The so-called deadlock <deadlock>: Refers to two or more than two processes in the course of execution,
As a result of competing for resources, a kind of waiting phenomenon, if there is no external force, they will not be able to proceed.
At this point the system is in a deadlock state or the system generates a deadlock, and these processes, which are always aulympic each other, are known as deadlock processes.
Table-level locks do not generate deadlocks. So the solution to the deadlock is mostly true for the most commonly used InnoDB.
How deadlocks are handled

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

Mysql> show Processlist; #查看正在执行的sql (show full processlist; view all SQL)
Mysql> Kill ID #杀死sql进程;
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