MySQL锁机制学习随笔
锁机制是什么？
锁是计算机协调多个进程或线程并发访问某一资源的机制。
在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需要用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。
锁分类：
- 乐观锁和悲观锁
从性能上分为乐观锁(用版本对比或CAS机制)和悲观锁，乐观锁适合读操作较多的场景，悲观锁适合写操作较多的场景，如果在写操作较多的场景使用乐观锁会导致比对次数过多，影响性能
乐观锁适合的场景：读多，对性能要求高的场景
悲观锁适合的场景：写多读少，对数据一致性要求高，某些资源只能被一个事务独占，悲观锁就可以有效避免冲突
-
从数据操作的粒度来分，表锁，页锁，行锁
-
从数据库的操作类型来分，分为读锁、写锁、意向锁
读锁：对于同一份数据，多个读操作不会相互影响
写锁：写操作时加上的锁，会阻断其他写锁和读锁，写写互斥，写读互斥，数据修改操作都会加写锁，select也可以加 for update强行上写锁
select * from T where id=1 for update
意向锁：是一种作用于表级锁的机制，主要目的是为了表明事务即将对表中的某些行加锁。
举个例子：
创建一张表：
CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10, 2)
);
- 事务A执行：
START TRANSACTION;
SELECT * FROM employee WHERE id = 1 FOR UPDATE;
SELECT * FROM employee WHERE id = 2 FOR UPDATE;
事务 A 在表 employee 上加了 意向排他锁（IX）。
同时，事务 A 分别对 id = 1 和 id = 2 的行加了 排他锁（X锁）。
- 事务B执行
事务 B 尝试对表 employee 加表级共享锁：
LOCK TABLES employee READ;
由于事务 A 的 IX 锁存在，事务 B 的共享锁申请会被阻塞。
- 事务 C 尝试对其他行加共享锁：
START TRANSACTION;
SELECT * FROM employee WHERE id = 3 LOCK IN SHARE MODE;
事务 C 的操作不会被阻塞，因为事务 C 只对 id = 3 加了共享锁（S锁），且意向锁机制允许行级共享锁和表级意向排他锁共存。
意向锁存在的意义
- 提升锁检查效率，当想给一个表加锁的时候，如果检测到这儿表有意向锁，就可以直接阻塞了
- 避免死锁的情况，事务之间的锁定冲突可以在表级别快速解决
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；一般用在整表数据迁移的场景。
基本操作
--建表SQL
CREATE TABLE `mylock` (
`id` INT (11) NOT NULL AUTO_INCREMENT,
`NAME` VARCHAR (20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE = MyISAM DEFAULT CHARSET = utf8;
--插入数据
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO`test`.`mylock` (`id`, `NAME`) VALUES ('4', 'd');
--手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
--查看表上加过的锁
show open tables;
--删除表锁
unlock tables;
页锁
只有BDB存储引擎支持页锁，页锁就是在页的粒度上进行锁定，锁定的数据资源比行锁要多，因为一个页中可以有多个行记录。当我们使用页锁的时候，会出现数据浪费的现象，但这样的浪费最多也就是一个页上的数据行。页锁的开销介于表锁和行锁之间，会出现死锁。锁定粒度介于表锁和行锁之间，并发度一般。
一般常用的是innoDB
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB相对于MYISAM的最大不同有两点：
- innoDB支持事务
- innoDB支持行级锁
行锁的优势是什么？
- 上锁的粒度更小，并发性能更高
- 避免不必要的锁冲突，只操作哪一部分行数据，就对哪一部分数据上锁
- 锁的粒度更小，操作的行更少，锁等待的概率就越小，锁等待的时间久越少
注意，InnoDB的行锁实际上是针对索引加的锁(在索引对应的索引项上做标记)，不是针对整个行记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁。(可重复读级别会升级为表锁，读已提交级别不会升级为表锁)
可重复读隔离级别下执行：
select * from account where name = 'lilei' for update; --where条件里的name字段无索引
则其它Session对该表任意一行记录做修改操作都会被阻塞住。
name行没索引，MySQL会全表扫描，导致行锁变成表锁
间隙锁
间隙锁，锁的就是两个值之间的空隙，间隙锁是在可重复读隔离级别下才会生效。
间隙锁是一种锁定索引记录之间的间隙（不包含记录本身）的机制，用于防止其他事务在该间隙内插入新的记录。
间隙锁的范围通常是 (a, b)，表示锁定索引 a 和 b 之间的所有空隙。如果没有明确的上限或下限，间隙锁会锁定从起点到无穷大的范围（如 (-∞, a) 或 (b, +∞)）
间隙锁解决了什么问题？
间隙锁主要是为了解决幻读的问题。幻读指的是，在同一个事务中，两次同样的查询，发现结果集的数据条数是不一样的。可能就是因为别的事务插入了新行，导致隔离性被破坏。
间隙锁就是锁住查询范围中的空隙，保证其他事务不会来插入新纪录。
例子
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
score INT
) ENGINE=InnoDB;
INSERT INTO students (id, name, score) VALUES
(1, 'Alice', 85),
(2, 'Bob', 90);
事务A启动：
-- 事务A启动
START TRANSACTION;
-- 查询分数大于80的学生
SELECT * FROM students WHERE score > 80;
事务B启动：
-- 事务B启动
START TRANSACTION;
-- 插入一条新记录
INSERT INTO students (id, name, score) VALUES (3, 'Charlie', 95);
-- 提交事务B
COMMIT;
再在事务A对新插入的数据进行更新，发现是可以更新的，接着再执行查询，就发生了幻读：
UPDATE students SET name = 'lili' WHERE score = 95
SELECT * FROM students WHERE score > 80;
如果想在可重复读隔离级别下完全避免幻读：
还是事务A：
-- 事务A
START TRANSACTION;
SELECT * FROM students WHERE score > 80 FOR UPDATE;
事务B再想去插入：
-- 事务B启动
START TRANSACTION;
-- 插入一条新记录
INSERT INTO students (id, name, score) VALUES (3, 'Charlie', 95);
-- 提交事务B
COMMIT;
上了间隙锁，无法完成插入操作
临键锁(Next-key Locks)
用于实现一致性读和防止幻读问题。它结合了行锁（record lock）和间隙锁（gap lock），锁定一条记录及其周围的间隙（范围），以确保事务的隔离性。
总结：
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。
另外，读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MYISAM高，甚至可能会更差。
锁等待分析
通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况
show status like 'innodb_row_lock%';
对各个状态量的说明如下：
Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间
Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg （等待平均时长）
Innodb_row_lock_waits （等待总次数）
Innodb_row_lock_time（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。
查看INFORMATION_SCHEMA系统库锁相关数据表
-- 查看事务
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁，8.0之后需要换成这张表performance_schema.data_locks
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待，8.0之后需要换成这张表performance_schema.data_lock_waits
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id
-- 查看锁等待详细信息
show engine innodb status;
死锁问题分析
--建表
CREATE TABLE `account` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`balance` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lilei', '450');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('hanmei', '16000');
INSERT INTO `test`.`account` (`name`, `balance`) VALUES ('lucy', '2400');
事务A执行：
select * from account where id=1 for update;
事务B执行：
select * from account where id=2 for update;
事务A执行：
select * from account where id=2 for update;
事务B执行：
select * from account where id=1 for update;
查看死锁日志：
show engine innodb status;
--事务A
START TRANSACTION;
select * from account where id=1 for update;
select * from account where id=2 for update;
COMMIT;
--事务B
START TRANSACTION;
select * from account where id=2 for update;
select * from account where id=1 for update;
COMMIT;
大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁，这种情况我们可以通过日志分析找到对应事务线程id，可以通过kill杀掉。
锁优化实践
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少检索条件范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
- 尽可能用低的事务隔离级别