mysql脏读、不可重复读、幻读

1. 脏读(侧重点在读取了未提交的数据)

指一个事务读取到了另一个事务未提交的数据，造成了与数据库中的数据不一致的情况。

比如事务A修改了一条数据，但没有提交，此时事务B却读取了该条数据，事务A由于出错发生了回滚，这时事务B就形成了脏读。

 

    2. 不可重复读(虚读)(侧重点在读取了已经提交的修改的数据，数据本身对比)

指一个线程中的事务读取到了另外一个线程中的事务已提交的update的数据。即一个事务A两次或多次读取数据，在此期间，事务B读取同一数据，并修改了此数据，然后提交了，造成了事务A两次或多次读取数据出现了数据不一致的情况。

不可重复读可能造成数据丢失：

第一种丢失更新：

第一类丢失更新就是两个事务同时更新一个数据，一个事务更新完毕并提交后，另一个事务回滚，造成提交的更新丢失。

 

事务A|事务B
-|-
开始事务|开始事务
读取N=5|读取N=5
修改N=8|修改N=9
 |提交,结束事务
回滚|
结束事务(N=5)|N=5，N本应为提交的9

 

第二类丢失更新：

第二类丢失更新就是两个事务同时更新一个数据，先更新的事务提交的数据会被后更新的事务提交的数据覆盖，即先更新的事务提交的数据丢失。

 

事务A|事务B

-|-

开始事务|开始事务

读取M=8|读取M=8

更新M=1|

提交事务,M=1,结束|

 |更新M=7

   |提交事务,M=7,事务A的更新丢失,结束

 

  3. 幻读(侧重点在读取到了新增或删除的数据，数据条数对比)

指一个线程中的事务读取到了另外一个线程中事务已提交的insert的数据。即一个事务A两次或多次读取数据，在此期间，事务B新增了N条数据，然后提交了，造成了事务A两次或多次读取数据出现了数据条数不一致的情况。

 

 

 

4．隔离级别-------级别越高，数据越安全，但性能越低

读数据一致性及允许的并   发副作用 隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读(Read uncommitted)	最低级别，只能保证不读取物理上损坏的数据	可能	可能	可能
已提交读(Read committed)	语句级	不可能	可能	可能
可重复读(Repeatable read)	事务级	不可能	不可能	可能
可串行化(Serializable)	最高级别，事务级	不可能	不可能	不可能

 

MySQL 默认的级别是:Repeatable read 可重复读，其他主流数据库，如Oracle；SQLServer默认级别为：Read committed

 

     1) 未提交读：写事务阻止其他写事务，避免了更新遗失。但是没有阻止其他读事务。

存在的问题：脏读。即读取到不一致的数据，因为另一个事务可能还没提交最终数据，这个读事务就读取了中途的数据，这个数据可能是不正确的。

解决办法：已提交读

     2) 已提交读：写事务会阻止其他读写事务。读事务不会阻止其他任何事务。

存在的问题：不可重复读。即在一次事务之间，进行了两次读取，但是结果不一致，可能第一次id为1的人叫“张三”，第二次读id为1的人就叫“王五”了。因为读取操作不会阻止其他事务。

解决办法：可重复读

     3) 可重复读

读事务会阻止其他写事务，但是不会阻止其他读事务。

存在的问题：幻读。可重复读阻止的写事务包括update和delete（只给存在的行加上了锁），但是不包括insert（新行不存在，所以没有办法加锁），所以一个事务第一次读取可能读取到了10条记录，但是第二次可能读取到11条，这就是幻读。

解决办法：串行化

     4) 可串行化

可避免幻读。读加共享锁，写加排他锁。这样读取事务可以并发，但是读写，写写事务之间都是互斥的，基本上就是一个个执行事务，所以叫串行化。

 

5. 封锁协议

封锁协议就是在用X锁或S锁时制定的一些规则，比如锁的持续时间,锁的加锁时间等。不同的封锁协议对应不同的隔离级别。事务的隔离级别一共有4种，由低到高分别是Read uncommitted、Read committed、Repeatable read、Serializable，分别对应的相应的封锁协议等级。

     1) 一级封锁协议

一级封锁协议对应的是Read uncommitted隔离级别，Read uncommitted读未提交，一个事务可以读取另一个事务未提交的数据，这是最低的级别。一级封锁协议本质上是在事务修改数据之前加上X锁，直到事务结束后才释放，事务结束包括正常结束(commit)与非正常结束(rollback)。

 

      2) 二级封锁协议

二级封锁协议本质上在一级协议的基础上(在修改数据时加X锁)，在读数据时加上S锁，读完后立即释放S锁，可以避免脏读。但有可能出现不可重复读与幻读。二级封锁协议对应的是Read committed与Repeatable Read隔离级别。

 

      3) 三级封锁协议

三级封锁协议，在一级封锁协议的基础上(修改时加X锁),读数据时加上S锁(与二级类似)，但是直到事务结束后才释放S锁，可以避免幻读，脏读与不可重复读。三级封锁协议对应的隔离级别是Serializable。

 

 

6. 锁机制

        1) 共享/排它锁(Shared and Exclusive Locks)

共享锁（Share Locks，记为S锁），读取数据时加S锁，共享锁之间不互斥，即读读可以并行。

排他锁（exclusive Locks，记为X锁），修改数据时加X锁，排他锁与任何锁互斥，即写读，写写不可以并行。

一旦写数据的任务没有完成，数据是不能被其他任务读取的，这对并发度有较大的影响。对应到数据库，可以理解为，写事务没有提交，读相关数据的select也会被阻塞，这里的select是指加了锁的，普通的select仍然可以读到数据(快照读)。

 

        2) 意向锁(Intention Locks)

InnoDB为了支持多粒度锁机制(multiple granularity locking)，即允许行级锁与表级锁共存，而引入了意向锁(intention locks)。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。

意向锁是一个表级别的锁(table-level locking)；

意向锁又分为：

意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁；

意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁；

   加锁的语法为：

select ... lock in share mode;　　要设置IS锁；

select ... for update;　　　　　　 要设置IX锁；

事务要获得某些行的S/X锁，必须先获得表对应的IS/IX锁，意向锁仅仅表明意向，意向锁之间相互兼容，兼容互斥表如下：

	IS	IX
IS	兼 容	兼 容
IX	兼 容	兼 容

 

 

 

 

虽然意向锁之间互相兼容，但是它与共享锁/排它锁互斥，其兼容互斥表如下:

	S	X
IS	兼 容	互 斥
IX	互 斥	互 斥

 

 

 

 

排它锁是很强的锁，不与其他类型的锁兼容。这其实很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。

        3) 记录锁(Record Locks)-----锁定某行

对单条索引记录进行加锁，锁住的是索引记录而非记录本身，即使表中没有任何索引，MySQL会自动创建一个隐式的row_id作为聚集索引来进行加锁。记录锁就是为某行记录加锁，它封锁该行的索引记录。查询条件必须为主键列或唯一索引，否则锁会变成临建锁。同时查询语句为精准匹配(=)，不能为>、<、like等，否则也会退化成临建锁。事务隔离级别是RR。

 

        4) 间隙锁(Gap Locks) -----锁定某个区间

间隙锁，它封锁索引记录中的一段间隔范围，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。

存储引擎是InnoDB，事务隔离级别是RR。

间隙锁的主要目的，就是为了防止其他事务在间隔中插入数据，以导致“不可重复读”。如果把事务的隔离级别降级为已提交读(Read Committed, RC)，间隙锁则会自动失效。例如：

Select * from test where id between 1 and 8 for update;

SQL语句会封锁区间(1,8)，以阻止其他事务插入id位于该区间的记录。

 

        5) 临键锁(Next-key Locks) -----锁定左开右闭的一段区间

临键锁，是记录锁与间隙锁的组合，它的封锁范围，既包含索引记录，又包含索引区间。

临键锁的主要目的，也是为了避免幻读。

如果把事务的隔离级别降级为RC，临键锁就也会失效。

每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

假设有如下表：
MySql，InnoDB，Repeatable-Read：table(id PK, age KEY, name)

id	age	name
1	10	Lee
3	24	Soraka
5	32	Zed
7	45	Talon

该表中 age 列潜在的临键锁有：
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],

 

在根据非唯一索引 对记录行进行 UPDATE \ FOR UPDATE \ LOCK IN SHARE MODE 操作时，InnoDB 会获取该记录行的 临键锁 ，并同时获取该记录行下一个区间的间隙锁。

 

InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。

记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。

间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。

临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

 

      6) 插入意向锁(Insert Intention Locks)

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

目的是提高插入并发。

       7) 自增锁(Auto-inc Locks)

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。