MySQL学习笔记（7）—— 事务控制

一、什么是事务控制

• 事务（Transaction）：指作为一个逻辑工作单元执行的一系列操作（多条sql语句的集合），这些操作要么全部成功，要么全部失败。事务确保对多个数据的修改作为一个单元来处理。

• 说明

  • 在MySQL中，只有使用了Innodb存储引擎的数据库或表才支持事务。

  • 事务用于维护数据库的完整性，保证成批的sql语句要么都执行，要么都不执行。

  • 事务用于管理INSERT、UPDATE和DELETE语句（增、删、改 这些会影响数据库中的数据，查询不影响就不管了）

• 举例

  • 张三在ATM机上给李四转账100元，在银行的业务系统中，主要会执行两步数据变更操作：

    1. 从张三的账户减去100元；

    2. 给李四的账户增加100元。

    试问，如果操作①执行成功，操作②执行失败会发生什么情况？

  • 在这个场景中，如果执行了部分操作，系统会出现错误，因此必须把两条操作放到一个事务中来控制。保证不会出现仅部分操作成功的情况

二、事务的四个特性

三、MySQL的事务控制

• 在默认情况下，MySQL是自动提交事务的，即每一条INSERT、UPDATE、DELETE的SQL语句提交后会立即执行COMMIT操作（每条操作都当成一个事务）。因此，要开启一个事务，可以使用start transaction或begin，或者将autocommit的值设置为0

1. 语法

# 查看自动提交是否开启
select @@autocommit;				# 方式1
show variables like "autocommit"; 	# 方式2

# 开启事务
begin;

# 开启事务的另一种方法
start transaction;

# 对于一个事务，只有两种操作
rollback;	# 回滚
commit 		# 提交

# 关闭自动提交：
set autocommit=0;

# 开启自动提交：
set autocommit=1;

2. 事务的两种使用方式

• 方式一：

  1. 不禁止autocommit

  2. 在执行inser / update / delete之前先执行一个begin;语句，进行了一段时间的数据库操作之后使用commit提交所有操作，或者使用rollback回滚所有操作

  注意：方式一要注意,一次commit或一次rollback表示最近一个begin到此次commit或rollback之间的操作都将提交或回滚，这之后的操作都必须重新设置begin，否则还会继续使用autocommit=ON的策略

• 方式二：

  1. 登录mysql数据库

  2. set autocommit='false';

  3. 查看是否警用： show variables like "autocommit"; （如果是OFF表示禁用成功）

  4. 执行一段insert / update / delete语句，检查无错误之后commit;这样，这段时间内的所有操作都将提交到数据库中，如果发现执行了不正确的insert / update / delete语句，则使用rollback，此时将会回滚到上一个commit之后数据库的状态

3. 示例

• 先准备数据

  # 注意：引擎必须用InnoDB
  create table bank_account(
  	id int not null auto_increment primary key,
  	name varchar(30) comment 'name',
  	balance decimal(18,2) comment '账户余额'
  )ENGINE=InnoDB;
  
  insert into bank_account(id,name,balance) values(1,'张三',0);
  insert into bank_account(id,name,balance) values(2,'李四',0);
  
  update bank_account set balance = balance+1000 where id=1;
  

  mysql> select * from bank_account;
  +----+------+---------+
  | id | name | balance |
  +----+------+---------+
  |  1 | 张三 | 1000.00 |
  |  2 | 李四 |    0.00 |
  +----+------+---------+
  2 rows in set (0.00 sec)
  

• 现在不用事务，自动提交进行操作：张三给李四转100

  # 现在是自动提交开启
  mysql> select @@autocommit;
  +--------------+
  | @@autocommit |
  +--------------+
  |            1 |
  +--------------+
  1 row in set (0.00 sec)
  
  # 张三：钱-100
  mysql> update bank_account set balance = balance-100 where name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched:1  Changed: 1  Warnings: 0
  
  # 李四：钱+100，但是由于sql语句错误，没有加成功
  mysql> update bank_account set balance = balance+100 where name='李';
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 0  Changed: 0  Warnings: 0
  
  # 检查，发现100块消失了
  mysql> select * from bank_account;
  +----+------+---------+
  | id | name | balance |
  +----+------+---------+
  |  1 | 张三 |  900.00 |
  |  2 | 李四 |    0.00 |
  +----+------+---------+
  2 rows in set (0.00 sec)
  

• 下面用事务的方式，注意关闭自动提交

  mysql> select * from bank_account;
  +----+------+---------+
  | id | name | balance |
  +----+------+---------+
  |  1 | 张三 | 1000.00 |
  |  2 | 李四 |    0.00 |
  +----+------+---------+
  2 rows in set (0.00 sec)
  
  # 关闭自动提交
  mysql> set autocommit=0;
  Query OK, 0 rows affected (0.00 sec)
  
  # 开启事务
  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  # 张三：钱-100
  mysql> update bank_account set balance = balance-100 where name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  # 李四：钱+100
  mysql> update bank_account set balance = balance+100 where name='李四';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  # 提交事务
  mysql> commit;
  Query OK, 0 rows affected (0.00 sec)
  
  # 查看，操作正确完成
  mysql> select * from bank_account;
  +----+------+---------+
  | id | name | balance |
  +----+------+---------+
  |  1 | 张三 |  900.00 |
  |  2 | 李四 |  100.00 |
  +----+------+---------+
  2 rows in set (0.00 sec)
  

• 试一下回滚

  # 先把张三的钱设为1000
  mysql> update bank_account set balance = 1000 where name='张三';
  Query OK, 0 rows affected (0.00 sec)
  Rows matched: 1  Changed: 0  Warnings: 0
  
  # 记录一个回滚位置
  mysql> commit;
  Query OK, 0 rows affected (0.12 sec)
  
  # 张三钱-100
  mysql> update bank_account set balance = balance-100 where name='张三';
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  # 不要commit，用rollback回滚到最近一个begin记录的回滚位置
  mysql> rollback;
  Query OK, 0 rows affected (0.13 sec)
  
  # 查表，发现已经回滚成功了
  mysql> select * from bank_account;
  +----+------+---------+
  | id | name | balance |
  +----+------+---------+
  |  1 | 张三 | 1000.00 |
  |  2 | 李四 |    0.00 |
  +----+------+---------+
  2 rows in set (0.00 sec)