Shulou(Shulou.com)06/01 Report--

This article was first posted on the official account of Wechat, vivo Internet technology.

Author: Zhang Shuo

This paper systematically introduces and summarizes the knowledge and principles of lock, transaction and concurrency control in MySQL database, hoping to help readers understand locks and transactions in MySQL more deeply, so that the interaction with database can be better optimized in the process of business system development.

Table of contents:

1.MySQL server logical architecture

2.MySQL lock

3. Business

4. Isolation level

5. Concurrency Control and MVCC

6.MySQL deadlock problem

1. MySQL server logical architecture

(photo source: MySQL official website)

Each connection generates a thread on the MySQL server (thread is managed internally through a thread pool), such as a select statement entering. MySQL will first find out in the query cache whether the result set of the select is cached, and if not, continue the process of parsing, optimization, and execution; otherwise, the result set will be obtained from the cache.

2. MySQL lock 2.1and Shared and Exclusive Locks (shared lock and exclusive lock)

They are all standard row-level locks.

Shared lock (S) shared lock is also known as read lock, which allows multiple connections to read the same resource concurrently at the same time without interfering with each other.

Exclusive lock (X) exclusive lock, also known as write lock, a write lock blocks other write or read locks, ensuring that only one connection can write data at a time, while preventing other users from reading and writing to this data.

Note: the so-called shared lock and exclusive lock are actually the strategies of the lock mechanism itself, and locks are distinguished by these two strategies.

2.2, Intention Locks (intention lock)

InnoDB supports multi-granularity locks (lock granularity can be divided into row locks and table locks), allowing row locks and table locks to coexist. For example, a statement, such as LOCK TABLES... WRITE accepts exclusive locks on the specified table. In order to achieve multiple levels of granularity locking, InnoDB uses intent locks.

Intention lock: a table-level lock. First declare an intention in advance and acquire a table-level intention lock (shared intention lock IS or exclusive intention lock IX). If the acquisition is successful, some rows of the table will be locked (S or X) later or will be being (only allowed). (except for LOCK TABLES... WRITE, which locks all rows in the table. In other scenarios, intention locks do not actually lock any rows.)

For example:

SELECT... LOCK IN SHARE MODE, to acquire the IS lock; An intention shared lock (IS)

SELECT... FOR UPDATE, to acquire the IX lock; An intention exclusive lock (IX) I

Before a transaction can acquire a shared lock on a row in a table, the intentional locking protocol must first acquire an IS lock or a stronger lock on the table. Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table.

As mentioned earlier, the background of the implementation of intention locks is the coexistence of multi-granularity locks. A summary of the following compatibility:

The intention lock only expresses the intention, and it is a kind of weak lock, and the intention lock is compatible with parallelism (the relationship between IS and IX is compatible and parallel). X and IS\ IX mutually exclusive; S and IX mutually exclusive. It can be realized that the intention lock is a weaker lock than X\ S, and there is a predictive significance! First acquire a weaker IX\ IS lock, if the acquisition fails, it is not necessary to spend a lot of money to acquire a stronger X\ S lock.

2.3.The Record Locks (index row lock)

Record lock is a lock recorded in the index row. For example, SELECT C1 FROM t WHERE C1 = 10 FOR UPDATE if the index on C1 is used. Prevent any other transaction from changing the line C1 = 10.

Record lock always puts locks on index rows. Even if a table does not have any indexes set, innoDB creates an implicit clustered index (primary Key) and then locks it.

When a query field does not have an index, such as update table set columnA= "A" where columnB= "B". If the columnB field does not have an index (or is not a combined index prefix), this statement locks all records, that is, the locked table. If the execution of the statement can execute the index of a columnB field, only the rows that satisfy the where (RecordLock) will be locked.

Lock appears for example:

(use the show engine innodb status command to view):

``range query

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t

Trx id 10078 lock_mode X locks rec but not gap

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc

1: len 6; hex 00000000274f; asc'o

2: len 7; hex b60000019d0110; asc

2.4By Gap locks (clearance lock)

Gap Locks: lock the gap between index records ([2]), either before locking an index record ([1]), or after locking an index record ([3]).

Example: see figure [1], [2], [3]. Scope filtering queries that generally work on us >,

< 、between...... 例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事务将值3插入到列 userId 中。因为该范围内所有现有值之间的间隙都是锁定的。 对于使用唯一索引来搜索唯一行的语句 select a from ，不产生间隙锁定。(不包含组合唯一索引，也就是说 gapLock 不作用于单列唯一索引）

For example, if the id column has a unique index, the following statement uses the index record lock only for the exercise with an id value of 100. it does not matter whether other sessions insert rows in the previous gap:

````SELECT * FROM T1 WHERE id = 100

```If the id does not have an index or has a non-unique index, the statement locks the previous gap.

Gaps can span a single index value, multiple index values (such as figure 2p3 above), or even empty.

Gap locks are a tradeoff between performance and concurrency and are used for certain transaction isolation levels, such as RC level (RC level: REPEATABLE READ, we close gap locks and use RR levels to reduce deadlocks).

Allow gap co-existence ratio in overlapping gaps (or overlapping row records) as in a gap, allowing one transaction to hold gap X-Lock (gap write lock\ exclusive lock) and another transaction to hold (gap write lock\ exclusive lock) in this gap

CREATE TABLE new_table (

Id int (11) NOT NULL AUTO_INCREMENT

An int (11) DEFAULT NULL

B varchar (45) DEFAULT NULL

PRIMARY KEY (id)

KEY idx_new_table_a (a)

KEY idx_new_table_b (b)

) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8

INSERT INTO new_table VALUES (1), (2)), (3), (3), (4), (4), (4), (6), (6), (7), (2), (8), (9), (4) and 4 (10')

# transaction one #

START TRANSACTION

SELECT * FROM new_table WHERE a between 5 and 8 FOR UPDATE

# # not commit for now

# transaction II #

SELECT * FROM new_table WHERE a = 4 FOR UPDATE

# # smooth execution! Because gap locks can coexist.

# transaction three #

SELECT * FROM new_table WHERE b = 3 FOR UPDATE

# # timed out when obtaining the lock and failed. Because the gap of transaction 1 locks the data of transaction 3.

2.5 、 next-key lock

Next-key lock is a combination of record lock and gap lock.

For example, if there is a query that matches the row of bread3 (there is a non-unique index on b), then the so-called NextLock is: all row records before ("before": index sorting) are locked by adding RecordLock to the row of bread3 and using GapLock.

The MySQL query executes a row-level locking strategy, which locks (X or S) the rows that match during the scan, that is, adding Record Lock, and adds GapLock locks on all rows before the record. Suppose an index contains values 10, 11, 13, and 20. The possible NexKey Lock of the index locks the following intervals:

(negative infinity, 10)

(10, 11)

(11, 13)

(13, 20)

(20, positive infinity)

In addition, it is worth mentioning that next key Lock is automatically enabled under the default isolation level (RR) in innodb. (it's easy to understand, because gap acts on RR, and if RC,gapLock doesn't work, then next key lock won't either.)

Lock appears for viewing example: (use the show engine innodb status command to view):

RECORD LOCKS space id 58 page no 3 n bits 72 index PRIMARY of table test.t

Trx id 10080 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 8000000a; asc

1: len 6; hex 00000000274f; asc'o

2: len 7; hex b60000019d0110; asc

2.6.The Insert Intention Locks (insert intention lock)

An insert intention lock is an gap lock gap lock that occurs when an insert insert statement, locking all rows before the row is inserted.

This lock indicates the intention of the insertion in such a way that if multiple transactions inserted into the same index gap are not inserted in the same position in that gap, they do not need to wait for each other.

Suppose there are index records with values of 4 and 7. Try to insert separate transactions with values 5 and 6, respectively. Before acquiring an exclusive lock on the inserted row, each transaction uses insert intention lock to lock the gap between 4 and 7, but does not block each other because the rows do not conflict.

Example:

Mysql > CREATE TABLE child (id int (11) NOT NULL, PRIMARY KEY (id)) ENGINE=InnoDB

Mysql > INSERT INTO child (id) values (90)

# # transaction 1

Mysql > START TRANSACTION

Mysql > SELECT * FROM child WHERE id > 100 FOR UPDATE

+-+

| | id |

+-+

| | 102 |

+-+

# # transaction 2

Mysql > START TRANSACTION

Mysql > INSERT INTO child (id) VALUES

# # failed and locked

Mysql > SHOW ENGINE INNODB STATUS

RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table test.child

Trx id 8731 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0

0: len 4; hex 80000066; asc f

1: len 6; hex 000000002215; asc "

2: len 7; hex 9000000172011c; asc r;;

2.7 、 AUTO-INC Locks

An AUTO-INC lock is a special table-level lock that arises in a scenario where a transaction is inserting into into a table with AUTO_INCREMENT columns.

In the simplest case, if a transaction is inserting values into a table, any other transaction must wait for their own values to be inserted into the table in order to receive consecutive primary key values by the row inserted by the first transaction.

2.8predicate locks for Predicate Locks for Spatial Indexes spatial indexes

Slightly

3. Transaction

A transaction is a set of atomic sql, or a separate unit of work. Transactions mean that either the MySQL engine executes all of this set of sql statements, or none of them (for example, if one of the statements fails).

Auto commit (AutoCommit,MySQL default)

Show variables like "autocommit"

Set autocommit=0; / / 0 indicates that AutoCommit is off

Set autocommit=1; / / 1 indicates that AutoCommit is on

MySQL defaults to AutoCommit mode, which means that each sql is a transaction and does not need to be displayed to execute transactions. If autoCommit is turned off, each sql will open a transaction by default, and the transaction will not be committed until "commit" is explicitly executed.

Show transactions (START TRANSACTION...COMMIT)

For example, tim wants to transfer 100RMB to bill:

1. Check whether the account balance of tim is greater than 100 yuan.

The account of 2.tim is reduced by 100 yuan.

The account of 3.bill increased by 100 yuan.

These three operations are a transaction that must be packaged and executed, either successfully or not, and the failure of any one of these operations will cause all three operations to be "not executed"-- rollback.

CREATE DATABASE IF NOT EXISTS employees

USE employees

CREATE TABLE employees.account (

Id BIGINT (11) NOT NULL AUTO_INCREMENT

P_name VARCHAR (4)

P_money DECIMAL (10,2) NOT NULL DEFAULT 0

PRIMARY KEY (id)

)

INSERT INTO employees.account (id, p_name, p_money) VALUES ('1th,' tim', '200')

INSERT INTO employees.account (id, p_name, p_money) VALUES ('2pm,' bill', '200')

START TRANSACTION

SELECT p_money FROM account WHERE pendant name = "tim";-- step1

UPDATE account SET p_money=p_money-100 WHERE pendant name = "tim";-- step2

UPDATE account SET p_money=p_money+100 WHERE pendant name = "bill";-- step3

COMMIT

A good transaction system must meet the characteristics of ACID:

3.1The ACID of the transaction:

A:atomiciy atomicity: a transaction must ensure that all of its operations are either performed or rolled back. It is not possible to have only a partial execution.

C:consistency consistency: data must be guaranteed to transition from one consistent state to another. For example, the system crashed when the second step was performed in the last transaction, and the data will not show that the bill account is missing 100 yuan, but the tim account remains the same. Either keep the original (roll back all), or the bill is 100 less and the tim is 100 more, only in these two consistent states.

I:isolation isolation: when a transaction is not completed, it is usually guaranteed that other Session cannot see the result of the transaction.

* * D:durability persistence: once the transaction is commit, the data will be saved. Even if the system crashes after the transaction is committed, the data will not be lost * *. 4. Isolation level

View the system isolation level:

Select @ @ global.tx_isolation

View the current session isolation level

Select @ @ tx_isolation

Set the current session isolation level

SET session TRANSACTION ISOLATION LEVEL serializable

Set the global system isolation level

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

4.1and READ UNCOMMITTED (not submitted for reading, but dirty)

Changes in a transaction are visible to other sessions, even if they are not committed. You can read uncommitted data-dirty reads. Dirty reading can cause a lot of problems, and this isolation level is generally not applicable. Example:

-read-uncommitted instance--

-- set the global system isolation level

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

-- Session A

START TRANSACTION

SELECT * FROM USER

UPDATE USER SET NAME= "READ UNCOMMITTED"

-- commit

-- Session B

SELECT * FROM USER

/ / SessionB Console can see Session A's uncommitted transaction processing, which is also seen in another Session, which is called dirty reading.

Id name

2 READ UNCOMMITTED

34 READ UNCOMMITTED

READ COMMITTED (submitted or unrepeatable, phantom)

This isolation level is used by default in general databases (MySQL is not), which ensures that if a transaction is not fully successful (commit execution is complete), the operations in the transaction are not visible to other sessions.

-read-cmmitted instance--

-- set the global system isolation level

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED

-- Session A

START TRANSACTION

SELECT * FROM USER

UPDATE USER SET NAME= "READ COMMITTED"

-- COMMIT

-- Session B

SELECT * FROM USER

/ / Console OUTPUT:

Id name

2 READ UNCOMMITTED

34 READ UNCOMMITTED

-- when Session An executes commit,Session B, the result is as follows:

Id name

2 READ COMMITTED

34 READ COMMITTED

This verifies that the data modified at the read committed level before the commit operation is completed is not visible to other Session, and will not be visible to other Session until commit is executed. We can see that Session B got different data from two queries.

The read committed isolation level solves the problem of dirty reads, but results in two inconsistent reads to other Session (because the other Session executes the transaction, consistency changes).

4. 3, REPEATABLE READ (repeatable)

The unified read SQL is executed multiple times in a transaction, and the result is the same. This isolation level solves the problem of dirty reading and phantom reading. This refers to the rr level of innodb, where next-key locks are used in innodb to lock "current reads", lock rows and insert positions that may cause phantom readings, and prevent new data inserts from creating phantom rows. It is analyzed in detail below. Please refer to the MySQL manual for details:

Https://dev.mysql.com/doc/refman/5.7/en/innodb-storage-engine.html

SERIALIZABLE (serializable)

The strongest isolation level ensures that there is no phantom read problem by locking and writing locks to each read in the transaction, but can lead to a large number of timeouts and lock contention problems.

5. Concurrency control and MVCC

MVCC (multiple-version-concurrency-control) is a variant of row-level locks that avoids locking operations in the case of normal reads, so the overhead is lower. Although the implementation is different, it is usually a non-blocking read, locking only the necessary rows for write operations.

Consistent read (that is, read snapshot) select * from table....

The current read (that is, reading the actual persistent data) special read operation, insert / update / delete operation, belongs to the current read, deals with the current data and needs to be locked. Select from table where? Lock in share mode; select * from table where? For update; insert; update; delete

Note: select. From where. (no additional lock suffix) use MVCC to ensure that snapshots are read (MySQL is called consistent read). The so-called consistent read or read snapshot is to read the data snapshot before the start of the current transaction, and updates after the start of the transaction will not be read. The details of the select are described in detail.

For locking SELECT with FOR UPDATE (exclusive locks) or LOCK IN SHARE MODE (shared locks), update, delete statements, consider whether it is an equivalent query with a unique index.

INNODB's MVCC is usually achieved by saving two hidden columns after each row of data (actually three columns, the third column is for transaction rollback, omitted here), one saves the row's creation version number, and the other saves the row's updated version number (the last updated data version number), which is the version number of each transaction, incremented. This ensures that the innodb read operation does not need to be locked to ensure the correct reading of data.

Version number of MVCC select lock-free operation and maintenance

Below under the default Repeatable Read isolation level of MySQL, take a look at the MVCC operation:

* Select (snapshot read, the so-called read snapshot is to read data before the current transaction. ):

A.InnoDB only looks for rows whose version number is earlier than the current version number, thus ensuring that the data read is either commit before the transaction starts (earlier than the current version number), or data that performs the creation operation in the transaction itself (equal to the current version number).

b. The update version number of the lookup row is either undefined or greater than the current version number (to ensure that the transaction can read the old data), which ensures that the transaction reads data that has not been updated since the start of the current transaction.

Note: the select here cannot have for update or lock in share statements. In short, only the row data that meets the following conditions should be returned to achieve the effect of snapshot reading:

(line creation version number

< =当前版本号 && (行更新版本号==null or 行更新版本号>

Current version number))

* Insert InnoDB creates a version number for the newly inserted row in this transaction, saving the row of the current transaction version number as the row creation version number.

Delete InnoDB saves the current transaction version number for each deleted row as the delete mark for the row.

* Update will have two pieces of data, keep the current version number as the new version number of the updated data, and save the current version number as the updated version number of the old data row.

Current version number-write-> New data Row creation version number & & current version number-write-> Old data Update version number ()

5.2.Dirty reading vs phantom reading vs can not be repeated

Dirty read: the update data of the intermediate state of an uncommitted transaction is read by other sessions.

When a transaction is accessing the data and has modified the data, and the modification has not been committed to the database (commit has not been executed), the session also accesses the data, because the data has not been submitted yet, then the data read by another session is dirty data, and the operation based on the dirty data may also be incorrect.

Unrepeatable read: simply put, the data read in a transaction may change, and ReadCommitted is also known as unrepeatable read.

In the same transaction, the results returned by reading the same data multiple times are different. In other words, subsequent reads can read updated data that has been committed by another session transaction. In contrast, when "repeatable read" reads data multiple times in the same transaction, it ensures that the read data is the same, that is, subsequent reads cannot read the updated data that has been committed by another session transaction.

Phantom reading: a query is executed in a session T1 transaction, and then session T2 inserts a new row of records that exactly meet the conditions of the query used by T1. T1 then uses the same query to retrieve the table again, but at this point you see the new row that transaction T2 just inserted. This new line is called an "illusion" because it is like a sudden appearance to T1. The RR level of innoDB cannot completely avoid phantom reading, which is analyzed in detail below.

5.3.How can I ensure that there is absolutely no illusion at the rr level?

Add for update (exclusive lock) or lock in share mode (shared lock) statement to the select... where statement used to implement it. In fact, it locks the data that may cause phantom reading and prevents the data from being written.

In fact, because the write operation of data (insert, update) needs to acquire the write lock first, because some kind of lock has been acquired in the part that may produce phantom reading, the premise of obtaining the write lock in another session is that all the locks caused by the locked statement are released in the current session.

5.4. Lock from another point of view: explicit lock, implicit lock

Implicit locks: the locks we mentioned above are implicit locks that do not require additional statements.

Display Lock:

SELECT... LOCK IN SHARE MODE (with shared lock)

SELECT... FOR UPDATE (plus exclusive lock)

The details have been mentioned above.

5.5. Check the lock condition

You can check the situation of waiting for a lock through the following sql

Select * from information_schema.innodb_trx where trx_state= "lock wait"

Or

Show engine innodb status

6. MySQL deadlock problem

Deadlock, is the creation of a circular waiting chain, I wait for your resources, but you wait for my resources, we all wait for each other, no one releases their own resources, leading to wireless waiting. For example:

/ / Session A

START TRANSACTION

UPDATE account SET p_money=p_money-100 WHERE pendant name = "tim"

UPDATE account SET p_money=p_money+100 WHERE pendant name = "bill"

COMMIT

/ / Thread B

START TRANSACTION

UPDATE account SET p_money=p_money+100 WHERE pendant name = "bill"

UPDATE account SET p_money=p_money-100 WHERE pendant name = "tim"

COMMIT

When thread An executes the first statement, UPDATE account SET p_money=p_money-100 WHERE pendant name = "tim"; locks the row data of pairname = "tim"; and attempts to get the data of pairname = "bill"

At this point, thread B also executes the first statement: UPDATE account SET p_money=p_money+100 WHERE pairname = "bill"; locks the data of pairname = "bill" while trying to get the data of pairname = "tim"

At this point, the two threads enter the deadlock, no one can get the resources they want to get, enter the wireless wait, until the timeout!

Innodb_lock_wait_timeout waits for lock timeout to roll back the transaction:

The intuitive method is that when two transactions wait for each other, when one waiting time exceeds a set threshold, one transaction is rolled back and the other transaction can continue to execute.

This method is simple and effective. In innodb, the parameter innodb_lock_wait_timeout is used to set the timeout.

Wait-for graph algorithm for active deadlock detection: innodb also provides wait-for graph algorithm for active deadlock detection, whenever the lock request can not immediately meet the needs and enter the wait, the wait-for graph algorithm will be triggered.

6.1. How to avoid deadlocks as much as possible

Access tables and rows in a fixed order. For example, for two transactions that update data, transaction A updates data in the order of 1mem2, and transaction B updates data in the order of 2mem1. This is more likely to cause a deadlock.

Big business, small business. Large transactions are more likely to be deadlocked. If business allows, large transactions will be split into smaller ones.

In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

Lower the quarantine level. If the business allows, lowering the isolation level is also a better choice. For example, changing the isolation level from RR to RC can avoid many deadlocks caused by gap locks. (our MySQL standard practice)

Add a reasonable index to the table. You can see that if you don't move the index, you will add a lock to each row of the table, which greatly increases the probability of deadlock.

Extended reading:

MySQL official website reference document: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.