MySQL中行锁真的只锁那一行吗？

MySQL中行锁真的只锁那一行吗？

我们经常会用到MySQL中的更新操作，或者是加锁读，但是有时候却会忽略锁的粒度而导致读写性能大大下降。由于锁的粒度并不会显式表现在我们写的SQL里，所以明白加锁的原理很重要。

锁的分类

MySQL中锁可以分为三类：

• 全局锁：给整个数据库加锁，此时整个数据库处于只读状态。典型应用场景是数据库备份，但是常用的InnoDB引擎支持事务，可以拿到一致性视图，不需要全局加锁，所以全局锁不常用。

• 表级锁：表锁可以通过lock tables … read/write显式使用，但是锁的粒度太大，尤其是对于支持行锁的InnoDB来说，也用不上。但是另一类表锁MDL（Metadata Lock）是在访问表时隐式加上的，当不涉及表结构的变更时（只是对数据增删改查），加MDL读锁；如果涉及表结构的变更（加字段、加索引等），则需要加MDL写锁。如果当前session拿到MDL写锁，则其他session拿不到MDL读锁都会被阻塞（即使只是select操作）。可能有人会提到MySQL新的online DDL，但是online DDL是先拿到MDL写锁再降级成读锁的，具体步骤如下：

  • 准备阶段拿到MDL写锁

  • 执行阶段降级成MDL读锁

  • 真正耗时的DDL相关操作

  • 升级成MDL写锁

  • 收尾工作

  • 释放MDL写锁

  第三步占据了绝大部分时间，但是这期间已经可以正常读写了。

• 行锁：针对数据表中行记录的锁，比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等事务A的操作完成后才能进行更新。行锁平常应用最多，内部又可以分为Record Locks 记录锁、Gap Locks 间隙锁、Next-Key Locks 锁，是个比较大的话题，具体介绍参见MySQL中的行锁。

行锁真的只锁那一行吗？

我们通常理解的行锁就是指Record Locks 记录锁，根据官网的信息MySQL：：MySQL 8.0 Reference Manual：：17.7.1 InnoDB Locking — MySQL :: MySQL 8.0 Reference Manual :: 17.7.1 InnoDB Locking可知：记录锁始终锁定索引记录。假如定义的表没有索引， InnoDB 使用主键索引进行加锁。由于没有索引的列会扫描全表，所有的记录都会加锁，这就相当于锁全表。

案例演示

CREATE TABLE `city` (
  `city_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`) REFERENCES `country` (`country_id`) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

如上所示，country_id和city_id上有索引。

索引列加锁读

此时我们另开一个会话加锁读不受影响：

非索引列加锁读

此时我们开另一个会话会发现毫不相干的行也会受到牵连：