MySQL performance tuning and Architecture Design-Chapter 7 MySQL database Locking Mechanism

Various locking mechanisms have emerged to ensure data integrity. The locking mechanism determines the concurrent processing capability and performance of a database.

The locking mechanism of the database is simply a rule designed by the database to ensure data consistency and make various shared resources orderly when they are accessed concurrently.

Three types of locking mechanisms are available: Row-level locking, page-level locking, and table-level locking.
Row-Level Lock: The minimum granularity lock provides greater concurrent processing capabilities, but it is also the easiest to deadlock and consumes a large amount of resources.
Table-level locking: Maximum granularity locking, the most difficult concurrency, and the least resource consumption.
Page-level locking: concurrency and performance overhead, between Row-level locking and table-level locking.

In MySQL databases, table-level locking is performed on non-transactional storage engines such as MyISAM, memory, and CSV. Row-level locking is mainly performed on InnoDB and NDB cluster, page-level locking mainly refers to berkeleydb.

Analysis of various locking mechanisms:

Table-Level Lock:
MySQL table-level locks are mainly divided into two types: Read locks and write locks. In MySQL, there are four
Queue to maintain these two locks: two stores the Read and Write locks currently being locked, and the other two stores the Read and Write locks in waiting.
Information, as follows:
Currentread-lockqueue (lock-> Read)
Pendingread-lockqueue (lock-> read_wait)
Currentwrite-lockqueue (lock-> write)
Pendingwrite-lockqueue (lock-> write_wait)

Lock type description
Ignore interacts with each other when a lock request occurs.
No information is stored in
The Interaction Type of the unlock release lock request.
Read common read lock
Write normal write lock
Read_with_shared_locks is used in InnoDB and is generated by the following method:
For example, select... lockin1_mode
Read_high_priority high-priority read lock
Read_no_insert does not allow locking of concurentinsert
The write_allow_write type is actually used when the storage engine handles the lock.
When, mysqld allows other threads to get read or write locks, because
The storage engine knows how to handle resource conflicts.
Write_allow_read occurs when the table is DDL (alter table ...).
Wait, MySQL can allow other threads to get read locks, because MySQL is
This function is implemented by recreating the entire table and then rename it.
The original table can still provide read services.
The locking method used when write_concurrent_insert is performing concurentinsert.
Except read_no_insert
Read lock requests will not be blocked
Write_delayed lock type when using insertdelayed
Write_low_priority indicates the low-level lock theory of the voice.
Generated by low_priority_updat = 1
Write_only: When a lock exception is interrupted during the operation
The row CloseTable operation, the lock type that appears during this process is
Is write_only

Read lock:
A new client request must meet two conditions when requesting to obtain read lock resources:
1. The requested resource is not currently written locked;
2. Write lock wait Queue (pendingwrite-lockqueue) with no higher priority;

InnoDB Lock mode and Implementation Mechanism:
InnoDB locks include shared locks and exclusive locks. To implement table-level locking, there are also intention-sharing locks and intention exclusive locks.
Share lock (s) exclusive lock (x) intention to share lock (is) Intention exclusive lock (IX)
Shared lock (s) Compatibility conflict compatibility
Conflict
Exclusive lock (x) Conflict
Conflict
Conflict between the intended shared lock (is) Compatibility
Compatible
Conflict of intention exclusive lock (IX)
Compatible

Oracle locks data mainly by adding lock information to the transaction slot on the physical block where a row of records to be locked is located.
The locking mechanism of InnoDB is implemented by marking the lock information of the airspace space before and after the first index key pointing to the data record, and becomes "Next-key locking ".
The gap lock has a fatal weakness, that is, when a range key value is locked, even some key values that do not exist will be innocent.
As a result, no data within the scope of the lock key value can be inserted during the lock. In some scenarios, this may cause performance
Great harm. InnoDB explains how to organize Phantom reads, so they choose the gap lock to implement locking.

In addition to the negative impact of gap locks on InnoDB performance, there are several other major performance risks when locking through indexes:
● When the query cannot use the index, InnoDB will discard row-level locking instead of table-level locking, resulting in lower concurrency performance;
● When the index used by quuery does not contain all the filtering conditions, only the data of the index key used for data retrieval can be
Some rows and columns that do not belong to the query result set will also be locked because the gap lock is a fan
Surrounding, rather than the specific index key;
● When a query uses an index to locate data, if the index key is the same but the accessed data rows are different (the index is only part of the filter condition), the query will be locked.

*********
The preceding content must be described as follows:
1. MyISAM is tested in versions 5.0 and 5.5 and cannot be locked at the table level. Because non-transaction engines have been automatically committed (for non-transaction tables, for example, in MyISAM tables or memory tables (memory tables), changing the autocommit value is meaningless. These tables are essentially operating in the autocommit mode ).
2. In InnoDB 5.0, the index key lock mechanism is followed, that is, the index key is upgraded to the table lock without the index key; but in 5.5, there is no index key, which is also a row-level index mechanism.
*********

Readuncommited, readcommited, and repeatable implemented by InnoDB in ISO/ansisql92 specifications
Read and serializable transaction isolation levels. In addition, to ensure data consistency in transactions, multi-version data is implemented.
Access.

Use the locking mechanism to optimize MYSQL:

Optimization suggestions for MyISAM Table locking:
The key is how to improve concurrency.
Shorten lock time:
· Reduce complex queries as much as possible and split complex queries into several small queries for further processing;
· Establish efficient indexes as much as possible to make data retrieval faster;
· Make MyISAM storage engine tables store only the required information and control the field type;
· Optimize the MyISAM table data file with appropriate opportunities.
Parallel operations with separation capability:
The MyISAM storage engine has a parameter option that controls whether to enable the concurrentinsert function: concurrent_insert, which can
Set to 0, 1, or 2. The three values are described as follows:
A) concurrent_insert = 2, regardless of whether the middle part of the table data file of the MyISAM storage engine exists because the data is deleted
The idle space left behind can be concurrentinsert at the end of the data file;
B) concurrent_insert = 1. When there is no free space in the data file of the MyISAM storage engine table
Execute concurrentinsert at the end of the part;
C) concurrent_insert = 0, regardless of whether the middle part of the table data file of the MyISAM storage engine exists because the data is deleted
Concurrentinsert is not allowed for idle space.
Rational use of read/write priorities:
In the various lock analysis sections in this chapter, we learned that MySQL table-level locking has different read and write priorities.
Generally, the write priority must be higher than the read priority. Therefore, if we can determine the reading and writing priority based on the differences in their respective system environments
Level. If our system is read-oriented and the query performance must be guaranteed first, we can set System Parameter options.
Low_priority_updates = 1. Set the write priority to lower than the read priority, so that MySQL can process the read as much as possible.
. Of course, if our system needs to guarantee the performance of data writing, we do not need to set low_priority_updates.
Parameter.

InnoDB row lock optimization suggestions:
To use InnoDB's Row-level locking properly, we must do the following:
A) Try to make all data retrieval completed through indexes, so as to avoid InnoDB upgrading because it cannot be locked by the index key.
Table-level lock;
B) design indexes reasonably so that InnoDB can be as accurate as possible when locking the index key and minimize the lock range to avoid
The execution of other queries is affected by unnecessary locks;
C) Minimize the range-based data search and filtering conditions to avoid locking due to the negative impact of the GAP lock.
;
D) control the transaction volume as much as possible to reduce the number of locked resources and lock duration;
E) when the business environment permits, try to use lower-level transaction isolation to reduce MySQL's transaction isolation level.
Additional costs;
Because InnoDB is Row-level locked and transactional, deadlocks are generated. Below are some commonly used methods to reduce the probability of deadlock.
TIPS: readers and friends can make targeted attempts based on their own business characteristics:
A) try to access similar business modules in the same access order to prevent deadlocks;
B) Try to lock all required resources at a time in the same transaction to reduce the probability of deadlock;
C) for businesses that are very prone to deadlocks, you can try to use the upgrade lock granularity to reduce deadlocks through table-level locks.
Probability of generation;

Query System lock contention:
For the two lock levels, MySQL has two groups of special status variables to record the contention of internal lock resources in the system. Let's take a look at them first.
Table-Level Lock contention status variables implemented by MySQL:
Mysql> showstatuslike 'table % ';
+ ----------------------- + ------- +
| Variable_name | value |
+ ----------------------- + ------- +
| Table_locks_immediate | 100 |
| Table_locks_waited | 0 |
+ ----------------------- + ------- +
There are two state variables that record MySQL internal table-level locking. The two variables are described as follows:
● Table_locks_immediate: Number of table-level locks;
● Table_locks_waited: the number of waiting times when table-Level Lock contention occurs;

For row-level locks used by InnoDB, another group of more detailed state variables are recorded in the system, as shown below:
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 |
+ ------------------------------- + -------- +
● Innodb_row_lock_current_waits: number of pending locks;
● Innodb_row_lock_time: the total time from system startup to current locking;
● Innodb_row_lock_time_avg: Average time spent on each wait;
● Innodb_row_lock_time_max: The most common time spent from system startup till now;
● Innodb_row_lock_waits: Total number of waiting times after the system is started;