Mysql database locking mechanism detailed introduction _mysql

Objective

In order to ensure consistent integrity of data, there is a locking mechanism in any database. The quality of locking mechanism should be directly thought of the concurrent processing ability and performance of a database system, so the realization of locking mechanism becomes one of the core technologies of various databases. This chapter will analyze the locking mechanisms of the two most frequently used storage engines MyISAM and InnoDB in MySQL.

Introduction to MySQL locking mechanism

The database locking mechanism is simply a rule that the database is designed to ensure the consistency of data so that various shared resources can be ordered by concurrent access access. For any kind of database need to have the corresponding locking mechanism, so MySQL nature is no exception. MySQL database because of its own architecture characteristics, there are a variety of data storage engine, each storage engine for the application of different features are not the same, in order to meet the needs of their specific application scenarios, each storage engine locking mechanism for each of the specific scenarios faced by the optimization design, So the locking mechanism of each storage engine also has a big difference.

Overall, the MySQL storage engine uses three types (level) locking mechanisms: row-level locking, page-level locking, and table-level locking. Here we first analyze the MySQL three kinds of locking characteristics and their pros and cons.

Row-level locking (Row-level)

The most important feature of row-level locking is that the particle size of the locked object is very small, which is the smallest of the locking granularity achieved by the large database management software at present. Because the locking particle size is very small, the probability of contention for the lock resource is also minimal, which can give the application as much concurrent processing ability as possible to improve the overall performance of the high concurrent application system.

Although the ability to deal with concurrent processing has a greater advantage, but row-level locking has brought a lot of drawbacks. Because the size of the lock resource is very small, so each lock and release lock need to do more things, the resulting consumption of natural is even greater. In addition, row-level locking is the most prone to deadlocks.

Table-level Locking (Table-level)

In contrast to row-level locking, table-level locking is the maximum granularity locking mechanism in the MySQL storage engine. The most important feature of the locking mechanism is that the implementation logic is very simple and the system has the least negative impact. So getting the lock and releasing the lock is fast. Because table-level locks lock the entire table at once, it's good to avoid the deadlock problem that bothers us.

Of course, the biggest negative impact of the locking particle size is that the probability of locking resource contention will be the highest, resulting in a generous discount.

Page-level locking (Page-level)

Page-level locking is one of the more unique locking levels in MySQL and is not too common in other database management software. Page-level locking is characterized by a locking granularity between a row-level lock and a table-level lock, so the resource overhead required to acquire a lock and the ability to provide concurrent processing are equally between the two. In addition, as page-level locking and row-level locking, deadlocks occur.

In the process of database resource lock-in, with the decrease of the granularity of the locking resource, the amount of memory needed to lock the same data quantity is more and more, and the algorithm is more and more complicated. However, with the decrease of the granularity of the locking resource, the possibility of the application's access request encountering the lock waiting will decrease, and the system's overall concurrency is also improved.

In the MySQL database, there are some non-transactional storage engines, such as myisam,memory,csv, that use table-level locking, while row-level locking is primarily the InnoDB storage engine and the Ndbcluster storage engine. Page-level locking is primarily the way the BerkeleyDB storage engine is locked.

MySQL's locking mechanism is largely determined by its original history. At first, MySQL wanted to design a locking mechanism that was completely independent of the various storage engines, and in the early MySQL database, the MySQL storage engine (MyISAM and Momery) was designed to "allow access (including read) to a single thread at any table at the same time." On this assumption. However, as MySQL continues to improve and the system improves, the MySQL developer has to revise the previous assumptions when the MySQL3.23 version is developed. Because they find that a thread is reading a table, another thread can insert the table, but only to the end of the data file. This is what we call the concurrent Insert, which is provided from MySQL starting with version 3.23.

After the concurrent insert, the MySQL developer had to modify the lock implementation functionality in the system, but only increased support for concurrent inserts and did not change the overall architecture. Before long, however, with the introduction of the BerkeleyDB storage engine, the previous locking mechanism faced a greater challenge. Because the BerkeleyDB storage engine does not have the MyISAM and memory storage engines allowing only a single thread to access a table at the same time, the granularity of this single-threaded access limit is reduced to a single page. This once again forces MySQL developers to modify the locking mechanism again.

Because of the introduction of the new storage engine, which caused the locking mechanism to not meet the requirements, the MySQL people realized that it was impossible to implement a completely independent locking implementation mechanism to meet the requirements of various storage engines. If the overall performance of the storage engine is degraded due to the poor implementation of the locking mechanism, it will certainly be a serious blow to the enthusiasm of the storage engine provider, which is something that MySQL does not want to see because it is completely out of line with MySQL's strategic approach to development. So engineers had to give up the original design and make changes in the lock implementation mechanism, allowing the storage engine to change the lock type that MySQL passed through the interface and decide how to lock the data itself.

Table-Level locking

MySQL's table-level locking is mainly divided into two types, one is read lock, the other is write lock. In MySQL, these two locks are maintained primarily through four queues: two read and write lock information in the currently locked lock, while the other two hold read-write locking information in the wait, as follows:

Copy Code code 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)

Information about all threads currently holding read locks can be found in currentread-lockqueue, and the information in the queue is stored in the order of time acquired to the lock. The information that is waiting for the lock resource is stored in the Pendingread-lockqueue, and the other two queues that hold the write lock information are stored according to the same rules above.

While for those of us who use MySQL as a lock (table lock) for both read and write locks, there are as many as 11 types of locking in the MySQL internal implementation, defined by an enumerator (Thr_lock_type) in the system, and the values are described as follows:

Lock type	Description
IGNORE	Internal interaction is used when a lock request occurs, and there is no information store in the lock structure and queue
UNLOCK	The type of interaction used to release the lock request
READ	Normal read lock
WRITE	Normal write lock
Read_with_shared_locks	Used in InnoDB, produced as follows: SELECT ... Lockinsharemode
Read_high_priority	High-priority Read lock
Read_no_insert	Concurentinsert locks are not allowed
Write_allow_write	This type is actually when the storage engine handles the lock itself, MYSQLD allows other threads to fetch read or write locks, because even if the resource conflicts, the storage engine will know how to handle the
Write_allow_read	This type of locking occurs when the table is being DDL (altertable ...). , MySQL can allow other threads to acquire read locks because MySQL is implemented by rebuilding the entire table and then rename it, where the original table can still provide read services
Write_concurrent_insert	The locking method used when the Concurentinsert is in progress, and any read lock requests other than Read_no_insert will not be blocked when the lock is in progress
Write_delayed	Type of lock when using insertdelayed
Write_low_priority	Displays the lower-level locking of the declaration, which is generated by setting the Low_priority_updat=1
Write_only	When a lock exception is interrupted during an operation, a closetable operation is required within the system, and the type of locking that occurs in this process is write_only

Read lock

A new client request needs to meet two criteria when requesting access to a read-locked resource:

1, the requested lock resource is not currently write lock;
2, write lock wait queue (Pendingwrite-lockqueue) There is no higher priority write lock wait;

If the above two conditions are met, the request is immediately passed and the relevant information is stored in the Currentread-lockqueue, if any of the above two conditions are not met, will be forced into the waiting queue pendingread-lockqueue to wait for the release of the resource.

Write lock

When a client requests a write lock, MySQL first checks to see if there are any information that locks the same resource in Currentwrite-lockqueue.

If Currentwrite-lockqueue does not, check pendingwrite-lockqueue again, and if found in Pendingwrite-lockqueue, you will need to enter the wait queue and suspend its own thread waiting to lock the resource. Conversely, if the pendingwrite-lockqueue is empty, then the detection of currentread-lockqueue, if there is a lock exists, the same need to enter the Pendingwrite-lockqueue wait. Of course, you may also experience the following two special situations:

1. The type of the request lock is write_delayed;
2. The type of the request lock is Write_concurrent_insert or tl_write_allow_write, and Currentreadlock is the locking type of the Read_no_insert.

When these two special cases are encountered, the write lock is immediately available and enters the current Write-lock queue.

If there is already a write lock that locks the same resource in Currentwrite-lockqueue for the first time, then it is only possible to enter the waiting queue to wait for the release of the corresponding resource lock.

The precedence rules for write lock requests in read requests and write-waiting queues are determined primarily by the following rules:

1. In addition to read_high_priority read lock, write lock in Pendingwrite-lockqueue can block all other read locks;
2. read_high_priority read Lock request can block all pendingwrite-lockqueue in the write lock;
3. In addition to write locking, any other write locks in Pendingwrite-lockqueue have a lower priority than read locks.

After a write lock appears in Currentwrite-lockqueue, all other locked requests are blocked except for the following:

1. With the permission of some storage engines, a Write_concurrent_insert write lock request can be allowed
2. When write lock is Write_allow_write, allow all read and write lock requests except Write_only
3. When write lock is Write_allow_read, allow all read lock requests except Read_no_insert
4. When write lock is write_delayed, allow all read lock requests except Read_no_insert
5. When write lock is Write_concurrent_insert, allow all read lock requests except Read_no_insert

With the development of MySQL storage engine, the locking mechanism provided by MySQL itself has no way to meet the demand, many storage engines have done the storage engine expansion and transformation on the basis of the locking mechanism provided by MySQL.

The MyISAM storage engine is essentially a storage engine that relies on the largest number of table-level locks implemented by the locking mechanism provided by MySQL, although the MyISAM storage engine does not add additional locking mechanisms on its own, but in order to better support the associated features, Based on the original locking mechanism, MySQL has implemented the corresponding transformation to support its concurrentinsert characteristics.

While several other support transaction storage storage engines, such as Innodb,ndbcluster and BerkeleyDB storage engine, allow MySQL to handle locked processing directly to the storage engine itself, holding only write_allow_ in MySQL The lock of the write type.

Since the locking mechanism used by the MyISAM storage engine is entirely a table-level lock provided by MySQL, we will use the MyISAM storage engine as the sample storage engine to illustrate some of the basic features of table-level locking. Because, in order to make the example more intuitive, I'll use the display to add a lock to the table to demonstrate: The Rite_allow_read type of write lock.

Moment	Session a	Session B
	Row Lock Basic Demo
1	Mysql> set autocommit=0; Query OK, 0 rows Affected (0.00 sec)	Mysql> set autocommit=0; Query OK, 0 rows Affected (0.00 sec)
	mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 Update, but not submitted
2		mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; be blocked, wait for
3	Mysql> commit; Query OK, 0 rows affected (0.05 sec) submit
4		mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; Query OK, 0 rows affected (36.14 sec) Rows matched:1 changed:0 warnings:0 unblocked, update normal
	No index upgrade to table lock Demo
5	mysql> Update Test_innodb_lock Set b = ' 2 ' where B = 2000; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0	mysql> Update Test_innodb_lock Set b = ' 3 ' where B = 3000; be blocked, wait for
6
7	Mysql> commit; Query OK, 0 rows affected (0.10 sec)
8		mysql> Update Test_innodb_lock Set b = ' 3 ' where B = 3000; Query OK, 1 row affected (1 min 3.41 sec) Rows matched:1 changed:1 warnings:0 Blocking lifting, completing the update
	A demo of the insertion problem brought by the clearance lock
9	Mysql> select * from Test_innodb_lock; +------+------+ | A | b |+------+------+ | 1 | B2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | B1 | +------+------+ 9 Rows in Set (0.00 sec) mysql> Update Test_innodb_lock Set B = A * where a < 4 and a > 1; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0
10		mysql> INSERT INTO Test_innodb_lock values (2, ' 200 '); be blocked, wait for
11	Mysql> commit; Query OK, 0 rows affected (0.02 sec)
12		mysql> INSERT INTO Test_innodb_lock values (2, ' 200 '); Query OK, 1 row affected (38.68 sec) Blocking lifted, completing insert
	Example of blocking using a common index for different data
13	mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B2 '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
14		mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B1 '; Be blocked
15	Mysql> commit; Query OK, 0 rows affected (0.02 sec)
16		mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B1 '; Query OK, 1 row affected (42.89 sec) Rows matched:1 changed:1 warnings:0 Session commit transaction, blocking removal, update complete
	Deadlock Example
17	mysql> update T1 Set id = $ where id = 11; Query OK, 0 rows Affected (0.00 sec) Rows matched:0 changed:0 warnings:0
18		mysql> update t2 Set id = 210 where id = 21; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
19	Mysql>update T2 set id=2100 where id=21; Wait for SESSIONB to release the resource and be blocked
20		mysql>update T1 set id=1100 where id=11; Query ok,0 rows Affected (0.39SEC) Rows matched:0 changed:0 warnings:0 Wait for Sessiona to release the resource and be blocked
	Two sessions waiting for each other's resources to release their resources, resulting in a deadlock

Row-level locking

Row-level locking is not the way MySQL does it itself, but it is implemented by other storage engines, such as the widely known InnoDB storage engine, and MySQL's distributed storage engine, ndbcluster, to achieve row-level locking.

Innodb locking mode and its implementation mechanism

Considering that row-level locking is implemented by individual storage engines and with different implementations, InnoDB is the most widely used storage engine in the current transaction-type storage engine, so here we analyze the locking features of InnoDB.

In general, there are many similarities between the INNODB locking mechanism and Oracle databases. InnoDB row-level locking is also divided into two types, shared and exclusive locks, and in the process of the locking mechanism in order to allow row-level locking and table-level locking coexist, InnoDB also used the concept of intent lock (table-level locking), there are intent to share the lock and the intent of exclusive lock these two kinds.

When a transaction needs to give its own need for a resource lock, if you encounter a shared lock is locked in the resources you need, you can add a shared lock, but can not add exclusive locks. However, if you encounter a resource that you need to lock and have been occupied by an exclusive lock, you can only wait for the lock to release the resource before you can acquire the locked resource and add your own locks. The purpose of the intent of the lock is when a transaction in need of access to the resource lock, if the need to meet their own resources have been exclusive locks occupy, the transaction can be locked row of the table above add a suitable intent lock. If you need a shared lock, add an intent share lock above the table. And if you need a line (or some line) to add an exclusive lock, first add an intent exclusive lock above the table. The intent sharing lock can coexist multiple simultaneously, but the intent exclusive lock can only have one existence at the same time. So, it can be said that the InnoDB lock mode can actually be divided into four kinds: Shared lock (S), exclusive lock (X), Intent shared lock (IS) and intent exclusive Lock (IX), we can use the following table to summarize the above four kinds of coexistence of the logical relationship:

Shared Lock (S)	Exclusive Lock (X)	Intent shared lock (IS)	Intent exclusive Lock (IX)
Shared Lock (S)	Compatible	Conflict	Compatible	Conflict
Exclusive Lock (X)	Conflict	Conflict	Conflict	Conflict
Intent shared lock (IS)	Compatible	Conflict	Compatible	Compatible
Intent exclusive Lock (IX)	Conflict	Conflict	Compatible	Compatible

Although InnoDB's locking mechanism is quite similar to Oracle's, the implementation of the two is quite different. In general, Oracle Lock data is a table-level locking information on a transaction slot on the physical block where a row of records needs to be locked. The InnoDB lock is achieved by marking the lock information before the first index key of the data record and the airspace space after the last index key. This locking implementation of InnoDB is known as the "next-keylocking" (Gap Lock), because when query is performed through a range lookup in China, he locks all the key values in the entire range, even if the key value does not exist.

The gap lock has a more fatal weakness, that is, when locking a range key value, even if some nonexistent key value will be locked by the innocent, which can not be inserted in the Lock key value range of data. This can be a significant performance hazard in some scenarios. The explanation given by InnoDB is to organize the appearance of Phantom reading, so they choose the clearance lock to achieve the lock.

In addition to the negative effects of gap locking on InnoDB performance, there are several other major performance risks associated with locking through indexing:

When query cannot take advantage of the index, InnoDB discards the use of row-level locking instead of table-level locking, resulting in lower concurrency performance;

When the index used by Quuery does not contain all the filter criteria, the data that the index key that the data retrieval uses may have a part that does not belong to the query's result set, but is also locked because the gap lock locks a range, not a specific index key;

When query uses indexes to locate data, it is locked if the same index key is used, but the data rows accessed are different (the index is only part of the filter condition).

Innodb locking and deadlock at various transaction isolation levels

InnoDB the four transaction isolation levels that are implemented in the ISO/ANSISQL92 specification as defined in the Readuncommited,readcommited,repeatableread and serializable. At the same time, in order to ensure the consistency of data in the transaction, multiple versions of data access are realized.

As we have already described in the first section, row-level locking is bound to lead to deadlock problems, and InnoDB is no exception. As for the deadlock generation process we are not here to describe in detail, in the following lock example will be a practical example for everyone to show the deadlock generation process. Here we mainly introduce how to deal with the InnoDB when the system detects the deadlock.

In the INNODB transaction management and locking mechanism, there is a special detection mechanism for deadlock, will be in the system after the deadlock in a very short time to detect the existence of a damn lock. When the InnoDB detects a deadlock in the system, the INNODB uses the appropriate judgment to select the smaller transaction in the two transaction that generated the deadlock and roll back, leaving another larger transaction successfully completed. What is the InnoDB to judge the size of the transaction? The problem is also mentioned in the official MySQL manual, which actually determines the size of two transactions by calculating the amount of data that two transactions are inserted, updated, or deleted, after InnoDB finds the deadlock. In other words, the more records a firm changes, the less it will be rolled back in the deadlock. But one thing to note is that when a deadlock-generating scenario involves more than InnoDB storage engines, InnoDB is not able to detect a damn lock, which can only be solved by locking out the timeout limit. In addition, an example of the deadlock generation process is demonstrated in the InnoDB locking example at the end of this section.

INNODB Locking Mechanism Example

Copy Code code as follows:

Mysql> CREATE TABLE Test_innodb_lock (a int (one), B varchar ()) Engine=innodb;
Query OK, 0 rows affected (0.02 sec)

Mysql> CREATE index Test_innodb_a_ind on Test_innodb_lock (a);
Query OK, 0 rows affected (0.05 sec)
records:0 duplicates:0 warnings:0

Mysql> CREATE index Test_innodb_lock_b_ind on Test_innodb_lock (b);
Query OK, rows affected (0.01 sec)
Records:11 duplicates:0 warnings:0

Moment	Session a	Session B
	Row Lock Basic Demo
1	Mysql> set autocommit=0; Query OK, 0 rows Affected (0.00 sec)	Mysql> set autocommit=0; Query OK, 0 rows Affected (0.00 sec)
	mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0 Update, but not submitted
2		mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; be blocked, wait for
3	Mysql> commit; Query OK, 0 rows affected (0.05 sec) submit
4		mysql> Update Test_innodb_lock Set b = ' B1 ' where a = 1; Query OK, 0 rows affected (36.14 sec) Rows matched:1 changed:0 warnings:0 unblocked, update normal
	No index upgrade to table lock Demo
5	mysql> Update Test_innodb_lock Set b = ' 2 ' where B = 2000; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0	mysql> Update Test_innodb_lock Set b = ' 3 ' where B = 3000; be blocked, wait for
6
7	Mysql> commit; Query OK, 0 rows affected (0.10 sec)
8		mysql> Update Test_innodb_lock Set b = ' 3 ' where B = 3000; Query OK, 1 row affected (1 min 3.41 sec) Rows matched:1 changed:1 warnings:0 Blocking lifting, completing the update
	A demo of the insertion problem brought by the clearance lock
9	Mysql> select * from Test_innodb_lock; +------+------+ | A | b |+------+------+ | 1 | B2 | | 3 | 3 | | 4 | 4000 | | 5 | 5000 | | 6 | 6000 | | 7 | 7000 | | 8 | 8000 | | 9 | 9000 | | 1 | B1 | +------+------+ 9 Rows in Set (0.00 sec) mysql> Update Test_innodb_lock Set B = A * where a < 4 and a > 1; Query OK, 1 row affected (0.02 sec) Rows matched:1 changed:1 warnings:0
10		mysql> INSERT INTO Test_innodb_lock values (2, ' 200 '); be blocked, wait for
11	Mysql> commit; Query OK, 0 rows affected (0.02 sec)
12		mysql> INSERT INTO Test_innodb_lock values (2, ' 200 '); Query OK, 1 row affected (38.68 sec) Blocking lifted, completing insert
	Example of blocking using a common index for different data
13	mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B2 '; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
14		mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B1 '; Be blocked
15	Mysql> commit; Query OK, 0 rows affected (0.02 sec)
16		mysql> Update Test_innodb_lock Set b = ' bbbbb ' where a = 1 and b = ' B1 '; Query OK, 1 row affected (42.89 sec) Rows matched:1 changed:1 warnings:0 Session commit transaction, blocking removal, update complete
	Deadlock Example
17	mysql> update T1 Set id = $ where id = 11; Query OK, 0 rows Affected (0.00 sec) Rows matched:0 changed:0 warnings:0
18		mysql> update t2 Set id = 210 where id = 21; Query OK, 1 row Affected (0.00 sec) Rows matched:1 changed:1 warnings:0
19	Mysql>update T2 set id=2100 where id=21; Wait for SESSIONB to release the resource and be blocked
20		mysql>update T1 set id=1100 where id=11; Query ok,0 rows Affected (0.39SEC) Rows matched:0 changed:0 warnings:0 Wait for Sessiona to release the resource and be blocked
	Two sessions waiting for each other's resources to release their resources, resulting in a deadlock

Optimizing MySQL by using lock mechanism rationally

MyISAM table Lock Optimization proposal

For the MyISAM storage engine, the lock itself consumes the least amount of resources, although using table-level locking is less costly than implementing row-level locking or page-level locking in the process of locking implementations. However, because of the size of the locked particles, the contention for locking resources is greater than that of other locking levels, which in turn reduces concurrent processing capacity.

Therefore, when optimizing the MyISAM storage engine lock problem, the most important thing is how to make it increase the concurrency degree. Because the locking level is not possible to change, we first need to shorten the locking time as much as possible, and then let the possible concurrent operations be as concurrent as possible.

1, shorten the lock time

Shorten the lock time, just a few words, it is really easy to say, but actually do it is not so simple. How do you keep the lockout time as short as possible? The only way to do this is to keep our query as short as possible.

Reduce the complexity of query by two, splitting complex query into several small query distributions;

Establish an index that is efficient enough to make data retrieval faster;

Try to keep the table of MyISAM storage engine only the necessary information, control the field type;

Optimize MyISAM table data files with appropriate opportunities;

2, the separation can be parallel operation

When it comes to MyISAM table locks and read-write, blocking table locks, some people may think that the MyISAM storage engine's table can only be completely serialized, no way to parallel. Let's not forget that the MyISAM storage engine also has a very useful feature, which is the feature of Concurrentinsert (concurrent insert).

The MyISAM storage engine has a parameter option that controls whether the concurrent Insert feature is turned on: Concurrent_insert, which can be set to 0,1 or 2. A specific description of the three values is as follows:

concurrent_insert=2, whether the middle part of the MyISAM Storage engine's table data file exists because of the free space left by deleting the data, it allows the concurrentinsert at the end of the data file.

Concurrent_insert=1, when there is no free space in the middle of the MyISAM Storage engine table data file, it can be concurrentinsert from the tail of the file.

Concurrent_insert=0, no concurrentinsert is allowed regardless of whether the middle portion of the MyISAM Storage engine's table data file has free space left by deleting the data.

3, reasonable use of reading and writing priority

In the various locking analysis sections of this chapter, we learned that MySQL's table-level locking has a different priority for read and write, and that by default the write priority is greater than the read priority. So, if we can decide the priority of reading and writing according to the difference of the respective system environment. If our system is a read-oriented, and priority to ensure query performance, we can set the system parameter options Low_priority_updates=1, the write priority set to lower than the priority of reading, you can tell MySQL as much as possible to process read requests. Of course, if our system needs a limited guarantee of data write performance, you can not set the Low_priority_updates parameter.

Here we can take advantage of this feature, set the Concurrent_insert parameter to 1, even if the possibility of data deletion is very small, if the temporary waste of a small amount of space is not particularly concerned about the words, will Concurrent_ The Insert parameter set to 2 can be attempted. Of course, there is space in the middle of the data file, in the waste of space, will also cause in the query need to read more data, so if the deletion is not very small, or recommend the Concurrent_insert set to 1 more appropriate.

Innodb Row Lock Optimization recommendations

InnoDB storage Engine because of the implementation of row-level locking, although the implementation of the locking mechanism may bring more performance loss than table-level locking will be higher, but in the overall concurrency processing capacity is much better than the MyISAM table-level locking. When the system concurrent quantity is high, the overall performance of InnoDB and MyISAM will have a more obvious advantage. However, InnoDB row-level locking also has its own fragile side, when we use improper, it may make the overall performance of InnoDB can not be higher than the MyISAM, and may even be worse.

To make reasonable use of InnoDB line-level locking, to avoid weaknesses, we must do the following work:

As much as possible, all data retrieval is done through the index to avoid innodb because it cannot be locked by the index key to the table-level lock;

The reasonable design index, lets the InnoDB when the index key locks on the time is as accurate as possible, reduces the locking scope as far as possible, avoids causes unnecessary locking to affect other query's execution;

Minimize the data retrieval filtering conditions based on the scope, avoid locking the records because of the negative effect caused by the gap lock;

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

Use lower-level transaction isolation as far as the business environment allows, to reduce the additional cost of MySQL due to the transaction isolation level being implemented;

Because of InnoDB row-level locking and transactional, deadlocks are sure to occur, and the following are some of the more commonly used to reduce the probability of deadlock generation

Of the little suggestion that reader friends can be based on their respective business characteristics of targeted attempts: A similar business module, as far as possible in accordance with the same access order to access, to prevent the creation of deadlocks; b in the same transaction, as much as possible to lock all the resources needed to reduce the deadlock generation probability; For business parts that are very prone to deadlock, you can try to use the upgraded locking granularity to reduce the probability of deadlock generation by table-level locking;

System lock contention query for two types of locking level, MySQL has two special sets of state variables inside the system lock resource contention, let's look at

MySQL-implemented contention state variables for table-level locking:

Copy Code code as follows:

Mysql> Show status like ' table% ';
+-----------------------+-------+ | variable_name | Value | +-----------------------+-------+
| Table_locks_immediate | 100 |
| table_locks_waited | 0 |
+-----------------------+-------+

Here are two state variables that record the MySQL internal table-level locking, with two variables described below:

Table_locks_immediate: Number of table-level locks generated;

Table_locks_waited: The number of waits that occurs when table-level locking contention occurs;

Both state values are recorded from the start of the system, and the number plus 1 does not occur once for the corresponding event. If the table_locks_waited state value is higher, then the table-level locking contention in the system is more serious, so it is necessary to further analyze why there are more locked resource contention.

For row-level locking used by InnoDB, the system is recorded by another set of more detailed state variables, as follows:

Copy Code code as follows:

Mysql>showstatuslike ' innodb_row_lock% ';
+-------------------------------+--------+| variable_name| value|+-------------------------------+--------+
| innodb_row_lock_current_waits|0|
| innodb_row_lock_time|490578|
| innodb_row_lock_time_avg|37736|
| innodb_row_lock_time_max|121411|
| innodb_row_lock_waits|13|
+-------------------------------+--------+

The Innodb row-level lock state variable not only records the number of lock waits, but also records the total length of the lock, the average length of each time, and the maximum length, in addition to a non-cumulative status indicating the number of waits currently awaiting a lock. The description of each state amount is as follows:

Innodb_row_lock_current_waits: The number of locks currently waiting;

Innodb_row_lock_time: From system boot to now lock total time length;

Innodb_row_lock_time_avg: Average time spent each wait;

Innodb_row_lock_time_max: From system startup to now wait for the most frequent time spent;

Innodb_row_lock_waits: The total number of waits since the system was started;

For these 5 state variables, it is important to innodb_row_lock_time_avg (wait average time), Innodb_row_lock_waits (total number of Waits), and Innodb_row_lock_ Time (wait total length) these three items. In particular, when the waiting times are high and each wait is not small, we need to analyze why there is so much waiting in the system, and then proceed to specify the optimization plan based on the results of the analysis.

In addition, the INNODB provides more abundant instant state information for our analysis than the five system state variables provided. You can view it in the following ways:

1. Open the InnoDB monitor feature by creating a innodbmonitor table:

Copy Code code as follows:

Mysql> CREATE TABLE Innodb_monitor (a int) engine=innodb;

Query OK, 0 rows affected (0.07 sec)

2. Then use "Showinnodbstatus" to view the details (because the output content is too much to be recorded here);

There may be readers who ask why you want to create a table called Innodb_monitor first? Because creating the table is actually telling InnoDB that we're starting to monitor his details, InnoDB will take a more detailed transaction and lock the information into MySQL's errorlog for further analysis later.

Summary

This chapter begins with the introduction to locking in MySQLServer, and analyzes the basic implementation mechanisms for locking table-level locking and row-level locking, which are most widely used in MySQL today. The two typical storage engines, MyISAM and InnoDB, are analyzed and demonstrated in detail with the table-level locking and row-level locking used by the sample storage engine. Then, by analyzing the characteristics of two kinds of locking modes, the corresponding optimization suggestions and strategies are given. Finally, I learned how to get the current resource contention status of various locking systems in MySQLServer. I hope this chapter will be helpful to your readers in understanding the MySQL locking mechanism.