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

 

隔级别	脏读	不可重复读	幻读
未提交读	可能	可能	可能
读已提交	不可能	可能	可能
可重复读	不可能	不可能	可能
串行	不可能	不可能	不可能

可重复读与幻读区别：不可重复读的重点是修改；同样的条件，第1次和第2次读取的值不一样。幻读的重点在于新增或者删除；同样的条件， 第1次和第2次读出来的记录数不一样。从控制角度来看，不可重复读只需要锁住满足条件的记录，幻读要锁住满足条件及其相近的记录。

避免可重复读：通常可以用 set session transaction  isolation level repeatable read 来设置隔离级别，这样事务A 在两次读取表T中的数据时，事务B如果企图更改表T中的数据（细节到事务A读取数据）时，就会被阻塞，知道事务A提交！ 这样就保证了，事务A两次读取的数据的一致性。

查看以及修改事务隔离级别：

查看事务隔离级别
show variables like 'transaction_isolation';
或者select @@transaction_isolation;
读未提交：
set transaction isolation level read uncommitted;
可重复读：
set session transaction  isolation level repeatable read

MySQL下的锁：

锁是网络数据库中的一个非常重要的概念，当多个用户同时对数据库并发操作时，会带来数据不一致的问题，所以，锁主要用于多用户环境下保证数据库完整性和一致性。

从程序员角度分为两种：一种是悲观锁，一种乐观锁。

从数据库系统角度分为三种：排他锁、共享锁、更新锁。

悲观锁按使用性质划分：共享锁（读锁，S锁），排它锁（写锁，X锁）

悲观锁按使用范围（锁的粒度）划分：表锁，页锁，行锁。

补充：

行锁 :锁的作用范围是行级别。表锁: 锁的作用范围是整张表。页锁: 介于两者之间

InnoDB ：支持行锁和表锁（实际上InnoDB 是通过给索引项加锁，来实现行锁的）   Smicar ：只支持表锁

锁的MySQL语句：

表锁：
读锁：lock tables t1 read;
写锁：lock tables t1 write;
解锁：unlock table;


行锁：
共享锁(s)：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁(x)：SELECT * FROM table_name WHERE ... FOR UPDATE
解锁：
1.查询 正在执行的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

2.杀死进程id（就是上面查询结果集的trx_mysql_thread_id列）
kill 线程ID
这样行锁锁定就解决了

查询mysql数据库中还可以使用:
查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
查询mysql数据库中存在的进程
select * from information_schema.PROCESSLIST(show processlist;)

在默认的可重复读隔离级别下：

执行查询语句（ SELECT ）前，由于 MVCC（多版本控制）的方式，什么锁都不会加。

在执行更新操作（ UPDATE、DELETE、INSERT 等）前，会自动给涉及的行加写锁，此时会阻塞其他用户的写操作，但是通过 MVCC（多版本控制）的方式允许读操作。

innodb下的记录锁（也叫行锁），间隙锁，next-key锁统统属于排他锁

MySQL InnoDB支持三种行锁定方式：InnoDB的默认加锁方式是next-key 锁。

行锁：记录锁其实很好理解，对表中的记录加锁，叫做记录锁，简称行锁。

next-key锁：其实包含了记录锁和间隙锁，即锁定一个范围，并且锁定记录本身，InnoDB默认加锁方式是next-key 锁。

MySQL InnoDB支持三种行锁定方式：InnoDB的默认加锁方式是next-key 锁。

1：行锁（Record Lock）:锁直接加在索引记录上面，锁住的是key。

2：间隙锁（Gap Lock）:锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。

3：Next-Key Lock ：行锁和间隙锁组合起来就叫Next-Key Lock。 

当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。Gap Lock在InnoDB的唯一作用就是防止其他事务的插入操作，以此防止幻读的发生。

Innodb自动使用间隙锁的条件：

（1）必须在Repeatable Read级别下

（2）检索条件必须有索引（没有索引的话，mysql会全表扫描，那样会锁定整张表所有的记录，包括不存在的记录，此时其他事务不能修改不能删除不能添加） 

间隙锁的目的是为了防止幻读，其主要通过两个方面实现这个目的：

（1）防止间隙内有新数据被插入

（2）防止已存在的数据，更新成间隙内的数据（例如防止numer=3的记录通过update变成number=5）

总结;

间隙锁在InnoDB的唯一作用就是防止其它事务的插入操作，以此来达到防止幻读的发生，所以间隙锁不分什么共享锁与排它锁。 默认情况下，InnoDB工作在Repeatable Read隔离级别下，并且以Next-Key Lock的方式对数据行进行加锁，这样可以有效防止幻读的发生。Next-Key Lock是行锁与间隙锁的组合，当对数据进行条件，范围检索时，对其范围内也许并存在的值进行加锁！当查询的索引含有唯一属性（唯一索引，主键索引）时，Innodb存储引擎会对next-key lock进行优化，将其降为record lock,即仅锁住索引本身，而不是范围！若是普通辅助索引，则会使用传统的next-key lock进行范围锁定！

要禁止间隙锁的话，可以把隔离级别降为Read Committed，或者开启参数innodb_locks_unsafe_for_binlog。