本文通过实验的角度,分析MySQL事务中UPDATE语句是行锁还是表锁

有如下结构:

CREATE TABLE `fund_sum` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `fund_code` varchar(10) COLLATE utf8mb4_bin NOT NULL COMMENT '基金代码',
  `fund_name` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '基金名称',
  `buy_share` decimal(18,4) DEFAULT NULL COMMENT '期间申购(亿份)',
  `sell_share` decimal(18,4) DEFAULT NULL COMMENT '期间赎回(亿份)',
  `total_share` decimal(18,4) DEFAULT NULL COMMENT '期末总份额(亿份)',
  `stock_amount` decimal(18,4) DEFAULT NULL COMMENT '期末净资产(亿元)',
  `season` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '季度',
  `uuid` varchar(40) COLLATE utf8mb4_bin NOT NULL COMMENT 'UUID',
  `remark` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
  `created_time` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `modified_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `yn` tinyint(4) DEFAULT '1' COMMENT '是否有效,1,有效,0,无效',
  PRIMARY KEY (`id`),
  UNIQUE KEY `UNIQ_UUID` (`uuid`),
  KEY `IDX_FUND_CODE` (`fund_code`)
) ENGINE=InnoDB AUTO_INCREMENT=152326 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

其中id为主键索引,uuid为唯一索引,fund_code为非唯一索引,remark没有索引。 我们用从上到下表示时间,是否缩进表示不同的客户端,其中客户端1开启了事务

# 客户端1:
SELECT @@tx_isolation;
BEGIN;
UPDATE fund_sum SET fund_code = '000198x' WHERE fund_code = '000198'; # 成功


		# 客户端2:
		SELECT @@tx_isolation;

		UPDATE fund_sum SET fund_code = '000198x' WHERE fund_code = '000198';  # 锁住
		UPDATE fund_sum SET fund_code = '000198xx' WHERE fund_code = '000198x'; # 锁住
		UPDATE fund_sum SET fund_code = '519888x' WHERE fund_code = '519888'; # 成功
		UPDATE fund_sum SET fund_code = '519888xx' WHERE fund_code = '519888x'; # 成功,说明非唯一索引锁行

		UPDATE fund_sum SET remark = 'remark1' WHERE UUID='59b470ed-7b22-11ea-b181-88d7f6c41fe1'; # 锁住,对应fund_code为000198
		UPDATE fund_sum SET remark = 'remark2' WHERE UUID='59b474de-7b22-11ea-b181-88d7f6c41fe1'; # 成功

UPDATE fund_sum SET remark = 'remark3' WHERE UUID='59b474de-7b22-11ea-b181-88d7f6c41fe1'; # 成功

		UPDATE fund_sum SET remark = 'remark4' WHERE UUID='59b474de-7b22-11ea-b181-88d7f6c41fe1'; # 锁住
		UPDATE fund_sum SET remark = 'remark5' WHERE UUID='59b47529-7b22-11ea-b181-88d7f6c41fe1'; # 成功,说明唯一索引锁行

UPDATE fund_sum SET remark = 'update by id 1' WHERE id = 5; # 成功
		
		UPDATE fund_sum SET remark = 'update by id 2' WHERE id = 5; # 锁住
		UPDATE fund_sum SET remark = 'update by id 3' WHERE id = 6; # 成功,说明主键索引锁行



UPDATE fund_sum SET remark = 'update by remark 1' WHERE remark = '1'; # 成功

		UPDATE fund_sum SET remark = 'update by remark 2' WHERE remark = '1'; # 锁住
		UPDATE fund_sum SET remark = 'update by remark 3' WHERE remark = '2'; # 锁住,说明UPDATE字段上如果没有索引,锁表
		
COMMIT;

根据上述验证,我们知道只要有索引,包括主键索引、唯一索引,非唯一索引都是锁行,如果没有索引则是锁表。 需要特别注意的是,有一种情况,行锁会升级为表锁,例如我们将id>=5并且fund_code!='000198x'的记录删除后,表里剩余29行数据,其中fund_code='000198x'的有26行,这时就会出现以下情况:

# 客户端1:
BEGIN;
UPDATE fund_sum SET remark = '123' WHERE fund_code = '000198x'; # 成功

		# 客户端2
		UPDATE fund_sum SET remark = '123' WHERE fund_code = '000198x'; # 锁住
		UPDATE fund_sum SET remark = '123' WHERE fund_code = '482002'; # 锁住,说明此时是表锁,与非唯一索引使用行锁的结论冲突

COMMIT;

查阅相关资料:

  • 行锁是建立在索引的基础上。
  • 普通索引的数据重复率过高导致索引失效,行锁升级为表锁

参考:https://blog.csdn.net/qq_35241080/article/details/88412133