MySQL 数据库事务

摘要：简介事务是一组原子性的查询或者说是一个独立的工作单元在事务内的语句要么全部执行成功要么全部执行失败事务的性质数据库事务拥有以下四个特性即性质原子性事务作为一个整体被执行包含在其中的对数据库的操作要么全部执行成功要么全部失败回滚对于一个事务来

事务是一组原子性的 SQL 查询, 或者说是一个独立的工作单元. 在事务内的语句, 要么全部执行成功, 要么全部执行失败.

数据库事务拥有以下四个特性, 即 ACID 性质:

原子性(Atomicity): 事务作为一个整体被执行, 包含在其中的对数据库的操作要么全部执行成功, 要么全部失败回滚. 对于一个事务来说, 不可能只执行其中一部分操作, 这就是事务的原子性.

一致性(Consistency): 事务应确保数据库的状态从一个一致状态转变为另一个一致状态.

隔离性(Isolation): 多个事务并发执行时, 一个事务的执行不应影响其他事务的执行.

持久性(Durability): 已被提交的事务对数据库的修改应该永久保存在数据库中.

SQL 中定义了四种隔离级别, 每种级别都规定了一个事务所做的修改,哪些在事务内和事务间是可见的, 哪些是不可见的. 较低的隔离通常可以执行更高的并发, 系统的开销也更低.
SQL 标准中的四种隔离有:

READ UNCOMMITED(未提交读)
在 READ UNCOMMITED 级别中, 事务的修改, 即使没有提交, 对其他事务也是可见的. 其他事务可以读取此事务中的未提交的数据, 这也被称为脏读(Dirty Read). 此事务隔离级别会导致很多问题, 并且性能也不会比其他事务隔离级别好多少, 因此在实际环境中很少使用.

READ COMMITED(提交读)
大多数的数据库默认隔离级别都是 READ COMMITED, 但是 MySQL 并不是. 在 READ COMMITED 级别中, 一个事务从开始到提交之前, 所做的任何修改对其他事务都是不可见的.

这个级别有时候也叫做不可重复读(nonrepeatable read), 因为两次执行相同的查询, 可能会得到不一样的结果.

REPEATABLE READ(可重复读)
REPEATABLE READ 解决了脏读的问题. 该级别保证了在同一个事务中多次读取同样记录的结果时一致的. 但是理论上, 可重复读隔离级别还是无法解决另一个幻读(Phantom Read)的问题. 所谓幻读, 指的是当某个事务在读取某个范围内的记录时, 另外一个事务又在该范围内插入了新的记录, 当之前的事务再次读取该范围的记录时, 会产生幻行(Phantom Row).

可重复读是 MySQL 的默认事务隔离级别.

SERIALIZABLE(可串行化)
SERIALIZABLE 是最高的隔离级别. 他通过强制事务串行执行, 避免了前面说的幻读的问题. 简单来说, SERIALIZABLE 会在读取的每一行数据上都加上锁, 所以可能导致大量的超时和锁争用的问题. 实际应用中也很少用到这个隔离级别, 只有在非常需要确保数据的一致性而且可以接受没有并发的情况下, 才考虑采用该级别.

死锁是指两个或多个事务在同一个资源上相互占用, 并请求锁定对方占用的资源, 从而导致恶性循环的现象. 当多个事务试图以不同顺序锁定资源时, 就可能产生死锁.
死锁发生以后, 只有部分或者完全回滚其中一个事务, 才能打破死锁.

在 MySQL 提供的众多存储引擎中, 只有 InnoDB 和 NDB Cluster 支持事务.

MySQL 默认采用自动提交(AUTOCOMMIT) 模式. 即如果不显示地开始一个事务, 则每个操作都被当做一个事务执行提交操作.
我们可以通过

SHOW VARIABLES LIKE "autocommit";

查询当前是否已经开启了字段提交事务, 例如:

mysql> SHOW VARIABLES LIKE "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果是 ON, 则表示已经开启了, 0 或 OFF 表示禁用.
可以通过 "set autocommit=0;" 来禁用自动提交:

set autocommit=0;

对于非事务型存储引擎, 例如 MyISAM, 修改 AUTOCOMMIT 属性试不会有影响的.

下面以一个例子来展示 autocommit 启动和非启动时的区别.
首先建立一个测试用的表:

CREATE TABLE `user` (
  `id`   BIGINT(20) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20)         DEFAULT "",
  `age`  INT(11)             DEFAULT "0",
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4

接着关闭自动提交功能:

mysql> set autocommit=0;

然后插入一个数据:

mysql> INSERT INTO user (`id`, `name`, `age`) VALUES (1, "xys", 18);

接着查看数据:

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xys  |   18 |
+----+------+------+
1 row in set (0.00 sec)

数据库中可以查询到这条数据了.
但是我们通过 SHOW BINLOG EVENTS 查看操作日志:

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)

会发现上面的输出中, 并没有插入数据相关的记录, 并且此时如果我们退出 MySQL 的话, 那么我们插入的数据并没有被保存:

mysql> exit
Bye
>>> mysql -u root -p
Enter password:

mysql> use test;
Database changed
mysql> select * from user;
Empty set (0.00 sec)

mysql>

如果我们不退出, 而是输入COMMIT; 时, 那么此时数据才真正保存到 MySQL 中:

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= "ANONYMOUS"  |
| mysql-bin.000001 | 219 | Query          |         1 |         291 | BEGIN                                 |
| mysql-bin.000001 | 291 | Table_map      |         1 |         342 | table_id: 108 (test.user)             |
| mysql-bin.000001 | 342 | Write_rows     |         1 |         394 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000001 | 394 | Xid            |         1 |         425 | COMMIT /* xid=58 */                   |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

从上面的操作中我们可以看到, 当禁用了 AUTOCOMMIT 后, 我们对数据库的写入操作并不会实际落地到数据库中, 除非我们显示地提交事务.

接下来, 我们使能 AUTOCOMMIT, 再次进行相同的操作.

mysql> set autocommit=1;
mysql> SHOW VARIABLES LIKE "autocommit";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

然后插入一个数据:

mysql> INSERT INTO user (`id`, `name`, `age`) VALUES (1, "xys", 18);

接着查看数据:

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | xys  |   18 |
+----+------+------+
1 row in set (0.00 sec)

数据库中可以查询到这条数据了.
我们再次查看 binlog, 对比一下和禁用 AUTOCOMMIT 时有什么差别:

mysql> show binlog events;
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.13-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids |         1 |         154 |                                       |
| mysql-bin.000001 | 154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= "ANONYMOUS"  |
| mysql-bin.000001 | 219 | Query          |         1 |         291 | BEGIN                                 |
| mysql-bin.000001 | 291 | Table_map      |         1 |         342 | table_id: 108 (test.user)             |
| mysql-bin.000001 | 342 | Write_rows     |         1 |         394 | table_id: 108 flags: STMT_END_F       |
| mysql-bin.000001 | 394 | Xid            |         1 |         425 | COMMIT /* xid=292 */                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
7 rows in set (0.00 sec)

我们看到, 和禁用 AUTOCOMMIT 不同的是, 使能 AUTOCOMMIT 时, 每个写操作都会进行事务的提交. 即上面的 insert 操作等效为:

BEGIN;
INSERT INTO user (`id`, `name`, `age`) VALUES (1, "xys", 18);
COMMIT;

本文由 yongshun 发表于个人博客, 采用署名-非商业性使用-相同方式共享 3.0 中国大陆许可协议.
非商业转载请注明作者及出处. 商业转载请联系作者本人
Email: yongshun1228@gmail.com
本文标题为: MySQL 数据库事务
本文链接为: https://segmentfault.com/a/11...