MySQL锁(2)：行锁

于 2019-10-28 11:16:04 发布
阅读量991 收藏 1
点赞数
CC 4.0 BY-SA版权
分类专栏： MySQL
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Jakob_Hu/article/details/102774005

行锁


MySQL数据库中InnoDB引擎支持行锁，。

事务隔离级别

不同的事务隔离级别对应的行锁是不同的，对应的行锁的锁定范围也是不同的。

隔离级别说明
read uncommitted 读未提交所有事务都可以看到其他未提交事务对表中数据所做的改变。可能出现脏读
read committed 读已提交事务只能看见已提交的事务对表中数据所做的改变。因为同一个事务的多个实例处理期间可能有其他事务被提交，所以可能出现幻读
repeatable read 可重复读MySQL的默认级别，同一个事务的多个实例在并发读取时看到的是相同的数据行。消除了脏读、幻读
serializable 串行最高的事务隔离级别，强行将事务的实例进行排序，事务之间不可能冲突，可以理解为一个一个来处理

脏读：读取未提交的事务
幻读（不可重复读）：一个事务按照相同的查询条件重新读取以前检索过的数据，发现其他事务插入了满足其查询条件的新数据
更详细的介绍可以看下方的锁问题部分

实际开发中，read uncommitted和serializable几乎不使用。

InnoDB行锁模式

1）共享锁和排它锁

InnoDB实现了两种基本的行锁，

  • 共享锁(S Lock)：允许一个事务去读一行，阻止其他持排它锁的事务获取相同数据
  • 排它锁(X Lock)：允许一个事务去读或更新一行数据，阻止其他持排它锁和共享锁的事务获取相同数据

对于普通的select语句，InnoDB不会加锁，因为DML会使用数据快照读取数据，但是可以显式地给语句加上锁，如

# 加共享锁
select * from t where ... lock in share mode;

# 加排它锁
select * from t where ... for update;
-S LockX Lock
S Lock兼容不兼容
X Lock不兼容不兼容

X Lock与任何锁都是不兼容的。
表格解读：

  • 若事务T1获得了第r行记录的S Lock，另一个事务T2也可以获得该行的S Lock；若存在另一个事务T3，该事务想获取该行记录的X Lock，必须等待T1和T2释放S Lock
  • 若事务T1获得了第r行记录的X Lock，则其他任何事务都无法获得该行的S Lock和X Lock

2）意向锁

InnoDB引擎支持多粒度锁定，允许事务同时对行和表上锁。为实现这一方式，InnoDB提供了意向锁。

意向锁将锁定的对象分为了 多个层次，库->表->页->行。假设要给行记录r上X Lock，需要先对库、表和页上Intention X Lock(IX Lock)，最后再给第r行记录上锁。若这一过程中任何一个部分导致等待，都需要等待粗粒度的锁先上锁完成。

InnoDB中意向锁的设计较为简单，只是表级别的锁，

  • 意向共享锁(IS Lock)：事务想获取表中某几行数据的共享锁，需先获取该表的意向共享锁
  • 意向排它锁(IX Lock)：事务想获取表中某几行数据的排它锁，需要先获取该表的意向排它锁
-IS LockIX LockS LockX Lock
IS Lock(表级)兼容兼容兼容不兼容
IX Lock(表级)兼容兼容不兼容不兼容
S Lock(行级)兼容不兼容不兼容不兼容
X Lock(行级)不兼容不兼容不兼容不兼容

锁问题

锁机制实现事务的隔离性要求，提高了并发，但是也带来了潜在问题。

1）脏读

首先对脏数据和脏页进行区分，

  • 脏页：缓冲池中已经被修改的页（页中修改的内容是事务已提交的内容），但是没有被刷入磁盘，即数据库内存中的页和磁盘中的页版本不一致。由数据库实例在内存和磁盘之间的异步造成，不会影响数据的一致性，因为脏页始终会被刷入磁盘。
  • 脏数据：一个事务读取到另一个事务未提交的数据，违反了数据库事物间的隔离性。

脏读的发生条件是数据库的隔离级别为read uncommitted，InnoDB默认的事务隔离级别是RR级别，所以一般不会出现脏读的情况。除非人为设置隔离级别为 read uncommitted。

2）幻读（不可重复读）

一个事务内多次读取同一数据集，但是相同的DQL操作前后两次读取到不同的数据。造成这种情况的原因是在两次DQL执行期间，另外一个事务在记录间隙处添加了符合查询条件的新纪录。（下方RC部分会有演示）

InnoDB通过使用next-key lock算法避免这一问题。（下方同样有讲解和演示）

3）丢失更新

一个事务的更新操作被另一个事务的更新操作覆盖，导致数据不一致。

但是理论上即使是在read uncommitted级别下，也不会发生两个事务同时对一条数据进行更新导致丢失更新（因为排它锁的存在及其不兼容性）。但是，有一种情况是开发中常见的，
在这里插入图片描述
两个事务并不是同时更新一条记录，而是按顺序更新同一条记录，互不冲突，满足事务间的隔离性。这种情况可能引发很严重的问题，如，
在这里插入图片描述
避免更新丢失的方法是让事务在这种情况下串行化。下面的流程演示了避免逻辑上丢失更新的方法，
在这里插入图片描述

InnoDB行锁算法

InnoDB有3种行锁算法，

  • record lock：单个行记录的锁
  • gap lock：间隙锁，锁定一个范围，但不包含记录本身
  • next-key lock：record lock + gap lock，锁定一个范围，同时锁定记录本身。是InnoDB的默认锁定算法，这种算法可以避免幻读

明确next-key lock的锁定范围，假设存在索引有10、13和20，则索引可能被上锁的区间有 ( − ∞ , 10 ] , ( 10 , 13 ] , ( 13 , 20 ] (-∞, 10], (10, 13], (13, 20] (−∞,10],(10,13],(13,20]和 ( 20 , + ∞ ] (20, +∞] (20,+∞]。

相对还有previous-key lock，同样是上述索引，previous-key lock上锁的区间有 [ − ∞ , 10 ) , [ 10 , 13 ) , [ 13 , 20 ) [-∞, 10), [10, 13), [13, 20) [−∞,10),[10,13),[13,20)和 [ 20 , + ∞ ) [20, +∞) [20,+∞)。

当索引为唯一索引时，InnoDB自动将next-key lock降级为 record lock。

阻塞

因为不同锁之间存在兼容性关系，所以有时一个事务需要等待另一个事务释放锁才可执行，这种等待就是阻塞。

InnoDB中使用参数innodb_wait_timeout控制等待时间（默认是50秒），用innodb_rollback_on_timeout来设定是否在超时时对事务进行回滚（默认是OFF，即不回滚）。

MySQL超时会报出 1205的错误，默认情况下，InnoDB不会回滚超时的事务对数据进行的修改，演示如下，
在这里插入图片描述
会话A开启的事务通过next-key lock锁定了≤4的所有的记录。另一个会话B中开启另一个事务，如下
在这里插入图片描述
会话B成功插入不在锁定区间内的记录5，但是在插入3时，因为记录3在会话A锁定的区域范文内，所以发生了阻塞并最终报出timeout的异常。

如果再对表中数据进行select操作，得到的结果如下，
在这里插入图片描述
会话B虽然抛出了异常，且没有进行rollback和commit操作，但是数据库中数据确实被修改。

在开发过程中，一定要对阻塞的这一特性进行考虑。决定是否有必要进行回滚。

RC级别下行锁实验

创建一个表格用于演示，

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_a (a) USING BTREE,
  KEY idx_c (c)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t(a,b,c) values (1,1,1),(2,2,2),(3,3,3),(4,4,3);

1）通过非索引字段查询

session1session2
set session transaction_isolation=“READ-COMMITRED”set session transaction_isolation=“READ-COMMITRED”
begin;
select * from t where b=1 for update; (正常返回结果)
-select * from t where b=2 for update; （阻塞）
commit;上方SQL语句在session1提交后立刻执行

P.S. 显式使用 for update的原因

通常使用的select语句使用的是快照读，不会看到别的食物插入的数据。加了 for update后，查询变为当前读。插入、删除和更新都属于当前读，也就是说此时select与其他DML操作一同争锁

image
b字段因为没有索引，所以只能走聚簇索引进行全表扫描。此时虽然只有一行数据满足条件，但是依旧是对全表上锁。故当在非索引字段查询时，RC级别的行锁变为表锁。

2）通过唯一索引查询

session1session2
set session transaction_isolation=“READ-COMMITRED”set session transaction_isolation=“READ-COMMITRED”
begin;
select * from t where a=1 for update; (正常返回结果)
-select * from t where a=2 for update; （正常返回结果）
-select * from t where a=1 for update; (阻塞)
commit;session1提交后，上方SQL立即执行

image
session1获取了辅助索引a=1处的X Lock锁，同时获取了相应主键id=1上的锁。不影响session2获取a=2及主键id=2的X Lock，但是会影响session2获取a=1上的X Lock。

如果查询的条件是唯一索引，那么 SQL 需要在满足条件的唯一索引上加锁，并且会在对应的聚簇索引上加锁。

3）通过普通索引查询

session1session2session3
set session transaction_isolation=“READ-COMMITRED”set session transaction_isolation=“READ-COMMITRED”set session transaction_isolation=“READ-COMMITRED”
begin;
select * from t where c=3 for update; (正常返回结果)
-select * from t where a=1 for update; (正常返回结果)select * from t where a=2 for update; (正常返回结果)
-select * from t where a=3 for update; (阻塞)select * from t where a=4 for update; (阻塞)
commit;session1提交后，上方SQL立刻执行session1提交后，上方SQL立刻执行

image
session1获取了辅助索引c=3处的X Lock锁，同时获取了相应主键id=3上的锁。不影响session2获取a=1及主键id=1的X Lock，也不影响session3获取a=2及主键id=2的X Lock。但是会影响session2获取a=3和session3获取a=4上的X Lock。

如果查询的条件是非唯一索引，那么 SQL 需要在满足条件的非唯一索引上都加上锁，并且会在它们对应的聚簇索引上加锁。

RC级别下记录间间隙(gap)

session1session2session3
set session transaction_isolation=“READ-COMMITRED”set session transaction_isolation=“READ-COMMITRED”
begin;begin;
-select * from t where c=3 for update; （正常返回结果）image
insert into t(a, b, c) values (5, 5, 3); （语句正常执行）
commit;
-select * from t where c=3; (语句正常执行)image
-commit;

这就是RC级别下出现幻读的情况。

在 session2 中，同一个事务中，按相同的查询条件重新读取以前检索过的数据，却发现了 session1 插入的满足查询条件的新数据。
image
从图中可以看出，RC 隔离级别下，只锁住了满足 c=3 的当前行，而不会对后面的位置（或者说间隙）加锁，因此导致 session1 的写入语句能正常执行并提交。

如何避免幻读？

从上面的分析可以知道，产生幻读的原因是：行锁只能锁住当前行，但是新插入的记录在被锁住记录之前的间隙。因此，为了解决幻读问题，InnoDB 在 RR 隔离级别下配置了间隙锁（Gap Lock）。

RR级别下行锁实验

创建新表格用于测试，

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_a (a) USING BTREE,
  KEY idx_c (c)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t17(id,a,b,c) values (1,1,1,1),(2,2,2,2),(4,4,4,4),(6,6,6,4);

1）通过非索引字段查询

session1session2session3
set session transaction_isolation=“REPEATABLE-READ”;set session transaction_isolation=“REPEATABLE-READ”;set session transaction_isolation=“REPEATABLE-READ”;
select * from t where b=1 for update; （正常返回结果）
-select * from t where b=2 for update; （阻塞）insert into t(a, b, c) values(10, 10, 10); （阻塞）
commit;session1提交后立刻执行session1提交后立刻执行

如果是在RC隔离级别下，session3的语句是不会受到影响的，因为RC级别下非索引字段只锁住了表中所有的记录。但是不会锁住记录之间的间隙。

RR 隔离级别下，非索引字段做条件的当前读不但会把每条记录都加上 X 锁，还会把每个 GAP 加上 GAP 锁。所以此时session3的语句会阻塞。

2）通过唯一索引查询

GAP 锁的目的是：为了防止同一事务两次当前读，出现幻读的情况。如果能确保索引字段唯一，等值查询最多就返回一条记录，而且相同索引记录的值，一定不会再新增，因此不会出现 GAP 锁。

因此以唯一索引为条件的当前读，不会有 GAP 锁。所以 RR 隔离级别下的唯一索引当前读加锁情况与 RC 隔离级别下的唯一索引当前读加锁情况一致。

3）通过普通索引查询

session1session2
set session transaction_isolation=“REPEATABLE-READ”;set session transaction_isolation=“REPEATABLE-READ”;
begin;begin;
-select * from t where c=4; （正常返回结果）image
insert into t(a, b, c) values(7, 7, 4); （阻塞）
-select * from t where c=4; （正常返回结果）image
session2提交后，阻塞语句立刻执行commit;
commit;

image
session1试图插入c=4的记录被阻塞的原因分析，

session2获取了c=4的记录，在辅助索引中对c=4的记录上锁。同时在聚簇索引中，对id=4和id=6的行记录及其间隙上锁

如果不对间隙上锁时，允许插入的c=4的值如下，

  • 因为 B+ 树索引是有序的，因此 [2,2]（分别代表 c 和 id 的值）前面，不可能插入 c=4 的记录了；
  • [2,2] 与 [4,4] 之间可以插入 [4,3]；
  • [4,4] 与 [4,6] 之间可以插入 [4,5]；
  • [4,6] 之后，可以插入的值就很多记录：[4,n](其中 n>6) ；

（其实上面的分析中，对主键的考虑有点多余，只要保证主键按照表格创建时的定义不重复即可）

为了保证这几个区间不会插入新的满足条件 c=4 的记录，MySQL RR 隔离级别选择了 GAP 锁，将这几个区间锁起来。

上面实验中，语句 insert into t (a,b,c) values (7,7,4); 其对应插入 c 和 id 的值为 [4,7]，是在最后这个被 GAP Lock 锁住的区间，因此如上面实验，insert 操作会等待。

总结RC与RR级别行记录上锁

隔离界别非索引字段唯一索引字段普通索引字段
RC级别对所有的记录加锁在满足条件的索引上加锁，并且会在聚簇索引相应的行记录上加锁同唯一索引
RR级别不但将全部记录上锁，同时将记录前后的Gap也上锁同RC级别，因为索引唯一，所以不需要在Gap上加锁在满足条件的索引上加锁，并且会在聚簇索引相应的行记录上加锁。同时在聚簇索引主键的Gap处加锁

RC级别下，行锁只能锁住一行的数据，如果再行前插入满足条件的记录，则第二次读取就会出现幻读。

练习

创建一个表，

CREATE TABLE t (
  id int(11) NOT NULL AUTO_INCREMENT,
  a int(11) NOT NULL,
  b int(11) NOT NULL,
  c int(11) NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_a (a) USING BTREE,
  KEY idx_c (c)
) ENGINE=InnoDB  CHARSET=utf8mb4;

insert into t(id,a,b,c) values (1,1,1,1),(3,4,2,5),(5,3,4,3),(7,7,7,7);

select * from t;

表中记录如下，
image
进行如下操作，

session1session2session3
set session transaction_isolation=“REPEATABLE-READ”;set session transaction_isolation=“REPEATABLE-READ”;set session transaction_isolation=“REPEATABLE-READ”;
begin;
select * from t where c=5 for update;
-insert into t values(2, 2, 2, 2);insert into t values(6, 6, 6, 6);
commit;

session2和session3的插入语句在session1提交之前哪个可以成功插入？
session2能够成功插入，而session3的插入会阻塞，具体分析如下，

表中字段c和id的对应的索引树如下，

cid
11
35
53
77
  • session1获取了c=5的行记录，对应的c和id的组合为[5, 3]
  • 同时锁住的还有c字段值[3, 5]和[5, 7]直接的间隙
  • session2插入的记录(2,2,2,2)中，c字段值为2，不在锁定区域中，且id=2能够满足主键不重复，所以session2能够正常插入
  • session3插入记录(6,6,6,6)中，尽管id=6满足主键不重复，但是c字段值为6，该值在锁定范围内，所以session3阻塞
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值