MySQL表锁和行锁

掘金白
727 阅读6分钟

我们都知道,数据库有事务隔离级别,那么数据库是如何保证事务之间的相互隔离的呢?这就需要提到数据库的锁了,一般来说,数据库锁通过粒度大小划分可分为行锁和表锁。

MySQL表锁和行锁

行锁

顾名思义，行锁就是一锁锁一行或者多行记录，mysql的行锁是基于索引加载的，所以行锁是要加在索引响应的行上，即命中索引，如下图所示：

image-20210204204247089

如上图所示，数据库表中有一个主键索引和一个普通索引，Sql语句基于索引查询，假如查询age为13的数据,命中两条记录。此时行锁一锁就锁定两条记录，当其他事务访问数据库同一张表时，被锁定的记录不能被访问，其他的记录都可以访问到。

行锁的特征：锁冲突概率低，并发性高，但是会有死锁的情况出现。

为了验证该说法,我们通过实际的演示来做试验,数据库就使用上面的数据库:

  1. 设置手动提交事务

    在数据库中执行以下命令

    set autocommit = 0;

  2. 对一条数据执行更新操作,注意:这个时候不要提交

    update a set name="李四" where id=1;

    image-20210204210917706

  3. 再开一个新的窗口,再执行一次上面的语句

    image-20210204210927812

  4. 接着我们再提交之前的,就会发现第二个也执行了,只需要提交一下

    image-20210204211218148

但这也只是测试了锁住了一条记录,为了测试是只锁住了一行记录,我们还需要再测试一下修改其他的数据

  1. 我们修改第二个窗口为自动提交事务

    set autocommit = 1;

  2. 还是执行上面的2的语句

  3. 第二个窗口执行新的语句

    update a set name="哈哈哈" where id=2;

    image-20210204211809651

可以发现,确实是锁住了那一行记录

其实行锁也有很多分类,我们介绍一下他们:

记录锁

上面我们找到行锁是命中索引，一锁锁的是一张表的一条记录或者是多条记录，记录锁是在行锁上衍生的锁，我们来看看你记录锁的特征:

记录锁：记录锁锁的是表中的某一条记录，记录锁的出现条件必须是精准命中索引并且索引是唯一索引，如主键id，就像我们上面描述行锁时使用的sql语句图，在这里就挺适用的。

间隙锁

间隙锁又称之为区间锁，每次锁定都是锁定一个区间，隶属行锁。既然间隙锁隶属行锁，那么，间隙锁的触发条件必然是命中索引的，当我们查询数据用范围查询而不是相等条件查询时，查询条件命中索引，并且没有查询到符合条件的记录，此时就会将查询条件中的范围数据进行锁定(即使是范围库中不存在的数据也会被锁定)

这么讲可能比较抽象,我们通过几个例子来说明什么是间隙锁

我们现在创建一张新的表,表的结构如下

image-20210204224105668

其中,id为主键,number加了索引,我们会发现,这id并不饱和,内部是有空缺的,这就会产生一个问题,假设我们查询一下所有的总条数,然后我们在查询时有一个事务添加了新的记录,我们再次查询就会发现总条数变多了,这就是幻读,我们都知道,如果想解决这个问题,可以将事务隔离级别设置为可重复读,那么间隙锁就是必须在可重复读下使用的,可以解决幻读

  1. 在1号窗口中设置手动提交事务并写下sql语句并运行

    注意:for update不可少
select * from b where number=2 for update

  2. 在2号窗口写下插入的sql语句并执行

    insert into b values(2,1,NULL);
insert into b values(4,1,NULL);

    image-20210204224954172

  3. 在2号窗口再写新的插入的sql语句并执行

    insert into b values(4,11,NULL);

    image-20210204225129550

你可能会很奇怪,为什么插入(4,11)可以但(4,1)不行,要明白这点我们需要把数据库的number改为升序

image-20210204225306535

间隔锁对于间隔的判定是这样的,我选择条件是number为2,所以间隔的区间就是2向前和向后找最近的也就是区间为number=1~3,假如插入的是(2,1),在(3,1)前,可以插入成功,但如果是(4,2),就会导致插入点在(3,1)和(6,2)之间,刚好在间隔锁的范围内,但如果是(4,11)就没有这个问题

前文说过,间隔锁必须在可重复读及以上情况下使用,我们如果把隔离级别改为读已提交,它就无效了,我们测试一下

  1. 修改隔离级别

    set session transaction isolation level read committed;

  2. 执行上面被阻塞的语句

    image-20210204230320181

但假如执行的语句是更新,那么间隔锁的范围就是左开右闭,比如说number范围为1~3,你在更新时number为1可以不阻塞更新,但是2和3就需要阻塞

临键锁

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

通俗的讲,上文的间隙锁锁的只是间隙,你可以修改已经存在的值,但临建锁会把已经存在的值也锁住,你无法修改

注：临键锁的主要目的，也是为了避免幻读(Phantom Read)。如果把事务的隔离级别降级为RC，临键锁则也会失效。

表锁

顾名思义，表锁就是一锁锁一整张表，在表被锁定期间，其他事务不能对该表进行操作，必须等当前表的锁被释放后才能进行操作。表锁响应的是非索引字段，即全表扫描，全表扫描时锁定整张表，sql语句可以通过执行计划看出扫描了多少条记录。

image-20210204204247089

还是这张图,假设我们这次使用的条件是age也就是非索引字段,那么就会走表锁,锁住全表,也就是说一次只有一个事务可以对表进行操作,我们实验一下:

  1. 写一个通过age更新的语句

    update a set name="哈哈哈" where age=50;

  2. 在第二个窗口中也写一个通过age更新的语句

    update a set name="哈哈哈2" where age=20;

  3. 运行这两条语句

    image-20210204213004727

    image-20210204213023585

确实是锁住了表

avatar
文章
阅读
粉丝