Home >Database >Mysql Tutorial >Previous article MySQL statement locking analysis
mysql learning column analyzes the locking of MySQL statements
Recommended ( Free): mysql learning (video)
Preparation
Create a store for the heroes of the Three Kingdomshero
Table:
CREATE TABLE hero ( number INT, name VARCHAR(100), country varchar(100), PRIMARY KEY (number), KEY idx_name (name)) Engine=InnoDB CHARSET=utf8;
Then insert a few records into this table:
INSERT INTO hero VALUES (1, 'l刘备', '蜀'), (3, 'z诸葛亮', '蜀'), (8, 'c曹操', '魏'), (15, 'x荀彧', '魏'), (20, 's孙权', '吴');
Then now the hero
table has two indexes (one secondary index, one cluster Cluster index), the schematic diagram is as follows:
Statement lock analysis
In fact, "XXX statement "What lock should be added?" itself is a false proposition. The lock that a statement needs to add is subject to many conditions, for example:
Isolation level of transaction
The index used when the statement is executed (such as clustered index, unique secondary index, ordinary secondary index)
Query conditions (for example, =
, =, <code>>=
, etc.)
Specific execution statement type
Before continuing to analyze the locking process of statements in detail, everyone must have a global concept: Locking
only solves dirty writes
, caused by concurrent transaction execution. Dirty read
, Non-repeatable read
, phantom read
is a solution to these problems (MVCC
is a solutionDirty read
, Non-repeatable reading
, phantom reading
is a solution to these problems), we must realize that the starting point of locking
is to solve these problems, different The problems to be solved in different scenarios are different, which leads to different locks. Never add locks just for the sake of locking, as it is easy to get yourself involved. Of course, sometimes due to the specific implementation of MySQL
, locking in some scenarios is not easy to understand, so we have to memorize it by rote~
We divide the statements here into There are 3 major categories: ordinary SELECT
statements, locked read statements, and INSERT
statements. Let’s look at them separately.
Ordinary SELECT statement
Ordinary SELECT
statement is in:
READ UNCOMMITTED
Under the isolation level, the latest version of the record is read directly without locking, and dirty reads
, non-repeatable reads
and may occur. Phantom reading
problem.
READ COMMITTED
Under the isolation level, there is no locking, and a ReadView will be generated every time a normal
SELECT statement is executed.
, This solves the dirty read
problem, but does not solve the non-repeatable read
and phantom read
problems.
REPEATABLE READ
Under the isolation level, no locking is performed, and a is generated only when the ordinary
SELECT statement is executed for the first time. ReadView
, this solves the problems of dirty reading
, non-repeatable reading
and phantom reading
.
But here is a small episode:
# 事务T1,REPEATABLE READ隔离级别下 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE number = 30; Empty set (0.01 sec) # 此时事务T2执行了:INSERT INTO hero VALUES(30, 'g关羽', '魏'); 并提交 mysql> UPDATE hero SET country = '蜀' WHERE number = 30; Query OK, 1 row affected (0.01 sec)Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM hero WHERE number = 30; | number | name | country | | 30 | g关羽 | 蜀 | 1 row in set (0.01 sec)
Under the REPEATABLE READ
isolation level, T1
executes a normal SELECT for the first time
statement generated a ReadView
, and then T2
inserted a new record into the hero
table and submitted it, ReadView
It does not prevent T1
from executing the UPDATE
or DELETE
statement to change the newly inserted record (because T2
has already submitted, change the record will not cause blocking), but in this way, the trx_id
hidden column of this new record will become the transaction id
of T1
, and then When you use the ordinary
SELECT statement in T1
to query this record, you can see this record and return this record to the client. Because of the existence of this special phenomenon, you can also think that MVCC
in InnoDB
cannot completely prohibit phantom reads.
SERIALIZABLE
Under the isolation level, the discussion needs to be divided into two situations:
In the system variableautocommit =0
, that is, when automatic submission is disabled, the ordinary SELECT
statement will be converted into a statement like SELECT ... LOCK IN SHARE MODE
, that is, when reading Before fetching the record, you need to obtain the S lock
of the record. The specific locking situation is the same as that under the REPEATABLE READ
isolation level. We will analyze it later.
When the system variable autocommit=1
, that is, when automatic submission is enabled, the ordinary SELECT
statement does not lock, but uses MVCC
to generate a ReadView
to read records.
为啥不加锁呢?因为启用自动提交意味着一个事务中只包含一条语句,一条语句也就没有啥不可重复读
、幻读
这样的问题了。
锁定读的语句
我们把下边四种语句放到一起讨论:
语句一:SELECT ... LOCK IN SHARE MODE;
语句二:SELECT ... FOR UPDATE;
语句三:UPDATE ...
语句四:DELETE ...
我们说语句一
和语句二
是MySQL
中规定的两种锁定读
的语法格式,而语句三
和语句四
由于在执行过程需要首先定位到被改动的记录并给记录加锁,也可以被认为是一种锁定读
。
在READ UNCOMMITTED
下语句的加锁方式和READ COMMITTED
隔离级别下语句的加锁方式基本一致,所以就放到一块儿说了。值得注意的是,采用加锁
方式解决并发事务带来的问题时,其实脏读
和不可重复读
在任何一个隔离级别下都不会发生(因为读-写
操作需要排队进行)。
使用SELECT ... LOCK IN SHARE MODE
来为记录加锁,比方说:
SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;
这个语句执行时只需要访问一下聚簇索引中number
值为8
的记录,所以只需要给它加一个S型正经记录锁
就好了,如图所示:
使用SELECT ... FOR UPDATE
来为记录加锁,比方说:
SELECT * FROM hero WHERE number = 8 FOR UPDATE;
这个语句执行时只需要访问一下聚簇索引中number
值为8
的记录,所以只需要给它加一个X型正经记录锁
就好了,如图所示:
小贴士: 为了区分S锁和X锁,我们之后在示意图中就把加了S锁的记录染成蓝色,把加了X锁的记录染成紫色。
使用UPDATE ...
来为记录加锁,比方说:
UPDATE hero SET country = '汉' WHERE number = 8;
这条UPDATE
语句并没有更新二级索引列,加锁方式和上边所说的SELECT ... FOR UPDATE
语句一致。
如果UPDATE
语句中更新了二级索引列,比方说:
UPDATE hero SET name = 'cao曹操' WHERE number = 8;
该语句的实际执行步骤是首先更新对应的number
值为8
的聚簇索引记录,再更新对应的二级索引记录,所以加锁的步骤就是:
为number
值为8
的聚簇索引记录加上X型正经记录锁
(该记录对应的)。
为该聚簇索引记录对应的idx_name
二级索引记录(也就是name
值为'c曹操'
,number
值为8
的那条二级索引记录)加上X型正经记录锁
。
画个图就是这样:
小贴士: 我们用带圆圈的数字来表示为各条记录加锁的顺序。
使用DELETE ...
来为记录加锁,比方说:
DELETE FROM hero WHERE number = 8;
我们平时所说的“DELETE表中的一条记录”其实意味着对聚簇索引和所有的二级索引中对应的记录做DELETE
操作,本例子中就是要先把number
值为8
的聚簇索引记录执行DELETE
操作,然后把对应的idx_name
二级索引记录删除,所以加锁的步骤和上边更新带有二级索引列的UPDATE
语句一致,就不画图了。
使用SELECT ... LOCK IN SHARE MODE
来为记录加锁,比方说:
SELECT * FROM hero WHERE number <p>这个语句看起来十分简单,但它的执行过程还是有一丢丢小复杂的:</p>
先到聚簇索引中定位到满足number 的第一条记录,也就是<code>number
值为1
的记录,然后为其加锁。
判断一下该记录是否符合索引条件下推
中的条件。
我们前边介绍过一个称之为索引条件下推
( Index Condition Pushdown
,简称ICP
)的功能,也就是把查询中与被使用索引有关的查询条件下推到存储引擎中判断,而不是返回到server
层再判断。不过需要注意的是,索引条件下推
只是为了减少回表次数,也就是减少读取完整的聚簇索引记录的次数,从而减少IO
操作。而对于聚簇索引
而言不需要回表,它本身就包含着全部的列,也起不到减少IO
操作的作用,所以设计InnoDB
的大叔们规定这个索引条件下推
特性只适用于二级索引
。也就是说在本例中与被使用索引有关的条件是:number ,而<code>number
列又是聚簇索引列,所以本例中并没有符合索引条件下推
的查询条件,自然也就不需要判断该记录是否符合索引条件下推
中的条件。
判断一下该记录是否符合范围查询的边界条件
因为在本例中是利用主键number
进行范围查询,设计InnoDB
的大叔规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件,也就是number 这个条件。如果符合的话将其返回给<code>server层
继续处理,否则的话需要释放掉在该记录上加的锁,并给server层
返回一个查询完毕的信息。
对于number
值为1
的记录是符合这个条件的,所以会将其返回到server层
继续处理。
将该记录返回到server层
继续判断。
server层
如果收到存储引擎层提供的查询完毕的信息,就结束查询,否则继续判断那些没有进行索引条件下推
的条件,在本例中就是继续判断number 这个条件是否成立。噫,不是在第3步中已经判断过了么,怎么在这又判断一回?是的,设计<code>InnoDB
的大叔采用的策略就是这么简单粗暴,把凡是没有经过索引条件下推
的条件都需要放到server
层再判断一遍。如果该记录符合剩余的条件(没有进行索引条件下推
的条件),那么就把它发送给客户端,不然的话需要释放掉在该记录上加的锁。
然后刚刚查询得到的这条记录(也就是number
值为1
的记录)组成的单向链表继续向后查找,得到了number
值为3
的记录,然后重复第2
,3
,4
、5
这几个步骤。
小贴士: 上述步骤是在MySQL 5.7.21这个版本中验证的,不保证其他版本有无出入。
但是这个过程有个问题,就是当找到number
值为8
的那条记录的时候,还得向后找一条记录(也就是number
值为15
的记录),在存储引擎读取这条记录的时候,也就是上述的第1
步中,就得为这条记录加锁,然后在第3步时,判断该记录不符合number 这个条件,又要释放掉这条记录的锁,这个过程导致<code>number
值为15
的记录先被加锁,然后把锁释放掉,过程就是这样:
这个过程有意思的一点就是,如果你先在事务T1
中执行:
# 事务T1BEGIN;SELECT * FROM hero WHERE number <p>然后再到事务<code>T2</code>中执行:</p><pre class="brush:php;toolbar:false"># 事务T2BEGIN;SELECT * FROM hero WHERE number = 15 FOR UPDATE;
是没有问题的,因为在T2
执行时,事务T1
已经释放掉了number
值为15
的记录的锁,但是如果你先执行T2
,再执行T1
,由于T2
已经持有了number
值为15
的记录的锁,事务T1
将因为获取不到这个锁而等待。
我们再看一个使用主键进行范围查询的例子:
SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;
这个语句的执行过程其实和我们举的上一个例子类似。也是先到聚簇索引中定位到满足number >= 8
这个条件的第一条记录,也就是number
值为8
的记录,然后就可以沿着由记录组成的单向链表一路向后找,每找到一条记录,就会为其加上锁,然后判断该记录符不符合范围查询的边界条件,不过这里的边界条件比较特殊:number >= 8
,只要记录不小于8就算符合边界条件,所以判断和没判断是一样一样的。最后把这条记录返回给server层
,server层
再判断number >= 8
这个条件是否成立,如果成立的话就发送给客户端,否则的话就结束查询。不过InnoDB
存储引擎找到索引中的最后一条记录,也就是Supremum
伪记录之后,在存储引擎内部就可以立即判断这是一条伪记录,不必要返回给server层
处理,也没必要给它也加上锁(也就是说在第1步中就压根儿没给这条记录加锁)。整个过程会给number
值为8
、15
、20
这三条记录加上S型正经记录锁
,画个图表示一下就是这样:
使用SELECT ... FOR UPDATE
语句来为记录加锁:
和SELECT ... FOR UPDATE
语句类似,只不过加的是X型正经记录锁
。
使用UPDATE ...
来为记录加锁,比方说:
UPDATE hero SET country = '汉' WHERE number >= 8;
这条UPDATE
语句并没有更新二级索引列,加锁方式和上边所说的SELECT ... FOR UPDATE
语句一致。
如果UPDATE
语句中更新了二级索引列,比方说:
UPDATE hero SET name = 'cao曹操' WHERE number >= 8;
这时候会首先更新聚簇索引记录,再更新对应的二级索引记录,所以加锁的步骤就是:
为number
值为8
的聚簇索引记录加上X型正经记录锁
。
然后为上一步中的记录索引记录对应的idx_name
二级索引记录加上X型正经记录锁
。
为number
值为15
的聚簇索引记录加上X型正经记录锁
。
然后为上一步中的记录索引记录对应的idx_name
二级索引记录加上X型正经记录锁
。
为number
值为20
的聚簇索引记录加上X型正经记录锁
。
然后为上一步中的记录索引记录对应的idx_name
二级索引记录加上X型正经记录锁
。
画个图就是这样:
如果是下边这个语句:
UPDATE hero SET namey = '汉' WHERE number <p>则会对<code>number</code>值为<code>1</code>、<code>3</code>、<code>8</code>聚簇索引记录以及它们对应的二级索引记录加<code>X型正经记录锁</code>,加锁顺序和上边语句中的加锁顺序类似,都是先对一条聚簇索引记录加锁后,再给对应的二级索引记录加锁。之后会继续对<code>number</code>值为<code>15</code>的聚簇索引记录加锁,但是随后<code>InnoDB</code>存储引擎判断它不符合边界条件,随即会释放掉该聚簇索引记录上的锁(注意这个过程中没有对<code>number</code>值为<code>15</code>的聚簇索引记录对应的二级索引记录加锁)。具体示意图就不画了。</p>
使用DELETE ...
来为记录加锁,比方说:
DELETE FROM hero WHERE number >= 8;
和
DELETE FROM hero WHERE number <p>这两个语句的加锁情况和更新带有二级索引列的<code>UPDATE</code>语句一致,就不画图了。</p>
小贴士: 在READ UNCOMMITTED和READ COMMITTED隔离级别下,使用普通的二级索引和唯一二级索引进行加锁的过程是一样的,所以我们也就不分开讨论了。
使用SELECT ... LOCK IN SHARE MODE
来为记录加锁,比方说:
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
这个语句的执行过程是先通过二级索引idx_name
定位到满足name = 'c曹操'
条件的二级索引记录,然后进行回表操作。所以先要对二级索引记录加S型正经记录锁
,然后再给对应的聚簇索引记录加S型正经记录锁
,示意图如下:
这里需要再次强调一下这个语句的加锁顺序:
先对name
列为'c曹操'
二级索引记录进行加锁。
再对相应的聚簇索引记录进行加锁
小贴士: 我们知道idx_name是一个普通的二级索引,到idx_name索引中定位到满足name= ‘c曹操’这个条件的第一条记录后,就可以沿着这条记录一路向后找。可是从我们上边的描述中可以看出来,并没有对下一条二级索引记录进行加锁,这是为什么呢?这是因为设计InnoDB的大叔对等值匹配的条件有特殊处理,他们规定在InnoDB存储引擎层查找到当前记录的下一条记录时,在对其加锁前就直接判断该记录是否满足等值匹配的条件,如果不满足直接返回(也就是不加锁了),否则的话需要将其加锁后再返回给server层。所以这里也就不需要对下一条二级索引记录进行加锁了。
现在要介绍一个非常有趣的事情,我们假设上边这个语句在事务T1
中运行,然后事务T2
中运行下边一个我们之前介绍过的语句:
UPDATE hero SET name = '曹操' WHERE number = 8;
这两个语句都是要对number
值为8
的聚簇索引记录和对应的二级索引记录加锁,但是不同点是加锁的顺序不一样。这个UPDATE
语句是先对聚簇索引记录进行加锁,后对二级索引记录进行加锁,如果在不同事务中运行上述两个语句,可能发生一种贼奇妙的事情 ——
事务T2
持有了聚簇索引记录的锁,事务T1
持有了二级索引记录的锁。
事务T2
在等待获取二级索引记录上的锁,事务T1
在等待获取聚簇索引记录上的锁。
两个事务都分别持有一个锁,而且都在等待对方已经持有的那个锁,这种情况就是所谓的死锁
,两个事务都无法运行下去,必须选择一个进行回滚,对性能影响比较大。
使用SELECT ... FOR UPDATE
语句时,比如:
SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
这种情况下与SELECT ... LOCK IN SHARE MODE
语句的加锁情况类似,都是给访问到的二级索引记录和对应的聚簇索引记录加锁,只不过加的是X型正经记录锁
罢了。
使用UPDATE ...
来为记录加锁,比方说:
与更新二级索引记录的SELECT ... FOR UPDATE
的加锁情况类似,不过如果被更新的列中还有别的二级索引列的话,对应的二级索引记录也会被加锁。
使用DELETE ...
来为记录加锁,比方说:
与SELECT ... FOR UPDATE
的加锁情况类似,不过如果表中还有别的二级索引列的话,对应的二级索引记录也会被加锁。
使用SELECT ... LOCK IN SHARE MODE
来为记录加锁,比方说:
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name >= 'c曹操' LOCK IN SHARE MODE;
小贴士: 因为优化器会计算使用二级索引进行查询的成本,在成本较大时可能选择以全表扫描的方式来执行查询,所以我们这里使用FORCE INDEX(idx_name)来强制使用二级索引idx_name来执行查询。
这个语句的执行过程其实是先到二级索引中定位到满足name >= 'c曹操'
的第一条记录,也就是name
值为c曹操
的记录,然后就可以沿着这条记录的链表一路向后找,从二级索引idx_name
的示意图中可以看出,所有的用户记录都满足name >= 'c曹操'
的这个条件,所以所有的二级索引记录都会被加S型正经记录锁
,它们对应的聚簇索引记录也会被加S型正经记录锁
。不过需要注意一下加锁顺序,对一条二级索引记录加锁完后,会接着对它相应的聚簇索引记录加锁,完后才会对下一条二级索引记录进行加锁,以此类推~ 画个图表示一下就是这样:
再来看下边这个语句:
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <p>这个语句的加锁情况就有点儿有趣了。前边说在使用<code>number 这个条件的语句中,需要把<code>number</code>值为<code>15</code>的记录也加一个锁,之后又判断它不符合边界条件而把锁释放掉。而对于查询条件<code>name 的语句来说,执行该语句需要使用到二级索引,而与二级索引相关的条件是可以使用<code>索引条件下推</code>这个特性的。设计<code>InnoDB</code>的大叔规定,如果一条记录不符合<code>索引条件下推</code>中的条件的话,直接跳到下一条记录(这个过程根本不将其返回到<code>server层</code>),如果这已经是最后一条记录,那么直接向<code>server层</code>报告查询完毕。但是这里头有个问题呀:先对一条记录加了锁,然后再判断该记录是不是符合索引条件下推的条件,如果不符合直接跳到下一条记录或者直接向server层报告查询完毕,这个过程中并没有把那条被加锁的记录上的锁释放掉呀!!!。本例中使用的查询条件是<code>name ,在为<code>name</code>值为<code>'c曹操'</code>的二级索引记录以及它对应的聚簇索引加锁之后,会接着二级索引中的下一条记录,也就是<code>name</code>值为<code>'l刘备'</code>的那条二级索引记录,由于该记录不符合<code>索引条件下推</code>的条件,而且是范围查询的最后一条记录,会直接向<code>server层</code>报告查询完毕,重点是这个过程中并不会释放<code>name</code>值为<code>'l刘备'</code>的二级索引记录上的锁,也就导致了语句执行完毕时的加锁情况如下所示:</code></code></code></p>
这样子会造成一个尴尬情况,假如T1
执行了上述语句并且尚未提交,T2
再执行这个语句:
SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;
T2
中的语句需要获取name
值为l刘备
的二级索引记录上的X型正经记录锁
,而T1
中仍然持有name
值为l刘备
的二级索引记录上的S型正经记录锁
,这就造成了T2
获取不到锁而进入等待状态。
小贴士: 为啥不能释放不符合索引条件下推中的条件的二级索引记录上的锁呢?这个问题我也没想明白,人家就是这么规定的,如果有明白的小伙伴可以加我微信 xiaohaizi4919 来讨论一下哈~ 再强调一下,我使用的MySQL版本是5.7.21,不保证其他版本中的加锁情景是否完全一致。
使用SELECT ... FOR UPDATE
语句时:
和SELECT ... FOR UPDATE
语句类似,只不过加的是X型正经记录锁
。
使用UPDATE ...
来为记录加锁,比方说:
UPDATE hero SET country = '汉' WHERE name >= 'c曹操';
小贴士: FORCE INDEX只对SELECT语句起作用,UPDATE语句虽然支持该语法,但实质上不起作用,DELETE语句压根儿不支持该语法。
假设该语句执行时使用了idx_name
二级索引来进行锁定读
,那么它的加锁方式和上边所说的SELECT ... FOR UPDATE
语句一致。如果有其他二级索引列也被更新,那么也会为对应的二级索引记录进行加锁,就不赘述了。不过还有一个有趣的情况,比方说:
UPDATE hero SET country = '汉' WHERE name <p>我们前边说的<code>索引条件下推</code>这个特性只适用于<code>SELECT</code>语句,也就是说<code>UPDATE</code>语句中无法使用,那么这个语句就会为<code>name</code>值为<code>'c曹操'</code>和<code>'l刘备'</code>的二级索引记录以及它们对应的聚簇索引进行加锁,之后在判断边界条件时发现<code>name</code>值为<code>'l刘备'</code>的二级索引记录不符合<code>name 条件,再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示:</code></p>
使用DELETE ...
来为记录加锁,比方说:
DELETE FROM hero WHERE name >= 'c曹操';
和
DELETE FROM hero WHERE name <p>如果这两个语句采用二级索引来进行<code>锁定读</code>,那么它们的加锁情况和更新带有二级索引列的<code>UPDATE</code>语句一致,就不画图了。</p>
比方说:
SELECT * FROM hero WHERE country = '魏' LOCK IN SHARE MODE;
由于country
列上未建索引,所以只能采用全表扫描的方式来执行这条查询语句,存储引擎每读取一条聚簇索引记录,就会为这条记录加锁一个S型正常记录锁
,然后返回给server层
,如果server层
判断country = '魏'
这个条件是否成立,如果成立则将其发送给客户端,否则会释放掉该记录上的锁,画个图就像这样:
使用SELECT ... FOR UPDATE
进行加锁的情况与上边类似,只不过加的是X型正经记录锁
,就不赘述了。
对于UPDATE ...
和DELETE ...
的语句来说,在遍历聚簇索引中的记录,都会为该聚簇索引记录加上X型正经记录锁
,然后:
如果该聚簇索引记录不满足条件,直接把该记录上的锁释放掉。
如果该聚簇索引记录满足条件,则会对相应的二级索引记录加上X型正经记录锁
(DELETE
语句会对所有二级索引列加锁,UPDATE
语句只会为更新的二级索引列对应的二级索引记录加锁)。
The above is the detailed content of Previous article MySQL statement locking analysis. For more information, please follow other related articles on the PHP Chinese website!