【mysql】关于事务的隔离级别

一、锁的种类

MySQL中锁的种类很多，有常见的表锁和行锁，也有新加入的Metadata Lock等等,表锁是对一整张表加锁，虽然可分为读锁和写锁，但毕竟是锁住整张表，会导致并发能力下降，一般是做ddl处理时使用。

行锁则是锁住数据行，这种加锁方法比较复杂，但是由于只锁住有限的数据，对于其它数据不加限制，所以并发能力强，MySQL一般都是用行锁来处理并发事务

二、锁粒度

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念

一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可

但是，加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁和是否已经解除、释放锁等，都会增加系统的开销。所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡

表锁：管理锁的开销最小，同时允许的并发量也最小的锁机制。MyIsam存储引擎使用的锁机制。当要写入数据时，把整个表都锁上，此时其他读、写动作一律等待。除了MyIsam存储引擎使用这种锁策略外，MySql本身也使用表锁来执行某些特定动作，比如alter table。另外，写锁比读锁有更高的优先级，因此一个写锁可能会被插入到读锁队列的前面。

行锁：可以支持最大并发的锁策略（同时也带来了最大的锁开销）。InnoDB和Falcon两种存储引擎都采用这种策略。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。MySql是一种开放的架构，你可以实现自己的存储引擎，并实现自己的锁粒度策略，不像Oracle，你没有机会改变锁策略，Oracle采用的是行锁。

三、死锁

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的假象。多个事务同时锁定同一个资源时，也会产生死锁。数据库系统实现了各种死锁检测和死锁超时的机制，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚

四、事务ACID原则

从业务角度出发，对数据库的一组操作要求保持4个特征：

Atomicity（原子性）：一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性
Consistency（一致性）：数据库总是从一个一致性状态转换到另一个一致状态。下面的银行列子会说到
Isolation（隔离性）：通常来说，一个事务所做的修改在最终提交以前，对其他事务是不可见的
Durability（持久性）：一旦事务提交，则其所做的修改就会永久保存到数据库中。此时即使系统崩溃，修改的数据也不会丢失。（持久性的安全性与刷新日志级别也存在一定关系，不同的级别对应不同的数据安全级别。）

为了更好地理解ACID，以银行账户转账为例：

BEGIN;

SELECT balance FROM checking WHERE customer_id = 10233276;

UPDATE checking SET balance = balance - 200.00 WHERE customer_id = 10233276;

UPDATE savings SET balance = balance + 200.00 WHERE customer_id = 10233276;

COMMIT;

原子性：要么完全提交（10233276的checking余额减少200，savings 的余额增加200），要么完全回滚（两个表的余额都不发生变化）

一致性：这个例子的一致性体现在 200元不会因为数据库系统运行到第3行之后，第4行之前时崩溃而不翼而飞，因为事物还没有提交

隔离性：允许在一个事务中的操作语句会与其他事务的语句隔离开，比如事务A运行到第3行之后，第4行之前，此时事务B去查询checking余额时，它仍然能够看到在事务A中被减去的200元（账户钱不变），因为事务A和B是彼此隔离的。在事务A提交之前，事务B观察不到数据的改变

五、并发问题可归纳为以下几类

1、丢失更新

撤销一个事务时，把其他事务已提交的更新数据覆盖

例子：A和B事务并发执行，A事务执行更新后，提交；B事务在A事务更新后，B事务结束前也做了对该行数据的更新操作，然后回滚，则两次更新操作都丢失了

2、脏读

一个事务读到另一个事务未提交的更新数据

例子：A和B事务并发执行，B事务执行更新后，A事务查询B事务没有提交的数据，B事务回滚，则A事务得到的数据不是数据库中的真实数据。也就是脏数据，即和数据库中不一致的数据

3、不可重复读

一个事务读到另一个事务已提交的更新数据

例子：A和B事务并发执行，A事务查询数据，然后B事务更新该数据，A再次查询该数据时，发现该数据变化了

4、覆盖更新

这是不可重复读中的特例，一个事务覆盖另一个事务已提交的更新数据

例子：A事务更新数据，然后B事务更新该数据，A事务查询发现自己更新的数据变了

5、虚读（幻读）

一个事务读到另一个事务已提交的新插入的数据

例子：A和B事务并发执行，A事务查询数据，B事务插入或者删除数据，A事务再次查询发现结果集中有以前没有的数据或者以前有的数据消失了

六、隔离级别

1、SERIALIZABLE（序列化）

一个事务在执行过程中完全看不到其他事务对数据库所做的更新，事务执行的时候不允许别的事务并发执行。完全串行化执行，只能一个接着一个地执行，每次读都需要获得表级共享锁，读写相互都会阻塞

2、REPEATABLE READ（可重复读）

一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他其他事务对已有记录的更新

对于读出的记录，添加共享锁直到transaction A结束。其它transaction B对这个记录的试图修改会一直等待直到transaction A结束

在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻读

3、READ COMMITTED（提交读）

一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新

在transaction A中读取数据时对记录添加共享锁，但读取结束立即释放。其它transaction B对这个记录的试图修改会一直等待直到A中的读取过程结束，而不需要整个transaction A的结束。所以，在transaction A的不同阶段对同一记录的读取结果可能是不同的。

可能发生的问题：不可重复读

4、READ UNCOMMITTED(未提交读)

一个事务在执行过程中可以看到其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新

不添加共享锁。所以其它transaction B可以在transaction A对记录的读取过程中修改同一记录，可能会导致A读取的数据是一个被破坏的或者说不完整不正确的数据。

另外，在transaction A中可以读取到transaction B（未提交）中修改的数据。比如transaction B对R记录修改了，但未提交。此时，在transaction A中读取R记录，读出的是被B修改过的数据。

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	可能
可串行化（Serializable ）	不可能	不可能	不可能

由于MySQL的InnoDB默认是使用的RR级别，所以我们先要将该session开启成RC级别，并且设置binlog的模式

mysql> select @@session.tx_isolation;

+-----------------------+

| @@global.tx_isolation |

+-----------------------+

| REPEATABLE-READ       |

+-----------------------+

SET sessionbinlog_format = 'ROW'; //MIXED

表结构

CREATE TABLE `users` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `name` varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL,

  `age` tinyint(11) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `id_age` (`age`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO users VALUES \

( 1 , 'Bob' , 27 ), \

( 2 , 'Mike' , 7 ),\

( 3 , 'Tony' , 40 ),\

( 4 , 'Bill' , 21 ),\

( 5 , 'Mark' , 18 );

幻读

SET session transaction isolation level  Repeatable read;

幻读发生在当两个完全相同的查询执行时，第二次查询所返回的结果集跟第一个查询不相同。发生的情况：没有范围锁

事务1	事务2
SELECT * FROM users WHERE age BETWEEN 10 AND 30
	INSERT INTO users VALUES ( 3 , 'Bob' , 27 );
SELECT * FROM users WHERE age BETWEEN 10 AND 30;

如何避免：实行序列化隔离模式，在任何一个低级别的隔离中都可能会发生。

不可重复读

SET session transaction isolation level read committed;

在基于锁的并行控制方法中，如果在执行select时不添加读锁，就会发生不可重复读问题。在多版本并行控制机制中，当一个遇到提交冲突的事务需要回退但却被释放时，会发生不可重复读问题。

事务1	事务2
SELECT * FROM users WHERE id = 1;
	UPDATE users SET age = 21 WHERE id = 1 ;
SELECT * FROM users WHERE id = 1;

在上面这个例子中，事务2提交成功，它所做的修改已经可见。然而，事务1已经读取了一个其它的值。在序列化和可重复读的隔离级别中，数据库管理系统会返回旧值，即在被事务2修改之前的值。在提交读和未提交读隔离级别下，可能会返回被更新的值，这就是“不可重复读”。

有两个策略可以防止这个问题的发生：

1. 推迟事务2的执行，直至事务1提交或者回退。这种策略在使用锁时应用。(悲观锁机制，比如用select for update为数据行加上一个排他锁)

2. 而在多版本并行控制中，事务2可以被先提交。而事务1，继续执行在旧版本的数据上。当事务1终于尝试提交时，数据库会检验它的结果是否和事务1、事务2顺序执行时一样。如果是，则事务1提交成功。如果不是，事务1会被回退。（乐观锁机制）

脏读

SET session transaction isolation level read uncommitted;

脏读发生在一个事务A读取了被另一个事务B修改，但是还未提交的数据。假如B回退，则事务A读取的是无效的数据。这跟不可重复读类似，但是第二个事务不需要执行提交。

事务1	事务2
SELECT * FROM users WHERE id = 1；
	UPDATE users SET age = 21 WHERE id = 1
SELECT FROM users WHERE id = 1；

7、隔离级别vs 锁持续时间

在基于锁的并发控制中，隔离级别决定了锁的持有时间。"C"-表示锁会持续到事务提交。 "S" –表示锁持续到当前语句执行完毕。如果锁在语句执行完毕就释放则另外一个事务就可以在这个事务提交前修改锁定的数据，从而造成混乱

隔离级别l	写操作	读操作	范围操作 (...where...)
未提交读	S	S	S
提交读	C	S	S
可重复读	C	C	S
可序列化	C	C	C

参考文章

https://zh.wikipedia.org/wiki/%E4%BA%8B%E5%8B%99%E9%9A%94%E9%9B%A2

秒客网