MySQL lock mechanism

First, the summary database locking mechanism is simply, the database in order to ensure the consistency of data, and so that the various shared resources in the concurrent access to become orderly design of a rule.
For any kind of database need to have a corresponding locking mechanism, so MySQL nature is no exception.
MySQL database because of its own architecture, the existence of a variety of data storage engine, each storage engine for the application scenario is not the same characteristics, in order to meet the needs of their specific application scenarios,
The locking mechanism of each storage engine is optimized for the particular scenario in which it is faced, so the locking mechanism of each storage engine is significantly different.
MySQL each storage engine uses three types (levels) of locking mechanisms: table-level locking, row-level locking, and page-level locking.

　1　　　　　　, lock classification ① from the type of data operation (read \ Write) read locks (shared locks): For the same data, multiple reads can be performed simultaneously without affecting each other.　　　　Write lock (Exclusive lock): Before the current write operation is completed, it blocks other write and read locks. ② Lock row lock          page lock from the granularity of data manipulation

2. Table-level lock (table-levels) features: Biased to myism storage engine, low overhead, lock block, no deadlock, locking granularity is high, the probability of lock conflict is highest, and the concurrency is the lowest.  3. Row-level locking (row-levels) features: biased InnoDB storage engine, high overhead, locking slow, deadlock, locking granularity is small, the probability of lock conflict is the lowest, the concurrency is the highest. InnoDB and MyISAM The largest difference has two points, one is support transactions, the second is the use of row-level lock.  4. Page Lock: Overhead and lock time are bounded between table and row locks, deadlock occurs, locking granularity bounds between table and row locks, and concurrency is common. Application: From the lock point of view, table-level lock is more suitable for query-oriented, only a small number of index conditions to update the data of the application, such as Web applications;
Row-level locks are more suitable for applications that have a large number of simultaneous updates of different data by index conditions, and concurrent queries, such as some online transaction processing (OLTP) systems.

Second, table-level locking (partial read) 1 . MySQL table-level lock lock mode the table-level lock for MySQL has two modes: table-Shared read-Lock and table-exclusive write-lock (tables write lock). Compatibility compatibility for Lock mode: read operations on the MyISAM table do not block other users from reading requests to the same table, but they block write requests to the same table, and write operations for other processes are performed only after the read lock is released. Writes to the MyISAM table block other users from reading and writing to the same table, and the read and write operations of other processes are performed only after the write lock is released. In short, the read lock blocks the write, but does not block the reading, and the write lock will block both reading and writing. 

three, row-level locking (partial write) 1 . InnoDB lock mode and implementation mechanism considering row-level locking is implemented by each storage engine, and the implementation is different, and InnoDB is the most widely used storage engine in the transactional storage engine, so here we mainly analyze the locking characteristics of InnoDB. 

In general, InnoDB's locking mechanism has many similarities with Oracle databases. Row-level locking of InnoDB is also divided into two types, shared and exclusive locks,
In the implementation of the locking mechanism in order to allow row-level locking and table-level locking coexistence, InnoDB also use the concept of intent-lock (table-level locking), there are intent to share the lock and the intent of the exclusive lock two.

When a transaction needs to add a lock to a resource of its own, it can add a shared lock if it encounters a shared lock that is locking the resource it needs, but cannot add an exclusive lock.
However, if you encounter a resource that you need to lock into an exclusive lock, you can only wait for the lock to release the resource before you can acquire the lock resource and add your own lock.
The function of intent lock is that when a transaction needs to acquire a resource lock, the transaction can need to lock the row's table with an appropriate intent lock if it encounters its own required resource that has been occupied by an exclusive lock.
If you need a shared lock, add an intent shared lock on the table. If you want to add an exclusive lock on a row (or some rows), first add an intent exclusive lock to the table.
Intent shared locks can coexist multiple simultaneously, but only one exists for an intent exclusive lock. So, it can be said that the InnoDB lock mode can be divided into four kinds: Shared lock (S), exclusive (X), Intent shared lock (IS)
and Intent exclusive Lock (IX), we can use the following table to summarize the coexistence of the above four types of logical relationships:

If the lock mode of a transaction request is compatible with the current lock, INNODB grants the requested lock to the transaction and, conversely, if the two are incompatible, the transaction waits for the lock to be released.

The intent lock is innodb automatically and does not require user intervention. For update, Delete, and INSERT statements, InnoDB automatically adds an exclusive lock (X) to the data set involved, and InnoDB does not add any locks for the normal SELECT statement;
Transactions can be displayed to a recordset with shared or exclusive locks through the following statements.

Shared Lock (S): SELECT ** FROM table_name WHERE ... For UPDATE

Use SELECT ... In SHARE mode, a shared lock is used primarily to confirm the existence of a row of records when data dependencies are needed, and to ensure that no one is doing an update or delete operation on the record. However, if the current transaction also requires an update to the record, it is most likely to cause deadlocks, and for applications that require an update operation after locking the row records, you should use SELECT ... The for Update method obtains an exclusive lock.

2 . InnoDB row Lock Implementation InnoDB row locks are implemented by locking the index entries on the index, only if the data is retrieved by index criteria, InnoDB uses row-level locks, otherwise, InnoDB will use table locks in the actual application, pay special attention to the InnoDB row lock feature, otherwise, May result in a large number of lock conflicts, which can affect concurrency performance. Here are some practical examples to illustrate. (1) The InnoDB does use a table lock instead of a row lock when querying without an index condition. (2) since the MySQL row lock is for the index plus lock, not for the record plus lock, so although it is access to the record, but if you use the same index key, there will be a lock conflict. (3) When a table has multiple indexes, different transactions can use different indexes to lock different rows, and InnoDB uses row locks to lock the data, whether it is using a primary key index, a unique index, or a normal index. (4) Even if the index field is used in the condition, whether the index is used to retrieve the data is determined by MySQL judging the cost of different execution plans, if MySQL considers the full-table scan to be more efficient,
For example, for some very small tables, it would not use an index, in which case the InnoDB would use a table lock instead of a row lock. Therefore, when parsing a lock conflict, don't forget to check the SQL execution plan to verify that the index is actually used.

3. Gap Lock (next-key Lock) When we retrieve data with a range condition rather than an equal condition and request a shared or exclusive lock, InnoDB locks the index entry for the qualifying existing data record, and for a record in which the key value is within the condition but does not exist, called "gap", InnoDB will Also Gap "Lock, this locking mechanism is called the Gap Lock (Next-key Lock). Example: If there are only 101 records in the EMP table, the value of the Empid is1,2,..., -,101, the following sql:mysql>Select* fromEmpwhereEmpid > -  forUpdate; is a range conditional retrieval, InnoDB not only locks up records that meet the criteria of a empid value of 101, but also locks out "gaps" where empid is greater than 101 (these records do not exist). InnoDB The purpose of using a gap lock: (1) to prevent Phantom reads to meet the requirements of the relevant isolation level. For the above example, if a gap lock is not used, if the other transaction inserts any record with empid greater than 100, then this transaction will occur if the above statement is executed again;2) in order to meet the needs of its recovery and replication. Obviously, when using range criteria to retrieve and lock a record, even if some nonexistent key value is also locked by an innocent, it is not possible to insert any data in the Lock key value range when locked. In some scenarios this can be very damaging to performance. In addition to the negative impact of the gap lock on the performance of InnoDB, there are several other major performance pitfalls in the way the lock is implemented by indexing: (1when 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;2when the index used by query does not contain all of the filter criteria, the index key used by the data retrieval may have a portion of the data that is not part of the query's result set, but it is also locked because the gap lock is locked in a range.
Rather than the specific index key;3when query locates data using an index, it is locked as if the index key is used but the data rows accessed are different (the index is only part of the filter).　　 Therefore, in the actual application development, especially the concurrent inserting more and more applications, we should try to optimize the business logic, try to use equal conditions to access the updated data, to avoid the use of scope conditions. It is also necessary to note that, in addition to using the clearance lock when the InnoDB is locked by the range condition, the InnoDB will also use a gap lock if the equality condition is used to request a lock for a record that does not exist. 

4 . Deadlock in InnoDB, in addition to a single SQL-composed transaction, the lock is progressively acquired, and when two transactions need to acquire an exclusive lock held by the other to continue the transaction, this cyclic lock wait is a typical deadlock. In InnoDB's transaction management and locking mechanism, there is a mechanism for detecting deadlocks, which can detect the existence of a damn lock in a short time after a deadlock has been generated in the system.
When InnoDB detects that a deadlock has been generated in the system, InnoDB will choose to roll back the smaller transactions in the two transactions that generated the deadlock by the appropriate decision, allowing the other larger transaction to complete successfully. What does the InnoDB do to determine the size of the transaction for the standard? This problem is also mentioned in the official MySQL manual, which, in effect, calculates the size of two transactions by calculating the amount of data inserted, updated, or deleted by each of the two transactions after InnoDB discovers a deadlock.
In other words, the more records the firm changes, the more it will not be rolled back in the deadlock. One thing to note, however, is that when the deadlock scenario involves more than InnoDB storage engines, InnoDB is not able to detect a damn lock, which can only be resolved by locking the timeout limit parameter innodb_lock_wait_timeout. It is necessary to note that this parameter is not only to solve the deadlock problem, if the concurrent access is high, if a large number of transactions due to the inability to obtain the required locks immediately suspended, it can consume a lot of computer resources, causing serious performance problems, or even drag across the database.
We can prevent this from happening by setting the appropriate lock wait timeout threshold. In general, deadlocks are an application design problem, and most deadlocks can be avoided by adjusting the business process, database object design, transaction size, and SQL statements that access the database. Here are some examples of common ways to avoid deadlocks:

 (1   2   3  . ( 4 ) under Repeatable-read isolation level, If two threads simultaneously record the same condition with select ... For UPDATE plus an exclusive lock, two threads will be locked successfully if the condition record is not met. The program discovery record does not already exist, 
 attempts to insert a new record, and if two threads do so, a deadlock occurs. In this case, you can avoid the problem by changing the isolation level to read COMMITTED. ( 5 ) when the isolation level is read Committed, if two threads first perform a SELECT ... For UPDATE, determine if there is a record that matches the criteria, and if not, insert the record. At this point, only one line Cheng Nen is inserted successfully, 
 Another thread will have a lock wait, and when the 1th thread commits, the 2nd thread will be faulted due to the primary key, but although the thread is wrong, it will get an exclusive lock. If there is a 3rd thread to apply for an exclusive lock, there will be a deadlock. 
 In this case, you can do the insert operation directly before catching the primary key weight exception, or when encountering a primary key weight error, always perform an exclusive lock obtained by the rollback release. 

 5   1   in which case you might consider using a table lock to increase the execution speed of the transaction. ( 2  

  1 ) use lock tables Although you can add a table-level lock to InnoDB, it must be stated that table locks are not managed by the InnoDB storage engine layer. Instead, it is owned by the previous layer of ──mysql server, 
 only if Autocommit=0 , Innodb_table_locks=1   (the default setting), the INNODB layer can know the MySQL plus table lock, MySQL server can also sense InnoDB plus row lock, 
 In this case, InnoDB can automatically identify deadlocks that involve table-level locks, otherwise innodb will not be able to automatically detect and process such deadlocks. ( 2   because unlock tables implicitly commits the transaction; commit or Rollback cannot be released with lock Tables table-level locks must be unlock tables to release the table lock. The correct way to see the following statement: For example, if you need to write the table T1 and read from the table T, you can do as follows: SET autocommit  =0  ; LOCK TABLES T1 WRITE, T2 READ, ...; [ do   something with tables T1 and T2 here]; COMMIT; UNLOCK TABLES;  

6. InnoDB row Lock Optimization recommendation InnoDB storage Engine because of the implementation of row-level locking, although the implementation of the locking mechanism of performance loss may be higher than the table-level locking, but in the overall concurrency processing power is much better than the MyISAM table-level locking.
When the system concurrency is high, the overall performance of InnoDB and MyISAM will have a more obvious advantage compared. However, InnoDB's row-level locking also has its own fragile side, and when we use it inappropriately,
It may make InnoDB's overall performance not only higher than MyISAM, but may even be worse. (1to make reasonable use of InnoDB row-level locking, to achieve weaknesses, we must do the following: a) as far as possible to make all the data retrieval through the index to complete, so as to avoid innodb because it cannot be locked by the index key to upgrade to table-level lock; b) reasonable design index, Allow InnoDB to lock on the index key as accurately as possible, reduce the locking range as far as possible, to avoid causing unnecessary locking and affect the execution of other queries; c) minimize the range-based data retrieval filtering conditions and avoid locking up records that should not be locked due to the negative effects of gap locks; d) Try to control the size of the transaction, reduce the amount of locked resources and the length of the lock; e) Use lower-level transaction isolation as much as possible in the context of the business environment to reduce the additional cost of MySQL for implementing transaction isolation levels. (2due to the innodb of row-level locking and transactional, there will definitely be a deadlock, the following are some of the more commonly used to reduce the probability of deadlocks: a) similar to the business module, as far as possible in the same order of access to prevent the deadlock; b) in the same transaction, As much as possible to lock all the resources needed to reduce the probability of deadlock; c) for a business segment that is prone to deadlocks, you can try to use the upgrade lock granularity to reduce the probability of deadlock generation through table-level locking. (3You can analyze the contention for row locks on the system by examining the Innodb_row_lock state variables:

Mysql> Show status like ' innodb_row_lock% ';
+-------------------------------+-------+
| variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+

The row-level lock state variable for InnoDB not only records the number of lock waits, but also the total duration of the lock, the average duration of each time, and the maximum duration, plus a non-cumulative status that shows the number of waits that are currently waiting to be locked.
The description of each state volume is as follows: Innodb_row_lock_current_waits: Number of currently waiting locks, innodb_row_lock_time: 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 up to now waiting for the most common time; Innodb_row_lock_waits: The total number of waits since the system started For these 5 state variables, it is important to innodb_row_lock_time_avg (waiting average duration), innodb_row_lock_waits (total number of Waits), and Innodb_row_lock_ Time (wait for the total length) of these three items.
Especially when the number of waits is high, and each time it waits is not small, we need to analyze why there is so much waiting in the system and then start to specify the optimization plan based on the results of the analysis.

If the lock contention is found to be more serious, such as innodb_row_lock_waits and INNODB_ROW_LOCK_TIME_AVG values are higher, you can also set the InnoDB Monitors to further observe the lock conflict of the table, data rows, etc.
and analyze the reason for lock contention. Locks conflicting tables, data rows, and so on, and analyzes the reason for lock contention. Here's how: MySQL> CREATE table Innodb_monitor (a INT) engine=InnoDB; then you can view it with the following statement: MySQL> Show Engine InnoDB status; The monitor can stop viewing by issuing the following statement: MySQL> drop table innodb_monitor;

After setting up the monitor, there will be detailed information about the current lock waiting, including the table name, lock type, locked record, etc. for further analysis and problem determination.
There may be readers who ask why they should create a table called Innodb_monitor first. Because creating the table actually tells InnoDB we're starting to monitor his details,
Then InnoDB will log the more detailed transactions and locking information into MySQL's errorlog so that we can do further analysis later.
When the monitor is turned on, the monitored content is logged to the log every 15 seconds by default, and if open for a long time causes. err files to become very large,
So after the user confirms the cause of the problem, remember to delete the monitoring table to turn off the monitor, or start the server to shut down the write log file by using the "--console" option.

Reference--https://www.cnblogs.com/luyucheng/p/6297752.html

MySQL lock mechanism