Each storage engine uses three types of locking mechanisms: row-level locking, table-level lock page-level locking. Table-level locking is mainly MyISAM, memory, CSV and other non-transactional storage engines, using row-level locking is mainly the InnoDB storage engine and NDB Cluster storage engine, page-level locking is mainly berkeleydb storage engine.
1. Introduction to MySQL lockdown mechanism
Three types of locking mechanisms used by each storage engine
- 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 MyISAM, memory, CSV and other non-transactional storage engines, using row-level locking is mainly the InnoDB storage engine and NDB Cluster storage engine, page-level locking is mainly berkeleydb storage engine
3. MyISAM table-level locking is mainly divided into two types
- Read lock, a new client needs to meet two conditions when requesting access to read-locked resources:
- The resource that requested the lock is not currently write-locked
- Write lock wait Queue (Pending write-lock queue) with no higher priority write lock waiting
- (Only write operations are affected)
- Write lock
- (affects read operations, and also affects write operations)
4. MySQL is primarily divided into 4 queues to maintain these two types of locks:
Two holds the read and write lock information that is currently being locked, and the other two write-and-read-lock West information in the wait, 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. Row-level locking of InnoDB is divided into four types
- Shared lock (there is called: Read lock)
- Allows a transaction to read one line, preventing other transactions from obtaining an exclusive lock on the same data.
- Exclusive lock (with the name: Write lock)
- Allow transactions that get exclusive locks to update data to block other transactions
- Intent shared lock
- Intent Exclusive Lock
6. InnoDB Clearance Lock
The locking of a InnoDB is achieved by marking the lock information before the first index key to the data record and after the last index key. This locking method is called "Next-key Locking" (Gap Lock)
Gap lock weakness: After locking a range, even if some non-existent key value is also locked by the innocent, causing the lock can not insert the key value locked in any data.
There are several other major performance pitfalls in how locking is achieved by 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 Query does not contain all of the filtering criteria, the data retrieved from the index key used by the data retrieval may have some of the rows that are not part of the result set of Query, but will also be locked because the gap lock is locked by a range, not a specific index key.
- When Query uses the index to locate data, they are locked as if they were using the same index key but accessing different rows of data (the index is only part of the filter).
7. MyISAM Table Lock Optimization Recommendations
- Shorten lockout time
- Minimize the complexity of the query and split the complex query into several small query executions.
- Build an index that is efficient enough to make data retrieval faster.
- Try to get the MyISAM storage engine table to hold only the necessary information and control the field type.
- Optimize MyISAM table data files with the right opportunities.
- Separation of operations that can be parallel
- Concurrent_insert = 2, allows concurrent insert at the end of the data file regardless of whether the middle part of the MyISAM Storage engine's table data file exists because of the free space left by deleting the data.
- Concurrent_insert = 1,myisam Storage Engine table data file There is no free space in the middle, you can concurrent insert from the tail of the file.
- Concurrent_insert = 0, concurrent insert is not allowed regardless of whether the middle part of the MyISAM Storage engine's table data file exists because there is no free space left to delete the data. (no insertion is allowed when the lock is read)
- MyISAM is not only fully serialized, the MyISAM storage engine has an attribute Concurrent insert (concurrent insert).
- The MyISAM storage engine has a parameter option that controls whether the Concurrent Insert feature is turned on: Concurrent_insert can be set to 0/1/2: as follows:
- Rational use of read-write priority
- Table-level locking by default, write priority is greater than read, if the read operation, you can set the read priority high, you can set the parameter Low_priority_updates = 1.
8. InnoDB Row Lock Optimization recommendations
- Make all data retrieval possible by index, avoiding InnoDB because it cannot be upgraded to table-level lock by index key lock
- Properly design the index, so that InnoDB when the index key is locked as accurately as possible, as far as possible to narrow the locking range, to avoid unnecessary locking and affect the execution of other Query.
- Minimize the range-based data retrieval filters to avoid locking down records that should not be locked due to the negative effects of gap locks.
- Try to control the transaction size, reduce the amount of locked resources and the length of time locked.
- Reduce the additional cost of MySQL by implementing transaction isolation levels, as much as possible with lower-level transaction isolation, where the business environment allows.
9. System Lock Race condition query
There are two sets of dedicated state variables inside MySQL to record the internal resource contention of the system.
- Race state variables for table-level locking
Mysql> Show status like ' table% ';
- Table_locks_immediate: The number of times a table-level lock is generated;
- table_locks_waited: Number of waits to occur when table-level lock contention occurs
Table_locks_immediate value greater than table_locks_waited 5000 is more appropriate, in large need to analyze the problem.
Both status values are recorded from the start of the system, each time plus 1, if the table_locks_waited state value is higher, the table-level lock contention is serious, need further analysis.
- InnoDB row-level lock state variable record
Sql> Show status like ' innodb_row_lock% ';
- Innodb_row_lock_current_waites: The number currently waiting to be locked;
- Innodb_row_lock_time: The total length of time from system boot to now lock;
- Innodb_row_lock_time_avg: The average time spent on each wait;
- Innodb_row_lock_time_max: The time it takes to start from the system and wait for the longest time;
- Innodb_row_lock_waits: The total number of waits from the system boot to the present.
5 states, the more important is innodb_row_lock_time_avg (waiting average time), Innodb_row_lock_waits (total number of Waits) and Innodb_row_lock_time (waiting total duration)
Ten. InnoDB
In addition to providing the above 5 system state variables, it also provides richer, instant state information, implemented as follows:
- Create the InnoDB Monitor table to turn on the monitor function of InnoDB
MySQL > CREATE table innodb_monitor (a int) engine=innodb;
- Then perform "Show InnoDB status" to view details
Why create a innodb_monitor table?
The table is created to tell InnoDB that we are going to start monitoring his details, and then InnoDB will log the more detailed transaction-level locking information to MySQL's error log for further analysis later.
MySQL database locking mechanism
