目录
1, 什么是事务
事务就是将多条sql语句作为一个整体来执行,要不全部成功,要么全部失败
语法:
begin;
sql语句1;
...;
sql语句n;
commit;
# 注意begin到commit的四条语句为一个事务,每条语句都是以分号结束, commit表示提交该事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update dept set sal = sal - 100 where deptno = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update dept set sal = sal + 100 where deptno = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.15 sec)
mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname | loc | sal |
+--------+------------+----------+------+
| 10 | ACCOUNTING | NEW YORK | 900 |
| 20 | RESEARCH | DALLAS | 1100 |
| 30 | SALES | CHICAGO | 1000 |
| 40 | OPERATIONS | BOSTON | 1000 |
| 50 | fdsf | fdsaf | 1000 |
| 60 | fdsf | fdsaf | 1000 |
| 70 | aaa | bbb | 1000 |
| 80 | fdabb | bbb | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)
2, 回滚事务
若先让事务执行失败,可以用如下的回滚事务实现
语法:
begin;
sql语句1;
...;
sql语句n;
rollback;
mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname | loc | sal |
+--------+------------+----------+------+
| 10 | ACCOUNTING | NEW YORK | 900 |
| 20 | RESEARCH | DALLAS | 1100 |
| 30 | SALES | CHICAGO | 1000 |
| 40 | OPERATIONS | BOSTON | 1000 |
| 50 | fdsf | fdsaf | 1000 |
| 60 | fdsf | fdsaf | 1000 |
| 70 | aaa | bbb | 1000 |
| 80 | fdabb | bbb | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)
# begin表示开始事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update dept set sal = sal + 100 where deptno = 10;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update dept set sal = sal - 100 where deptno = 20;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname | loc | sal |
+--------+------------+----------+------+
| 10 | ACCOUNTING | NEW YORK | 1000 |
| 20 | RESEARCH | DALLAS | 1000 |
| 30 | SALES | CHICAGO | 1000 |
| 40 | OPERATIONS | BOSTON | 1000 |
| 50 | fdsf | fdsaf | 1000 |
| 60 | fdsf | fdsaf | 1000 |
| 70 | aaa | bbb | 1000 |
| 80 | fdabb | bbb | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)
# rollback表示回滚该事务
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from dept;
+--------+------------+----------+------+
| deptno | dname | loc | sal |
+--------+------------+----------+------+
| 10 | ACCOUNTING | NEW YORK | 900 |
| 20 | RESEARCH | DALLAS | 1100 |
| 30 | SALES | CHICAGO | 1000 |
| 40 | OPERATIONS | BOSTON | 1000 |
| 50 | fdsf | fdsaf | 1000 |
| 60 | fdsf | fdsaf | 1000 |
| 70 | aaa | bbb | 1000 |
| 80 | fdabb | bbb | 1000 |
+--------+------------+----------+------+
8 rows in set (0.00 sec)