3. InnoDB行锁案例分析
行锁介绍
每次操作锁住一行数据。开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度最高。
InnoDB与MYISAM的最大不同有两点:
InnoDB支持事务(TRANSACTION)
InnoDB支持行级锁
行锁演示
一个session开启事务更新不提交,另一个session更新同一条记录会阻塞,更新不同记录不会阻塞
总结:
MyISAM在执行查询语句SELECT前,会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自
动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别),不会加锁。但是update、insert、delete操作会加行
锁。
简而言之,就是读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞。
行锁演示
参考博客:MySQL行级锁效果演示
创建表
CREATE TABLE mylock_innodb ( id INT PRIMARY KEY, NAME VARCHAR ( 10 ) ) ENGINE = INNODB; insert into mylock_innodb values(1,'a'); insert into mylock_innodb values(2,'b'); insert into mylock_innodb values(3,'c');
演示一
1、打开一个会话窗口1,开启了一个事务,并执行更新操作,然后不对事务进行提交。
-- 开启事务 start transaction; -- 更新id为1的数据 update mylock_innodb set name = 'a1' where id = 1;
2、此时我再重新打开一个窗口2,还是开启一个事务,并对id为2的这一行执行更新操作,你会发现返回执行成功了。
-- 开启事务 start transaction; -- 更新id为2的数据 update mylock_innodb set name = 'b2' where id = 2;
输出为:
[SQL]-- 开启事务 start transaction; 受影响的行: 0 时间: 0.001s [SQL] -- 更新id为1的数据 update mylock_innodb set name = 'b2' where id = 2; 受影响的行: 1 时间: 0.001s
3、当然如果你在窗口2,也执行更新id为1的这一行数据,那就会一直阻塞在那。
-- 更新id为1的数据 update mylock_innodb set name = 'a1_newsession' where id = 1;
输出为:
4、直到窗口1,对事务进行提交或者回滚后,窗口2才会返回。
-- 执行COMMIT; 或 ROLLBACK; COMMIT;
但如果等待时间过长,窗口二会提示如下错误:
演示二
1、打开一个会话窗口1,开启了一个事务,并执行如下操作:
-- 开启事务 start transaction; -- 执行查询 select * from mylock_innodb where id = 1 for update; -- 不提交事务
输出为:
2、再重新打开一个窗口2,还是开启一个事务,并对id为2的这一行执行操作:
-- 开启事务 start transaction; -- 执行查询 窗口2 select * from mylock_innodb where id = 2 for update; -- 不提交事务
输出为:
执行成功了,这是因为id不同,相互操作不影响。
然后提交或回滚事务,避免影响接下来的操作。
演示三
1、打开窗口1,开启事务,执行如下操作,查询条件为name
-- 开启事务 start transaction; -- 执行查询 窗口1 select * from mylock_innodb where NAME = 'c' for update; -- 不提交事务
2、打开窗口2,开启事务,执行如下操作,查询条件为name
-- 开启事务 start transaction; -- 执行查询 窗口2 select * from mylock_innodb where NAME = 'a1' for update; -- 不提交事务
输出为:
这时,会发现窗口2阻塞了。
演示2和演示3的问题
通过id查询不同的行,不会受到影响,但通过name查询不同的行,为什么会互相影响呢?
原因在于 for update 是一种排他锁。又可以称写锁。若事务T对数据对象A加上X锁,事务T可以读A也可以修改A,其他事务不能再对A加任何锁,直到T释放A上的锁。可以自行演示,非常简单。
这个排他锁的问题就在于,当明确查询带索引时,就是行锁,如果查询不带索引时,就是表锁,所以才出现了为什么用id查询时不影响,用name查询时就会阻塞,所以可以给name加上了索引后,再去尝试就不会阻塞了,有兴趣的朋友可以自行尝试,这里就不演示了。
总结
Innod支持的行锁,在共享锁,排他锁时,要注意查询时是否是通过索引来查询,如果不是,则还是会锁表。