[MySQL基础5] 事务-CSDN博客

事务是一组操作的集合，是不可分割的工作单位。事务会把所有的操作作为一个整体像系统提交或撤销操作请求，即这些请求要么同时成功，要么同时失败。

比如说，银行的转账，A给B转账1k，要经历查询A余额——A账户余额减小1k——B账户余额增加1k三步。这三步要么同时成功，要么同时失败，如果在中间的过程业务程序抛异常，那么要回滚到之前的状态。

因此，我们需要手动指定事务的开始、结束和回滚。

1. 事务的基本操作

我们以前面的转账为例子，先建表：

CREATE TABLE account (
    id int PRIMARY KEY AUTO_INCREMENT, 
    name VARCHAR(10) NOT NULL, 
    money int NOT NULL
);

INSERT INTO account VALUES (NULL, 'abc', 2000), (NULL, 'bcd', 2000);

+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  2000 |
|  2 | bcd  |  2000 |
+----+------+-------+

正常来讲，转账会有以下三步：

-- 查询abc账户余额
SELECT money FROM account WHERE name='abc';

-- abc余额减1000
UPDATE account SET money = money - 1000 WHERE name='abc';

-- bcd余额加1000
UPDATE account SET money = money + 1000 WHERE name='bcd';

但如果中间抛出异常，就不会正确完成。在MySQL中，事务都是自动提交的，即上述的每个对数据库增删改的语句执行完毕后都会自动提交，为此，我们需要手动设置事务。

基础操作：

-- 查看 设置事务提交方式
SELECT @@autocommit;

+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+


-- 为1说明现在的状态是自动提交，我们可以手动设置为0
SET @@autocommit = 0;  -- 只对当前窗口的命令有效

-- 手动提交事务：
COMMIT;

-- 手动回滚事务：
ROLLBACK;

完整的用法为：

方式一：

-- 设定手动提交
SET @@autocommit = 0;

-- 查询abc账户余额
SELECT money FROM account WHERE name='abc';

-- abc余额减1000
UPDATE account SET money = money - 1000 WHERE name='abc';

-- 故意加一个报错
INSERT INTO account VALUES (123, 123);

-- bcd余额加1000
UPDATE account SET money = money + 1000 WHERE name='bcd';

-- 如果成功则提交
COMMIT;

-- 不成功则回滚, 注意 commit 和 rollback二者运行其一！
ROLLBACK;

方式二：

也可以不设定autocommit，开启事务用start transaction或者是begin：

USE test;

-- 开启事务
START TRANSACTION;

-- abc余额减1000
UPDATE account SET money = money - 1000 WHERE name='abc';

-- 故意加一个报错
INSERT INTO account VALUES (123, 123);

-- bcd余额加1000
UPDATE account SET money = money + 1000 WHERE name='bcd';

-- 如果成功则提交
COMMIT;

-- 不成功则回滚
ROLLBACK;

**特别注意：**手动启用提交回滚，只是多了一个反悔的机会，比如上面的例子，在报错之前，实际上数据库abc的余额已经减了1000，而且由于报错，bcd的余额并没有加1000，只不过，报错之后，我们可以手动rollback使得数据不受影响

2. 事务的四大特性ACID

原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。
隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3. 并发事务问题

如果有多个事务同时操作数据库，可能会出现并发问题：

问题	描述
脏读	一个事务读到另一个事务还没提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。

下面简单说明一下：

脏读

比如说，事务A对id=1的数据执行了update，但还没有commit，这时事务B对id=1的数据进行查询，查询到的实际上就是已经更改的数据，但这个数据在理论上还不应该被commit
不可重复读

比如说，事务A对id=1的数据进行了一个查询操作，随后，事务B对id=1的数据进行了更改，**并进行了commit，**随后，事务A再次对id=1的数据查询，这两次查询的数据不一样，就是不可重复读
幻读

比如说，事务A对id=1的数据进行了一个查询操作，发现没有，随后事务B insert了id=1的数据，事务A发现没有id=1后，会insert id=1的数据，这时会报错，因为id重复了。**如果不可重复读的问题解决，则事务A再去查询id=1时，仍然是没有这个数据的，**但又不能成功插入，就是幻读。

4. 事务的隔离级别

几个隔离级别如下：

隔离级别	脏读	不可重复读	幻读
read uncommitted	会发生	会发生	会发生
read committed	不会发生	会发生	会发生
repeatable read(mysql默认)	不会发生	不会发生	会发生
serializable	不会发生	不会发生	不会发生

**记忆：**看英文字面意思：读未提交的（就会有脏读），读提交的（就不会有脏读），repeatable read（可重复读），serializable（序列化，所以不会有冲突）

语法如下：

-- 查看事务隔离级别
SELECT @@TRANSACTION_ISOLATION;

+-------------------------+
| @@TRANSACTION_ISOLATION |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+

-- 设置事务隔离级别
SET {SESSION 或 GLOBAL} TRANSACTION ISOLATION LEVEL {READ UNCOMMITED 或 READ COMMITED 或 REPEATABLE READ 或 SERIALIZABLE};

其中，SESSION是在当前对话下设置隔离级别，GLOBAL是设置全局的

例子：

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

验证一下隔离级别是否会出现并发事务问题

脏读：

在事务A, 首先设定隔离级别为read uncommited，随后执行查询：

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

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  2000 |
|  2 | bcd  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

此时，在事务B，我们修改id=1的数据，但是不commit：

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> update account set money=1000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

这时，在事务A，我们再次查询，就出现了事务B修改但未commit的数据：

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  1000 |
|  2 | bcd  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

如果我们把事务A的隔离级别调成read committed，再进行上述的更改：

-- 事务B修改不提交
mysql> update account set money=3000 where id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 事务A查询
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  2000 |
|  2 | bcd  |  2000 |
+----+------+-------+

-- 事务B提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

-- 事务A查询
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  3000 |
|  2 | bcd  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

当然，上面的例子也验证了read commited不能解决不可重复读

不可重复读

-- 事务A

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

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  3000 |
|  2 | bcd  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

-- 事务B
mysql> update account set money=4000 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 事务A
mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  3000 |
|  2 | bcd  |  2000 |
+----+------+-------+
2 rows in set (0.00 sec)

可以发现，A没有出现不可重复读的问题

幻读

-- 紧接上面的例子，如果A查询一个id=5的数据
mysql> select * from account where id=5;
Empty set (0.00 sec)  -- 查询不到

-- 事务B 插入
mysql> insert into account values (null, 'ert', 2000);
Query OK, 1 row affected (0.01 sec)

mysql> select * from account;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | abc  |  4000 |
|  2 | bcd  |  2000 |
|  3 | qwe  |  2000 |
|  5 | ert  |  2000 |
+----+------+-------+

-- 事务A 插入
mysql> insert into account values (5, '123123', 1000);
ERROR 1062 (23000): Duplicate entry '5' for key 'account.PRIMARY'

-- 不能插入 但是也查询不到 就像幻影
mysql> select * from account where id=5;
Empty set (0.00 sec)

我们再试一下最高等级serializable:

-- 事务A 设定level 目前查询不到id=6的数据
mysql> set session transaction isolation level serializable;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account where id=6;
Empty set (0.00 sec)

-- 事务B
insert into account values (null, 'asdt', 8000);
-- 直接卡主了！说明serializable直接把整个表锁住了，不让任何其他的事务更改，根本不会有冲突

-- 事务A 结束
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)