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

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

• 说明

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

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

  • 事务用于管理INSERT、UPDATE和DELETE语句&＃xff08;增、删、改 这些会影响数据库中的数据&＃xff0c;查询不影响就不管了&＃xff09;

• 举例

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

    1. 从张三的账户减去100元&＃xff1b;

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

    试问&＃xff0c;如果操作①执行成功&＃xff0c;操作②执行失败会发生什么情况&＃xff1f;

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

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

1. 语法

# 查看自动提交是否开启
select &＃64;&＃64;autocommit; # 方式1
show variables like "autocommit"; # 方式2# 开启事务
begin;# 开启事务的另一种方法
start transaction;# 对于一个事务&＃xff0c;只有两种操作
rollback; # 回滚
commit # 提交# 关闭自动提交&＃xff1a;
set autocommit&＃61;0;# 开启自动提交&＃xff1a;
set autocommit&＃61;1;


2. 事务的两种使用方式

• 方式一&＃xff1a;

  1. 不禁止autocommit

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

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

• 方式二&＃xff1a;

  1. 登录mysql数据库

  2. set autocommit&＃61;&＃39;false&＃39;;

  3. 查看是否警用&＃xff1a; show variables like "autocommit"; &＃xff08;如果是OFF表示禁用成功&＃xff09;

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

3. 示例

• 先准备数据

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


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


• 现在不用事务&＃xff0c;自动提交进行操作&＃xff1a;张三给李四转100

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


• 下面用事务的方式&＃xff0c;注意关闭自动提交

  mysql> select * from bank_account;
&＃43;----&＃43;------&＃43;---------&＃43;
| id | name | balance |
&＃43;----&＃43;------&＃43;---------&＃43;
| 1 | 张三 | 1000.00 |
| 2 | 李四 | 0.00 |
&＃43;----&＃43;------&＃43;---------&＃43;
2 rows in set (0.00 sec)# 关闭自动提交
mysql> set autocommit&＃61;0;
Query OK, 0 rows affected (0.00 sec)# 开启事务
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)# 张三&＃xff1a;钱-100
mysql> update bank_account set balance &＃61; balance-100 where name&＃61;&＃39;张三&＃39;;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0# 李四&＃xff1a;钱&＃43;100
mysql> update bank_account set balance &＃61; balance&＃43;100 where name&＃61;&＃39;李四&＃39;;
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)# 查看&＃xff0c;操作正确完成
mysql> select * from bank_account;
&＃43;----&＃43;------&＃43;---------&＃43;
| id | name | balance |
&＃43;----&＃43;------&＃43;---------&＃43;
| 1 | 张三 | 900.00 |
| 2 | 李四 | 100.00 |
&＃43;----&＃43;------&＃43;---------&＃43;
2 rows in set (0.00 sec)


• 试一下回滚

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