All kinds of locks in MySQL

Time:2021-2-24

Lock is a mechanism for computer to coordinate multiple processes or threads to access a resource concurrently. Lock ensures the consistency and effectiveness of data concurrent access; lock conflict is also an important factor affecting the performance of database concurrent access. Lock is the concurrency control of MySQL in server layer and storage engine layer.
Locking consumes resources. Various operations of the lock include obtaining the lock, detecting whether the lock has been released, releasing the lock, etc.

Shared lock and exclusive lock

  • Shared lock (read lock): other transactions can read, but not write.
  • Exclusive lock (write lock): other transactions cannot read or write.

Different storage engines of MySQL support different lock mechanisms. All storage engines present the lock mechanism in their own way. The server layer does not understand the lock implementation in the storage engine at all
1. MyISAM storage engine adopts table level locking
2. InnoDB storage engine supports both row level locking and table level locking, but row level locking is adopted by default
3. By default, table lock and row lock are obtained automatically, and no additional command is required

Table level lock: low cost, fast locking; no deadlock; large locking granularity, the highest probability of lock conflict, the lowest concurrency.
Row level lock: high cost, slow locking, deadlock, minimum locking granularity, lowest probability of lock conflict and highest concurrency

MyISAM table level lock mode:
Table read lock: it will not block other users’ read requests to the same table, but it will block write requests to the same table;
Table write lock: it will block other users from reading and writing the same table;
The read and write operations of the MyISAM table are serial. When a thread obtains a write lock on a table, only the thread holding the lock can update the table. The read and write operations of other threads will wait until the lock is released.
By default, a write lock has a higher priority than a read lock: when a lock is released, the lock will give priority to the lock acquisition request waiting in the write lock queue, and then give priority to the lock acquisition request waiting in the read lock queue.
This is the reason why MyISAM tables are not suitable for applications with a large number of update operations and query operations, because a large number of update operations will make it difficult for query operations to obtain read locks, which may block forever. At the same time, some query operations that need to run for a long time will also make the writing thread “starve”. In the application, we should try to avoid long-running query operations (if possible, we can use intermediate table and other measures to “decompose” the SQL statement, so that each step of the query can be completed in a short time, so as to reduce the lock conflict. If complex queries are unavoidable, they should be executed in the idle time of the database as far as possible. For example, some regular statistics can be executed at night.)
You can set and change the priority of read lock and write lock
By specifying the start parameter low priority updates, MyISAM engine gives priority to read requests by default.
By executing the command set low_ PRIORITY_ Updates = 1, which reduces the priority of update requests issued by the connection.
By specifying the low of insert, update and delete statements_ Priority property to reduce the priority of the statement.
Give the system parameter max_ write_ lock_ Set an appropriate value of count. When the read lock of a table reaches this value, MySQL will temporarily reduce the priority of the write request, giving the read process a chance to obtain the lock.

MyISAM locking method:
MyISAM will automatically add read lock to the involved table before executing the query statement (select) and update the table before executing the update operation
(update, delete, insert, etc.) will automatically lock the involved tables. This process does not require user intervention. Therefore, users generally do not need to explicitly lock the MyISAM table directly with the lock table command.
In the case of automatic locking, MyISAM always gets all the locks needed by SQL statement at one time, which is the reason why MyISAM table will not have deadlock free.

To view table level lock contention:
This can be done by checking the table_ locks_ Waited and table_ locks_ The immediate state variable is used to analyze the contention of table lock on the system_ locks_ If the value of waited is high, there is serious table level lock contention
All kinds of locks in MySQL
Table_ locks_ Immediate: the number of lock requests that can obtain the table level lock immediately
Table_ locks_ Waited: the number of lock requests that need to wait because the table level lock cannot be obtained immediately
If table_ locks_ immediate / Table_ locks_ It’s better to use InnoDB engine if it’s waited > 5000,
Because InnoDB is a row lock and MyISAM is a table lock, the effect of InnoDB will be better for applications with high concurrent writing!

MySQLInnoDB lock mechanism based on
There are two major differences between InnoDB and MyISAM: one is that it supports transaction; the other is that it adopts row level lock. There are many differences between row level lock and table level lock. InnoDB’s normal select id from table where id = 1 does not lock. Next, we will discuss InnoDB’s lock in detail;

1: Introduction of InnoDB row lock
Shared lock (s): allows a transaction to read a row and prevents other transactions from obtaining the exclusive lock of the same data set, that is, the row I read. You can’t modify it, but everyone can read this row, and others can read other data in the table or operate other row data;
Exclusive lock (x): allows transactions that obtain exclusive locks to update data, and prevents other transactions from obtaining shared read locks and exclusive write locks of the same dataset. That is to say, the row I update does not allow other transactions to read and update the same row;
In addition, in order to allow row lock and table lock to coexist and realize multi granularity lock mechanism, InnoDB also has two kinds of internal intention locks, both of which are table locks.
Intentionally shared lock (is): a transaction intends to add a row shared lock to a data row. Before adding a row shared lock to a data row, the transaction must obtain the is lock of the table.
Intent exclusive lock (IX): a transaction intends to add an exclusive lock to a data row. Before adding an exclusive lock to a data row, the transaction must obtain the IX lock of the table.
The intent lock is automatically added by InnoDB and does not need user intervention. For update, delete and insert statements, InnoDB will automatically add exclusive locks (x) to the data sets involved; for ordinary select statements, InnoDB will not add any locks;

As for InnoDB locking mechanism, the implementation principle is as follows
InnoDB row lock is realized by locking the index entries on the index, which is similar to MySQLOracleThe latter is realized by locking the corresponding data row in the data block. This row lock implementation feature of InnoDB means that only by retrieving data through index conditions, InnoDB can use row level lock, otherwise, InnoDB will use table lock! Indexes are divided into primary key index and secondary index. If a SQL statement operates the primary key index, MySQL will lock the primary key index; if a statement operates the secondary index, MySQL will lock the secondary index first, and then lock the related primary key index. InnoDB row locking is realized by locking the index items on the index, which is different from mysqlOracleThe latter is realized by locking the corresponding data row in the data block. This row lock implementation feature of InnoDB means that only by retrieving data through index conditions, InnoDB can use row level lock, otherwise, InnoDB will use table lock! Indexes are divided into primary key index and secondary index. If a SQL statement operates the primary key index, MySQL will lock the primary key index; if a statement operates the secondary index, MySQL will lock the secondary index first, and then lock the related primary key index
All kinds of locks in MySQL

We should pay attention to the InnoDB locking mechanism
1) InnoDB row lock is implemented by locking the index entries. If there is no index, InnoDB will lock the records through hidden cluster index. In other words: if the data is not retrieved by index conditions, InnoDB will lock all the data in the table, and the actual effect is the same as the table lock.
2) Since the row lock of MySQL is a lock for the index, not for the record, although it is to access the records of different rows, if the same index key is used, there will be a lock conflict. To put it bluntly, where id = 1 for update will lock all data rows with id = 1. If it is where id = 1 and name =’liuwenhe’for update, it will lock all data rows with id = 1 and name =’liuwenhe ‘;
3) When a table has multiple indexes, different transactions can use different indexes to lock different rows. In addition, InnoDB will use row locks to lock data, whether using primary key index, unique index or ordinary index.
4) Even if the index field is used in the condition, whether to use the index to retrieve data is decided by the MySQL optimizer by judging the cost of different execution plans. If MySQL thinks that the full table scanning efficiency is higher, for example, for some very small tables, it will not use the index, or the diet conversion, or the like percent sign is in the front, etc., InnoDB will use the table lock in this case Instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan to see if the index is actually used. 4) Even if the index field is used in the condition, whether to use the index to retrieve data is decided by the MySQL optimizer by judging the cost of different execution plans. If MySQL thinks that the full table scanning efficiency is higher, for example, for some very small tables, it will not use the index, or the diet conversion, or the like percent sign is in the front, etc., InnoDB will use the table lock in this case Instead of row locks. Therefore, when analyzing lock conflicts, don’t forget to check the SQL execution plan to see if the index is actually used.

This can be done by checking InnoDB_ row_ Lock state variable to analyze the contention of row locks on the system

show status like 'innodb_row_lock%';

All kinds of locks in MySQL
Innodb_ row_ lock_ current_ Waits: the current number of locks waiting
Innodb_ row_ lock_ Time: the total locking time since the system was started, unit: Ms.
Innodb_ row_ lock_ time_ AVG: average locking time, unit: Ms.
Innodb_ row_ lock_ time_ Max: maximum locking time, unit: Ms.
Innodb_ row_ lock_ Waits: the total number of times the system has been locked since the system was started.
If the lock contention is serious, such as InnoDB_ row_ lock_ Waits and InnoDB_ row_ lock_ time_ The value of AVG is relatively high

This work adoptsCC agreementReprint must indicate the author and the link of this article

Hu Jun

Recommended Today

Bullet shooting based on sceneform (drawing bullet trajectory)

Bullet shooting based on sceneform (drawing bullet trajectory) The sceneform framework is very powerful. When I don’t understand sceneform, I feel that I need to know OpenGL in order to make 3D scenes, and the learning curve of OpenGL is very steep. After contacting this framework, I feel that Xiaobai can also get started quickly, […]