Shulou(Shulou.com)05/31 Report--

This article is about what the MySQL database locking mechanism is, the editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article. Let's take a look at it with the editor.

1. Brief introduction of MySQL locking mechanism

Each storage engine uses three types of locking mechanisms

Row-level locking (row-level)

Table level locking (table-level)

Page level locking (page-leve): page level locking is between row level locking and table level locking.

2. In MySQL database

Table-level locking is mainly non-transactional storage engines such as MyISAM, Memory, CSV, etc. Row-level locking is mainly InnoDB storage engine and NDB Cluster storage engine, and page-level locking is mainly BerkeleyDB storage engine.

3. There are two main types of MyISAM table-level locking.

Read lock. When a new client applies for read lock resources, it needs to meet two conditions:

The resource requested to be locked is not currently written locked

There is no higher priority write lock waiting in the write lock waiting queue (Pending write-lock queue)

(only write operations are affected)

Write lock

(affects both read and write operations)

4. MySQL is mainly divided into four queues to maintain these two locks:

Two store the read and write locking information that is currently being locked, and the other two store the read and write locking west information while waiting, as follows:

Current read-lock queue (lock- > read)

Pending read-lock queue (lock- > read_wait)

Current write-lock queue (lock- > write)

Pending write-lock queue (lock- > write_wait)

5. There are four types of row-level locking for InnoDB.

Shared lock (there are called read locks)

Allows one transaction to read a row, preventing other transactions from acquiring exclusive locks of the same data.

Exclusive lock (there is a write lock)

Allow transactions that acquire exclusive locks to update data and block other transactions

Intention shared lock

Intention exclusive lock

6. InnoDB gap lock

The locking of the InnoDB is achieved by the airspace space tag locking information before and after the * * index key pointing to the data record. This locking method is called "NEXT-KEY locking" (gap lock).

Gap lock weakness: after locking a range, even some key values that do not exist will be innocently locked, making it impossible to insert any data in the key value lock.

There are several other major performance pitfalls in the way locking is achieved through indexes:

Hongmeng official Strategic Cooperation to build HarmonyOS Technology Community

When Query cannot take advantage of indexes, InnoDB will abandon row-level locking and use table-level locking instead, resulting in concurrency degradation

When the index used by Query does not contain all the filtering conditions, some of the data in the index keys used in data retrieval may not belong to the row of the result set of Query, but will also be locked because the gap lock locks a range, not a specific index key.

When Query uses indexes to locate data, they will also be locked if they use the same index keys but access different rows of data (indexes are only part of the filter criteria).

7. Recommendations for optimizing MyISAM table locks

Shorten lock time

Minimize the large complex Query and split the complex Query into several small Query implementations.

Build a sufficiently efficient index as much as possible to make data retrieval faster.

Try to make the tables of the MyISAM storage engine store only the necessary information and control the field types.

Take advantage of the appropriate opportunity to optimize the MyISAM data file.

Separate operations that can be performed in parallel

Concurrent_insert = 2, Concurrent Insert is allowed at the end of the data file regardless of whether there is free space left by deleting data in the middle of the table data file of the MyISAM storage engine.

Concurrent_insert = 1MyISAM stores when there is no free space in the middle of the engine table data file, you can Concurrent Insert from the end of the file.

Concurrent_insert = 0, Concurrent Insert is not allowed regardless of whether there is free space left by deleting data in the middle of the table data file of the MyISAM storage engine. (insertion is not allowed when reading locks)

MyISAM is not only completely serialized, but the MyISAM storage engine also has a feature called Concurrent Insert (concurrent insertion).

The MyISAM storage engine has a parameter option that controls whether the Concurrent insert feature is enabled: concurrent_insert can be set to 0 / 1 / 2: details are as follows:

Make rational use of reading and writing priority

Table-level locking: by default, the write priority is higher than the read priority. If there are many read operations, you can set the read priority to be high, and the parameter low_priority_updates = 1.

8. InnoDB row lock optimization recommendation

Let all data retrieval be done through the index as much as possible, so as to prevent InnoDB from upgrading to table-level locking because it cannot be locked by the index key.

Design the index reasonably, make InnoDB lock on the index key as accurately as possible, narrow the locking range as much as possible, and avoid causing unnecessary locking and affecting the execution of other Query.

Reduce the filtering conditions of range-based data retrieval as much as possible to avoid locking records that should not be locked because of the negative impact of gap locks.

Try to control the transaction size, reduce the amount of locked resources and the length of time locked.

As far as the business environment allows, try to use a lower level of transaction isolation to reduce the additional cost of implementing the transaction isolation level in MySQL.

9. System lock contention query

There are two sets of dedicated state variables within MySQL to record the resource contention within the system.

Contention state variables for table-level locking

Mysql > show status like 'table%'

Table_locks_immediate: the number of times table-level locks are generated

Table_locks_waited: the number of waits due to table-level lock contention

It is appropriate that the Table_locks_immediate value is greater than Table_locks_waited 5000, and it is necessary to analyze the problem when it is large.

Both status values are recorded after the system starts, adding 1 each time. If the Table_locks_waited status value here is relatively high, it means that the table-level lock contention is serious and needs further analysis.

InnoDB row-level lock state variable record

Sql > show status like 'innodb_row_lock%'

Innodb_row_lock_current_waites: number of locks currently waiting

Innodb_row_lock_time: the total length of time since the system was booted to the present lock

Innodb_row_lock_time_avg: average time spent waiting

Innodb_row_lock_time_max: the time it took to wait for the longest time since the system started.

Innodb_row_lock_waits: the total number of waits since the system was started.

5 states, the more important are Innodb_row_lock_time_avg (average waiting time), Innodb_row_lock_waits (total waiting time) and Innodb_row_lock_time (total waiting time)

10. InnoDB

In addition to the above five system state variables, it also provides more abundant real-time state information, which is implemented as follows:

Create an InnoDB Monitor table to turn on the monitor function of InnoDB

Mysql > create table innodb_monitor (an int) engine=innodb

Then execute "show innodb status" to view the details

Why create innodb_monitor tables?

The above is what the MySQL database locking mechanism is, and the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.