MySQL悲观锁，乐观锁，行锁，表锁，共享锁，排他锁，MDL锁，意向锁，间隙锁，next-key lock,死锁

1. 什么是MySQL锁？为什么要使用锁？

MySQL的锁和我们的日常生活中锁的作用是相同的。
我们在操作业务的时候肯定是希望看到正确的数据，而这里MySQL锁的作用就是为了保证数据的正确性。

2. 悲观锁和乐观锁

MySQL数据库的锁都是悲观锁。

• 悲观锁
  悲观锁就是一旦有线程获取到锁，其他线程就无法拿到锁，一般的响应就是直接报错。
  流程就是线程A对数据d1进行更改为d2，加悲观锁，线程B也操作
  d1更改为d3,在拿到数据的时候就发现数据被添加上悲观锁，直接返回。
• 乐观锁
  乐观锁只在数据提交的时候体现，常见的是Java中的CAS,以及版本比较，数据库的版本比较可以实现分布式锁的特征。
  流程就是线程A对数据d1进行更改为d2，线程B也操作d1更改为d3,并且都可以更改成功，但是在提交之前线程A和线程B会判断提交之前的数据是不是仍然是d1,如果是就提交，如果不是提交失败！这个就是乐观锁的实现。

3. 表锁和行锁

这里讨论下MyISAM存储引擎和InnoDB存储引擎。

1. MyISAM和InnoDB都可以使用表锁，而且两种存储引擎表锁的表现都是相同的，但是InnoDB产生新事务的同时会释放表锁。
2. 只有InnoDB具备行锁。

因为表锁和行锁都是悲观锁，当正常执行业务的时候肯定是

• 表锁的复杂度低，行锁的复杂度高
• 表锁锁的粒度大，并发低，行锁的粒度小，并发量高
• 表锁加锁快，行锁加锁慢
• 表锁不会出现死锁，并且锁冲突比较高，但是行锁会出现死锁，锁冲突比较低

4. MySQL锁分类

此处锁介绍都是基于InnoDB存储引擎。

1. 示例表：

CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

2. 插入数据：

insert into test(name,address) values(“zhangsan”,“beijing”),(“lisi”,“nanjing”);
select * from test;

3. 结果：
   

4.1 读锁（共享锁）

MySQL普通查询的时候是不会加读锁的，如何给SQL加读锁呢？

lock in share mode

当一个事务A获取读锁的时候，其他事务无法获取写锁，也就是无法更改数据，但是可以获取读锁。
3.1.1 错误示例：

select * from test where id = 1 lock in share mode;
update test set name = 'zs' where id = 1;

这个时候发现并没有什么效果，其实是必须在事务同时进行的时候才会有阻塞。
3.1.2 正确示例：

-- 窗口1
begin;
select * from test where id = 1 lock in share mode;
-- 窗口2
begin;
select * from test where id = 1; -- 正确展示
update test set name = 'zs' where id = 1; --阻塞
-- 窗口1
commit; -- 窗口2 由阻塞到运行成功
-- 窗口2
commit; 

4.2 写锁（排他锁）

1. MDL语句，例如update,delete,insert
2. select … for update
   当事务获取写锁之后，其他事务就不能获取该行的锁。

示例：

-- 窗口1
begin;
select * from test where id = 1 for update;
-- 窗口2
begin;
update test set name = 'zs' where id = 1; --阻塞
select * from test where id = 1 lock in share mode; --阻塞
select * from test where id = 1  -- 正确展示

-- 窗口1
commit; -- 窗口2 由阻塞到运行成功
-- 窗口2
commit; 

测试结果复合预期，当数据加上写锁后，就发生无法再加上读锁或者写锁，但是正常的数据查询仍然可以进行。

4.3 MDL锁和意向锁

• MDL锁
  V5.5 引入了meta data lock ,即为MDL锁。为了保证数据的正确行，当进行事务的时候，其他事务无法进行DDL语句。
  示例：

-- 窗口1
begin;
select * from test ;
-- 窗口2
begin;
alter table test add column n int(11);
-- 窗口1
commit; -- 窗口2 由阻塞到运行成功
-- 窗口2
commit; 

测试结果达到预期，当然会话1 假如进行MDL语句也可以达到同样的效果。

• 意向锁
  意向锁是一种表锁，也是为了保证数据的正确性，分为意向共享锁和意向排他锁。

意向共享锁： 当获取数据的共享锁之前必须获取表的意向共享锁。
意向排他锁： 当获取数据的排他锁之前必须获取表的意向排他锁。

5. 行锁分类

行锁分为 单行锁（record lock），间隙锁（gap lock）,next-key lock

• record lock
  普通意义上的行锁，当事务隔离级别是RC的时候只有单行锁，即为操作哪一行数据对这行数据进行加锁。
  锁是添加在索引上的，当没有索引的普通行进行更新的时候会进行全表加锁。

示例：

-- 添加字段 grade
alter table test add grade int(3) DEFAULT 0; 
update test set grade =88 where id in (1,2);
insert into test (name,address,grade) value ("xiaoming","zhengzhou",60),("xiaohu","zhengzhou",67);
alter table test add index grade_index(grade); -- 给grade 增加索引 grade_index 
show index from test; -- 可以查看到 所有 索引

-- 会话1
begin;
update test set name = 'zzs' where grade = '67';

-- 会话2
begin;
update test set name = 'zzs' where grade = '67'; -- 锁等待
update test set name = 'zzs2' where grade = '88'; -- 成功

-- 会话1，会话2
commit;

-- 去除索引:
 
 alter table test drop index grade_index;
 show index from test;

grade_index 索引已经删除。再次测试：

-- 会话1
begin;
update test set name = 'zzs' where grade = '67';

-- 会话2
begin;
update test set name = 'zzs' where grade = '67'; -- 锁等待
update test set name = 'zzs2' where grade = '88'; -- 锁等待

-- 会话1，会话2
commit;

也就是说，行锁其实锁的是索引，没有索引的时候锁的是全表数据（不是表锁）。
为什么会有这种情况呢?
主要是添加索引的时候进行了排序，我只要按照B+tree进行查询即可，但是没有索引的时候无法确定值大小顺序，就必须全表扫描。

• 间隙锁（ gap lock）
  为了解决幻读，MySQL增加了间隙锁。间隙锁只发生在RR隔离级别，RC不起作用。
  间隙锁是锁住操作行本身两方的索引列。例如上述grade 分别为60，67，88，88，当测试 65 < grade <70 的时候会把(60，88)范围锁住。
  间隙锁对单列的唯一索引不起作用。

示例：

-- 会话1
begin;
select * from test;

-- 会话2
begin;
insert into test (name,address,grade) value ("xiaoming2","zhengzhou",60) -- 阻塞

-- 会话1，会话2
commit;

为什么要使用间隙锁呢？
官网是这么说的，间隙锁是性能和并发的折衷方案，并且保证了间隙锁范围内数据距离的固定值，不允许插入新数据。

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record. For example, SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

Gap locks are part of the tradeoff between performance and concurrency, and are used in some transaction isolation levels and not others.

• next-key lock
  就是 record lock + gap lock。InnoDB默认的行锁模式，InnoDB执行行级锁定的方式是，当它搜索或扫描表索引时，它会对遇到的索引记录设置共享或排他锁。添加间隙锁的时候会进行判断。

例如上述grade 分别为60，67，88，88，当测试 65 < grade <70 的时候会把(60，88)范围锁住，而且会对60，88值对应的数据行锁住。

6. 锁等待和死锁

锁等待就是上述测试中阻塞的情况，当发生一个事务加锁，其他事务要对同样数据操作的时候可能会发生锁等待。具体分为读锁，写锁和间隙锁，而MySQL主要的锁也是这三种。
死锁就是多个事务发生相互争抢资源。

-- 会话1
begin;
update test set name = 'zzs' where grade = '67'; 

-- 会话2
begin;
update test set name = 'zzs' where grade = '60';

-- 会话1
update test set name = 'zzs2' where grade = '60'; -- 锁等待
-- 会话2 
update test set name = 'zzs2' where grade = '67'; -- 死锁

-- 会话1，会话2
rollback;

• 如何避免死锁？

1. 执行业务的时候尽量按照相同的顺序执行。
2. 事务的粒度尽量低，避免锁等待时间过长，导致死锁，要及时提交或回滚。
3. 非常容易产生死锁的部分可以使用过表锁。
4. 一个事务中的操作尽量少，获取资源的次数尽量少。

7. 锁监控

当发生锁等待，或者死锁的时候如何查看呢？

1. show processlist; 查看当前数据库线程的情况
2. show engine innodb status;
3. 查看表中的事务情况：INNODB_TRX,INNODB_LOCKS,INNODB_LOCK_WAITS
   这三张表在 infomation_schame中，是数据库初始化时创建的库和表。

示例：

-- 会话1
begin;
select * from test lock in share mode

-- 会话2
begin;
select * from test for update

查看INNODB_TRX表数据（部分）：

1. trx_id 事务ID
2. trx_state 线程状态 LOCK_WAIT 锁等待，RUNNING 正常运行
3. trx_mysql_thread_id 线程id ,可以通过 kill 线程id来终止
4. trx_query 运行SQL