本文是《彻底搞懂MySQL数据库锁机制》系列第3篇,是对数据库锁机制的总结。对数据库锁的了解,往往也是区分Java程序员中高级的一个重要标志。日常开发中,锁也是我们容易踩坑的地方。尤其当你的系统遇到高并发大访问量操作数据库时,稍有不慎可能造成线上事故。因此,掌握好锁机制的重要性不言而喻。
后面系列还会讲述死锁问题,还会抽丝剥缕地回放一个生产环境的死锁案例。欢迎关注我。
八、SQL脚本测试case九、特殊case
系列的前3篇文章主要侧重于理论讲解和经验总结,这篇文章从SQL实战角度对前面的功能点逐一验证和复现。
八、SQL脚本测试case
建表语句:
drop table if EXISTS innodb_row_lock_test;CREATE TABLE `innodb_row_lock_test` (`id` int(11) NOT NULL AUTO_INCREMENT comment '自增主键',`uniq_key` VARCHAR (100) DEFAULT null comment '唯一索引',`index_key` INTEGER default 0 comment '普通索引',`no_index_key` INTEGER DEFAULT 0 comment '没有索引',PRIMARY KEY (`id`),UNIQUE KEY `uniq_key`(`uniq_key`),key `idx_key`(`index_key`))ENGINE=InnoDB DEFAULT CHARSET=utf8;
1.select ... for update where id = 1 (id为自增加主键,存在该条记录)
该语句锁住id=1的索引,锁类型为RecordLock
步骤1begin;步骤2select * from innodb_row_lock_test where id=1 for update;步骤3update innodb_row_lock_test set no_index_key=123 where id=1;
步骤3执行,block后超时失败。
ps:可以使用show engine innodb status查看事务状态或者是使用select * from information.innodb_locks;查看锁状态(注意:这两条命令都只能查看当前被block住的锁)
innodb_locks 结果
lock_mode 锁模式(x,s或者gap) lock_index锁住的索引类型,lock_data,锁住的索引数据,lock_rec 锁住的行数(包括了undo页),锁住的页数量,锁住相同页可能不冲突。
插入数据结果正常。
2.select ... for update where uniq_key='1' (uniq_key为唯一索引,存在该条记录)
sessionA:
session:B:
按照该索引查询条件被锁住
innodb_lock结果lock_index变成uniq_key,
更改where 条件,where条件查询为主键,和根据uniq_key查询的行是同一行
innodb_lock结果,lock_index为primary
由此可见,当where条件数据存在的时候,锁住的索引除了筛选的索引外,还会锁住瞒住行的主键索引。事务中,一条执行语句可能存在多个锁,新事务会遍历除当前事务以外其他事务持有的锁。等待其它事务释放或者等待超时。
3.select ... for update where index_key='3' (index_key为普通索引,存在该条记录)
该语句会对index_key='1'的索引加上recordLock锁,以及小于index_key以及大于index_key的范围加上gap锁,以及id为4的主键索引加上recordLock。
如以下记录,执行select * from innodb_row_lock_test where index_key='3' for update,加锁为:
1. index_key 为3的recordLock,
2.id=4的recordLock,
3. index_key>='1' and index_key<6 的gap锁。
会话A:
会话B:更新索引为3的记录
locks结果:
会话C:插入索引值为1的记录
locks结果:
可以看到锁模式为x,Gap,组合其实就是nextKey锁。lock_data。3代表右驱为3),左驱目前无法看到,通过官网描述,测试得出gap范围为[1,3)
会话D:插入索引为5的记录
locks结果
会话E: 插入索引为6的记录,能够成功插入
4.select ... for update where no_index_key='1' (no_index_key非索引)
会话A:
会话B:
lock结果
supremum pseudo-record 上界虚记录。他不是一个真实的索引记录,可以看做是正无穷。意味着大于某一范围,小于该虚记录都被锁住。可以看做和上述所说的gap锁一样。
会话C:
lock结果
锁住结果为主键索引。
5.select ... for update where id = 1 (id为自增加主键,或者唯一键,或普通索引不存在该条记录)
如果记录不存在,如果有索引,则会加上小于该索引某一区间或者是大于某一区间的gap锁,如果不存在索引,并且不开启优化的情况下,则会对所有记录间隙均加上锁。
6.insert...into
如果是插入操作,则加上插入意向锁。
7.update...set yyy = where xxx=?? (xxx为,自增加主键,唯一索引,普通索引,无索引,yyyy为唯一索引,普通索引,无索引)
如果set后有索引,则对该索引加上recordLock。
九、SQL脚本特殊case
步骤1 begin;步骤2 select * from innodb_row_lock_test where uniq_key=1;步骤3 select * from innodb_row_lock_test where uniq_key=2 for update;
注意步骤3以及步骤2的记录均存在,并且uniq_key为唯一索引,uniq_key类型为字符串类型。
按照uniq_key为唯一索引,并且记录存在可以,步骤二非主键加锁情况为uniq_key的recordLock无gap锁,并且不会和步骤4发生锁等待的情况,但是事实上却被block住。
注意uniq_key的类型为字符串,但是步骤2条件的uniq_key传的是uniq_key=1,1为整型。这个涉及到类型强制转换,因此会扫描全表,会对所有的记录加上recordLock以及gap锁,表现和无索引一样。这只是用不到索引的一种case,当然还有其它用不了索引的情况,有or走不了索引或者or连接多个索引,或者是数据量占比较大的索引。可以自己explain测试下block的情况。
如果觉得本文对您有用,欢迎点赞和转发。
呦呦鹿鸣,食野之苹,也欢迎大家关注我,我会坚持不断输出有价值的技术文章。