我们相信:世界是美好的,你是我也是。平行空间的世界里面,不同版本的生活也在继续...

本文接着描述mysql悲观锁中的select ... for update语句,通过上文的描述,可以知道:通常来说,select...for update锁定的是行,但是有的时候锁定的是表。锁定是表的时候,就比较麻烦了,影响面会比较大。那么,什么时候会锁行,什么时候会锁表呢?这就是本文要谈论的问题。

苏南大叔:mysql悲观锁,select for update锁表类型与索引的关系 - 锁表类型与索引关系
mysql悲观锁,select for update锁表类型与索引的关系(图1-1)

苏南大叔的“程序如此灵动”博客,记录苏南大叔的编程经验文章。测试环境:win10mysql@5.7.26mysql-front@5.3

前文回顾

mysqlselect ... for update语句必须配合事务使用:

mysqlselect ... for update,通常来说会产生行锁。但是使用不当的情况下,会产生表锁。

事实证明,如果命中了索引,并且有数据被查出来,就产生的是行锁。关于mysql索引的使用:

龙套数据表

依然引入龙套数据表,增加了一个新的对比字段age

DROP TABLE IF EXISTS `innodb`;
CREATE TABLE `innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `age` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `innodb` VALUES (1,'苏南大叔',18),(2,'sunan大叔',20);

查看一下索引情况:

show keys from innodb;

可以看到:有个主键id,还有个普通索引index_name,字段age上面没有任何索引。

例1,命中索引就是行锁

线程一,就是负责上锁的,通过线程二来查看效果。
线程一执行语句:

begin;
explain select * from innodb where name='苏南大叔' for update;
select * from innodb where name='苏南大叔' for update;

线程二执行:

update innodb set age=20 where id=1;
update innodb set age=22 where id=2;

第一条语句更新id=2顺利执行,而更新id=1被卡住,证明是线程一加的是行锁。

例2,where中有没有索引的字段也是行锁

线程一,就是负责上锁的,通过线程二来查看效果。这个例子中,where中有一个字段是有索引的,另外一个没有索引。
线程一执行语句:

begin;
explain select * from innodb where age=18 and name='苏南大叔' for update;
select * from innodb where age=18 and name='苏南大叔' for update;

线程二执行:

update innodb set age=20 where id=1;
update innodb set age=22 where id=2;

第一条语句更新id=2顺利执行,而更新id=1被卡住,证明是线程一加的是行锁。

例3,没有索引但有数据就是表锁

线程一,就是负责上锁的,通过线程二来查看效果。这个例子中,whereage字段没有索引。
线程一执行语句:

begin;
explain select * from innodb where age=18 for update;
select * from innodb where age=18 for update;

线程二执行:

update innodb set age=20 where id=1;
update innodb set age=22 where id=2;

两条语句都是被卡住,证明是线程一加的是表锁。

例4,有索引但没有数据无锁

线程一,就是负责上锁的,通过线程二来查看效果。这个例子中,虽然命中id主键索引,但是没有符合条件的数据。这种情况下不会产生任何类型的锁。

线程一执行语句:

begin;
explain select * from innodb where id=5 for update;
select * from innodb where id=5 for update;

线程二执行:

update innodb set age=20 where id=1;
update innodb set age=22 where id=2;

两条语句都没有被卡住,证明没有锁。

表格总结

where所用到索引查的到数据
id行锁
name行锁
age表锁
id+age行锁
id没锁

当然,这里不排除有其它特殊情况,本文仅供参考,还是要根据实际的测试情况来进行分析。总的原则就是,尽量使用索引,尽量不产生表锁,使用行锁最安全可靠。

结语

加油,一切都会好起来的。苏南大叔的更多mysql经验文章,请点击:

如果本文对您有帮助,或者节约了您的时间,欢迎打赏瓶饮料,建立下友谊关系。
本博客不欢迎:各种镜像采集行为。请尊重原创文章内容,转载请保留作者链接。

 【福利】 腾讯云最新爆款活动!1核2G云服务器首年50元!

 【源码】本文代码片段及相关软件,请点此获取更多信息

 【绝密】秘籍文章入口,仅传授于有缘之人   mysql