MySQL lock mechanism

I. Overview
MySQL's lock 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.
• Table-level Lock: Low overhead, lock fast, no deadlock, lock granularity, lock collision 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 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.
From the above features, table-level locks are more suitable for queries, only a small number of applications that update data by index criteria, such as Web applications, and row-level locks are more suitable for applications that have a large number of simultaneous updates by index criteria and concurrent queries, such as some online transaction processing (OLTP) systems.
Second, MyISAM table lock
The MyISAM storage engine supports only table locks and is now the most used storage engine. 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, and so on), and this process does not require user intervention.

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 still do not solve the problem of query and update fundamentally 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.
three, InnoDB line lock

InnoDB implements the following two types of row locks.

? Shared Lock (S): Allows a transaction to read one line, preventing other transactions from acquiring an exclusive lock on the same data set.

? Exclusive Lock (X): A transaction that allows an exclusive lock to update data, preventing other transactions from acquiring shared read and exclusive write locks of the same data set.

In addition, in order to allow row and table locks to coexist and implement a multi-granularity locking mechanism, INNODB also has two intent locks (Intention Locks) that are used internally, both of which are table locks.

? Intent shared Lock (IS): The transaction intends to add a row of shared locks to the data row, and the transaction must obtain the IS lock of the table before sharing it with a data row.

? Intent exclusive Lock (ix): The transaction intends to add an exclusive lock to the data row, and the transaction must obtain an IX lock on the table before adding an exclusive lock to the data row.

The compatibility of the above lock modes is shown in the following table.

InnoDB line lock mode compatibility List

Request Lock mode is compatible Current lock mode	X	Ix	S	Is
X	Conflict	Conflict	Conflict	Conflict
Ix	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
Is	Conflict	Compatible	Compatible	Compatible

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.

The intent lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement, and the transaction can be displayed to the recordset with shared or exclusive locks.

• Shared Lock (S): SELECT * FROM table_name WHERE ... LOCK in SHARE MODE.

• Exclusive Lock (X): SELECT * FROM table_name WHERE ... For UPDATE.

Use SELECT ... In SHARE mode, a shared lock is used primarily to confirm the existence of a row of records when data dependencies are needed, and to ensure that no one is doing an update or delete operation on the record. However, if the current transaction also requires an update to the record, it is most likely to cause deadlocks, and for applications that require an update operation after locking the row records, you should use SELECT ... The for Update method obtains an exclusive lock.

Reprint Please specify address: http://www.phpddt.com/db/mysql-locking.html respect for the work of others is to respect their own!

MySQL lock mechanism