MySQL--不同隔离级别下的事务并发问题

一、什么是事务
1、概念
在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序。
2、特性
（1）事务是恢复和并发控制的基本单位。
（2）MySQL中只有使用了InnoDB据库引擎的数据库或表才支持事务。
（3）事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
（4）事务用来管理insert、delete、update语句
3、满足条件
一般来说，事务必须满足4个条件：原子性（Atomicity，不可分割性）、一致性（Consistency）、隔离性（Isolation，独立性）、持久性（Durability）。
（1）原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
（2）一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
（3）隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable）。
（4）持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在MySQL命令行的默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一个事务务须使用命令BEGIN或START TRANSACTION，或者执行命令SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
4、MySQL的Buffer Pool机制
（1）操作系统会有缓冲池Buffer Pool机制，避免每次访问磁盘，以加速数据的访问。MySQL同样具有Buffer Pool机制，以避免每次查询数据都进行磁盘IO。
（2）InnoDB作为MySQL的存储引擎，数据是存放在磁盘中的，如果每次读写数据都需要磁盘IO，效率会很低，因此有了缓存Buffer Pool。Buffer Pool中包含了磁盘中部分数据页的映射，作为访问数据库的缓冲。当从数据库读取数据时，会先从Buffer Pool中读取，如果Buffer Pool中没有，则从磁盘读取后放入Buffer Pool；当向数据库写入数据时，先写入Buffer Pool，Buffer Pool中修改的数据会定期刷新到磁盘中（刷脏）。
（3）Buffer Pool提高了读写数据的效率，也带了新的问题，如果MySQL宕机，而此时Buffer Pool中修改的数据还没有刷新到磁盘，就会导致数据的丢失，事务的持久性无法保证，所以要使用MySQL中的日志来保证数据的持久性。
（4）持久性实现原理——redo log日志
当数据修改时，除了修改Buffer Pool中的数据，还会在redo log记录这次操作。
当事务提交时，会调用fsync接口对redo log进行刷盘，如果MySQL宕机，重启时可以读取redo log中的数据，对数据库进行恢复。
redo log采用WAL（Write-ahead logging，预写式日志），所有修改先写入日志，再更新到Buffer Pool，保证了数据不会因MySQL宕机而丢失，从而满足了持久性要求。
（5）原子性实现原理——undo log日志
原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。
InnoDB实现回滚靠undo log，当事务对数据库进行修改时，InnoDB会生成对应的undo log，如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
undo log属于逻辑日志，记录了sql执行相关的信息，当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作（对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去）。以update操作为例，当事务执行update时，其生成的undo log中会包含被修改行的主键，以便知道修改了哪些行、列、及其在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。
（6）隔离性的实现原理——锁机制+MVCC机制
①Record Locks记录锁：在索引记录上加锁。
②Gap Locks间隙锁：在索引记录之间加锁，或者在第一个索引记录之前加锁，或者在最后一个索引记录之后加锁。
③Next-Key Locks：在索引记录上加锁，并且在索引记录之前的间隙加锁。它相当于 是Record Locks与Gap Locks的一个结合
④共享读锁和排他写锁
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁，如：select。
排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的，如：update、insert、delete。
（读读不互斥，写写互斥，读写互斥）
⑤行锁和表锁：
表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率高，并发度低。
行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。
⑥MVCC机制：
Multi-Version Concurrency Control（多版本并发控制），是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问；在编程语言中实现事务内存。可将MVCC看成行级别锁的一种妥协，它在许多情况下避免了使用锁，同时可以提供更小的开销。根据实现的不同，它可以允许非阻塞式读，在写操作进行时只锁定必要的记录。即尽量的不去使用这些锁，因为加上这些锁之后，事务与事务之间变成了完全的串行执行。
MVCC通过保存数据在某一个时间点的快照来实现，每一个事务只操作自己版本下的数据。因此每一个事务无论执行多长时间看到的数据，都是一样的，所以MVCC实现可重复读。
InnoDB的一致性的非锁定读就是通过在MVCC实现的，MySQL的大多数事务型存储引擎实现的都不是简单的行级锁。基于提升并发性能的考虑，它们一般都实现了MVCC。
5、控制语句
（1）BEGIN或START TRANSACTION：显式地开启一个事务；
（2）COMMIT：提交事务，并使已对数据库进行的所有修改成为永久性的；
（3）ROLLBACK：结束用户的事务，并撤销正在进行的所有未提交的修改；
（4）SAVEPOINT：允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
（5）RELEASE SAVEPOINT：删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
（6）ROLLBACK TO：把事务回滚到标记点；
（7）SET TRANSACTION：设置事务的隔离级别。InnoDB存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
6、MySQL事务处理的两种方法
（1）用BEGIN、ROLLBACK、COMMIT来实现。BEGIN开始一个事务，ROLLBACK事务回滚，COMMIT事务确认。
（2）直接用 SET 来改变 MySQL的自动提交模式。SET AUTOCOMMIT=0禁止自动提交，SET AUTOCOMMIT=1开启自动提交。
二、关于事务的隔离性
1、隔离性一共分了4种隔离级别：未提交读，已提交读，可重复读，和序列化
（1）未提交读（Read uncommitted）——什么都没做
特点：任何操作都不会加锁，数据库一般不会用。
（2）已提交读（Read committed）——没有加间隙锁
特点：数据的读取不加锁，数据的写入、修改和删除加锁（Record Locks）；通过MVCC机制解决脏读问题，只要没有COMMIT那么修改的就是自己版本下的数据。
（3）可重复读（Repeatable read）——没有加间隙锁
特点：数据的读取不加锁，数据的写入、修改和删除加锁（Record Locks）+MVCC机制；即使COMMIT操作的也是自己版本下的数据。
（4）序列化（Serializable）——所有锁都加
特点：没有MVCC机制；效率最低；读加共享锁，写加排他锁，读写互斥；实现简单、数据安全、并发能力差，如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。由于InnoDB引擎加的是行锁，并且是加在索引记录上，所以为了不产生幻读在还会加间隙锁。
注：完全加锁处理使并发变成串行。
2、示例
建立一个含有money和name属性的表account，表含有一条name为aaa，money为100的数据。
（1）把事务的隔离级别设置为read uncommitted时会引发脏读、不可重复读和虚读。

A窗口：

set transaction isolation level  read uncommitted;
start transaction;
select * from account;

——查看到aaa对应的账户为100元，转到B窗口

B窗口：

start transaction;
update account set money=money+100 where name='aaa';

——不要提交，转到A窗口

A窗口：

select * from account;

——发现多了100元，说明A窗口读到了B窗口未提交的数据，即发生了脏读
（2）把事务的隔离级别设置为read committed时会引发不可重复读和虚读，但避免了脏读。

A窗口：

set transaction isolation level  read committed;
start transaction;
select * from account;

——发现帐户是100元，转到B窗口

B窗口：

start transaction;
update account set money=money+100 where name='aaa';
commit;

——提交，转到A窗口

A窗口：

select * from account;

——发现帐户多了100，这时候，A窗口读到了别的窗口的事务已提交的数据，且两次读取到不同的结果，即发生了不可重复读
（3）把事务的隔离级别设置为repeatable read时会引发虚读，但避免了脏读、不可重复读（repeatable read是MySQL默认级别）。

A窗口：

set transaction isolation level repeatable read;
start transaction;
select * from account;

——发现表有1个记录，转到B窗口

B窗口：

start transaction;
insert into account(name,money) values('bbb',500);
commit;

——提交，转到A窗口

A窗口：

select * from account;

——发现表多了一条记录，说明A读取到另一个事务插入的数据，即发生了虚读
（4）把事务的隔离级别设置为serializable时，会避免所有问题。

A窗口：

set transaction isolation level serializable;
start transaction;
select * from account;

——转到B窗口

B窗口：

start transaction;
insert into account(name,money) values('ccc',800);

——发现不能插入，只能等待A窗口结束事务才能插入