【数据库基础】03_MySQL 基础 Transaction 事务

什么是事务

数据库事务(Database Transaction)，是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。

简单的说：事务就是将一堆的SQL语句(通常是增删改操作)绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则就会恢复到这堆SQL执行之前的状态。

下面以银行转账为例，A转100块到B的账户，这至少需要两条SQL语句：

• 给A的账户减去100元；

update 账户表 set money=money**-100** where name='A';

• 给B的账户加上100元。

update 账户表 set money=money**+100** where name='B';

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

如果在转账过程中加入事务，则整个转账过程中执行的所有SQL语句会在一个事务中，而事务中的所有操作，要么全都成功，要么全都失败，不可能存在成功一半的情况。

也就是说给A的账户减去100元如果成功了，那么给B的账户加上100元的操作也必须是成功的；否则，给A减去100元以及给B加上100元都是失败的。

事务4个特性ACID

一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

• 原子性：==原子意为最小的粒子，或者说不能再分的事物。==数据库事务的不可再分的原则即为原子性。一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

• 一致性：==在事务开始之前和事务结束以后，数据库的完整性没有被破坏。==这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

• 隔离性：==简单点说，某个事务的操作对其他事务不可见的。==数据库允许多个并发事务同时对其数据进行读写和修改的能力（一个事务的执行不受另外一个事务的干扰），隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。

• 持久性：事务处理结束后，对数据的修改就是永久的，数据将持久化到本地，即便系统故障也不会丢失，除非其他事务对其进行修改。

事务的用法

DCL 用来控制数据库的访问，包括如下 SQL 语句：

• GRANT：授予访问权限
• REVOKE：撤销访问权限
• COMMIT：提交事务处理
• ROLLBACK：事务处理回退
• SAVEPOINT：设置保存点
• LOCK：对数据库的特定部分进行锁定
• savepoint 断点
• commit to 断点
• rollback to 断点
   

隐式事务，没有明显的开启和结束事务的标志

比如 insert、update、delete 语句本身就是一个事务

显式事务，具有明显的开启和结束事务的标志

相关步骤：

1. 开启事务 start transaction（取消自动提交事务的功能）
2. 执行 sql 操作，编写事务的一组逻辑操作单元（普通 sql 操作，多条sql语句）
   insert
   update
   delete
3. 提交事务或回滚事务(commit/rollback)

事务处理

• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
• 事务处理可以用来维护数据的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行
• 事务用来管理 insert、update、delete 语句，因为这些操作才会“破坏”数据，查询select语句是不会的
• MySQL默认数据库的事务是开启的，执行SQL后自动提交。
• MySQL的事务也可以改成手动提交，那就有两个步骤：先开启，写完SQL后，再手动提交。

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

• 开启事务：start transaction；
• 结束事务：commit（提交事务）或rollback（回滚事务）。

提交 commit

多条语句时，批量执行，事务提交

有了事务，多步操作就形成了原子性操作，高并发下也不会引起数据错乱

mysql的事务默认就是开启的 – 多条语句一起操作时,要么一起成功要么一起失败

BEGIN; #关闭事务的自动提交

INSERT INTO user (id) VALUES(25);#成功

INSERT INTO user (id) VALUES(5);#已经存在5了，会失败

COMMIT; #手动提交事务

回滚 rollback

#多条语句，批量执行，insert插入重复的主键导致失败时，事务回滚

BEGIN;

INSERT INTO user (id) VALUES(15);

INSERT INTO user (id) VALUES(35);#存在了

ROLLBACK;#事务回滚，就不会再提交了

注意:

• 建表的时候，选择 Innodb 引擎才支持事务。

• 默认情况下，MySQL 是自动提交事务，每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。如果某一组操作需要在一个事务中，那么需要使用 start transaction，一旦 rollback 或 commit 就结束当次事务，之后的操作又自动提交。

• 如果需要在当前会话的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置 set autocommit = false; 或 set autocommit = 0; 那样的话每一句 SQL 都需要手动 commit 提交才会真正生效。rollback 或 commit 之前的所有操作都视为一个事务，之后的操作视为另一个事务，还需要手动提交或回滚。

• 和 Oracle 一样，DDL 语句（数据定义语言，用于定义数据库的结构）是不能回滚的（一致性），并且部分的 DDL 语句会造成隐式的提交，因此最好事务中不要涉及DDL 语句。

DDL 语句：

• CREATE TABLE：创建数据库表
• ALTER TABLE：更改表结构、添加、删除、修改列长度
• DROP TABLE：删除表
• CREATE INDEX：在表上建立索引
• DROP INDEX：删除索引

#开启手动处理事务模式
#set autocommit = false;

#开始事务（推荐）
start transaction;

#查看当前表的数据
select * from t_stu_detail;
#删除整张表的数据
delete from t_stu_detail;
#查询该表数据，发现显示删除后的结果
select * from t_stu_detail;

#回滚
rollback

#开启手动处理事务模式
#set autocommit = false;
#开始事务（推荐）
start transaction;

#查看当前表的数据
select * from t_stu_detail;
#删除整张表的数据
delete from t_stu_detail;
#查询该表数据，发现显示删除后的结果
select * from t_stu_detail;
#回滚
rollback

#查看当前表的数据，发现又回来了
select * from t_stu_detail;

#删除整张表的数据
delete from t_stu_detail;
#提交事务
commit;
#查看当前表的数据，发现真删除了
select * from t_stu_detail

#插入一条记录
INSERT INTO t_stu_detail VALUES
(1, '123456789012345678', '1990-01-21', '12345678901', 'a@163.com', '北七家');
#保存还原点 1
savepoint point1;
#插入一条记录
INSERT INTO t_stu_detail VALUES
(2, '123456789012345677', '1990-02-21', '12345678902', 'b@163.com', '北七家');
#保存还原点 2
savepoint point2;
#查看当前效果
select * from t_stu_detail;
#回滚到某个还原点
rollback to point1;
#提交事务
commit;

#清空表
truncate t_stu_detail;
#回滚，对于 truncate 无法回滚
rollback;

#清空表
truncate t_stu_detail;
#回滚，对于 truncate 无法回滚
rollback;

数据库事务的并发问题

对于同时运行的多个事务（多线程并发），当这些事务访问数据库中相同的数据时，如果没有采取必要的隔离机制， 就会导致各种并发问题: （问题的本质就是线程安全问题，共享数据的问题）

1）脏读

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

比方：进入餐厅发现“梦中情人”旁边座位已经有“帅哥”坐那儿了，正郁闷，打完饭，发现那个位置是空着的，又欣喜若狂，其实刚刚那个“帅哥”只是临时过去打个招呼。

2） 不可重复读

对于两个事务 T1, T2, T1 读取了一个字段, 然后 T2 更新并提交了该字段。 之后, T1再次读取同一个字段, 值就不同了。

比方：在图书馆门口，发现自己占的位置旁边有位“美女”，等刷完卡，兴冲冲的走到那儿，发现已经变成一“如花”了。

3）幻读

== 对于两个事务 T1, T2, T1 从一个表中读取了一个字段， 然后 T2 在该表中插入、删除了一些新的行。 之后, 如果 T1 再次读取同一个表， 就会多出、少了几行。==

比方：大学考前画重点，老师说“第一章 xxxxxx 概念”，你赶紧找，“天啊，在哪儿啊”，等你画完，就听老师说：“第四章 xxxxx”，中间那些你都没听到。

4）当使用序列化时

数据库事务的隔离级别

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

一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

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

Mysql 支持 4 中事务隔离级别，事务隔离分为不同级别，包括：

• 读未提交（Read uncommitted） 安全性最差,可能发生并发数据问题,性能最好
• 读提交（read committed） Oracle默认的隔离级别
• 可重复读（repeatable read）MySQL默认的隔离级别，安全性较好，性能一般
• 串行化（Serializable） 表级锁，读写都加锁，效率低下,安全性高，不能并发

查询mysql的隔离级别

Mysql 默认的事务隔离级别为: REPEATABLE-READ,mySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。

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

• 查看当前的隔离级别: SELECT @@tx_isolation;
• 查看全局的隔离级别：select @@global.tx_isolation;
• 设置当前 mySQL 连接的隔离级别: set tx_isolation =‘repeatable-read’;
• 设置数据库系统的全局的隔离级别: set global tx_isolation ='read-committ
  在默认情况下，MySQL每执行一条SQL语句，都是一个单独的事务。如果需要在一个事务中包含多条SQL语句，那么需要手动开启事务和结束事务。

锁

行级锁

InnoDB 的前三个隔离级别是行级锁

表级锁

InnoDB 事务隔离级别是序列化，将会发生整张表的锁。