InnoDB行锁与表锁、共享锁与排它锁

本文详细介绍了MySQL中InnoDB存储引擎的行锁与表锁,包括共享锁(S锁)和排它锁(X锁)。通过实际测试展示了在不同索引条件下,update操作如何在行级和表级应用锁。测试表明,当通过索引条件更新数据时,InnoDB使用行锁,否则使用表锁。提交或回滚事务会释放锁。
摘要由CSDN通过智能技术生成

之前经常听说mysql的行锁表锁,共享锁排它锁,间隙锁意向锁等等,但都只是大体有个了解,并没有实际的测试一下它们的作用与使用效果,接下来几遍记录一下关于mysql中关于锁的学习测试情况,感兴趣的可以持续关注,各位看到此博客的小伙伴,如有不对的地方请及时通过私信我或者评论此博客的方式指出,以免误人子弟。多谢!

共享锁与排它锁

前面就提到,InnoDB是支持事务和行级锁的,顾名思义,行锁就是在某一数据行上加锁,InnoDB 的行锁是通过给索引上的索引项加锁来实现的,并且只有通过索引条件进行数据检索,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁。

而共享锁和排它锁也是行锁的一种,那什么是共享锁和排它锁呢?

共享锁:又称为读锁,简称 S 锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改,并且阻止其他事务获得相同数据集的排他锁。

通常通过 select .......where ......lock in share mode给某一行加上读锁。

排他锁:又称为写锁,简称 X 锁,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的锁(共享锁、排他锁)。

通常通过select ...... where.....for update给某一行就上写锁,delete / update / insert 默认加上X锁。

当提交事务或者回滚事务时会释放锁。

测试行锁与表锁

上面提到,delete / update / insert 默认加上X锁,并且只有通过索引条件进行数据检索,InnoDB 才使用行级锁,否则,InnoDB 将使用表锁,下面就以update和索引来测试下InnoDB中的行锁与表锁的效果。

数据准备:

CREATE TABLE t_lock_test(
  id INT AUTO_INCREMENT PRIMARY KEY,
  NAME VARCHAR(36),
  age INT
);
INSERT INTO t_lock_test VALUES(NULL,'zhangsan',1),(NULL,'lisi',1);

测试一:

如上:在窗口1中开启事务并将id为1的age修改为10,没有提交事务,然后在窗口2中将id为2的age修改为20发现可以执行成功,说明id为2的记录没有被锁定,继续执行将id为1的age修改为20,此时发现处于阻塞状态,直到在窗口1中执行commit操作提交事务,窗口2中第四步的更改操作才成功,可以看到总共花费了9.75秒的时间。

在表中,id为主键,上面测试初步判断,InnoDB 通过索引条件进行数据检索时使用行级锁,没使用索引条件检索数据时使用的表锁。

测试二:

如上:在窗口1中开启事务并将name为张三的年龄修改为10,然后在窗口2中将id为2的age修改为20,发现会阻塞,当窗口1中commit后,窗口2中更改才执行成功。

效果同上。

在表中,name字段上并没有索引,在执行第二步时,同时把id为1和2的记录都锁住了,可以看出没使用索引为搜索条件时,InnoDB使用的是表锁。

测试三:

如上:给name字段添加索引,开启事务后,执行第三步,在窗口2中第四第五步的sql都可以正常执行,但是第六步阻塞了,直到在窗口1中执行提交后才执行成功。

ji

如上:发现窗口2中第三步也会阻塞,直到窗口1提交事务。

由以上三个测试结果可以得出:

InnoDB中,如果查询条件为索引,那么update给表加的是行锁;如果查询条件没有索引,那么update给表加的是表锁;提交事务(回滚事务)会释放锁。

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值