加锁测试

0、准备

参考：MySQL 加锁处理分析

记得先打开innodb monitor中的锁详细信息输出。

set GLOBAL innodb_status_output=ON;
set GLOBAL innodb_status_output_locks=ON;

　　

一、表结构及原始数据

DROP TABLE IF EXISTS test_lock;
CREATE TABLE `test_lock` (
`id` int(8) unsigned NOT NULL AUTO_INCREMENT,
`key_uniq` varchar(100) NOT NULL DEFAULT '',
`name_index` varchar(100) NOT NULL DEFAULT '',
`code_index` varchar(100) NOT NULL DEFAULT '',
`no_index` varchar(100) NOT NULL DEFAULT '',

PRIMARY KEY (`id`),
UNIQUE KEY `uniq_idx_key_uniq` (`key_uniq`),
KEY `idx_code` (`code_index`), KEY `idx_name` (`name_index`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO test_lock (key_uniq, name_index, code_index, no_index ) VALUES ("uniq3", 'name3', 'code3', 'no5'),("uniq5", 'name1', 'code2', 'no3'),("uniq1", 'name5', 'code1', 'no4'),("uniq4", 'name2', 'code3', 'no1'),("uniq2", 'name4', 'code4', 'no2');

　　

 

　

1	uniq3	name3	code3	no5
2	uniq5	name1	code2	no3
3	uniq1	name5	code1	no4
4	uniq4	name2	code3	no1
5	uniq2	name4	code4	no2

　

二、常用SQL

2.1 查看innodb锁信息

SHOW ENGINE INNODB STATUS \G

　　

2.1 查看当前事务ID

select trx_id  from information_schema.INNODB_TRX where trx_mysql_thread_id = CONNECTION_ID();

　　

三、update/delete 加锁

3.1 主键

这里只讨论聚簇索引的加锁。

3.1.1 point

行锁（lock_mode X locks rec but not gap）

3.1.2 不存在记录

大于该记录的有效记录加gap锁（lock_mode X locks gap before rec）。

3.1.3 range

范围内的每个有效记录加NK锁，最大的有效记录的下个记录也加NK锁。

 

3.2 唯一索引

这里只讨论唯一索引的加锁。

3.2.1 point

行锁（lock_mode X locks rec but not gap）

3.2.2 不存在记录

大于该记录的有效记录加gap锁（lock_mode X locks gap before rec）。

3.2.3 range

范围内的每个有效记录加NK锁，最大的有效记录的下个记录也加NK锁。

 

3.3 普通二级索引

这里只讨论普通二级索引的加锁。

一个索引可能对应多条记录。这里将每个记录称为索引子记录。

3.3.1 point

命中的所有索引子记录+NK锁，命中索引的下一个索引加gap锁(lock_mode X locks gap before rec)

相当于，命中索引加NK锁，下一个索引加gap锁。

3.3.2 不存在记录

大于该记录的有效记录加gap锁（lock_mode X locks gap before rec）。

3.3.3 range

范围内的每个有效子记录加NK锁，最大的有效记录的下个记录也加NK锁。

 

3.4 无索引

这里只讨论聚簇索引的加锁。

3.4.1 point

所有记录加NK锁，即全表锁。

3.4.2 不存在记录

所有记录加NK锁，即全表锁。

3.4.3 range

所有记录加NK锁，即全表锁。

 

3.5 总结

类型		唯一索引	非唯一索引	无索引
等值查询	存在	行锁	NK+Gap（以及主键的记录锁）	全表锁
	不存在	Gap	Gap
范围查询		NK 左边有等值存在的退化为行锁，如>=； 右边无等值存在的退化为Gap，如<。	NK（不会退化）