MYSQL 事务 并发 隔离

1、事务简介

TCL Transaction Control Language 事务控制语言

事务：

    事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一且执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。

    简单来说，事务就是 一个或一组sql语句组成一个执行单元，这个执行单元要么全部执行，要么全部不执行。

扩展：存储引擎（表类型）

（1）概念:在mysql中的数据用各种不同的技术存储在文件(或内存)中。

（2）通过show engines;可以查看mysql支持的存储引擎。

（3）在mysql中用的最多的存储引擎有: innodb,myisam , memory等。其中innodb支持事务,而myisaml. memory等不支持事务。

2、事务的ACID（acid）属性 面试题

• 原子性(Atomicity)：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性(Consistency)：一个事务执行会使数据从一个 一致状态 切换到 另外一个 一致状态

• 隔离性(Isolation)：一个事务的执行不受其他事务的干扰。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

• 持久性(Durability)：一个事务一旦提交，则会永久的改变数据库的数据，即便系统故障也不会丢失。

3、事务的创建

3.1 隐式事务和显式事务

（1）隐式事务：事务没有明显的开启和结束标记

比如：insert（插入语句）、update（修改语句）、delete（删除语句），都符合事务特性，但又没有明显标记，因此是隐式事务

（2）显式事务：事务具有明显的开启和结束标记

说明：显式事务一般都是隐式事务的集合。

3.2 创建前提

必须先设置自动提交功能为禁用（默认为开启）

语法：set autocommit=0；

说明：一般显式事务都是隐式事务的集合。autocommit开启，会自动提交隐式事务，就没法组合隐式事务合成的语句。

3.3 创建事务

step1：开启事务

set autocommit = 0; //该语句在禁用自动提交功能的同时会自动开启事务

start transaction; //可选语句，前面已经开启事务了，cmd里面可以不写，但客户端里面又必须写

step2：编写事务中的sql语句（select、insert、update、delete） 增删改查

语句1；

语句2；

........

step3：结束事务

commit；//提交事务

rollback 【TO 保存点】; //或事务有问题没法提交，回滚事务到开始，或者某个保存点

savepoint 保存点. //设置回滚节保存点，赋值给变

说明：回滚事务，意味着前面的组合语句都没执行。

Exp：数据表account里面，余额列为balance， 张无忌和赵敏各有1000，张无忌转账500 给赵敏

SET autocommit = 0;

START TRANSACTION;

UPDATE account SET balance =500 WHERE username = ‘张无忌’；

UPDATE account SET balance =1500 WHERE username= '赵敏';

COMMIT;

Exp2：

SET autocommit =0；

START TRANSACTION;

DELETE FROM account WHERE id = 25;

SAVEPOINT a; #设置保存点a

DELETE FROM account WHERE id = 28；

ROLLBACK TO a； #回滚到保存点

说明：25行删除，28行没有删除

4、事务的并发（类似多线程）

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

4.1 并发问题

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

设想：T1、T2可以看成两个同时连接到mysql服务器的两个客户端。然后T1、T2都去操作同一个数据库的同一张数据表，并把自动提交关闭。然后就发生以下情况：

说明：每个事务（T1、或者T2）执行一条语句，Mysql数据表都会变，但此时，是否允许除自己以外的事务TX读取呢？

（1）脏读（坚决不允许读取可能发生回滚的数据）

       对于两个事务T1，T2。T1读取了已经被T2更新但还没有被提交的字段之后，若T2回滚，T1读取内容就是临时且无效的。（T2发生回滚）

  解释： 妈妈给小明打钱，不小心打了1w，刚好小明在查账，读取到了这1w很高兴。妈妈此时发现打错了，回滚。小明就白高兴了，读取的1w是临时且无效的。

（2）不可重复的读：（确实影响挺大的，mysql默认不允许，但Oracle默认就允许）

    对于两个事务T1，T2，T1读取了一个字段，然后T2更新了该字段之后（commit了），T1再次读取同一个字段，值就已经不同了 ，可T1以为还是原来的值（T2 发生update）

 解释：小刚带着信用卡去洗浴中心洗澡（卡里当然是只有3.6万），当他买单时（小刚事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！小刚的儿子要把钱全部转出买皮肤，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待儿子买皮肤事务完成并提交完）。小刚很郁闷，明明卡里是有钱的，所以影响还是挺大的。

（3）幻读（T2新插入的数据不会影响T1对已读取的数据的任何运算，所以一般都容许幻读）

    对于两个事务T1，T2，T1从一个表中读取了一个字段，然后T2在该表中插入了一些新的行。之后，如果T1再次读取同一个表，就会多出几行。（T2发生INSERT插入）

   这个大多数情况下问题不大，除非是统计数据表的行数，就影响大。只是从数据表取个值出去计算，就没影响。

4.2 隔离级别 

    一个事务与其他事务隔离的程度称为隔离级别,数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱.（显然T2做的事情越简单，隔离起来开销系统资源就越小，并发性就越好。）

Mysql支持4种事务隔离级别。Mysql默认的事务隔离级别为REPEATABLE READ

Oracle只支持Read Commited 和  Serilizable

（1）查看隔离级别

语法：select @@tx_isolation; 

show variables like `tx_isolation`;

（2）修改隔离级别

语法：set session transaction isolation level 某隔离级别;  //单个对话框的隔离级别

语法：set global transaction isolation level 某隔离级别;  //设置全局隔离级别，不管开多少个mysql

隔离级别案例：可参考https://www.cnblogs.com/jian-gao/p/10795407.html

5、delete 和 truncate 在事务使用时的区别  可参考https://blog.csdn.net/chengqingshihuishui/article/details/108228314

delete 和 truncate 是删除数据表内容，数据表还在。删除数据表是（drop）

delete 删除的数据表可以回滚 而 truncate没法回滚 

Exp：

SET autocommit=0;
START TANSACTION;
DELETE FROM account;
ROLLBACK;
#回滚成功，并没删除数据表account的内容

SET autocommit=0;
START TRANSACTION;
TRUNCATE TABLE account;
ROLLBACK;
#回滚失败，account数据表的内容还是被删除完了