一.事务
0.引入
在mysql中，事务是一个最小的不可分割的单元，事务能保障一个业务的完整性。
例如银行转账：
a ----> -100
update user set money = money = money - 100 where name = 'a';b ----> +100
update user set money = money = money + 100 where name = 'b';实际程序中，如果只有一条程序执行成功，而另一条没有成功，出现数据不一致。
update user set money = money = money - 100 where name = 'a';
update user set money = money = money + 100 where name = 'b';多条sql程序，有同事执行成功的需求。要么就同时失败。
mysql中如何控制事务？
1.mysql是默认开启事务的（自动提交）。
select @@autocommit;+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+默认事务开启的作用是什么？
当我们去执行一个说起来语句时，效果会立即体现出来，且不能回滚。
create database bank;
use bank;
create table user(id int primary key,name varchar(20),money int
);insert into user value(1,'张三',2000);mysql> select * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
+----+------+-------+回滚（rollback）：撤销sql语句执行效果；
rollback;
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
+----+------+-------+
1 row in set (0.00 sec)那么该如何撤销呢？
设置mysql自动提交为FALSE：
set autocommit = 0;mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)select @@autocommit;+--------------+
| @@autocommit |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)上面的操作关闭了mysql的自动提交（commit）；
测试一下：
mysql> insert into user value(2,'李四',6000);
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+----+------+-------+ ##这是虚拟的表，真是的表没有提交
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
| 2 | 李四 | 6000 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from user;
+----+------+-------+##真是的表还未提交。
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
+----+------+-------+
1 row in set (0.00 sec)如何让他真是的插入数据呢？
##再一次插入数据
mysql> insert into user value(2,'李四',6000);
Query OK, 1 row affected (0.00 sec)
##手动插入数据
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
##无法撤销
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
| 2 | 李四 | 6000 |
+----+------+-------+
2 rows in set (0.00 sec)小结
自动提交；@@autocommmit = 1
手动提交；commit;
事务回滚；rollback;
如果说这时候转账，
update user set money = money - 100 where name = '张三';
update user set money = money + 100 where name = '李四';mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
| 2 | 李四 | 6000 |
+----+------+-------+
2 rows in set (0.00 sec)2.手动开启事务
set autocommit = 1;mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
1 row in set (0.00 sec)除了上述方法之外，begin; 或者 start transaction;都可以手动开启一个事务。
mysql> select *from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 2000 |
| 2 | 李四 | 6000 |
+----+------+-------+
mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from user;
+----+------+-------+## rollback是没有效果的
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)手动开启事务（1）
mysql> begin ;
Query OK, 0 rows affected (0.00 sec)mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1800 |
| 2 | 李四 | 6200 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from user;
+----+------+-------+##成功回滚了
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)手动开启事务（2）
mysql> start transaction;mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> update user set money = money - 100 where name = '张三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update user set money = money + 100 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1800 |
| 2 | 李四 | 6200 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.01 sec)mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)
事务开启之后，一旦commit调，就不能回滚。
mysql> commit;
Query OK, 0 rows affected (0.00 sec)mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)mysql> rollback;
Query OK, 0 rows affected (0.00 sec)mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | 张三 | 1900 |
| 2 | 李四 | 6100 |
+----+------+-------+
2 rows in set (0.00 sec)3.事物的特征
A.原子性：事务是最小的单位，不可分割；
C.一致性：事务要求，同一事物中的sql语句，必须保证同时成功或同时失败；
I . 隔离性：事务a和事务b之间是有隔离性的；
D.持久性：事务一旦结束（commit; rollback），就不可能返回。
4.事物的开启
①事务默认提交： set autocommit = 0;
②begin;
③start transection;
5.事务手动提交
commit;
6.事务手动回滚
rollback;
