6.0深入理解MySQL事务隔离级别与锁机制

6.0深入理解MySQL事务隔离级别与锁机制

1、MySQL事务及ACID特性详解

概述

​ 数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能会导致脏写、脏读、不可重复读、幻读这些问题。

​ 这些问题的本质就是数据库的多事务并发问题，为了解决多事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

事务及其ACID属性

​ 事务是有一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

• 原子性（Atomicity）：事务是一个原子操作，对数据修改，要么全执行，要么全不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都应必须用于书屋的修改，以保持数据的完成性。
• 隔离性（Isolation）:数据库提供一定的隔离机制，保证事务在不受外部并发操作的影响的“独立”环境执行。意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失(Lost Update )或脏写

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题-最后的更新覆盖了有其他事务所做的更新。

脏读（Dirty Reads）

一个事务正在对一条记录做修改，在这个事务完成并提交，这条记录的数据处于不一致的状态，这时另一个事务也来读取同一条记录，如果不加控制，第二事务读取了这些“脏”数据，并据此作进一步的处理，就会产生未提交的数据依赖关系。这种现象称作“脏读”。

简单说：事务A读取到了事务B已经但尚未提交的数据，还在这个数据基础上做了操作。此时如果B事务回滚，A读取的数据无效，不符合一致性要求。

不可重复读（Non-Repeatable Reads）

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现场称作“不可重复读”。

简单说：事务A内部的相同查询语句在不同时刻读出的结果不一样，不符合隔离性。

幻读（Phantom Reads）

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询的新数据，这种现象称为“幻读”。

简单说：事务A读取到了事务B事务提交的新增数据，不符合隔离性。

脏读、不可重复读和幻读，其实都是数据库一致性问题，必须有数据库提供一定的事务隔离机制来解决。

​ 数据库的事务隔离越沿河，并发的副作用越小，但付出的代价也就越来越大，因为事务隔离实质上就是使用事务在一定程度“串行化”进行，这显然与“并发”是矛盾的。

​ 同时，不同的应用对读一致性和事务隔离的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

查看当前数据库事务隔离级别：show variables like 'tx_isolation';

设置事务隔离级别：set tx_isolation=‘REPEATABLE-READ’

MySQL默认的事务隔离级别是可重复读，用Spring开发程序时，如果不设置隔离级别默认就是使用MySQL设置的隔离级别，如果Spring设置了就用已经设置的隔离级别。

3、MySQL锁机制详情

锁详解

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除了传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供需用户共享资源。如果保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。

锁分类

• 从性能上分为乐观锁（用版本对比来实现）和悲观锁
• 从对数据库操作的类型分，读锁和写锁（属于悲观锁）
  读锁（共享锁,S锁Shared）：针对同一份数据，多个读操作可以同时进行而不会互相影响
  写锁（排它锁，X锁eXclusive）:当前写操作没有完成前，它还会阻断其他写锁和读锁
• 从数据操作的粒度分，表锁和行锁

表锁-不太重要

​ 每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生冲的的概率高，并发度最低；一般用在整表数据迁移的场景。

基本操作

--手动增加表锁
lock table 表名 read(write),表名read(write);
--查看表上加过的锁 查询所有表锁
show open tables;
--删除表锁 接触表锁
unlock tables;

总结：

1）对MySQL表的加读锁，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只要当读锁释放后，才会执行其他进程的写操作。

2）对MySQL表加写锁，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

行锁

​ 每次操作锁住一行数据。开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发最高。

InnoDB和MYISAM的最大不同的两点：

1）InnoDB支持事务（TRANSATION）

2）InnoDB支持行级锁

行锁解释：

​ 一个session开启事务更新不提交，另一个session更新同一条记录会阻塞，更新不同记录也会阻塞

总结：

​ MyISAM在执行查询语句select前，会自动给设计的所有表加读锁，在执行update、insert、delete操作会自动给涉及的表加写锁。

​ InnoDB在执行查询语句时，因为有MVCC机制不会加锁。但是update、insert、delete操作会加行锁。

简而言之：读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞。

行锁与事务隔离级别案例分析

读未提交

1）打开一个客户端A（一个连接，一个session）,并设置当前事务隔离级别为 read uncommited(读未提交)，查询表test的初始值：注意不同session可以有不同的隔离级别。

set tx_isolation=‘read-uncommited’;

结论：当A事务读到B事务中修改的数据，尽管B事务还没有提交，若B事务回滚，那么A事务中读取的就是脏数据，若A事务对这个数据操作就是脏写。

注意：用数据库的计算会避免一部分脏读。

读已提交

set tx_isolation=‘read-commited’;

结论：在这种隔离级别下解决脏读的问题，因为无法读取未提交的数据，只能读到已经提交的数据，但是问题也是存在的，尽管在同一个事务可能出现，读取同一行数据，在不同的时间结果不一样，以那个结果为准呢？值还会发生变化吗？这就是不可重复读的问题

可重复读（用的最多的）

set tx_isolation=‘repeatable-read’;

概念：不管其他事务对此数据是否修改过，只要在同一个事务中查询过就默认后面查询结果都是同一个值（可重复读隔离级别下使用了MVCC机制，select操作不会更新版本号，是快照读（历史版本），insert，update，delete会更新版本号，是当前版本），可能产生幻读。

串行化

set tx_isolation=‘serializable’;

在此隔离级别下，所有的查询语句和更新语句都会加上行锁。并发性极低，开发中很少用到。

如果事务A执行的是一个范围查询，那么该范围内的所有行及每行记录所在的间隙区间范围(就算该行数据还未被插入也会加锁，这种是间隙锁)都会被加锁。此时如果事务B在该范围内插入数据都会被阻塞，所以就避免了幻读。

间隙锁（Gap lock）

​ 锁的就是两个值之间的空隙。MySQL默认级别是repeatable-read，有办法解决幻读吗？间隙锁某些情况下可以解决的。

假设一张表的id有值1，2，3，10，20这个五个值那么间隙就有id为（3,10），(10,20)，(20,正无穷)三个区间。

在一个事务下面执行 update test set name=‘zhang’ where id >8 and id<18;则在其他事务里没法在这个范围所包含的所有行记录以及范围内间隙行记录所在的原表所有间隙里插入或修改任何数据，即id在（3,20]区间无法修改数据。注意20也包含在内的

间隙锁是在可重复读隔离级别下才会生效的。

临键锁（Next-key Locks）

​ 是行锁和间隙锁的组合。像间隙锁中的例子(3,20]的整个区间可以称作临键锁。

无索引行会升级为表锁

锁主要是加在索引上，如果对非索引字段更新，行锁可能会变表锁。

session1执行：update test set age=30 where name=‘aa’;

session2对该表任何一行操作都会进行阻塞

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁。

锁定某一行可以用共享锁和排它锁，例如：select * from test where id = 2 for update ;这样其他session只能读这行数据，修改则会被阻塞，知道锁定行的session提交事务。

行锁分析

通过检查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: 系统启动后到现在总共等待的次数
比较重要的如下

• 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’；

Session_1执行：select * from test where id=1 for update;

Session_2执行：select * from test where id=2 for update;

Session_1执行：select * from test where id=2 for update;

Session_2执行：select * from test where id=1 for update;

查看近期死锁日志信息：show engine innodb status;

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

4、MySQL锁优化建议

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

总结： Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一下，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定的。当系统并发量高的时候，Innodb的整体性能和MYISAM相比就会有比较明显的优势了。