深入理解Mysql事务隔离级别与锁机制问题

原作者: [db:作者] 来自: [db:来源] 收藏邀请

概述

数据库一般都会并发执行多个事务，多个事务可能会并发的对相同的一批数据进行增删改查操作，可能导致脏读、脏写、不可重复度和幻读。这些问题的本质都是数据库的多事务并发问题，为了解决事务并发问题，数据库设计了事务隔离机制、锁机制、MVCC多版本并发控制隔离机制，用一整套机制来解决多事务并发问题。

事务及其ACID属性

原子性：操作的不可分割；

一致性：数据的一致性；

隔离性：事务之间互不干扰；

持久性：数据的修改时永久的；

并发事务处理带来的问题

脏写：丢失更新，最后的更新覆盖了由其他事务所做的更新；

脏读：事务A读取到了事务B已经修改但未提交的数据；

不可重复读：事务内部相同的查询在不同时刻结果不一样，针对的是数据的更新、删除操作；

幻读：事务A读取到了其后开始的事务B提交的新增数据；针对的是数据的插入；

事务隔离级别

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

READ-UNCONMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE

查看当前数据库的事务隔离级别：

show variables like 'tx_isolation'

设置事务隔离级别：

set tx_isolation='REPEATABLE-READ';

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

锁详解

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

锁分类

从性能上分为：乐观锁（用版本对比来实现）和悲观锁；

从对数据库操作类型分：读写和写锁（悲观锁）；

读锁（共享锁，S锁（Shared））：针对同一份数据，多个读操作可以同时进行而不会互相影响；

写锁（排它锁，X锁（exclusive））:当前写操作没有完成前，阻断其他写锁和读锁；

从对数据库操作的粒度分：表锁和行锁

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

# 手动增加表锁
lock table 表名称 read(write),表名称2 read(write);
# 查看表上加过的锁
show open tables;
# 删除表锁
unlock tables;

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

InnoDB与MYISAM的最大不同点：1、InnoDB支持事务；2、InnoDB支持行级锁。

总结：

MyISAM在执行查询语句前，会自动给涉及的表加读锁；执行update、insert、delete操作加写锁；

InnoDB在执行查询语句前（非串行隔离级别），不会加锁；执行update、insert、delete操作会加行锁。

读锁会阻塞写，但不会阻塞读。而写锁会把读写都阻塞。

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

mysql准备一张表

1、脏读，事务A读取到另外一个事务已修改但未提交的数据，此种情形简单，不具体阐述。对应的事务隔离级别：read uncommitted（读未提交）。

2、不可重复读，对应的事务隔离级别：read committed（读已提交）

事务A：

set session transaction isolation level read committed;
 
start transaction;
 
select * from t_user;

事务B：

set session transaction isolation level read committed;
 
start transaction;
 
-- insert into t_user values (1,'张',8);
update t_user set age = 9 where id = 1;
 
commit;

事务A第一次执行到查询语句，结果如下：

此时，事务B执行完毕，事务A还未结束，继续执行一次查询，结果如下：

产生了不可重复读的问题，一个事务内前后两次查询的数据结果不一致，读取到了其他事务已经提交的数据。

3、可重复读，设置事务隔离级别为repeatable read（可重复读）；

事务A第一次执行结果如下：

事务B执行，修改操作，update age=8并提交，结果对比如下

左边为事务A，查询结果与开始时一样，解决了不可重复读的问题；直接查询，此时age=8；

可重复读的隔离级别下使用了MVCC（multi-version concurrency control）机制，select操作不会更新版本号，是快照读（历史版本）；insert、update和delete会更新版本号，是当前读（当前版本）。

4、幻读，在3中，新增一条数据，如下

此时事务A再次查询，结果如下：

结果依然和开始的一样，此种场景，可重复读隔离级别有效的防止了不可重复读和幻读的问题；

如果，事务A，在第一次查询后，执行不加条件的update，这个update会作用在所有的行上面，包括事务B新增加的数据。此时，再执行查询，结果如下：

出现了幻读，Mysql官方给出的幻读解释是：只要在一个事务中，第二次select多出了row计算幻读。

5、串行化，serializable，InnoDB的查询也会被加上行锁。如果查询的是一个范围，那么该范围内的所有记录行包括每行记录所在的间隙区间范围都会被加锁，即使该行数据还没有被插入。

间隙锁（Gap Lock）

session_1执行update t_user set name ='哈哈' where id>8 and id<18;则其他session无法在这个范围包含的所有行记录以及行记录所在的间隙里插入或修改任何数据

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

临键锁（Next-key Locks）

Next-key Locks是行锁与间隙锁的组合。在间隙锁（8,18）这个范围，实际会找到存在的值，比如id距离这个区间最近的是，3,20；则实际在（3,20]这个范围都处在行锁范围内。

无索引行锁会升级为表锁

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

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

锁定某一行还可以用lock in share mode(共享锁)和for update(排它锁)

结论：

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高，但是在整体并发处理能力方面要远远优于MYISAM的表级锁定。

但是，Innodb的行级锁定同样有其脆弱的一面，如使用不当，可能会让整体的性能更差。

行锁分析

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

比较重要的主要有：

Innodb_row_lock_time_avg（等待平均时长）

Innodb_row_lock_waits（等待总次数）

Innodb_row_lock_time（等待总时长）

当等待次数很高，且每次等待时长也不小的时候，就需要分析系统中为什么会有如此多的等待，根据分析结果制定优化计划。

死锁

set session transaction isolation level repeatable read;
 
start transaction;
 
select * from t_user where id = 2 for update;
select * from t_user where id = 1 for update;

事务A先锁定id=1,再锁定id=2;事务B顺序相反，出现死锁，结果如下：

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

查看近期死锁日志信息：