Mysql事务隔离级别与锁机制

Mysql事务

并发事务带来的三种问题

• 脏读：可能导致脏读意思就是一个事务对数据就行了增删改，但未提交，数据只是放在了临时表里面，然后另一个事务
  读到临时表未提交的数据，如果第一个事务这时候进行了回滚，那么第二个事务就读到了脏数据。

• 不可重复读：一个事务中发生了两次读操作，第一次和第二次之间另外一个事务对数据进行了修改，
  这时候第二次读到的数据和第一次读到的数据是不一致的，这时候就要考虑锁和并发的问题了。

• 幻读：第一个事务对一定范围的数据进行批量修改，第二个事务在这个范围新增了一条数据，这时候第一个事务
  就会丢失对第二个事务新增进来的数据的修改。

查询mysql默认隔离级别

-- 8.0版本之前
SHOW VARIABLES LIKE 'tx_isolation';
-- 8.0版本之后
SHOW VARIABLES LIKE 'transaction_isolation';

事务的四个原则

1.原子性：是指一个事务要么全部执行，要么全部不执行。
2.一致性：是指事务执行前数据库的数据处于正确的状态，事务执行完之后数据库的数据处于正确的状态，
比如A给B转钱，A少了一千，B多了一千，两个人转账之前的账户总和等于转账之后的总和。
3.隔离性：是指一个事务正在进行时，不能被其他事务干扰，并发事务执行时候也互不影响，
比如一个人在自助取款机取钱的时候，其他人是不能进来的。
4.持久性：是指事务一旦执行成功，他对数据库的改变是永久的，数据被写到磁盘上面了。

事务的隔离级别

1.Read-Uncommitted：读未提交，意思是允许读取到还未提交改变的数据，隔离级别的值 0，可能导致脏读、不可重复读、幻读。
2.Read-committed：读已提交，允许在并发事务已经提交后读取，隔离级别的值为 1，可以防止脏读，但是不可避免不可重复读、幻读。
3.REPEATABLE-READ：可重复读，对相同字段的多次读取是一致的，除非数据被事务本身改变，隔离级别的值 2，可防止脏读、不可重复读，但幻读仍可能发生。
4.Serializable：可串行化，完全服务ACID(事务的四种属性)的隔离级别，隔离级别的值 3，确保不发生脏、幻、不可重复读。这在所有的隔离级别中是最慢的，
他是典型的通过完全锁定在事务中涉及的数据表来完成的。
5.Defualt：使用后端数据库默认的事务隔离级别(spring中的选择项)，默认的隔离级别值 -1，上面四个都是数据库默认的，这个是spring自己封装的

Mysql默认是可重复读，Spring如果不设置的话默认使用Mysql的设置的隔离级别。

隔离级别	脏读	不可重复读	幻读
读未提交	Y	Y	Y
读已提交	N	Y	Y
可重复读	N	N	Y
串行化	N	N	N

MySQL锁

锁分类

• 乐观锁
  • 表加 version 字段进行隔离
  • juc工具包的cas
• 悲观锁
  • 读锁(共享锁Shared)：在写数据的时候可以共享同一把锁，但是只能读数据不能修改数据
  • 写锁(排它锁eXclusive)：一个事务获取了某条数据的排它锁之后，其他事务不能再获取。排它锁可以进行修改和读取数据
  • synchronized：当前线程没有释放锁其他线程不能获取锁
  • 表锁：不会出现死锁，发生锁冲突几率高，并发低。Myisam和InnnDB都支持表锁
  • 行锁：会出现死锁，发生锁冲突几率低，并发高。Myisam不支持行锁，InnoDB支持行锁

表锁

表锁开销小，加锁快，锁的粒度大，一般用在数据迁移。锁冲突概率高，并发度低。

因为表锁是锁住了整张表，所以直接找到表即可，效率比较高，且表锁不会出现死锁。

一般表锁用在数据库做数据迁移的场景，在迁移的过程中，防止有其他事物提交数据。

-- read表示读锁，write表示写锁
lock table 表名 read(write);
-- 删除表锁
unlock tables;

查看哪张表加过锁，通过 In_use 字段为1表示加上过锁

show open tables;

释放锁

unlock tables;

行锁

每次操作锁住一行数据，行锁开销大，加锁慢(因为没有索引可能找到那一条数据比较慢)，锁的力度小。锁冲突概率低，并发度高。

Myisam不支持事务和行锁，Innodb两者都支持。

mysql默认是开启事务的，通过 begin; 手动开启，通过 commit; 手动提交。

# 事务1
begin;
update t_user set user_name = 'zhangsan' where id = 1;
commit;
# 事务2在事务1没有提交id为1的数据之前会一直卡着，操作其他条记录不会影响。

Myisam存储引擎在执行 SELECT 语句之前会自动给表加读锁，在执行 UPDATE/INSERT/DELETE 语句会自动给表加写锁。

Innodb存储引擎在执行 SELECT 语句不会加锁，因为有MVCC机制，在执行 UPDATE/INSERT/DELETE 语句会自动加行锁。

读锁会阻塞写，不会阻塞读。写锁会阻塞读和写。

通过主键更新会先锁住主键索引，如果一条语句使用了非主键索引，mysql会先锁住非主键索引，再锁住主键索引。

对于没有索引的操作，会采用表级锁。

行锁与隔离级别案例

创建表

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)设置隔离级别为读未提交，两个事务都要设置

set tx_isolation='read-uncommitted';

(2)事务1首先查询，可以看到余额为450

(3)事务2去开启事务，并把余额减去50，此时并不提交事务

(4)事务1再去查询，余额已经变成了400

(5)此时事务2回滚，事务1就拿到了脏数据

读已提交

(1)设置离级别为读已提交，两个事务都要设置

set tx_isolation='read-committed';

(2)事务1还是先查询余额为450

(3)事务2开启事务并把余额减去50，并不提交事务

(4)事务1再去查询余额还是450，解决了脏读的问题

(5)事务2去提交事务

(6)事务1再去读取，就能看拿到余额已经变成减去50之后的400了。事务1在同一个事务里面，两次查询结果不一样了，虽然解决了脏读的问题，但是会有不可重复读、幻读的问题。

可重复读

(1)设置隔离级别为可重复读，两个事务都要设置

set tx_isolation='repeatable-read';

(2)事务1开启事物，并查询余额为400

(3)事务2开启事务把余额减去50，并提交事务，此时余额应该只剩350

此时可再起一个窗口，也就是事务3查询验证余额是否为350，发现数据没有问题

(4)但此时通过事务1继续查询，还是现实400，也就是解决了不可重复读的问题

(5)此时事务1的查询还没有提交事务，在事务1里面也把余额减去50

再去执行查询语句，发现余额却变成了300。也就是没有按照事务自己看到的400-50来算的，而是按照事务2的400-50=350之后，在事务1里面再去减50去计算的，这就是mysql的MVCC(multi-version concurrency control)机制。可重复读的隔离级别下会用了MVCC机制，读已提交的隔离级别也是用了MVCC机制。select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

(6)验证幻读，重新打开事务2，开启事务，并插入一条数据

(7)在事务1里面根据刚刚插入的id为4的记录去更新balance

再事务1里面再次查看所有数据，发现id为4的余额已经有600变成了550，这就是脏读的问题。

串行化

(1)设置隔离级别为串行化，两个事务都要设置

set tx_isolation='serializable';

(2)事务1开启事务，并查询id为1的数据

(3)事务2修改id为1的数据，会一直阻塞在这里

等待一会，会发现事务2超时报错

但是修改其他数据不会受影响，比如修改id为的数据可正常修改。如果客户端A执行的是一个范围查询，那么该范围内的所有行包括每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁。此时如果客户端B在该范围内插入数据都会被阻塞，所以就避免了幻读。这种隔离级别并发性极低，开发中很少会用到。

(4)同样的一个事务在做插入或更新某一条数据，另一个事务查询的范围涉及到了该条数据，查询的事务也会阻塞，

比如事务2开启事务，并插入一条id为5的记录

(5)事务1查询所有数据，会涉及到刚刚事务2插入的数据，所以会造成阻塞。把事务2提交，事务1也就可以查询出来了。

间隙锁(Gap Lock)

间隙锁，锁的就是两个值之间的空隙。Mysql默认级别是repeatable-read，有办法解决幻读问题吗？间隙锁
在某些情况下可以解决幻读问题。
假设account表里有id为1,2,3,10,20的数据，

那么间隙就有 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)

Next-Key Locks是行锁与间隙锁的组合。像上面那个例子里的这个(3,20]的整个区间可以叫做临键锁。

无索引行锁会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。
session1 执行：update account set balance = 800 where name = ‘lilei’;
session2 对该表任一行操作都会阻塞住
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为
表锁。锁定某一行还可以用lock in share mode(共享锁) 和for update(排它锁)，例如：select * from
test_innodb_lock where a = 2 for update; 这样其他session只能读这行数据，修改则会被阻塞，直到锁定
行的session提交
结论：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;
-- 查看锁
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 释放锁，trx_mysql_thread_id可以从INNODB_TRX表里查看到
kill trx_mysql_thread_id;
-- 查看锁等待详细信息
show engine innodb status\G;

死锁

设置隔离级别为可重复读

set tx_isolation='repeatable-read';

事务1开启事务并查询id为1的数据

BEGIN;
select * from account where id=1 for update;

事务2开启事务并查询id为2的数据，这时候一切正常

BEGIN;
select * from account where id=2 for update;

事务1再执行查询id为2的数据就会阻塞

select * from account where id=2 for update;

事务2再执行查询id为1的数据就会直接报错，显示死锁异常信息 Deadlock found when trying to get lock; try restarting transaction

select * from account where id=1 for update;

查看近期死锁日志信息

show engine innodb status\G;

大多数情况mysql可以自动检测死锁并回滚产生死锁的那个事务，但是有些情况mysql没法自动检测死锁

锁优化建议

1.尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2.合理设计索引，尽量缩小锁的范围
3.尽可能减少检索条件范围，避免间隙锁
4.尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行
5.尽可能低级别事务隔离

MVCC机制

MVCC机制详解

在Read Uncommitted级别下，事务总是读取到最新的数据，因此根本用不到历史版本，所以MVCC不在该级别下工作。

在Serializable级别下，事务总是顺序执行。写会加写锁，读会加读锁，完全用不到MVCC，所以MVCC也不在该级别下工作。

Mysql在读已提交和可重复读隔离级别下都实现了MVCC机制。

Mysql在可重复读隔离级别下如何保证事务较高的隔离性，同样的sql查询语句在一个事务里多次执行查询结果相同，就算其它事务对数据有修改也不会影响当前事务sql语句的查询结果。这个隔离性就是靠MVCC(Multi-Version Concurrency Control)机制来保证的，对一行数据的读和写两个操作默认是不会通过加锁互斥来保证隔离性，避免了频繁加锁互斥，而在串行化隔离级别为了保证较高的隔离性是通过将所有操作加锁互斥来实现的。

undo日志版本链与read view机制详解

undo日志版本链是指一行数据被多个事务依次修改过后，在每个事务修改完后，Mysql会保留修改前的数据undo回滚
日志，并且用两个隐藏字段trx_id和roll_pointer把这些undo日志串联起来形成一个历史记录版本链。

InnoDB存储引擎在数据库每行数据的后面添加了三个字段

• 6字节的事务ID(trx_id)字段：当一个事务开始执前，mysql会为这个事务分配一个全局自增的事务id。

  之后该事务对当前行进行的增、删、改操作时，都会将自己的事务id记录到trx_id中。

• 7字节的回滚指针(DB_ROLL_PTR)字段：事务对当前行进行改动时，会将旧数据写入进undo log中，再将新数据写入当前行，且当前行的roll_pointer指向刚才那个undo log，因此可以通过roll_pointer找到该行的前一个版本。

• 6字节的DB_ROW_ID字段：如果当前表有整数类型的主键，则row_id就是主键的值。如果没有整数类型的主键，则mysql会按照字段顺序选择一个非空的整数类型的唯一索引作为row_id。如果mysql没有找到，则会自动生成一个自动增长的整数作为row_id。

当一直有事务对该行改动时，就会一直生成undo log，最终将会形成undo log版本链。

Read View

在可重复读隔离级别，当事务开启，执行任何查询sql时会生成当前事务的一致性视图read-view，该视图在事务结束
之前都不会变化(如果是读已提交隔离级别在每次执行查询sql时都会重新生成)，这个视图由执行查询时所有未提交事
务m_ids数组（数组里最小的id为min_id）和已创建的最大事务id（max_id）组成，事务里的任何sql查询结果需要从对应
版本链里的最新数据开始逐条跟read-view做比对从而得到最终的快照结果。

在RC级别下，当前事务总是希望读取到别的事务已经提交的数据，因此当前事务事务会在执行每一次快照读的情况下都会去生成ReadView，实时更新m_ids，及时发现那些已经提交的事务。

在RR级别下，当前事务当然也能够读取到别的事务已经提交的数据，但为了避免不可重复读，因此只会在执行第一次快照读的情况下去生成ReadView，之后的快照读会一直沿用该ReadView。

• m_ids：在创建ReadView的那一刻，mysql中所有未提交的事务id集合。
• min_id：m_ids中的最小值。
• max_id：mysql即将为下一个事务分配的事务id，并不是m_ids中的最大值。
• creator_trx_id：即创建此ReadView的事务id。

undo日志版本链比对规则：

1.当【版本链中记录的 trx_id 等于当前事务id（trx_id = creator_trx_id）】时，说明版本链中的这个版本是当前事务修改的，所以该快照记录对当前事务可见。

2.如果 row 的 trx_id 落在绿色部分( trx_id < min_id )，表示这个版本是已提交的事务生成的，这个数据是可见的；

3.如果 row 的 trx_id 落在红色部分( trx_id > max_id )，表示这个版本是由将来启动的事务生成的，是不可见的(若
row 的 trx_id 就是当前自己的事务是可见的）；

4.如果 row 的 trx_id 落在黄色部分(min_id <= trx_id < max_id)，那就包括两种情况

• 若 row 的 trx_id 在视图数组中，表示这个版本是由还没提交的事务生成的，不可见(若 row 的 trx_id 就是当前自
  己的事务是可见的)；
• 若 row 的 trx_id 不在视图数组中，表示这个版本是已经提交了的事务生成的，可见。
  对于删除的情况可以认为是update的特殊情况，会将版本链上最新的数据复制一份，然后将trx_id修改成删除操作的
  trx_id，同时在该条记录的头信息（record header）里的（deleted_flag）标记位写上true，来表示当前记录已经被
  删除，在查询时按照上面的规则查到对应的记录如果delete_flag标记位为true，意味着记录已被删除，则不返回数
  据。
  注意：begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个修改操作InnoDB表的语句，
  事务才真正启动，才会向mysql申请事务id，mysql内部是严格按照事务的启动顺序来分配事务id的。

Buffer Pool机制

mysql的数据都是存放在磁盘下的，为了加快cpu从磁盘i/o读取数据的效率，Innodb存储引擎在cpu和磁盘中间添加了一个缓冲区buffer pool。当一个请求进来，会先从buffer pool中去看需要的查询结果数据是否已经存在，存在则直接返回，不存在，则从磁盘读取记录所在页的数据，加载到buffer pool中缓存起来。

Buffer pool是Innodb向操作系统申请的一块连续的内存空间，在多线程环境下，访问buffer pool的各种链表都需要加锁处理，在多线程并发的情况下，会严重影响请求的速度。Innodb支持同时使用多个buffer pool，每个buffer pool都称为一个实例，他们独立去申请内存空间，独立管理各种链表，在多线程并发访问的情况下互不影响，提高并发能力。

查询buffer pool大小。 Buffer pool的默认值是128M，我们可以通过变量innodb_buffer_pool_size的大小来设置，最小不能小于5M，若是设置为5M以下，则系统会自动设置为5M。在专用数据库中，buffer pool的大小一般设置为服务器内存的60%。

show variables like 'innodb_buffer_pool_size';

buffer pool的大小是有限的，一直往里面加入缓存页，free链表会有移除为空的时候，当free链表为空，有新的页数据需要加入进来，就需要对已经加载到buffer pool中的缓存页进行淘汰处理，系统肯定希望淘汰最不频繁使用的缓存页。innodb是这样处理的：使用LRU（Least Recently Used）最近最少使用算法来淘汰缓存页，LRU链表记录访问过的缓存页对应的控制块。

LRU链表的工作流程是这样的：当访问某个页时，如果此页不在buffer pool中，则从磁盘加载此页数据到缓存页中，把该缓存页对应的控制块加入到LRU链表的头部；当此页已经在buffer pool中，则把该页对应的控制块移动到LRU链表头部。

这样LRU链表的尾部就是最近最少使用的控制块，当缓存页不够的时候，可以从LRU链表尾部淘汰控制块，连带淘汰控制块对应的缓存页。