事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性
创建数据库db12
create database db12 charset=utf8;
use db12;
create table user(
id int primary key auto_increment,
name char(32),
balance int
); insert into user(name,balance)
values
('wsb',1000),
('egon',1000),
('ysb',1000);
mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 4 | wsb | 1000 |
| 5 | egon | 1000 |
| 6 | ysb | 1000 |
+----+------+---------+
3 rows in set (0.00 sec)
#原子操作
start transaction; 开启事务
事务结束：End Transaction;
start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元
commit;
commit ;提交
#出现异常，回滚到初始状态
rollback ;回滚
只要没有commit提交 就可以回滚到初始的金额start transaction;
update user set balance=900 where name='wsb'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到
rollback;
commit;
没有commit; 执行rollback;
mysql> start transaction; mysql> update user set balance=900 where name='wsb'; #买支付100元
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1010 where name='egon'; #中介拿走10元
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1090 where name='ysb'; #卖家拿到90元
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | wsb | 900 |
| 2 | egon | 1010 |
| 3 | ysb | 1090 |
+----+------+---------+
3 rows in set (0.00 sec)
回滚到初始
mysql> rollback;
Query OK, 0 rows affected (0.12 sec) mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 1 | wsb | 1000 |
| 2 | egon | 1000 |
| 3 | ysb | 1000 |
+----+------+---------+
3 rows in set (0.00 sec)
commit ; 提交后 再执行 rollback; 不能回滚了
只要不执行commit; 就不会修改数据 你都可以任意回滚回去
ysql> update user set balance=900 where name='wsb'; #买支付100元
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1010 where name='egon'; #中介拿走10元
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> update user set balance=1090 where name='ysb'; #卖家拿到90元
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 4 | wsb | 900 |
| 5 | egon | 1010 |
| 6 | ysb | 1090 |
+----+------+---------+
3 rows in set (0.00 sec) mysql> commit;
Query OK, 0 rows affected (0.00 sec) mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 4 | wsb | 900 |
| 5 | egon | 1010 |
| 6 | ysb | 1090 |
+----+------+---------+
3 rows in set (0.00 sec) mysql> rollback;
Query OK, 0 rows affected (0.00 sec) mysql> select * from user;
+----+------+---------+
| id | name | balance |
+----+------+---------+
| 4 | wsb | 900 |
| 5 | egon | 1010 |
| 6 | ysb | 1090 |
+----+------+---------+
3 rows in set (0.00 sec)
开启事务检测sql语句的执行，如果监测到一条sql语句有错误时候，执行失败，立马所有执行回滚操作