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

This article will explain in detail the example analysis of select locking in Mysql. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Introduction

Did you encounter the interviewer asking you the difference of the following six sentences of Sql during the interview?

Select * from table where id =? select * from table where id

< ?select * from table where id = ? lock in share modeselect * from table where id < ? lock in share modeselect * from table where id = ? for updateselect * from table where id < ? for update 如果你能清楚的说出，这六句sql在不同的事务隔离级别下，是否加锁，加的是共享锁还是排他锁，是否存在间隙锁，那这篇文章就没有看的意义了。 之所以写这篇文章是因为目前为止网上这方面的文章太片面，都只说了一半，且大多没指明隔离级别，以及where后跟的是否为索引条件列。在此，我就不一一列举那些有误的文章了，大家可以自行百度一下，大多都是讲不清楚。 OK，要回答这个问题，先问自己三个问题 当前事务隔离级别是什么 id列是否存在索引 如果存在索引是聚簇索引还是非聚簇索引呢？ OK，开始回答 正文 innodb一定存在聚簇索引，默认以主键作为聚簇索引 有几个索引，就有几棵B+树(不考虑hash索引的情形) 聚簇索引的叶子节点为磁盘上的真实数据。非聚簇索引的叶子节点还是索引，指向聚簇索引B+树。 下面啰嗦点基础知识 锁类型 共享锁(S锁):假设事务T1对数据A加上共享锁，那么事务T2可以读数据A，不能修改数据A。 排他锁(X锁):假设事务T1对数据A加上共享锁，那么事务T2不能读数据A，不能修改数据A。 我们通过update、delete等语句加上的锁都是行级别的锁。只有LOCK TABLE … READ和LOCK TABLE … WRITE才能申请表级别的锁。 意向共享锁(IS锁):一个事务在获取（任何一行/或者全表）S锁之前，一定会先在所在的表上加IS锁。 意向排他锁(IX锁):一个事务在获取（任何一行/或者全表）X锁之前，一定会先在所在的表上加IX锁。 意向锁存在的目的? OK，这里说一下意向锁存在的目的。假设事务T1，用X锁来锁住了表上的几条记录，那么此时表上存在IX锁，即意向排他锁。那么此时事务T2要进行LOCK TABLE … WRITE的表级别锁的请求，可以直接根据意向锁是否存在而判断是否有锁冲突。 加锁算法 我的说法是来自官方文档: https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html 加上自己矫揉造作的见解得出。 ok，记得如下三种，本文就够用了 Record Locks：简单翻译为行锁吧。注意了，该锁是对索引记录进行加锁！锁是在加索引上而不是行上的。注意了，innodb一定存在聚簇索引，因此行锁最终都会落到聚簇索引上！ Gap Locks：简单翻译为间隙锁，是对索引的间隙加锁，其目的只有一个，防止其他事物插入数据。在Read Committed隔离级别下，不会使用间隙锁。这里我对官网补充一下，隔离级别比Read Committed低的情况下，也不会使用间隙锁，如隔离级别为Read Uncommited时，也不存在间隙锁。当隔离级别为Repeatable Read和Serializable时，就会存在间隙锁。 Next-Key Locks：这个理解为Record Lock+索引前面的Gap Lock。记住了，锁住的是索引前面的间隙！比如一个索引包含值，10，11，13和20。那么，间隙锁的范围如下 (negative infinity, 10](10, 11](11, 13](13, 20](20, positive infinity)快照读和当前读 最后一点基础知识了，大家坚持看完，这些是后面分析的基础！ 在mysql中select分为快照读和当前读，执行下面的语句 select * from table where id = ?; 执行的是快照读，读的是数据库记录的快照版本，是不加锁的。（这种说法在隔离级别为Serializable中不成立，后面我会补充。） 那么，执行 select * from table where id = ? lock in share mode; 会对读取记录加S锁 (共享锁)，执行 select * from table where id = ? for update 会对读取记录加X锁 (排他锁)，那么 加的是表锁还是行锁呢？ 针对这点，我们先回忆一下事务的四个隔离级别，他们由弱到强如下所示: Read Uncommited(RU)：读未提交，一个事务可以读到另一个事务未提交的数据！ Read Committed (RC)：读已提交，一个事务可以读到另一个事务已提交的数据! Repeatable Read (RR):可重复读，加入间隙锁，一定程度上避免了幻读的产生！注意了，只是一定程度上，并没有完全避免!我会在下一篇文章说明!另外就是记住从该级别才开始加入间隙锁(这句话记下来，后面有用到)! Serializable：串行化，该级别下读写串行化，且所有的select语句后都自动加上lock in share mode，即使用了共享锁。因此在该隔离级别下，使用的是当前读，而不是快照读。 那么关于是表锁还是行锁，大家可以看到网上最流传的一个说法是这样的， InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。 InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！ 这句话大家可以搜一下，都是你抄我的，我抄你的。那么，这句话本身有两处错误！ 错误一:并不是用表锁来实现锁表的操作，而是利用了Next-Key Locks，也可以理解为是用了行锁+间隙锁来实现锁表的操作! 为了便于说明，我来个例子，假设有表数据如下，pId为主键索引 pId(int)name(varchar)num(int)1aaa1002bbb2007ccc200 执行语句(name列无索引) select * from table where name = `aaa` for update 那么此时在pId=1,2,7这三条记录上存在行锁(把行锁住了)。另外，在(-∞,1)(1,2)(2,7)(7,+∞)上存在间隙锁(把间隙锁住了)。因此，给人一种整个表锁住的错觉！ ps:对该结论有疑问的，可自行执行show engine innodb status;语句进行分析。 错误二:所有文章都不提隔离级别！ 注意我上面说的，之所以能够锁表，是通过行锁+间隙锁来实现的。那么，RU和RC都不存在间隙锁，这种说法在RU和RC中还能成立么？ 因此，该说法只在RR和Serializable中是成立的。如果隔离级别为RU和RC，无论条件列上是否有索引，都不会锁表，只锁行！ 分析 下面来对开始的问题作出解答，假设有表如下，pId为主键索引 pId(int)name(varchar)num(int)1aaa1002bbb2003bbb3007ccc200RC/RU+条件列非索引 (1)select * from table where num = 200 不加任何锁，是快照读。 (2)select * from table where num >

two hundred

Without any lock, it is a snapshot read.

(3) select * from table where num = 200lock in share mode

When num = 200, there are two records. These two records correspond to the pId=2,7, so the row-level S lock is added to the clustered index of pId=2,7, using the current read.

(4) select * from table where num > 200 lock in share mode

When num > 200, there is a record. This record corresponds to the pId=3, so the row-level S lock is added to the clustered index of pId=3, using the current read.

(5) select * from table where num = 200for update

When num = 200, there are two records. These two records correspond to the pId=2,7, so the row-level X lock is added to the clustered index of pId=2,7, using the current read.

(6) select * from table where num > 200 for update

When num > 200, there is a record. This record corresponds to the pId=3, so a row-level X lock is added to the clustered index of pId=3, using the current read.

RC/RU+ conditional column is a clustered index

Well, you should know that pId is the primary key column, so pId uses clustered indexes. This situation is actually similar to the non-indexed case of RC/RU+ conditional columns.

(1) select * from table where pId = 2

Without any lock, it is a snapshot read.

(2) select * from table where pId > 2

Without any lock, it is a snapshot read.

(3) select * from table where pId = 2 lock in share mode

On the clustered index of pId=2, add an S lock for the current read.

(4) select * from table where pId > 2 lock in share mode

On the clustered index of pId=3,7, add an S lock for the current read.

(5) select * from table where pId = 2 for update

On the clustered index of pId=2, add an X lock for the current read.

(6) select * from table where pId > 2 for update

On the clustered index of pId=3,7, add an X lock for the current read.

Here, you may have questions.

Why is it the same for conditional columns without indexing and locking?

Ok, in fact, is different. In the RC/RU isolation level, MySQL Server is optimized. In the case that the conditional column does not have an index, although the whole table is scanned and locked through the clustering index. However, the MySQL Server layer filters and releases unqualified locks immediately, so you look like the end result is the same. But the RC/RU+ conditional column is non-indexed with an extra process of releasing ineligible locks than in this example!

RC/RU+ conditional column is a non-clustered index

We build a non-unique index on the num column. At this time, there is a B+ index tree formed by a clustered index (primary key index, pId), and its leaf node is the real data on the hard disk. And the B+ index tree formed by another non-clustered index (non-unique index, num), whose leaf node is still the index node, which stores the field values of the num column and the corresponding clustered index.

Next, the analysis begins.

(1) select * from table where num = 200

Without any lock, it is a snapshot read.

(2) select * from table where num > 200

Without any lock, it is a snapshot read.

(3) select * from table where num = 200lock in share mode

When num = 200, because there is an index on the num column, first add a row-level S lock on the two index records of num = 200. Next, query on the clustered index tree, and the two records correspond to the pId=2,7, so the row-level S lock is added to the clustered index of pId=2,7 and the current read is adopted.

(4) select * from table where num > 200 lock in share mode

When num > 200, because there is an index on the num column, we first add a row-level S lock on an index record that meets the condition of num = 300. Next, the query is made on the clustered index tree, and the record corresponds to the pId=3, so the row-level S lock is added to the clustered index of pId=3, using the current read.

(5) select * from table where num = 200for update

When num = 200, because there is an index on the num column, first add a row-level X lock on the two index records of num = 200. Then, the query is made on the clustered index tree, and the two records correspond to the pId=2,7, so the row-level X lock is added to the clustered index of pId=2,7, and the current read is adopted.

(6) select * from table where num > 200 for update

When num > 200, because there is an index on the num column, first add a row-level X lock on an index record that meets the condition of num = 300. Next, the query is made on the clustered index tree, and the record corresponds to the pId=3, so the row-level X lock is added to the clustered index of pId=3, using the current read.

RR/Serializable+ conditional column is not indexed

The RR level needs to be considered more than gap lock, and its locking feature is that no matter how you check it, it will lock the whole table. As shown below

Next, the analysis begins.

(1) select * from table where num = 200

At the RR level, there is no lock, and it is a snapshot read.

At the Serializable level, add an S lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

(2) select * from table where num > 200

At the RR level, there is no lock, and it is a snapshot read.

At the Serializable level, add an S lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

(3) select * from table where num = 200lock in share mode

Add an S lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

(4) select * from table where num > 200 lock in share mode

Add an S lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

(5) select * from table where num = 200for update

Add an X lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

(6) select * from table where num > 200 for update

Add an X lock on the clustered index of pId = 1, 2, 3, 7 (all records in the whole table). And in

All gaps of a clustered index (- ∞, 1) (1) (1) (2) (2) (3) (3) (7) + ∞) plus gap lock

RR/Serializable+ conditional column is a clustered index

Well, you should know that pId is the primary key column, so pId uses clustered indexes. The locking feature of this case is that if the condition after where is an exact query (in the case of =), then only record lock exists. If the condition after where is a range query (> or 2

At the RR level, there is no lock, and it is a snapshot read.

At the Serializable level, it is currently read, with an S lock on the clustered index of pId=3,7. Add gap lock to (2) (3) (3) (7) + ∞)

(3) select * from table where pId = 2 lock in share mode

Is the current read, adds an S lock on the clustered index of pId=2, and there is no gap lock.

(4) select * from table where pId > 2 lock in share mode

Is the current read, adding an S lock on the clustered index of pId=3,7. Add gap lock to (2) (3) (3) (7) + ∞)

(5) select * from table where pId = 2 for update

Is the current read, adding an X lock on the clustered index of pId=2.

(6) select * from table where pId > 2 for update

Add an X lock to the clustered index of pId=3,7. Add gap lock to (2) (3) (3) (7) + ∞)

(7) select * from table where pId = 6 [lock in share mode | for update]

Note that pId=6 is a non-existent column, and in this case, gap lock will be added to (3. 7).

(8) select * from table where pId > 18 [lock in share mode | for update]

Notice that pId > 18, the query result is empty. In this case, it is to add gap lock to (7 ∞ + 7).

RR/Serializable+ conditional column is a non-clustered index

Here, a non-clustered index needs to distinguish whether it is a unique index or not. Because if it is a non-unique index, there is a difference in how the gap lock is locked.

Let's start with the case of a unique index. If it is a unique index, the situation is similar to that of a RR/Serializable+ conditional column that is a clustered index, except that there are two index trees at this time, and the lock is added to the corresponding non-clustered index tree and clustered index tree! You can decide for yourself!

As we can see below, a non-clustered index is a non-unique index. The difference between a non-clustered index and a unique index is that there is not only record lock but also gap lock after a precise query through the index. After a precise query through a unique index, there is only record lock, not gap lock. The old rule is to build a non-unique index on the num column

(1) select * from table where num = 200

At the RR level, there is no lock, and it is a snapshot read.

At the Serializable level, is the current read, adding S locks on the clustered index of pId=2,7, S locks on the nonclustered index of num=200, and gap lock on (100200) (200300).

(2) select * from table where num > 200

At the RR level, there is no lock, and it is a snapshot read.

At the Serializable level, it is the current read, with S locks on the clustered index of pId=3 and S locks on the nonclustered index of num=300. Add gap lock to (200300) (300 + ∞)

(3) select * from table where num = 200lock in share mode

Is currently read, adding S locks on pId=2,7 's clustered index, S lock on num=200 's nonclustered index, and gap lock on (100200) (200300).

(4) select * from table where num > 200 lock in share mode

Is the current read, adding S locks on pId=3 's clustered indexes and S locks on num=300 's nonclustered indexes. Add gap lock to (200300) (300 + ∞).

(5) select * from table where num = 200for update

Is currently read, adding S lock on pId=2,7 's clustered index, X lock on num=200 's nonclustered index, and gap lock on (100200) (200300).

(6) select * from table where num > 200 for update

Is the current read, adding S locks on pId=3 's clustered indexes and X locks on num=300 's nonclustered indexes. Add gap lock to (200300) (300 + ∞)

(7) select * from table where num = 250 [lock in share mode | for update]

Note that num=250 is a non-existent column, which adds gap lock to (200300).

(8) select * from table where num > 400 [lock in share mode | for update]

Notice that pId > 400, the query result is empty. In this case, gap lock is added to (400) + ∞.

This is the end of the article on "example Analysis of select locking in Mysql". I hope the above content can be helpful to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

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.