MySQL事务简介

原创只是甲 2020-07-30

1094

备注:测试数据库版本为MySQL 8.0

这个blog我们来聊聊MySQL 事务

一.事务的定义
二.事务的属性
三.事务的隔离级别
四.MySQL的事务

4.1 自动提交
4.2 事务的隔离级别
4.3 MySQL开启和关闭事务
4.4 MySQL 事务的savepoint

一.事务的定义

数据库事务(Database Transaction) ，是指作为单个逻辑工作单元执行的一系列操作，要么完整地执行，要么完全地不执行。事务处理可以确保除非事务性单元内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元，可以简化错误恢复并使应用程序更加可靠。一个逻辑工作单元要成为事务，必须满足所谓的ACID（原子性、一致性、隔离性和持久性）属性。

举个银行转账的例子:
A从银行转账1w块钱给B，此时数据库会涉及3个操作:
1.新增一条交易记录，记录A转账给B
2.A银行卡余额减少1w
3.B银行卡余额增加1w

那么这3个操作是一个整体，要么一起成功，要么一起失败，不会存在只有部分成功。
例如A银行卡余额减少1w，但是B银行卡余额没有增加1w，这就会出大问题了。

二.事务的属性

事务的四个属性：
1.原子性（Atomic）（Atomicity)
事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。通常，与某个事务关联的操作具有共同的目标，并且是相互依赖的。如果系统只执行这些操作的一个子集，则可能会破坏事务的总体目标。原子性消除了系统处理操作子集的可能性。
2.一致性（Consistent）(Consistency)
事务在完成时，必须使所有的数据都保持一致状态。在相关数据库中，所有规则都必须应用于事务的修改，以保持所有数据的完整性。事务结束时，所有的内部数据结构（如 B 树索引或双向链表）都必须是正确的。某些维护一致性的责任由应用程序开发人员承担，他们必须确保应用程序已强制所有已知的完整性约束。例如，当开发用于转帐的应用程序时，应避免在转帐过程中任意移动小数点。
3.隔离性（Insulation）(Isolation)
由并发事务所作的修改必须与任何其它并发事务所作的修改隔离。事务查看数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是另一事务修改它之后的状态，事务不会查看中间状态的数据。这称为隔离性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。当事务可序列化时将获得最高的隔离级别。在此级别上，从一组可并行执行的事务获得的结果与通过连续运行每个事务所获得的结果相同。由于高度隔离会限制可并行执行的事务数，所以一些应用程序降低隔离级别以换取更大的吞吐量。
4.持久性（Duration）(Durability）
事务完成之后，它对于系统的影响是永久性的。该修改即使出现致命的系统故障也将一直保持。

三.事务的隔离级别

直接看图:

事务的存在就是为了解决并发总数据一致性的问题
并发期间，可能会产生几种问题
1.dirty read(脏读)
某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

2.不可重复读(Non-repeatable read)
在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

3.幻读(Phantom Read)
在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

四.MySQL的事务

MySQL中只有InnoDB、NDB Cluster存储引擎支持事务，其它的存储引擎是不支持事务的。

4.1 自动提交

-- MySQL自动提交是默认开启的
show variables like 'AUTOCOMMIT';
-- 关闭MySQL自动提交
set autocommit =0;
show variables like 'AUTOCOMMIT';
-- 再次开启MySQL自动提交
set autocommit =1;
show variables like 'AUTOCOMMIT';

测试记录:

mysql> show variables like 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

mysql>
mysql> set autocommit =0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> set autocommit =1;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'AUTOCOMMIT';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

4.2 事务的隔离级别

MySQL的默认事务隔离级别是 REPEATABLE-READ(可重复读)

select @@tx_isolation;
却提示错误:Unknown system variable ‘tx_isolation’

在MySQL 8.0.3 中，该变量已经被 transaction_isolation 替换了。

-- MySQL 8.0 查询事务隔离级别
select @@transaction_isolation;
show variables like 'transaction_isolation';

调整MySQL的事务隔离级别

-- 查询事务隔离级别
select @@transaction_isolation;
-- 修改当前事务隔离级别
set session transaction isolation level READ COMMITTED;
-- 修改全局事务隔离级别
set global transaction isolation level READ COMMITTED;

测试记录:

mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)
mysql> set session transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> set global transaction isolation level READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-COMMITTED          |
+-------------------------+

4.3 MySQL开启和关闭事务

语法；

START TRANSACTION
    [transaction_characteristic [, transaction_characteristic] ...]

transaction_characteristic: {
    WITH CONSISTENT SNAPSHOT
  | READ WRITE
  | READ ONLY
}

BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET autocommit = {0 | 1}

开启事务的三种方法:
1.autocommit
set autocommit=0;
关闭自动提交所有事务都需要手工commit 或 rollback

START TRANSACTION
开启单个事务，不受autocommit影响
BEGIN
开启单个事务，不受autocommit影响

-- 关闭自动提交  需要手工commit 或 rollback
set autocommit=0;
-- 开启事务
START TRANSACTION;
BEGIN;

autocommit测试记录

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> create table t8(id int,name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t8 values (1,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
-- rollback对insert语句进行回滚了，所以表的数据依旧为空
mysql> select * from t8;
Empty set (0.00 sec)

mysql>
mysql>
mysql> set autocommit = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t8 values (1,'abc');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
-- 因为自动提交已开启，所以上面的rollback无用
mysql> select * from t8;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql>

start tranaction和begin测试记录:

mysql> create table t9(id int,name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t9 values (1,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
-- begin 开启了一个事务，进行回滚后 insert会失效
mysql> select * from t9;
Empty set (0.00 sec)

mysql>
mysql>
mysql>
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t9 values (2,'def');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t9;
+------+------+
| id   | name |
+------+------+
|    2 | def  |
+------+------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
-- start transaction 开启了一个事务，进行回滚后 insert会失效
mysql> select * from t9;
Empty set (0.00 sec)

4.4 MySQL 事务的savepoint

savepoiont用于事务中，记录事务的中间状态，rollback可以回滚到指定的savepoint点

create table t10(id int,name varchar(100));
begin;
insert into t10(id,name) values (1,'abc');
select * from t10;
-- 保留此时的状态，命名为a
savepoint a;
insert into t10(id,name) values (2,'def');
select * from t10;
-- 会滚到savepoint a的位置
rollback to a;
-- 此时可以看到 第一条insert成功，第二条insert失败了
select * from t10;

测试记录:

mysql> create table t10(id int,name varchar(100));
Query OK, 0 rows affected (0.02 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t10(id,name) values (1,'abc');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql> savepoint a;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t10(id,name) values (2,'def');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t10;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
|    2 | def  |
+------+------+
2 rows in set (0.00 sec)
mysql> rollback to a;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t10;
+------+------+
| id   | name |
+------+------+
|    1 | abc  |
+------+------+
1 row in set (0.00 sec)

mysql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

MySQL运维（共58篇）

MySQL运维

MySQL事务简介

Table of Contents

一.事务的定义

二.事务的属性

三.事务的隔离级别

四.MySQL的事务

4.1 自动提交

4.2 事务的隔离级别

4.3 MySQL开启和关闭事务

4.4 MySQL 事务的savepoint

文章被以下合辑收录

评论