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

This article mainly explains "What is MySQ database locking mechanism", interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let Xiaobian take you to learn "What is MySQ database locking mechanism"!

1. MySQL locking mechanism introduction

Each storage engine uses three types of locking mechanisms

Row-level locking

Table-level locking

Page-leve: Page-leve is between row-leve and table-leve.

2. MySQL database

Table-level locking in Mysql database is mainly MyISAM, Memory, CSV and other non-transactional storage engines, row-level locking is mainly InnoDB storage engine and NDB Cluster storage engine, page-level locking is mainly Berkeley DB storage engine.

3. MyISAM table-level locking falls into two main types

Read lock: When a new client requests to acquire a read lock resource, it needs to satisfy two conditions:

The resource requested is not currently write-locked

No higher priority write locks in the Pending write-lock queue

(Only affects write operations)

write lock

(Affects both read and write operations)

4. queue

Mysql is divided into four queues to maintain these two types of locks: two to store the currently locked read and write lock information, and the other two to store the pending read and write lock information, 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 locks (also called read locks)

An exclusive lock that allows one transaction to read a row and prevents other transactions from acquiring the same data.

Exclusive lock (also known as: write lock)

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

Intentional shared lock

intentional exclusive lock

6. InnoDB Gap Lock

The locking of InnoDB is achieved by spatially marking the locking information before the first index key and after the last index key pointing to the data record. This locking method is called "NEXT-KEY locking"

Gap Lock Weakness: After locking a range, even some non-existent keys will be locked innocently, causing no data to be inserted into the key lock when locked.

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

InnoDB forgoes row-level locking in favor of table-level locking when Query cannot utilize indexes, resulting in reduced concurrency performance;

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

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

7. MyISAM table lock optimization advice

Shorten lock time

Minimize large complex queries by splitting them into several smaller Query executions.

Build indexes as efficient as possible to make data retrieval faster.

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

Take advantage of the right opportunities to optimize MyISAM table data files.

separation of parallel operations

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

concurrent_insert = 1. When there is no free space in the MyISAM storage engine table data file, Concurrent Insert can be performed from the end of the file.

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

MyISAM is not only fully serialized, MyISAM storage engine also has a feature Concurrent Insert (concurrent insert) feature.

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

Rational use of read and write priorities

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

8. InnoDB row lock optimization advice

Try to make all data retrieval done through indexes, so as to avoid InnoDB upgrading to table-level locking because it cannot be locked through index keys.

Reasonable index design, so that InnoDB locks on index keys as accurately as possible, as much as possible to narrow the scope of locking, to avoid unnecessary locking and affect the execution of other queries.

Minimize range-based data retrieval filters to avoid locking records that should not be locked due to the negative impact of gap locks.

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

Use a lower level of transaction isolation whenever the business environment allows, reducing the additional cost of MySQL implementing a transaction isolation level.

9. System lock contention query

MySQL internally has two dedicated sets of state variables to record resource contention within the system.

contention state variable for table-level locking

mysql> show status like 'table%';

Table_locks_immediate: Number of table-level locks generated;

Table_locks_waited: Number of times a table lock contention occurred and waited

Table_locks_immediate values greater than Table_locks_waited 5000 are more appropriate, in large need to analyze the problem.

The two status values are recorded from system startup, and 1 is added for each occurrence. If the status value of Table_locks_waited is relatively high here, it indicates that the table-level locking contention is serious, and further analysis is needed.

InnoDB row-level lock state variable records

sql> show status like 'innodb_row_lock%';

Innodb_row_lock_current_waites: the number of locks currently pending;

Innodb_row_lock_time: total time length from system startup to lock;

Innodb_row_lock_time_avg: Average time spent per wait;

Innodb_row_lock_time_max: the longest time spent waiting from system startup to now;

Innodb_row_lock_waits: Total number of waits since system startup.

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

10. in addition

In addition to providing the above five system state variables, InnoDB also provides richer real-time state information. The implementation method is as follows:

Create the InnoDB Monitor table to enable InnoDB monitor functionality

mysql > create table innodb_monitor(a int) engine=innodb;

Then execute "show innodb status" to view details

Why create innodb_monitor table?

Creating this table tells InnoDB that we need to start monitoring its details, and InnoDB will then record the more detailed transaction-level locking information in MySQL's error log for further analysis.

At this point, I believe that everyone has a deeper understanding of "what is the MySQ database locking mechanism". Let's actually operate it! Here is the website, more related content can enter the relevant channels for inquiry, pay attention to us, continue to learn!

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.