MySQL的事务处理(Transation)和自动执行(AutoCommit)与提交类型(Completion)

最新推荐文章于 2024-08-15 02:00:00 发布

原创最新推荐文章于 2024-08-15 02:00:00 发布 · 2.3k 阅读

3 ·

CC 4.0 BY-SA版权

SQL 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了事务的概念，它用于处理操作量大、复杂度高的数据。阐述了MySQL支持事务的引擎，以及使用事务的两种方式：隐式事务和显式事务。通过多个验证例子，探讨了在不同配置和异常情况下事务的执行情况，如自动提交、唯一性约束异常等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、事务（transaction）
事务在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务主要用于处理操作量大，复杂度高的数据。如果你要删除一条主表信息，而主表有多个从表的时候，你需要有步骤地删除明细后再删除主表信息，这个过程极其容易出错，那么这个时候就是用事务才处理是最合适的了。

2、事务（Transcation）的用法

事务开始(start transaction or begin)
提交(commit)
回滚(rollback)

3、MYSQL支持的引擎(InnoDB)

show engines;

在这里插入图片描述

4、自动执行(AutoCommit)与提交类型(Completion)
使用事务有两种方式，分别为隐式事务和显式事务。隐式事务实际上就是自动提交，Oracle 默认不自动提交，需要手写 COMMIT。在MySQL中，自动提交（autocommit）在支持事务（transaction）的引擎中，若autocommit=true,则不需要commit的情况下直接提交语句形成永久性修改，Mysql默认打开autocommit,也可以通过配置设置。

set autocommit=0;(AutoCommit Off)
set autocommit=1;(AutoCommit On)
set completion_type=0;(No Chain)
set completion_type=1;(Chain)
set completion_type=2;(Release)

也可以通过语句查询当前配置

show variables like '%autocommit%';
show variables   like '%completion%';

在这里插入图片描述

5、验证例子

mysql> BEGIN;
    -> INSERT INTO test SELECT '关羽';
    -> COMMIT;
    -> BEGIN;
    -> INSERT INTO test SELECT '张飞';
    -> INSERT INTO test SELECT '张飞';
    -> ROLLBACK;
    -> SELECT * FROM test;
    -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.03 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'

mysql> select * from test;//

当前窗口结果：
在这里插入图片描述
新建窗口结果：

结论：由于test表中name的唯一性约束,看代码的话，两个"张飞"执行后（无论有无触发主键约束），实际由于第二个"张飞"触发了唯一性约束异常，所以我认为事务会跳出,所以在当前连接中，test表中可以看到一个张飞,实际上第二个事务，并没有提交成功。

接下来，我们试试正常提交的数据。

mysql> BEGIN;
    -> INSERT INTO test SELECT '关羽';
    -> COMMIT;
    -> BEGIN;
    -> INSERT INTO test SELECT '张飞';
    -> INSERT INTO test SELECT '刘备';
    -> ROLLBACK;
    -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.04 sec)


mysql> select * from test;//

当前窗口结果：
在这里插入图片描述
新建窗口结果：

结论：对比这次的测试结果，所以我认为第一个测试，并没有执行rollback,而是跳出事务处理异常机制了。

因为mysql默认打开了autocommit,那么我想验证下在无显示事务的情况下(即无begin开头)，两个"张飞"是如何自处的？

mysql> BEGIN;
    -> INSERT INTO test SELECT '关羽';
    -> COMMIT;
    -> INSERT INTO test SELECT '张飞';
    -> INSERT INTO test SELECT '张飞';
    -> ROLLBACK;
    -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.05 sec)
Records: 1  Duplicates: 0  Warnings: 0

ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'
mysql> select *from test;//

当前窗口结果：
在这里插入图片描述
新建窗口结果：

结论：其实看执行结果就知道了，第1个"张飞"作为单独的事务已经执行成功了，第二个"张飞"由于触发了主键约束所以执行失败了。除了显示事务外，有没有办法在没结束事务前(无Commit)前，可以让两个"张飞"合并成一个事务呢？
可以设置set completion_type=1;(chain)，也就是说无论几个"张飞"在还没提交之前，都是一个事务。

代码与上面的一样。

mysql> SET @@completion_type = 1;
    -> BEGIN;
    -> INSERT INTO test SELECT '关羽';
    -> COMMIT;
    -> INSERT INTO test SELECT '张飞';
    -> INSERT INTO test SELECT '张飞';
    -> ROLLBACK;
    -> //
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
Query OK, 0 rows affected (0.02 sec)
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0
ERROR 1062 (23000): Duplicate entry '张飞' for key 'PRIMARY'

当前窗口结果：
在这里插入图片描述
新建窗口结果：

结论：因为我设置了set completion_type=1(chain),相当与在第一个"张飞"前加了begin,所以第二个"张飞"由于触发了主键约束所以导致事务失败，插入数据失败。