数据库事物——mysql为例

事物

在数据库中，事物就是一组操作，是互相联系的，不可分割，要么做，要么不做。
重要的是事务控制，以确保数据的完整性和处理数据库错误。

事物的四大属性

1. 原子性(Atomicity):事务是一个完整的操作。
2. 一致性（Consistency）：当事务完成时，数据必须处于一致状态。
3. 隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
4. 持久性(Durability):事务完成后，它对于系统的影响是永久性的，不可能被回滚。

T-SQL中管理事务的语句：

1. 开始事务（ begin transaction）：标记事务开始
2. 提交事务（commit transaction）：表示一组事物已经完成了，数据也处理完毕。
3. 回滚事务（rollback transaction）：回滚就是在数据处理时出错，就回滚到数据没有处理之前。
4. 保留点（savepoint）：这个真的很重要，你想如果你在之前已经处理了很多数据，但是后边的一个数据出了错，怎么办？难道要直接回滚到开始吗？事实上是不用的，只要你在前面的操作中设置了保留点，回滚到那里即可。（如果你用过Git就不难理解这种操作）。

mysql中的事物

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务
ROLLBACK 事务回滚
COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

举个例子

正常事物操作

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
+----+------+--------------+
3 rows in set (0.00 sec)

MariaDB [school]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> insert into teacher values(5,10,"A");
Query OK, 1 row affected (0.03 sec)

MariaDB [school]> insert into teacher values(6,10,"B");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> commit;
Query OK, 0 rows affected (0.01 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
|  5 |   10 | A            |
|  6 |   10 | B            |
+----+------+--------------+
5 rows in set (0.01 sec)

回滚事物

MariaDB [school]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> insert into teacher values(7,10,"c");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> rollback;
Query OK, 0 rows affected (0.02 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
|  5 |   10 | A            |
|  6 |   10 | B            |
+----+------+--------------+
5 rows in set (0.00 sec)

事物提交后回滚

MariaDB [school]> insert into teacher values(7,10,"c");
Query OK, 1 row affected (0.15 sec)

MariaDB [school]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> rollback;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
|  5 |   10 | A            |
|  6 |   10 | B            |
|  7 |   10 | c            |
+----+------+--------------+
6 rows in set (0.00 sec)

设置保存点

MariaDB [school]> begin;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> insert into teacher values(11,10,"G");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> savepoint Gpoint;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> rollback to Gpoint;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
|  5 |   10 | A            |
|  6 |   10 | B            |
|  7 |   10 | c            |
|  8 |   10 | D            |
|  9 |   10 | E            |
| 10 |   10 | F            |
| 11 |   10 | G            |
+----+------+--------------+
10 rows in set (0.00 sec)

MariaDB [school]> insert into teacher values(12,10,"H");
Query OK, 1 row affected (0.00 sec)

MariaDB [school]> savepoint Hpoint;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> rollback to Gpoint;
Query OK, 0 rows affected (0.00 sec)

MariaDB [school]> select * from teacher;
+----+------+--------------+
| id | age  | teacher_name |
+----+------+--------------+
|  1 |   34 | wen          |
|  2 |   45 | lei          |
|  3 |   43 | jia          |
|  5 |   10 | A            |
|  6 |   10 | B            |
|  7 |   10 | c            |
|  8 |   10 | D            |
|  9 |   10 | E            |
| 10 |   10 | F            |
| 11 |   10 | G            |
+----+------+--------------+
10 rows in set (0.00 sec)

---

---

END !