深入理解Mysql事务隔离级别与锁机制
- 一：概述
- 二：事务及其ACID特性
- 三：并发事务处理带来的问题
- 四：事务隔离级别
- 五：详解
- 六：行锁与事务隔离级别案例分析
- 读未提交：
- 读已提交
- 可重复读
- 七：间隙锁(Gap Lock)
- 八：锁优化建议
- 查看锁相关
- 间隙锁
- 临键锁
- 无索引行锁会升级为表锁
- 行锁分析
一：概述
我们的数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能就会导致我们说的 脏写、脏读、不可重复读、幻读 这些问题。
这些问题的本质都是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机 制、MVCC多版本并发控制隔离机制 ，用一整套机制来 解决多事务并发问题 。
二：事务及其ACID特性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
原子性(Atomicity) ：事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent) ：在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规 则都必须应用于事务的修改,以保持数据的完整性。
隔离性(Isolation) ：数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独 立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable) ：事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
三：并发事务处理带来的问题
脏读：事务A读取到了事务B已经修改但尚未提交的数据
不可重读：事务A内部的相同查询语句在不同时刻读出的结果不一致，不符合隔离性
幻读：一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数 据，这种现象就称为“幻读”。 一句话：事务A读取到了事务B提交的新增数据，不符合隔离性
不可重复读的重点是修改：
同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除：
同样的条件，第 1 次和第 2 次读出来的记录数不一样
四：事务隔离级别
常看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;
设置事务隔离级别： set tx_isolatinotallow=‘REPEATABLE-READ’;
Mysql默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认用Mysql设置的隔 离级别，如果Spring设置了就用已经设置的隔离级别
五：详解
锁是计算机协调多个进程或线程并发访问某一资源的机制。
锁分类
从性能上分为----> 乐观锁 (用版本对比来实现)和 悲观锁
从对数据库操作的类型分为----> 读锁和写锁 (都属于悲观锁)
读锁（共享锁，S锁( S hared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁，X锁(e X clusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁
从对数据操作的粒度分，分为----> 表锁和行锁
表锁
每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低； 一般用在整表数据迁移的场景。
行锁
每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB与MYISAM的最大不同有两点：
InnoDB支持事务（ TRANSACTION）
InnoDB支持行级锁
行锁演示
一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录不会阻塞
总结：
MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁,在执行update、insert、delete操作会自动给涉及的表加写锁。
InnoDB在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加 行锁。
简而言之，就是 读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞 。
六：行锁与事务隔离级别案例分析
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' );读未提交：
（1）打开一个客户端A，并设置当前事务模式为read uncommitted（未提交读），查询表account的初始值： set tx_isolation=’ read-uncommitted ';
（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：
（3）这时，虽然客户端B的事务还没提交，但是客户端A就可以查询到B已经更新的数据：
（4）一旦客户端B的事务因为某种原因回滚，所有的操作都将会被撤销，那客户端A查询到的数据其实就是脏 数据
（5）在客户端A执行更新语句update account set balance = balance - 50 where id =1，lilei的balance没有变成350，居然是400，是不是很奇怪，数据不一致啊， 如果你这么想就太天真了，在应用程序中，我们会用400-50=350，并不知道其他会话回滚了，要想解决这个问题可以采用读已提交的隔离级别
读已提交
（1）打开一个客户端A，并设置当前事务模式为read committed（未提交读），查询表account的所有记录： set tx_isolation=’ read-committed ';
（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account：
（3）这时，客户端B的事务还没提交，客户端A不能查询到B已经更新的数据， 解决了脏读问题
（4）客户端B的事务提交
（5）客户端A执行与上一步相同的查询，结果 与上一步不一致，即产生了不可重复读的问题
可重复读
（1）打开一个客户端A，并设置当前事务模式为repeatable read，查询表account的所有记录 set tx_isolation=’ repeatable-read ';
（2）在客户端A的事务提交之前，打开另一个客户端B，更新表account并提交
（3）在客户端A查询表account的所有记录，与步骤（1）查询结果一致，没有出现不可重复读的问题
（4）在客户端A，接着执行update account set balance = balance - 50 where id = 1，balance没有变成400-50=350，lilei的balance值用的是步骤2中的350来算的，所以是300，数据的一致性倒是没有被破坏。可重复读的隔离级别下使用了 MVCC(multi-version concurrency control)机制，select操作不会更新版本号，是快照读（历史版本） ； insert、update和delete会更新版本号，是当前读（当前版本）
（5）重新打开客户端B，插入一条新数据后提交
（6）在客户端A查询表account的所有记录，没有查出新增数据，所以没有出现幻读
（7)验证幻读 —>在客户端A执行update account set balance=888 where id = 4;能更新成功，再次查询能查到客户端B新增的数据
七：间隙锁(Gap Lock)
举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：
Select * from emp where empid > 100 for update;是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。有关其恢复和复制对锁机制的影响，以及不同隔离级别下InnoDB使用间隙锁的情况，在后续的章节中会做进一步介绍。
很显然，在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。因此，在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。
八：锁优化建议
1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引，避 免无索引行锁升级为表锁
2.最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
1 EXPLAIN SELECT * FROM employees WHERE name = ‘Bill’ and age = 31;
2 EXPLAIN SELECT * FROM employees WHERE age = 30 AND position = ‘dev’;
3 EXPLAIN SELECT * FROM employees WHERE position = ‘manager’;
3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
因为你操作后得到得字符，到索引B+树里面去找，是找不到得！
4.存储引擎不能使用索引中范围条件右边的列
例如这个图，查找name=Bill，age>29 and position=“dev” 中这个dev不会走索引，因为age已经根据大小排好了，那么下一列得position肯定是无序得，只能全表扫描
5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少 select * 语句
6.mysql在使用不等于（！=或者<>），not in ，not exists 的时候无法使用索引会导致全表扫描 < 小于、 > 大于、 = 这些，mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引
7.is null,is not null 一般情况下也无法使用索引
8.like以通配符开头（‘$abc…’）mysql索引失效会变成全表扫描操作
一般通配符在前面走不了索引，在后面可以
问题：解决like’%字符串%'索引不被使用的方法？
a）使用覆盖索引，查询字段必须是建立覆盖索引字段
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Lei%';、b）如果不能使用覆盖索引则可能需要借助搜索引擎
9.字符串不加单引号索引失效
10.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小等多个因素整体评 估是否使用索引，详见范围查询优化
查看锁相关
-- 查看是否有锁表
show OPEN TABLES where In_use > 0;
-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看正在锁的事务 Mysql8.0 后改成：
select * from performance_schema.data_locks;
-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看等待锁的事务 Mysql8.0 后改成：
select * from performance_schema.data_lock_waits;
-- 查看innodb引擎的运行时信息 （可以看到死锁的具体SQL DBA使用）
show engine innodb status;
-- 查看服务器状态
show status like '%lock%';
-- 查看超时时间：
show variables like '%timeout%';
-- 查看所有进程
#MySQL:
show processlist;
-- mariabd:
show full processlist;
-- 查看事务信息
select * from INFORMATION_SCHEMA.INNODB_TRX;
-- 释放锁，trx_mysql_thread_id 可以从Innodb_TRX 表里查看到,
-- thread_id 通过 select * from INFORMATION_SCHEMA.INNODB_TRX; 查看事务对应的thread id；
kill trx_mysql_thread_id ;
-- 例如: kill 64;间隙锁
那么间隙就有 id 为 (3,10)，(10,20)，(20,正无穷) 这三个区间
在Session_1下面执行 update account set name = ‘zhuge’ where id > 8 and id <18;，则其他Session没 法在这个范围所包含的所有行记录(包括间隙行记录)以及行记录所在的间隙里插入或修改任何数据，即id在 (3,20]区间都无法修改数据，注意最后那个20也是包含在内的。
间隙锁是在可重复读隔离级别下才会生效。
临键锁
Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。
无索引行锁会升级为表锁
锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。
session1 执行：update account set balance = 800 where name = ‘lilei’;
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。
行锁分析
通过检查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（等待总时长）
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待， 然后根据分析结果着手制定优化计划。
