事务的自动提交机制

InnoDB,所有用户活动都发生在事务中。

InnoDB 默认采用事务自动提交autocommit)机制。也就是说,如果不是显式开启一个事务,则每条 SQL 语句都形成独立事务。如果该语句执行后没有返回错误,MySQL 会自动执行 COMMIT。但如果该语句返回错误,则根据错误情况执行 COMMITROLLBACK

如何修改当前会话的提交模式?

1
2
3
4
5
6
7
8
9
SHOW VARIABLES LIKE 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+

--1 或者 ON 表示启用, 0 或者 OFF 表示禁用
SET AUTOCOMMIT = 0;

注意:

  • 关闭后,会话将始终开启一个事务。直到你显式提交或回滚该事务后,一个新事务又被开启。
  • 如果一个关闭了 autocommit 的会话没有显式提交事务,然后会话被关闭,MySQL 将回滚该事务。
  • 有一些命令,在执行之后会强制执行 COMMIT 提交当前的活动事务。例如:
    • ALTER TABLE
    • LOCK TABLES

提交多语句事务

如何在一个事务中组合多条 SQL 语句(multiple-statement transaction)?有两种方式:

  1. 方式一:显式关闭当前会话的 autocommit,然后提交或回滚事务。

    1
    2
    3
    SET autocommit=0;
    INSERT INTO parent VALUES (10, 'Heikki');
    COMMIT;
  2. 方式二:如果不想关闭 autocommit,可以通过 START TRANSACTIONBEGIN 语句显式开启事务,然后通过 COMMITROLLBACK 语句显式结束事务。

    1
    2
    3
    4
    5
    START TRANSACTION;
    INSERT INTO parent VALUES (15, 'John');
    INSERT INTO parent VALUES (20, 'Paul');
    DELETE FROM parent WHERE b = 'Heikki';
    ROLLBACK;

最终结果:

1
2
3
4
5
6
SELECT * FROM parent;
+------+--------+
| id | name |
+------+--------+
| 10 | Heikki |
+------+--------+

在事务中混合使用存储引擎问题

MySQL 服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。

如果在事务中混合使用了事务型和非事务型的表(例如 InnoDBMyISAM 表),可能会有意想不到的情况发生。请看下例:

1
2
3
4
5
6
7
--引入一张 MyISAM 表
CREATE TABLE `people` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`last_name` varchar(50) NOT NULL,
`first_name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM;

示例一,在事务中混合使用存储引擎,出现报错:

1
2
3
4
5
6
START TRANSACTION;
--执行成功
INSERT INTO parent(name) VALUES('Heikki');
--执行失败
INSERT INTO people(last_name, first_name) VALUES('pete', 'Lee');
1785 - When @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1, updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

示例二,当事务回滚,非事务型的表上的变更无法撤销,这会导致数据库处于不一致的状态,这种情况很难修复,事务的最终结果将无法确定。

1
2
3
4
5
START TRANSACTION;
INSERT INTO people(last_name, first_name) VALUES('pete', 'Lee');
INSERT INTO parent(name) VALUES('Heikki');
--parent表(InnoDB)回滚成功,people表(MyISAM)回滚失败
ROLLBACK;

所以,为每张表选择合适的存储引擎非常重要。

参考

《高性能 MySQL》

https://dev.mysql.com/doc/refman/5.7/en/innodb-consistent-read.html

https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-transactions.html