mysql锁机制总结，以及优化建议

锁概念

1. 什么是锁

    锁是数据库系统区别于文件系统的一个关键特性。锁机制用于管理对共享资源的并发访问。
    每种数据库锁的算法都有可能不同， innoDB 存储引擎行锁，myisam是表锁
   

• 按照锁的粒度划分：行锁、表锁、页锁
• 按照锁的使用方式划分：共享锁、排它锁（悲观锁的一种实现）
• 还有两种思想上的锁：悲观锁、乐观锁。
• InnoDB中有几种行级锁类型：Record Lock、Gap Lock、Next-key Lock
• Record Lock：在索引记录上加锁
• Gap Lock：间隙锁
• Next-key Lock：Record Lock+Gap Lock

2. 锁有什么作用

    数据库系统使用锁是为了支持对共享资源的并发访问，提供数据的完
    整性和一致性
   

3. innodb 中的锁

• InnoDB默认支持的是行锁，并且也支持表锁。
• 在InnoDB中并不是在数据行上加锁，而是在对应的索引上加锁，这一点和oracle并不同，后者是在数据行上加锁的。这种实现的特点是：只有通过索引条件检索数据的时候加的是行锁，否则加表锁！

innoDB 存储引擎实现了如下两种标准的行级锁

• 共享锁（S Lock）允许事务读取一行数据
• 排它锁（X Lock）允许事务删除或更新一行数据

	X	S
X	冲突	冲突
S	冲突	兼容

innoDB 支持多粒度锁，这种锁定允许行级锁和表级锁同时存在。为了支持在不同粒度上进行加锁操作，innoDB支持 意向锁。

• 意向共享锁， 事务想要获得一个表中的某几行的共享锁

• 意向排它锁， 事务想要获得一个表中某几行的排它锁
  因为InnoDB 存储引擎支持的是行级别的锁，所以意向锁其实不会阻塞除全表扫描意外的任何请求。
  一致性非锁定行读（快照读)

    innoDB存储引擎通过多版本控制MVCC的方式读取当前之ing时间爱你数据库中
    行的数据，如果读取的行正在进行DElETE、UPDATE操作，不会等锁释放，回
    去读行的一个快照数据
  

4. 锁相关语句

查看当前请求锁的信息
show engine innodb status  
查看当前事务
select * from information_schema.INNODB_TRX
查看当前锁信息
select * from information_schema.INNODB_LOCKS\G;

5. 自增长和锁
   innoDB存储引擎下，自增长值的列必须是索引，并且是索引的第一列，如果是第二个列
   则会报错
6. 外键和锁
   外键主要用于引用完整性的约束检查，在innoDB 存储引擎中，如果没有对外键显式的添加索引，innoDB会自动添加索引，防止表锁，oracle不会自动添加。
7. 锁的算法
   innoDB存储引擎有3中行锁的算法设计

• 记录锁Record Lock 单行记录上的锁

• 
  事务加锁后锁住的只是表的某一条记录。
  记录锁出现条件：精准条件命中，并且命中的条件字段是唯一索引；
  例如：update user_info set name=’张三’ where id=1 ,这里的id是唯一索引。
  记录锁的作用：加了记录锁之后可以避免数据在查询的时候被修改的重复读问题，也避免了在修改的事务未提交前被其他事务读取的脏读问题。

• 间隙锁 Gap Lock 间隙锁， 锁定一个范围，但不包含记录本身

• InnoDB 通过间隙锁解决幻读

     事务加锁后锁住的是表记录的某一个区间，当表的相邻ID之间出现空隙则会形成一个区间，遵循左开右闭原则。
    间隙锁出现的条件：范围查询并且查询未命中记录，查询条件必须命中索引、间隙锁只会出现在REPEATABLE_READ（重复读)的事务级别中。
    间隙锁作用
    防止幻读问题，事务并发的时候，如果没有间隙锁，就会发生如下图的问题，在同一个事务里，A事务的两次查询出的结果会不一样。
  

• 临键锁 Next-Key Lock : Gap Lock + Record Lock 锁定一个范围 并锁定记录本身
  临键锁是INNODB的行锁默认算法，它是记录锁和间隙锁的组合，临键锁会把查询出来的记录锁住，同时也会把该范围查询内的所有间隙空间也会锁住，再之它会把相邻的下一个区间也会锁住。
  比如下面表的数据执行 select * from user_info where id>1 and id<=13 for update ;
  会锁住ID为 1,5,10的记录；同时会锁住，1至5,5至10,10至15的区间。
  出现条件：范围查询并命中，查询命中了索引。
  临键锁的作用
  结合记录锁和间隙锁的特性，临键锁避免了在范围查询时出现脏读、重复读、幻读问题。加了临键锁之后，在范围区间内数据不允许被修改和插入。
  

表锁（开销最小、并发最低、不会出现死锁）

	偏向MyISAM存储引擎，开销最小，加锁快；无死锁；锁定粒度大，发生锁冲突的
	概率最高,并发度最低。

表加写锁

--session1
手动增加表锁
mysql> lock table user write;			--操作1
Query OK, 0 rows affected (0.00 sec)
mysql> unlock tables;					--操作4
Query OK, 0 rows affected (0.00 sec)


--session2
查看已经加锁的表，省略很多行
mysql>show open tables;  				--操作2
*************************** 55. row ***************************
   Database: study
      Table: user
     In_use: 1
Name_locked: 0
查询数据 被阻塞
mysql> select * from user;   			--操作3
-- 查询被阻塞中......
--操作4 完成后
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 19 | cdb1 |   1 |   1 |
+----+------+-----+-----+
1 row in set (46.18 sec)

表加读锁

session1
--加读锁
mysql> lock table user read;			--操作1
Query OK, 0 rows affected (0.00 sec)
查询锁定表 不阻塞
mysql> select * from user;				--操作2
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 19 | cdb1 |   1 |   1 |
+----+------+-----+-----+
1 row in set (0.00 sec)

无法查询其他未锁定的表 
mysql> select * from score;
ERROR 1100 (HY000): Table 'score' was not locked with LOCK TABLES

--无法增、删、改 已经加锁的表
mysql> update user set age=2 where id=19;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> insert into user(name,age) values('cdb2',2);
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated
mysql> delete from user;
ERROR 1099 (HY000): Table 'user' was locked with a READ lock and can't be updated



session2 
其他会话查询不阻塞
mysql> select * from user;				--操作3
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 19 | cdb1 |   1 |   1 |
+----+------+-----+-----+
可查询其他未锁定的表
mysql> select * from score;				--操作4
+----+-------------+---------------------+-------+---------+
| id | change_type | create_time         | score | user_id |
+----+-------------+---------------------+-------+---------+
|  1 | 吃饭        | 2019-05-06 11:46:09 |    10 |       1 |

	MyISAM存储引擎有一个系统变量concurrent_insert，专门用于处理其并发插入的行为，其值
	可为0，1，2       
	 1 当concurrent_insert = 0 时，不允许并发插入       
	 2 当concurrent_insert = 1时，如果myisam表中没有空洞（即表的中间没有被删除的行），
	 myisam允许在一个进程读的同时，另一个进程从表尾插入记录，这就是mysql的默认设置        
	3 当concurrent_insert =时，无论myisam表中有没有空洞，都允许在表尾并发插入记录

如何分析表锁定
可以通过检查table_lock_waited和table_locks_immediate状态变量来分析系统上的表锁定

mysql> show status like 'table%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Table_locks_immediate      | 162   --产生表级锁定的次数
| Table_locks_waited         | 2     --Table_locks_waited 出现表级锁定争用而发生等待的次数
| Table_open_cache_hits      | 28    |
| Table_open_cache_misses    | 6     |
| Table_open_cache_overflows | 0     |
+----------------------------+-------+
Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1 
Table_locks_waited 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

MYISAM的读写锁调度是写优先，这也是MYISAM不适合做写为主表的引擎。因为写锁后，其他
线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞

行锁（并发高、开销大、会死锁）

行锁偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

显式锁和隐式锁

• InnoDB采用两阶段锁定协议：
  在事务执行过程中，随时可以执行加锁，但锁只有在执行commit或rollback时候才会释放，并且是所有的锁同一时刻释放，这些都是隐式锁定，innoDB会根据隔离级别在需要的时候自动加锁。当然，InnoDB也支持显式加锁：

select ...  lock in share mode;
select ...  for update

• InnoDB与MYISAM的最大不同有两点：
  一是支持事务（TRANSACTION）；
  二是采用了行级锁。

行锁支持事务

1. 更新丢失（Lost Update）
   　　当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题–最后的更新覆盖了由其他事务所做的更新。
2. 脏读（Dirty Reads）
   　　一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致的状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象的叫做“脏读”。
   　　一句话：事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
3. 不可重读（Non-Repeatable Reads）
   　　一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读”。
   　　一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性
4. 幻读（Phantom Reads）
   　　一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
   　　一句话：事务A读取到了事务B提交的新增数据，不符合隔离性

脏读是事务B里面修改了数据
幻读是事务B里面新增了数据

行锁实验

session1  
--查看原表数据
mysql> select * from user; 
+----+------+-----+-----+
| id | name | sex | age |
+----+------+-----+-----+
| 19 | cdb1 |   1 |   3 |
| 20 | cdb2 |   1 |   0 |
| 21 | cdb2 |   1 |   0 |
+----+------+-----+-----+
3 rows in set (0.00 sec)
关闭自动提交
mysql> set autocommit  =0;  		操作1
Query OK, 0 rows affected (0.00 sec)
mysql> update user set name='cdb3' where id=21;  操作2 
mysql> commit;					操作4
Query OK, 0 rows affected (0.01 sec)

---------------------------------------
session2 
mysql> update user set name='cdb4' where id=21;		操作3
.....阻塞中
--操作4 完成后立即执行
Rows matched: 1  Changed: 1  Warnings: 0  

意向锁分析

2-4、状态锁：意向共享锁、意向排他锁

意向锁
当一个事务试图对整个表进行加锁（共享锁或排它锁）之前，首先需要获得对应类型的意向锁（意向共享锁或意向共享锁）。

意向共享锁
当一个事务试图对整个表进行加共享锁之前，首先需要获得这个表的意向共享锁。

意向排他锁
当一个事务试图对整个表进行加排它锁之前，首先需要获得这个表的意向排它锁。

为什么我们需要意向锁？
首先我们先要有一个概念那就是innodb加锁的方式是基于索引，并且加锁粒度是行锁。

然后我们看下面的案例：

第一步：

事务A对user_info表执行一个SQL:update user_info set name =”张三” where id=6 加锁情况如下图;

第二步：

与此同时数据库又接收到事务B修改数据的请求：SQL: update user_info set name =”李四”；

1、因为事务B是对整个表进行修改操作，那么此SQL是需要对整个表进行加排它锁的（update加锁类型为排他锁）；

2、我们首先做的第一件事是先检查这个表有没有被别的事务锁住，只要有事务对表里的任何一行数据加了共享锁或排他锁我们就无法对整个表加锁（排他锁不能与任何属性的锁兼容）。

3、因为INNODB锁的机制是基于行锁，那么这个时候我们会对整个索引每个节点一个个检查，我们需要检查每个节点是否被别的事务加了共享锁或排它锁。

4、最后检查到索引ID为6的节点被事务A锁住了，最后导致事务B只能等待事务A锁的释放才能进行加锁操作。

思考：

在A事务的操作过程中，后面的每个需要对user_info加持表锁的事务都需要遍历整个索引树才能知道自己是否能够进行加锁，这种方式是不是太浪费时间和损耗数据库性能了？

所以INNODB就加了意向锁的概念：如果当事务A加锁成功之后就设置一个状态告诉后面的人，已经有人对表里的行加了一个排他锁了，你们不能对整个表加共享锁或排它锁了，那么后面需要对整个表加锁的人只需要获取这个状态就知道自己是不是可以对表加锁，避免了对整个索引树的每个节点扫描是否加锁。

页锁

​ 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。BDB支持页级锁

参考
https://zhuanlan.zhihu.com/p/70889229
https://www.jianshu.com/p/9eff9bf00f8a
https://blog.csdn.net/why15732625998/article/details/80439315
https://blog.csdn.net/qq_38238296/article/details/88362999 很详细
https://zhuanlan.zhihu.com/p/52312376
mysql技术内幕 innodb 存储引擎
高性能mysql第三版