言文七(senver_wen)的博客

记录,分享,成长!

数据库事物——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 !

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/senver_wen/article/details/79951356
个人分类: 数据库
想对作者说点什么? 我来说一句

java连接mysql

2011年12月25日 55KB 下载

没有更多推荐了,返回首页

不良信息举报

数据库事物——mysql为例

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭