MySQL实战11 事物控制

MySQL实战 目录

前言

TCL:Transaction Control Language,事物控制语言

事务:在MySQL数据库中表示一条或多条Sql语句组合在一起的一个执行单元。
这个执行单元要么全部执行，要么全部不执行，否则就会出现逻辑错误！

比如银行里的转账这个事情：
A账号余额：1000
B账号余额：1000
现在A转500元给B，那么完成这个转账的事务，数据中的SQL应该是这样的执行过程:
步骤1：A账号上要减少500元
update 储蓄表 set A.余额=A.余额-500 where 账号名='A';
步骤2：B账号上要增加500元
update 储蓄表 set B.余额=B.余额+500 where 账号名='B';

如果没有事务处理这个功能，上面的情况下，很可能会发生这样的情况：
步骤1执行成功 A的余额变为:500
刚开始执行步骤2的时候，突然出现某系统错误，导致步骤2执行失败！
步骤1成功 步骤2失败：A的钱减少了，B的钱没增加！

所以在类似的场景需求中我们需要事务处理：实现将步骤1和步骤2的SQL语句绑定在一起，要么都执行成功，要么不管是步骤1执行出错还是2出错，数据库里的数据状态会回滚到没有执行任何步骤1或2的SQL语句之前！

1.MySQL数据中的存储引擎

在具体讲事务之前，还是说说MySQL数据中的存储引擎：innoDB
1.什么是存储引擎：在mysql中的数据使用各种不同的技术来存储在磁盘文件（或内存）
当中的，这种具体的存储技术就是我们说的存储引擎。
2.我们可以通过show engines;命令来查看mysql支持的存储引擎。
3.在mysql可以选择的这些存储引擎中，innodb,myisam,memory这三个是最常用的，但其中只有innodb支持事务处理，而其他是不支持事务处理的。

2.事务的ACID特点:

1. 原子性(Atomicity): 组成事务的SQL语句不可再分，要么都执行，要么都不执行。
2. 一致性(Consistency): 事务必须让数据的数据状态变化到另一个一致性的状态，比如：
   刚刚的例子中A和B的余额总和是2000，转账后，A和B的余额总和不能变。前后具有一致性。
3. 隔离性(Isolation): 一个事务的执行，不受其他事务的干扰，相互应该是隔离的，但是实际上是很难做到的，要通过隔离级别做选择！
4. 持久性(Durability): 一个事务被提交，并成功执行，那么它对数据的修改就是永久性的，接下来的其他操作或出现的故障，不能影响到它执行的结果！

3.MySQL的事务的创建：

1.隐视事务：事务没有明显的开始和结束的标记，这时候像insert语句，update语句和delete语句，每一条SQL语句就默认是一个事务。

显然，隐视事务在类似转账的逻辑业务需求的时候，就无法处理了！

2.显示事务：说白了，这个事务模式，就要我们的中程序手动的用命令来开启事务，和结束事务，并让事务里的多余SQL语句去执行。

注意：默认MySQL是开启自动提交事务的，用show variables like ‘autocommit’;命令可以查看到。所以，开启显示事务前，需要 关掉它，用set autocommit=0;只对本身回话有效。

Show ENGINES;

show VARIABLES like 'autocommit';

#@1.开始事务
SET autocommit = 0;
show VARIABLES like 'autocommit';

START TRANSACTION;#可选的，执行set autocommit=0已经默认开启了！

#@2编写事务中的SQL语句(主要是:SELECT UPDATE DELETE INSERT等语句)
  语句1;语句;.......
#@3结束事务
END
    commit;  :提交事务去真正执行 # 或者 rollback;  :回滚事务，恢复数据库执行前等状态!
    

示例：

DROP TABLE IF EXISTS account;

CREATE TABLE account(
  id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(20),
    balance DOUBLE
);

INSERT INTO account(username,balance ) VALUES('A',1000),('B',1000);
    
    START TRANSACTION;
   #实现A账号转帐500元到B账号
     UPDATE account SET balance = 500 WHERE username = 'A';
     UPDATE account SET balance = 1500 WHERE username = 'B';
     ROLLBACK; #事务回滚

     SELECT * FROM account;

        START TRANSACTION;
   #实现A账号转帐500元到B账号
     UPDATE account SET balance = 500 WHERE username = 'A';
     UPDATE account SET balance = 1500 WHERE username = 'B';
     COMMIT; #事务执行

     SELECT * FROM account;

4.运行多事务导致多并发问题

同时运行的多个事务, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制,就会导致各种并发问题 , 比如

4.1 脏读（没有被提交的操作）

对于两个事务 T1,T2, T1 读取了已经被 T2 更新但还没有被提交的字段之后, 若T2 回滚, T1读取的内容就是临时且无效的。T1：张飞女朋友转500元给张飞，但是没有提交事务T1T2:张飞看账户余额500元(开心坏了)然后女朋友撤销500元转账操作（T1回滚），那么张飞看到的500元是临时无效的数据，是脏读的数据。

4.2 不可重复读（在脏读基础之上，更新update操作）

对于两个事务T1, T2, T1读取了一个字段, 然后T2更新了该字段之后, T1再次读取同一个字段, 值就不同了。张飞第一次读账户余额500元张飞第二次读账户余额0元

4.3 幻读（插入insert/删除delete）

对于两个事务T1, T2, T1 从一个表中读取了一个字段, 然后T2在该表中插入了一些新的行之后, 如果T1 再次读取同一个表, 就会多出几行。张飞：请班级班上同学吃饭（班上就两位同学）然后在没有请客之前，班上有来了一位同学（由原来的请两位同学吃饭、变成请三位同学吃饭，感觉出现了幻觉）

5.隔离级别

数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力,使它们不会相互影响, 避免各种并发问题。

当数据库系统采用read Commited隔离级别时，会导致不可重复读喝第二类丢失更新的并发问题，可以在应用程序中采用悲观锁或乐观锁来避免这类问题。从应用程序的角度，锁可以分为以下几类：
Serializable(串行化)：一个事务在执行过程中完全看不到其他事务对数据库所做的更新。
Repeatable Read(可重复读)：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，但是不能看到其他事务对已有记录的更新。
Read Commited（读已提交数据）：一个事务在执行过程中可以看到其他事务已经提交的新插入的记录，而且能看到其他事务已经提交的对已有记录的更新
Read Uncomitted（读未提交数据）：一个事务在执行过程中可以拷打其他事务没有提交的新插入的记录，而且能看到其他事务没有提交的对已有记录的更新。

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。对于多数应用程序，可以有优先考虑把数据库系统的隔离级别设为Read Commited，它能够避免脏读，而且具有较好的并发性能。尽管它会导致不可重复读、虚读和第二类丢失更新这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制。

当数据库系统采用read Commited隔离级别时，会导致不可重复读喝第二类丢失更新的并发问题，可以在应用程序中采用悲观锁或乐观锁来避免这类问题。从应用程序的角度，锁可以分为以下几类：

A.悲观锁：指在应用程序中显示的为数据资源加锁。尽管能防止丢失更新和不可重复读这类并发问题，但是它会影响并发性能，因此应该谨慎地使用。

B.乐观锁：乐观锁假定当前事务操作数据资源时，不回有其他事务同时访问该数据资源，因此完全依靠数据库的隔离级别来自动管理锁的工作。应用程序采用版本控制手段来避免可能出现的并发问题。

5.保存点(SAVEPOINT) 回滚

我们可以在MySQL处理过程中定义保存点(SAVEPOINT)，然后回滚到指定的保存点前的状态。

定义保存点，以及回滚到指定保存点前状态的语法如下。

1. 定义保存点---SAVEPOINT 保存点名;
2. 回滚到指定保存点---ROLLBACK TO SAVEPOINT 保存点名:

下面演示将向表user中连续插入3条数据，在插入第2条数据的后面定义一个保存点，最后看看能否回滚到此保存点。

1.查看user表中的数据

     SELECT * from account;

2.MySQL事务开始

     BEGIN;

3.向表user中插入2条数据

    INSERT INTO account(username,balance ) VALUES('C',1000),('D',1000);
    SELECT * from account;

4.指定保存点，保存点名为test

     SAVEPOINT test;

5.向表user中插入1条数据

     INSERT INTO account(username,balance ) VALUES('E',1000);
     SELECT * from account;

6.回滚到保存点test

     ROLLBACK TO SAVEPOINT test;
     SELECT * from account;

我们可以看到保存点test以后插入的记录没有显示了，即成功团滚到了定义保存点test前的状态。利用保存点可以实现只提交事务中部分处理的功能。