sql where中用case_「实战实操」SQL模拟场景让你彻底搞懂MySQL的锁机制（中篇）

本文是《彻底搞懂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的情况。

---

如果觉得本文对您有用，欢迎点赞和转发。

呦呦鹿鸣，食野之苹，也欢迎大家关注我，我会坚持不断输出有价值的技术文章。