之前经常听说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给表加的是表锁;提交事务(回滚事务)会释放锁。