【MySQL必知必会】管理事务处理

什么是事务处理

事务处理是为了维护数据的完整性的技术手段，是对数据库数据安全的一种保证。它能确保成批的MySQL语句要么完全执行要么完全不执行，不会存在执行部分语句而造成该处理的数据没有被处理导致数据的错乱。

举例来说：公司某位员工离职了，除了要删除职员表中的数据还需要删除财务数据表中的相关数据以及其他业务上的数据，客户关系数据等。这些操作都需要全部一起执行，否则会造成很严重的问题。

ACID

事务是DBMS的执行单位。它由有限个数据库操作语句组成。但不是任意的数据库操作序列都能成为事务。一般来说，事务是必须满足4个条件（ACID）

原子性（Atomicity）
原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。

一致性（Consistency）
一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致的状态。

在事务T开始时，此时数据库有一种状态，这个状态是所有的MySQL对象处于一致的状态，例如数据库完整性约束正确，日志状态一致等，当事务T提交后，这时数据库又有了一个新的状态，不同的数据，不同的索引，不同的日志等，但此时，约束，数据，索引，日志等MySQL各种对象还是要保持一致性（正确性）。这就是从一个一致性的状态，变到另一个一致性的状态。也就是事务执行后，并没有破坏数据库的完整性约束（一切都是对的）
来自知乎

隔离性(Isolation)
隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离

即要达到这么一种效果：对于任意两个并发的事务T1和T2，在事务T1看来，T2要么在T1开始之前就已经结束，要么在T1结束之后才开始，这样每个事务都感觉不到有其他事务在并发地执行。

持久性(Durability)
持久性是指一个事务一旦被提交了，那么对于数据库中的数据改变就是永久性的，即便是在数据库系统遭遇到故障的情况下也不会丢失提交事务的操作。

例如我们在使用JDBC操作数据库时，在提交事务方法后，提示用户事务操作完成，当我们程序执行完成直到看到提示后，就可以认定事务以及正确提交，即使这时候数据库出现了问题，也必须要将我们的事务完全执行完成，否则就会造成我们看到提示事务处理完毕，但是数据库因为故障而没有执行事务的重大错误。

支持事务处理的引擎

目前只有InnoDB支持事务处理，而MyISAM不支持

MySQL中使用事务

提交事务

>set autocommit = 0 禁止自动提交
>start transaction;
>select * FROM students;
>commit;

+------+-----------+------------+------+
| s_id | name      | birth      | sex  |
+------+-----------+------------+------+
|    1 | 赵雷      | 1990-01-01 |    0 |
|    2 | 赵三      | 1990-02-02 |    0 |
|    3 | 赵二      | 1990-02-03 |    0 |
|    4 | 王菊      | 1989-12-03 |    1 |
|    5 | 陈百祥    | 1992-10-03 |    1 |
|    6 | 王志鹏    | 1991-05-12 |    0 |
|    7 | 欧阳      | 1991-07-11 |    1 |
|    8 | 王兴      | 1989-04-27 |    0 |
+------+-----------+------------+------+
8 rows in set (0.00 sec)

回退事务

start transaction;
delete from student; //删除表中数据
select * from student;
rollback;  //回滚
select * from student;

这里显式地展示了回退命令，事务的回退特性只能在事务处理内使用。通常在事务过程中某行mysql语句出现崩溃会导致整个事务取消提交，这个时候之前执行的语句将会被自动回退，当然create和drop语句将不会被回退，所以千万不要在事务中使用drop语句。当然我们也可以写好条件来回退。

使用保留点

如果在事务过程中发生错误，我们不想在自动回退的时候撤销的某部分语句这个时候可以使用保留点。这个功能可以理解为存档，这样可以不用rollback到起点而是rollback savepoint。

创建保留点占位符

savepoint delete1;

回退到保留点

ROLLBACK TO delete1;

事务的隔离性

由于事务的原子性，当多个线程都启用了事务的时候数据库系统必须进行隔离操作以确保各个线程的数据准确性。

如果没有隔离，会出现以下几种情况：

脏读（Dirty Read）
一个事务处理过程里读取了另一个未提交的事务中的数据

不可重复读（NonRepeatable Read）
对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询的间隔期间，另外一个事务修改并提交了该数据

不可重复读和脏读的区别是，脏读是某一事务读取了另一个事务未提交的脏数据，而不可重复读则是读取了前一事务提交的数据。
　　在某些情况下，不可重复读并不是问题，比如我们多次查询某个数据当然以最后查询得到的结果为主。但在另一些情况下就有可能发生问题，例如对于同一个数据A和B依次查询就可能不同，A和B就可能打起来了……

幻读（Phantom Read）
在一个事务中读取到了别的事务插入的数据，导致前后不一致

总结来说就是多个线程中如果没有做事务隔离，那么不管其中一个事务有没有提交。修改或者插入数据都会影响到另外一条事务。

READ-UNCOMMITTED 读未提交—脏读
顾名思义就是会读取未提交事务的数据，此现象也称为脏读。以下面这个例子来说明：

准备两个终端，终端A和终端B并且调整隔离级别为READ-UNCOMMITTED,任意一终端执行:

mysql> SET @@session.transaction_isolation = 'READ-UNCOMMITTED';
mysql> update teacher set name="赵老师" where teacher_number=2;
mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 赵老师    |
|              3 | 刘老师    |
+----------------+-----------+

登录终端B, 开启事务后查看表中数据:

mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 赵老师    |
|              3 | 刘老师    |
+----------------+-----------+

此时终端A的事务并没有提交但是终端B却读取到了该数据，即脏读。

READ-COMMITTED 读已提交（不可重复读）
一个事务可以读取另一个已提交的事务，多次读取会造成不同的结果，这种现象称为不可重复读。

同样准备两个终端，调整隔离级别为READ-COMMITTED:

mysql> SET @@session.transaction_isolation = 'READ-COMMITTED';
mysql> begin;
mysql> update teacher set name="李老师" where teacher_number=2;
mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 李老师    |
|              3 | 刘老师    |
+----------------+-----------+

此时在终端A中看到数据更改为‘李老师’，切换到终端B,查询数据为未更改之前：

select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 赵老师    |
|              3 | 刘老师    |
+----------------+-----------+

接着登录终端A去提交：

commit;

切换到终端B,一定要加begin开启事务

mysql> begin;
mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 李老师    |
|              3 | 刘老师    |
+----------------+-----------+

终端B在开启事务后，第一次未能查询到最新的数据，在终端A中提交事务后才能读取到。这也说明了在此隔离级别下能查询到已提交的事务。

REPEATABLE-READ 可重复读
这个是MySQL的默认隔离级别，在同一个事务里，select的结果是事务开始时的状态。因此，同样的select操作读到的结果是一致的，但是会有幻读效果。
但是MySQL 的 InnoDB 引擎可以通过 next-key locks机制来避免幻读。

同样准备两个终端，设定隔离级别为REPEATABLE-READ并且开启事务。

mysql> SET @@session.transaction_isolation = 'REPEATABLE-READ';
mysql> begin;
mysql> select * from teacher;

+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师     |
|              2 | 蔡老师     |
|              3 | 刘老师     |
+----------------+-----------+

在另外一个终端也同样开启事务：

mysql> begin;
mysql> select * from teacher;

+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师     |
|              2 | 蔡老师     |
|              3 | 刘老师     |
+----------------+-----------+

在终端A中修改数据并提交：

mysql> update teacher set name "张老师" where teacher_number=2;
mysql> commit;

登录终端B查询数据：

mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师     |
|              2 | 蔡老师     |
|              3 | 刘老师     |
+----------------+-----------+

通过这一步可以证明，在该隔离级别下已经读取不到别的已提交的事务，如果想看到 mysql 终端 A 提交的事务，在 mysql 终端 B 将当前事务提交后或者再重新开启事务然后查询就可以读取到 mysql 终端 A 提交的事务。我们接着实验，看看在该隔离级别下是否会存在别的问题。

同样的情况不是发生在修改数据而是插入数据的时候,会遇到主键冲突的错误。

insert into teacher(teacher_number, name) values(4, '龚老师');
Lock wait timeout exceeded; try restarting transaction

mysql> select * from teacher;
+----------------+-----------+
| teacher_number | name      |
+----------------+-----------+
|              1 | 王老师    |
|              2 | 金老师    |
|              3 | 刘老师    |
+----------------+-----------+
3 rows in set (0.00 sec)

此时在终端B中明明没有查询到第4条数据为什么会出现主键冲突呢？这个就是在可重复读隔离级别下可能产生的幻读。这种情况只在MySQL中出现，Oracle则不会。

序列化 SERIALIZABLE
在该隔离级别下事务都是串行顺序执行的，MySQL数据库的InnoDB引擎会给读操作隐式加一把读共享锁，从而避免了脏读、不可重读复读和幻读问题。

准备两个终端，设置隔离级别为SERIALIZABLE,开启事务并且插入数据

SET @@session.transaction_isolation = 'SERIALIZABLE';
mysql> begin;
mysql> insert into teacher(teacher_number,name) values(4, '李老师');

先不提交，登录终端B打开事务，查询数据:

mysql> begin;
mysql> select * from teacher;
Lock wait timeout exceeded; try restarting transaction

会发现事务会被锁住，出现这种情况就说明我们的事务没有提交导致，切换到终端A去提交：

commit;

一旦事务提交后，终端B会立即解锁返回相应的数据。否则会一直卡住，直到超时，其中超时参数是由innodb_lock_wait_timeout控制。由于每条select语句都会加锁，所以该隔离级别的数据库并发能力最弱，但是有些资料表明该结论也不一定对。

loannes's blog