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

一、锁的种类

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

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

二、锁粒度

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

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

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

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

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

三、死锁

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

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;

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修改过的数据。

由于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;

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

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时不添加读锁，就会发生不可重复读问题。在多版本并行控制机制中，当一个遇到提交冲突的事务需要回退但却被释放时，会发生不可重复读问题。

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读取的是无效的数据。这跟不可重复读类似，但是第二个事务不需要执行提交。 

SELECT * FROM users WHERE id = 1；

UPDATE users SET age = 21 WHERE id = 1

SELECT FROM users WHERE id = 1；

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

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

参考文章

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