TCL事务控制语句

TCL事务控制语句

事务属于TCL控制语言（Transaction Control Language ）

一. 事务概述

1.1 什么是事务？为什么要用事务？

​ 一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！

默认情况下，每条单独的sql语句就是一个单独的事务！

举例：

银行转账！张三转10000块到李四的账户，这其实需要两条SQL语句：

给张三的账户减去10000元；update account set balance=balance-1000 where name=‘张三’

给李四的账户加上10000元。update account set balance=balance+1000 where name=‘李四’

如果在第一条SQL语句执行成功后，在执行第二条SQL语句之前，程序被中断了（可能是抛出了某个异常，也可能是其他什么原因），那么李四的账户没有加上10000元，而张三却减去了10000元。这肯定是不行的！

你现在可能已经知道什么是事务了吧！事务中的多个操作，要么完全成功，要么完全失败！不可能存在成功一半的情况！也就是说给张三的账户减去10000元如果成功了，那么给李四的账户加上10000元的操作也必须是成功的；否则给张三减去10000元，以及给李四加上10000元都是失败的！

1.2 MySQL中的存储引擎

**1、存储引擎的概念：**在mysql中的数据用各种不同的技术存储在文件（或内存）中。

**2、通过show engines；**来查看mysql支持的存储引擎。

3、 在mysql中用的最多的存储引擎有：innodb， myisam ,memory 等。其中innodb支持事务，而

myisam、memory等不支持事务。

二.事务的分类

1.隐式事务：没有明显的开启和结束标记，比如dml语句的insert、update、delete语句本身就是一条事务

insert into stuinfo values(1,'Jack','男','123@qq.com',12);

2.显式事务：具有明显的开启和结束标记，一般由多条sql语句组成，必须具有明显的开启和结束标记

步骤：
1、开启事务
	取消隐式事务自动开启的功能

2、编写事务需要的sql语句（1条或多条）
	-- insert into stuinfo values(1,'Mark','男','123@qq.com',12);
	-- insert into stuinfo values(2,'Lisa','女','123@qq.com',12);

3、结束事务
	提交事务或回滚事务

三.事务的操作

​ 在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要开启事务和结束事务。

-- 开启事务：start transaction;

-- 结束事务：commit或rollback;

-- 断点  savepoint  断点   rollback to 断点

​

​ 在执行SQL语句之前，先执行strat transaction，这就开启了一个事务（事务的起点），然后可以去执行多条SQL语句，最后要结束事务，commit表示提交，即事务中的多条SQL语句所做出的影响会持久化到数据库中。或者rollback，表示回滚，即回滚到事务的起点，之前做的所有操作都被撤消了。

由于事务是自动开启的，需要先取消事务的自动开启

-- 查看含有auto的变量
SHOW VARIABLES LIKE '%autocommit%'

-- 取消事务自动开启
SET autocommit = 0;

案例：

创建事务要用到的表

CREATE TABLE account(

	id INT PRIMARY KEY AUTO_INCREMENT,

	NAME VARCHAR(30),

	balance double(10,2)

);

INSERT INTO account(NAME,balance) VALUES('zs', 100000);

INSERT INTO account(NAME,balance) VALUES('ls', 100000);

SELECT * FROM account;

Ø 演示事务执行失败

-- 案例一：回滚 事务执行失败
START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

ROLLBACK;

-- 案例二：退出mysql客户端 事务执行失败！
START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

Ø 演示事务执行成功

START TRANSACTION;

UPDATE account SET balance=balance-10000 WHERE id=1;

UPDATE account SET balance=balance+10000 WHERE id=2;

COMMIT;

四.事务的四大特性（ACID）

3.1 概念

原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要么全部执行失败。

一致性（Consistency）：事务执行后，数据库状态与其它业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。

隔离性（Isolation）：隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会相互干扰。

持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

3.2 ACID实现原理

​ MySQL中的日志有很多，比如undo log、redo log、bin log、错误日志、慢查询日志、查询日志。InnoDB引擎提供了两种事务的日志，分别是undo log和redo log。

原子性

​ 原子性是指一个事务是一个不可分割的单位，是一个最小的操作单元，这个操作单元包含若干个操作（一个sql就是一个操作）。这些若干个操作要么全部成功，要么全部失败。

​ 如果一个事务中的一个sql语句执行失败，那么已经执行的sql语句需要执行回滚，回滚到事务执行前的状态。它的实现原理就是undo log。

​ 原子性的体现就是体现在回滚上面。如果事务中的某个sql报错会执行回滚。回滚到执行事务之前的状态。因此我们要把执行事务之前的状态记录下来，如果一旦sql语句发生错误，就可以通过执行事务前记录下来的状态信息，回滚到执行事务之前的状态。

​ 当事务对数据进行修改的时候，innodb会生成对应的undo log，他会记录每条sql执行的相关信息。如果sql执行失败，innodb会根据undo log的信息执行相反的操作来回滚，比如某一个事务中的某个sql是执行insert操作，那么回滚的时候会执行delete操作。对于delete操作，会执行一个insert操作。对于update操作，会执行相反的update操作。这些相反的操作的执行都基于这个undo log，要把sql的执行信息记录到undo log里面，回滚的时候就根据undo log 去回滚。

持久性

	持久性是指一个事务一旦提交，它对数据库的改变是永久性的。==持久性也是通过一个log来实现，它叫redo log。==

​ MySQL数据是存在磁盘中的，如果每次读取数据都去磁盘读取则会有一个磁盘IO的过程，那么它的效率会很低。因此InnoDB提供了一个缓存Buffer，该Buffer包含磁盘中部分的数据页的一个映射，作为访问数据库的一个缓冲。当从数据库读取数据，会先从这个Buffer中读取，如果Buffer中没有则会从磁盘中取，读取完才会放在这个Buffer缓存中。当向数据库写入数据的时候，也会先向该Buffer中写入数据，并将Buffer中的数据刷新到磁盘中。但是这时存在一个问题，虽然读写效率提升了，但是如果Buffer中的数据还没来得及同步到磁盘上，此时MySQL宕机了，那么Buffer中的数据就会丢失掉，进而造成数据的丢失。数据丢失了，事务的持久性也就无法保证了。因此redo log就被引入进来解决这个问题。

​ 改进后的流程变为：**当数据库的数据要进行新增或修改的时候，除了修改Buffer中的数据，还要把这次的操作记录到redo log里面。**如果MySQL宕机了，还可以从redo log恢复数据。redo log是一个预写式日志，指它会将所有的修改先写入到日志里面，再更新到Buffer里面（此思想与Java更新缓存的方式相通，都是先写库再更新缓存），保证了数据不会丢失。

//为什么将redo log的数据写到磁盘比将Buffer中的数据写到磁盘快？
1.Buffer中的数据持久化是随机写的IO。每次修改的数据位置都是随机的。但是redo log是追加模式的，它是在文件的尾部追加，属于一种顺序IO的操作。这种方式就很快。KafKa也是采用顺序IO机制操作的。

2.Buffer持久化数据是以数据页page为单位的，MySQL默认配置大小为16k，一个数据页上一个小小的数据修改，都需要把整个页的数据写入。而redo log只需要写入真正的部分即可。这个无效的IO就大大减小了。所以redo log比Buffer同步数据要快很多很多。

//redo log什么时候同步到磁盘里去？
redo log没有同步到磁盘之前是在缓存区中，它叫redo log缓冲区。MySQL宕机也没有关系，因为事务没有执行完，即事务没有提交，恢复好数据库后可以通过undo log走回滚操作。
redo log的持久化机制可以通过innodb_flush_log_at_trx_commit。

值为0时，表示当事务提交时，并不将缓冲区的redo log写入到磁盘的日志文件，而是等待主线程每秒刷新
值为1时，表示当事务提交时将缓冲区中的redo log 同步写入到磁盘中， 保证一定会写入成功 。同步写入的速度很快，比Buffer同步快很多。
值为2时，表示事务提交时将缓冲区的redo log 异步写入到磁盘中。不能保证在commit时一定会将redo log写入磁盘中去。

隔离性

​ 不同事务之间不能相互影响。 写-写操作，通过锁解决。写-读操作，通过MVCC(Multi-Version Concurrency Control，多版本并发控制)解决，写读操作会产生幻读、脏读、不可重复读 。

写-写的情况：

InnoDB的锁大致分为行锁、表锁、间隙锁。若干事务对同一行进行写操作，那么此时只能有一个事务对数据进行操作，那么这个需要由锁来保证。锁能保证在同一时刻只有一个人在操作数据。事务在修改数据之前，这个事务需要获取到相应的锁，获取到之后该事务就可以修改数据了。如果其他事务想要修改事务，那么必须要等到拿到锁的事务提交事务或者回滚后释放锁，下一个事务才能来抢这个锁去执行事务。

写-读操作的情况： 通过四个隔离级别解决

3.3 总结

原子性： 一个事务内的sql语句要么全部执行，要么全不执行，是通过undo log原理实现的。

一致性： 一致性是指事务执行之后，数据库的完整性约束没有被破坏，事务执行前后都是一个合法的数据状态。他的完整性主要体现在数据库主键要唯一，字段的类型、大小、长度、外键约束要符合要求。一致性是事务追求的最终目标，ACID中三种特性都是为了实现最终的一致性。

隔离性： 写-写操作主要是通过锁实现的。如果是读- 写操作则是通过mvcc。

持久性： 通过redo log保证的。

-- 原子性（Atomicity）：指在事务中包含所有操作，要么都做，要么都不做（不可分割）

-- 一致性（Consistency）：数据的改变保证一致

-- 隔离性（Isolation）：数据库允许多个并发事务同时对其数据进行读写和修改的能力，防止多个事务并发执行时由于交叉执行而导致数据的不一致。

-- 持久性（Durability）：事务处理结束之后，对数据的修改是永久的，即便你的系统出现故障，也不会丢失。（将数据存在了磁盘上）

五.事务的隔离级别

5.1 事务并发问题如何发生？

当多个事务同时操作同一个数据库的相同数据时

5.2 事务的并发问题有哪些？

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

-- 多个事务同时处理，会遇到的现象：
脏读：用户读了修改后未提交的数据(rollback产生)

不可重复读：读取同一条记录，但是两次数据不一样(两次提交同一条记录)

幻读：读取同一张表，两次记录数不一样(两次提交不同的记录)

5.3 如何避免事务的并发问题？

​ 数据库事务的隔离性: 数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别. 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

​ Oracle 支持的 2 种事务隔离级别：READ COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED。

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

-- 通过设置事务的隔离级别
读未提交READ UNCOMMITTED：允许幻读和不可重复读，脏读

读提交READ COMMITTED：允许幻读和不可重复读，但是不允许脏读
--读提交：本次操作若发生改变了，必须提交，其他人才能看见改变的（Oracle默认）

重复读REPEATABLE READ：允许幻读，但是不允许不可重复读和脏读（MySQL默认）

串行化读SERIALIZABLE：都不允许幻读和不可重复读，脏读
--串行化读：首先重新建立连接，只管自己本次事务操作，不管外部数据的变化（即使提交也看不见）

​ 每启动一个 mysql 程序, 就会获得一个单独的数据库连接. 每 个数据库连接都有一个全局变量@@tx_isolation, 表示当前的事务隔离级别。

-- mysql中默认  第三个隔离级别 repeatable read
-- oracle中默认 第二个隔离级别 read committed

-- 查看当前的隔离级别
-- select @@tx_isolation;mysql5
select @@transaction_isolation; -- mysql8

-- 设置 当前会话 | 数据库系统的全局的 MySQL连接的隔离级别
set session|global transaction isolation level 隔离级别;

读未提交READ UNCOMMITTED：允许幻读和不可重复读，脏读

读提交READ COMMITTED：允许幻读和不可重复读，但是不允许脏读

重复读REPEATABLE READ：允许幻读，但是不允许不可重复读和脏读（MySQL默认）

不允许不可重复读和脏读

允许幻读

串行化读SERIALIZABLE：都不允许幻读和不可重复读，脏读