mysql 内置功能 事务 介绍

 

事务用于将某些操作的多个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语句有错误时候，执行失败，立马所有执行回滚操作