Mysql Lock Basics

This article was also published in HTTPS://GITHUB.COM/ZHANGYACHEN/ZHANGYACHEN.GITHUB.IO/ISSUES/53

Lock and latch

In a database, both lock and latch can be locks, but both have distinct meanings.
Latch is generally called a latch (lightweight lock) because it requires a very short time to lock, if the duration of a long time, then the application performance is very poor, in the InnoDB storage engine, latch can be divided into mutexes (mutexes) and Rwlock (read-write lock) to ensure that the concurrent thread The Operation critical resource is correct, and there is no mechanism for deadlock detection.

The object of lock is a transaction, which is used to lock objects in the database, such as tables, pages, and rows. and the general lock object is released only after the transaction commits or rollback (The time at which different transaction isolation levels are freed may be different), and lock, as in most databases, is a deadlock mechanism.

Intent lock

Dividing a locked object into layers means that the transaction wants to be locked on a finer granularity.
For example, if you add an IS lock to a data object, it indicates that its child nodes have intent plus s lock. For example, to add an S lock to a tuple, you first add the is lock to the relationship and database.

The hierarchical structure of the database is as follows:

Because the InnoDB storage engine supports row-level locks, intent locks do not block any requests other than full-table sweeps. An intent lock simply means that the transaction intentionally adds a lock to the child node.

The compatibility of intent locks with row-level locks is as follows:

Role

The intent lock is introduced to improve the efficiency of the blockade subsystem. The blocking subsystem supports a variety of blocking granularity. The reason: In a multi-granularity blocking method, one data object may be locked in two ways-explicit blocking and implicit blocking. Therefore, when locking a data object, the system will not only check whether there is a conflict between the (explicit and implicit) blockade on the data object, but also check all its superior nodes and all subordinate nodes to see if the requested blockade conflicts with the (explicit and implicit) blockade on these nodes, obviously, such a method is inefficient. An intent lock was introduced for this purpose.

Consistent non-lock read

Consistent non-lock read (consistent nonlocking read) refers to the InnoDB storage engine using multiple versioning (multi versionning) to read data from a row in the current execution time database. If the read row is performing a delete or update operation, this is the release of the read operation that will not wait for the row to be locked. Instead, InnoDB will read a snapshot of the row data.

The above shows the non-locking read of InnoDB storage engine conformance. It is called non-locking read because there is no need to wait for the release of the X lock on the accessed row.

However, under different transaction isolation levels, the read is different, not every transaction isolation level is a non-locking consistent read, in addition, even if the use of non-locking consistent read, but the definition of snapshot data are different.
Under the transaction isolation level RC and RR, the INNODB storage engine engine uses a non-locking, consistent read. However, the definition of snapshot data is not the same. Under the RC transaction isolation level, for snapshot data, non-conforming reads are always locked to the latest snapshot data of the row. At the RR transaction isolation level, for snapshot data, non-conforming reads always read the row data version at the beginning of the transaction. That is, the RC level will see the latest version of the data after other transactions are committed. The RR level always reads the row data at the start of the transaction, even if other transactions are committed. (Do not do a demo).

Three algorithms of lock algorithm row lock

• Reord Lock: Lock on single-line record
• Gap Lock: A lock that locks a range but does not contain the record itself
• Next-key lock:gap lock + reord lock, locks a range and locks the record itself

Next-key Lock is a locking algorithm that combines gap lock and record LOCKD, and under the Nex-key lock algorithm, InnoDB queries for rows are based on this locking algorithm, such as an index of 10 11 13 20 with four values, Then the index may be next-key locking the interval is:
(-∞,10]
(10,11]
(11,13]
(13,20]
(20,+∞)

When the index of a query contains unique attributes, InnoDB optimizes Next-key lock to Reord lock, which locks the index itself rather than the scope.

Table T has a total of 1 2 53 values, which are first X-locked for a=5 in session a. Because A is a primary key and unique, so the lock is only 5 of this value, instead of (2,5) this range, so that in session B insert 4 will not block, can immediately insert and return, and lock down by the Next-key lock algorithm for record lock, thereby improving the concurrency of the application.

As described earlier, Next-key Lock is downgraded to record lock only if the queried column is a unique index, and if it is a secondary index, it is different:

tableINTINTPRIMARYKEY(a),KEYINTOSELECT1,1INTOSELECT3,1INTOSELECT5,3INTOSELECT7,6INTOSELECT10,8;

Column B of Table z is the secondary index, if the following SQL statement is executed in session a:

>SELECT  FROMWHERE b=3FORUPDATE;

Obviously, this is a SQL statement that is queried by index B, so it is locked using the traditional next-key locking technology, and because there are two indexes, it needs to be locked separately. For a clustered index, it only adds the record lock (the value of a in the B=3 column) to the index of column a equal to 5, and for the secondary index, it is Next-key lock, and the range of locking is (1,3) in particular, it is important to note that The InnoDB storage engine also adds Gap lock to the next key value of the secondary index, which is also a lock with a secondary index range of (3,6). Therefore, running the following statement in session B is blocked:

FROMWHERE a=5LOCKINSHAREMODEINTOSELECT4,2INTOSELECT6,5;

There are two ways in which you can explicitly turn off gap Lock:

• To set the isolation level of a transaction to RC
• Set the parameter Innodb_locks_unsafe_for_binlog to 1

Keep in mind, however, that the above settings undermine the isolation of transactions and, for replication, may cause the master to never be consistent. Also, from a performance perspective, RC will not outperform the default transaction isolation level RR.

Again, for a lock on a unique key value, Next-key lock is demoted to record lock, which only exists in the query for all unique index columns. If a unique index consists of multiple columns, and the query is only one of several unique indexed columns, then the query is actually a range type query instead of a point type query, so the InnoDB storage engine still locks with next-key lock.

Note: A gap lock is also generated by locking a nonexistent value with a primary key or a unique index. The ID of the
is the primary key in the following example.

mysql> select  * from  a;+----+------------+  | id  |  data  |+----+------------+  |          1  |  1  | |          2  |  0  | |          3  |  3  | |          4  |  4  | |          5  |  0  | |          6  |  3  | | 7  | 4294967295  |+----+------------+  7  rows  in  set  (0.04  sec) 

Time

	User 1	User 2
1	BEGIN
2	Mysql> SELECT * from a where id=10 for update; Empty Set (0.03 sec)
3		Insert into a (Id,data) values (8,10); #等待

Lock problem Dirty Read

Read the uncommitted data from other transactions, the production environment will not be used, not explained.

Non-REPEATABLE READ

Read the data that has been committed by other transactions. This issue occurs with the RC isolated environment.
However, in general, non-repeatable reads are acceptable because they read data that has already been committed and do not inherently pose a major problem, so many database producers set their transaction isolation level to RC by default, allowing non-repeatable reads to occur at this isolation level.
In the InnoDB storage engine, through the Next-key lock algorithm to avoid the problem of non-repeatable reading, in the official MySQL document, the non-repeatable read problem is defined as Phantom problem, the Phantom problem. Under the Next-key lock algorithm, the scan of an index is not only locked in the scanned index, but also locks the range of gaps covered by these indexes, so the insertion within this range is not allowed, thus avoiding the problem of non-repeatable reads caused by another transaction inserting data within this range. Therefore, the default transaction isolation level for the InnoDB storage engine is RR, which uses the Next-key lock algorithm to avoid non-repeatable reads.

Missing updates

An update operation for one transaction is overwritten by an update operation lock on another transaction, which results in inconsistent data.

事务T1将行记录r更新为v1,但是事务T1并未提交与此同时，事务T2将行记录r更新为v2，事务T2未提交事务T1提交事务T2提交

However, under any isolation level of the current database, there is no theoretical loss of database update issues. This is because, even if the transaction isolation level of READ UNCOMMITTED, for the DML operation of the row, the row or other coarse-grained objects need to be locked, so in step b above, transaction T2 does not update the row record R, the rest is blocked until the transaction T1 commits.

While the database can prevent the loss of update problems, there is another logical missing update problem in production applications, which is not caused by the problem with the database itself. In fact, this problem can occur in all multi-user computer system environments. Simply put, the following situation occurs and a missing update occurs.

事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1事务T2也查询该行数据，并将取得的数据显示给终端用户User2User1修改这行的记录，更新数据库并提交User2修改这行的记录，更新数据库并提交

We take 12306 as an example: if the user wants to purchase the ticket, the application should first inquire at this time the remaining ticket, if the remaining ticket >0, the user then carries on the purchase.

查询余票数量 select 余票 into ticketsif(tickets > 0){    数据库进行余票数量更新 update 票数 = tickets - 1}

Two users 1 and 2 simultaneous login 12306 for purchase, which triggered two transactions T1 and T2. At this time T1 and T2 query to the number of the remaining votes is the same, for example, are 1,t1 and T2 update, so users 1 and 2 have to buy tickets successfully. But one problem is that the previous tickets were 1, but there were two users who bought the tickets successfully.

To avoid the loss of updates, you need to serialize the transactions in this case instead of parallel operations.

Time

	User 1	User 2
1	BEGIN
2	Select Ticket into @ticket the from XX where id=xx for update (equivalent to the number of variables stored in the application)
3		Select Ticket to @ticket from XX where id=xx for update #等待中 ...
4	Update xx set [email protected] where id=xx
5	Commit
6		Update xx set [email protected] where id=xx
7		Commit

Blocking

Blocking is not a bad thing, it is to ensure that transactions can run concurrently and normally.
In the InnoDB storage engine, the parameter innodb_lock_wait_timeout is used to control the wait time (default 50 seconds), and Innodb_rollback_on_timeout is used to set whether to roll back the in-progress transaction when the wait time-out ( The default is off, which means no rollback). The parameter innodb_lock_wait_timeout is dynamic and can be adjusted in the run.

>set @@innodb_lock_wait_timeout=60;

The innodb_rollback_on_timeout is static and cannot be modified at startup.

set1238‘innodb_rollback_on_timeout‘isreadonly variable

When a timeout occurs, MySQL throws a 1205 error.

FROMWHERE a=1FORUPDATE1205Locktimeouttransaction

It is worth remembering that, by default, the InnoDB storage engine does not cause a rollback due to timeouts, in fact the InnoDB storage engine does not roll back the exception in most cases, such as adding the following statement to a session:
Session A:

FROMFROMWHERE a<4FORUPDATE;

A transaction is opened in session a, and all records under the Next-key lock algorithm that are less than 4 are locked (in fact, the record itself is locked 4) Execute the following statement in another session B

INTOSELECT5INTOSELECT31205Locktimeouttransaction

As you can see, inserting record 5 in session B is possible, but when the insert record is 3, because the relationship of the Next-key lock algorithm in session A is required to wait for the transaction in session a to release the resource, the wait generates a timeout, but when the user makes a select operation after the timeout, 5 This record still exists.

This is because the transaction in session B throws an exception, but neither commit nor rollback is performed. This is a very dangerous state, so the user must determine if a commit or rollback is required, and then proceed to the next step.

Deadlock concept

A phenomenon of waiting between two or two or more transactions in the course of execution, as a result of contention for resources.

Solutions

The simplest way to do this is to time out, when two transactions are waiting for each other, when a wait time exceeds a certain threshold set, one of the transactions is rolled back, and the other waiting transaction can continue to run, in the InnoDB storage engine, the parameter Innodb_lock_wait_ Timeout is used to set the time-out.
Although the time-out mechanism is simple, it only passes the rollback of the transaction after time-out, or it chooses to rollback the object according to the FIFO order, but if the time-out of the office is relatively large, such as the transaction operation update a lot of rows, occupy more undo log, this is the FIFO mode, is inappropriate because the time to roll back the transaction may take more time than the other firm.

In addition to the time-out mechanism, the current database uses wait-for graph (wait graph) for deadlock detection, which is a more active method of deadlock detection than a timeout solution. The InnoDB storage engine is also used in this way. Wait-for graph requires the database to hold the following two kinds of information:

• Information chain List of locks
• Transaction waiting list

We can see a total of 4 transactions.
Row1 row is T2 occupied by the X lock, T1 is waiting for T2 release x lock.
Row2 line by T1, T4 at the same time occupy S lock, T2 waiting for T1, T4 release S lock, the same T3 in waiting for T1, T4, T2.
So the wait-for-graph chart is as follows:

, the Loop (T1,T2) can be found to have a deadlock. Through the above introduction, it can be found that wait-for graph is a more active deadlock detection mechanism, in each transaction request lock and wait to determine whether there is a loop, if there is a deadlock, usually, the InnoDB storage engine will choose to roll back the transaction of the least amount of undo.

Deadlock Example

time	session a	session b
1	BEGIN
2	select * FROM t where id=1 for update	begin
3		select * FROM t where id=2 for update
4	select * from T where id=2 for update #等待
5		select * from T where id=1 for update ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting trans Action

In the above operation, the transaction in session B throws a 1213 error, which means that a deadlock occurs because the resources of session A and conversation B are waiting for each other. Most deadlocks InnoDB the storage engine itself can be detected without human intervention, but in the above example, after the transaction in session B throws a deadlock exception, session a immediately gets the resource recorded as 2 because the transaction in session B has been rolled back. Otherwise, the transaction in session A is unlikely to get the resource, and the InnoDB storage engine does not roll back most of the error exceptions, except for deadlocks. After a deadlock occurs, the InnoDB storage engine rolls back a transaction immediately, which requires attention, so if you catch 1213 of this error in your application, you don't need to roll it back.

There is also another deadlock, where the current transaction holds the X lock of the next record to be inserted, but a request for a s lock in the waiting queue, a deadlock may occur, see an example:

Time

	session a	Session B
1	BEGIN
2		Begin
3	SELECT * FROM t where a=4 for update
4		SELECT * FROM T where a<=4 lock in share mode
5		INSERT into T values (3); ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction

A gets the x lock of the a=4, and B waits for a to release the X lock after the S lock is added to the a=1,2,3. In Event 5 A wants to insert the a=3, it needs to wait for B to release a=3 S lock, and the deadlock occurs.

Lock escalation

refers to reducing the granularity of the current lock.
For example, a database can upgrade a table's 1000 row-level locks to a single page lock, or a page lock to a table lock, as this avoids the overhead of locking.
The upgrade of a lock may occur in SQL Server under the following circumstances:

• The number of locks held on an object by a single SQL statement exceeds the threshold, which defaults to 5000. If the object is different, the lock escalation does not occur.
• Lock escalation occurs when the lock resource consumes more than 40% of the active memory.

However, lock escalation can lead to a decrease in concurrency.
The InnoDB storage engine does not have an issue with lock escalation because it does not produce row locks on a per-record basis, but instead, it manages the way a bitmap is used for each page accessed by each transaction. Therefore, the overhead is usually consistent regardless of whether a transaction locks one record or multiple records of a page.

Reference: "MySQL technology insider-innodb Storage Engine"
http://baike.baidu.com/link?url=ZYqMGtJ-urHQzkfER91GmTkIZfgmNobdHLk2rsrI4e11EmQIdnrp2HAMxvp7jVt9srKg6dnXahWt4MBCrtXu3q

Mysql Lock Basics