MySQL - MySQL事物与事物管理

---

概述

并不是MySQL中所有的引擎都支持事物，常见的存储引擎如InnoDB支持事物，MyISAM不支持事物。

Transaction，事物，在事物型RDBMS值被支持，对应一个完整的业务（比如转账，我账户扣钱，对方账户收到钱，必须确保资金的总数不变），它通常由INSERT,UPDATE和DELETE组成。事物可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

---

一、事物的四大特性(ACID)

• 原子性(A)：事务是最小单位，不可再分。

• 一致性©：事务要求所有的MySQL操作要么完全执行，要么完全不执行（比如我被扣的钱和对方收到的钱数量必须是一致的）。由日志处理机制配合实现。

• 隔离性(I)：事务A和事务B之间具有隔离性，事物的结果只在它完全被执行后才能看到。

• 持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)。

  默认情况下InnoDB表持久性最长，MyISAM表提供部分持久性。

---

二、事物的处理

1.事物处理的几个术语

• 事务（transaction） 指一组SQL语句；
• 回退（rollback） 指撤销指定SQL语句的过程；
• 提交（commit） 指将未存储的SQL语句结果写入数据库表；
• 保留点（savepoint） 指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

---

2.事物处理的指令

• START TRANSACTION：开启事务，指的是
• COMMIT：提交事务
• ROLLBACK [TO 保留点]：回滚事务，方括号里面内容可选
• SAVEPOINT 保留点：创建保留点

---

3.控制事物处理

MySQL使用START TRANSACTION来标识事物的开始。

我们先建立名为user_db的表和插入数据（使用的数据库引擎默认为InnoDB）：

--1.建立名为user_db的表
CREATE TABLE user_db(
	id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL
);
--2.插入两条数据后并查看数据表
INSERT INTO user_db(username) VALUES('张三'),('李四');
mysql> SELECT * FROM user_db;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  2 | 李四     |
+----+----------+

3.1 使用ROLLBACK

MySQL的ROLLBACK命令用来回退（撤销）MySQL语句，请看下面的语句：

START TRANSACTION;
DELETE FROM user_db;
SELECT * FROM user_db;
ROLLBACK;
SELECT * FROM user_db;

根据上面建的数据表，这里使用START TRANSACTION开启事物，之后执行清空user_db数据表的操作（删除用DROP），再查询数据表确实为空，之后执行ROLLBACK事物回滚操作，再查询数据表数据还是和原来的一样。显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

ROLLBACK回滚操作对INSERT,UPDATE和DELETE有效，对SELECT,CERATE,DROP操作无效。并且，在事物进行过程中，未结束之前，DML语句是不会更改底层数据，只是将历史操作记录一下，在内存中完成记录。只有在事物成功结束的时候，才会修改底层硬盘文件中的数据。

---

3.2 使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。 但是在事务处理块中，提交不会隐含地进行，而是进行明确的提交， 使用COMMIT语句，如下所示：

START TRANSACTION;
INSERT INTO user_db(username) VALUES('王五');
INSERT INTO user_db(username) VALUES('麻六');
COMMIT;
SELECT * FROM user_db;

使用事物块来保证两个插入语句要么都成功，要么都失败。不会出现只成功一个的情况。

事物关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

---

3.3 使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分COMMIT或ROLLBACK回退。在事务处理块中合适的位置放置占位符，如果需要回退，可以回退到某个占位符的为真。 这些占位符称为保留点。创建占位符使用SAVEPOINT语句：

SAVEPOINT insert1;

示例：

START TRANSACTION;
INSERT INTO user_db(username) VALUES('王七');
SAVEPOINT insert1;
INSERT INTO user_db(username) VALUES('王九');#不会被执行
ROLLBACK TO insert1;
COMMIT;
SELECT * FROM user_db;

在事物块中，ROLLBAKC到insert1保留点，使得第二条insert语句得不到提交。

3.4 更改默认的提交行为

MySQL行为是自动提交所有更改，即执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做 的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

SET AUTOCOMMIT=0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT 语句，设置autocommit为0（假）指示MySQL不自动提交更改 （直到autocommit被设置为真为止）。下面使用一个示例帮助理解：

SET AUTOCOMMIT=0;
INSERT INTO user_db(username) VALUES('┗|｀O′|┛ 嗷~~');
EXIT;#关闭数据库

现在重新启动数据库后查询对应的表，发现这一条数据并没有插入成功。

---

4.不能事物处理的命名

大部分命名都可以通过事物处理，但是以下指令不能通过事物处理，会直接COMMIT：

DROP DATABASE、DROP TABLE、DROP、SELECT、ALTER TABLE

---

三、事物的隔离性

在对多线程情况下，针对于数据安全的问题，我们有一种锁的概念，先了解锁：

1. 锁分类：锁分为共享锁（shared lock）和排他锁（exclusive lock）。其中共享锁也称为只读锁，当一个用户在操作数据库时，其他用户只能读取数据，不能进行数据的增删改；排他锁当执有一个用户在执行增、删、改的指令时，其他用户不能有任何的操作。
2. 锁的粒度：锁对象的大小就是锁的粒度，分为三种：记录锁、表锁和数据库锁。

而数据库如果使用锁的话性能会下降很多，频繁访问不一定合理，则使用事物的隔离级别来表示事务处理之间的影响程度。

---

1.事物的隔离级别

假设有两事物，事物A和事物B，它们之间具有一定的隔离性。不同的事物之间具有一定的隔离性

事物的隔离级别有四个：读未提交(read uncommitted)、读已提交(read committed)、可重复度(repeatable read)、串行化(serializable)。假定存在事物A和事物B。

读未提交(read uncommitted)

事物A可以读取到事物B未提交的数据。读取的数据叫做脏数据。这种隔离级别最低，这种级别一般是理论上存在，数据库中的事物的隔离级别一般都高于该级别。

读已提交(read committed)

事物A可以读取到事物B已经提交的数据。这种隔离级别高于读未提交。这种隔离级别可以避免读取到脏数据，但是会导致不可重复读，且读已提交是Oracle数据库的默认隔离级别。

可重复读(repeatable read)

事物A读取不到事物B已经提交的数据。事物A可实现多次读取数据是一致的，这种隔离级别高于读已提交，可以避免不可重复读，但是会导致幻读 ，就算事物A在开启之后，数据库的数据被其他事物更改了，那么事物A读取的数据还是事物A开启之前的数据，不会是数据库最新的数据。可重复度是MySQL数据库的默认隔离级别。

串行化(serializable)

不支持多事物并发执行，即事物A在操作数据库时，事物B只能等待。每次读取的数据是数据库的真实数据，这种隔离级别很少使用，吞吐量太低。

---

2.隔离级别与一致性的关系

隔离级别	脏读取	不可重复读	幻读
读未提交	√	√	√
读已提交	×	√	√
可重复度	×	×	对InnoDB不能
串行化	×	×	×

---

3.设置事物的隔离级别

3.1 方式一

在my.ini配置文件中使用transaction-isolation选型来设置服务器的缺省事物隔离级别。示例：

# 1.READ-UNCOMMITTED
# 2.READ-COMMITTED
# 3.REPEATABLE-READ
# 4.SERIALIZABLE
[mysqld]
transaction-isolation = READ-COMMITTED

---

3.2 方式二

在此之前，先了解一下隔离级别的作用范围。事物的隔离级别的作用范围分为两种：全局级(GLOBAL)和会话级(SESSION)。全局级对所有会话有效，会话级对当前会话有效。如果不指定隔离级别的作用范围的话，默认为会话级。

通过命令行动态设置事物的隔离级别。使用SET TRANSACTION ISOLATION LEVEL语句。

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL <isolation-level>
# GLOBAL表示此动态设置的隔离级别为全局级
# SESSION表示此动态设置的隔离级别为会话级

# 其中的<isolation-level>可以是：
#	1.READ UNCOMMITTED
#	2.READ COMMITTED
#	3.REPEATABLE READ
#	4.SERIALIZABLE

#例如：
#	1.动态设置会话级隔离级别为可重复读
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# 或
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
#	2.动态设置全局级隔离级别为可重复读
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

这种动态的设置隔离级别并不能永久有效，下一次启动数据库时，数据库还是会使用默认的隔离级别。想要实现设置数据库的隔离级别永久生效，就得使用方式一设置，更改.ini配置文件。

---

4.查看事物的隔离级别

事物隔离级别的作用范围分为两种：全局级和会话级。查看事物的隔离级别分为当前的和查看系统的隔离级别（因为存在动态设置，可能当前的隔离级别与系统默认的隔离级别不一样）。

查看当前事物的隔离级别：SELECT @@tx_isolation

查看系统当前事物的隔离级别：SELECT @@global.tx_isolation

# 看一个示例:系统默认的隔离级别为可重复读，我设置全局级的隔离级别为 读已提交
mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| READ-COMMITTED        |
+-----------------------+
1 row in set, 1 warning (0.00 sec)