mysql 事务

最新推荐文章于 2022-11-05 21:00:24 发布

路飞小朋友

最新推荐文章于 2022-11-05 21:00:24 发布

阅读量288

点赞数

分类专栏： MySQL

本文链接：https://blog.csdn.net/wozhangyi/article/details/109920037

版权

MySQL 专栏收录该内容

34 篇文章 1 订阅

订阅专栏

为什么要有事务？

事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景：A用户和B用户是银行的储户。现在A要给B转账100元。那么需要做以下几件事：

检查A的账户余额>100元；
A账户扣除100元；
B账户增加100元；

正常的流程走下来，A账户扣了100，B账户加了100，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？A白白损失了100，而B也没有收到本该属于他的100。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。

MySQL 事务简介

基本概念

事务就是一组dml语句，这些语句存在逻辑上的相关性，这一组dml语句要么全部成功，要么全部失败，是一个整体，mysql提供一种机制，保证我们可以达到这样的效果

在 mysql 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 sal 语句要么全部执行，要么全部不执行。
事务用来管理 insert，update，delete 语句（dml）

在mysql 命令行的默认设置下，事务都是自动提交的，即执行 sql 语句后就会马上执行 commit 操作。因此要显式地开启一个事务务须使用命令 begin 或 start transaction，或者执行命令 set autommit=0，用来禁止使用当前会话的自动提交。

事务的ACID四大特性

A (atomicity) 原子性。一个事务的执行被视为一个不可分割的最小单元。事务里面的操作，要么全部成功执行，要么全部失败回滚，不可以只执行其中的一部分。
C (consistency) 一致性。一个事务的执行不应该破坏数据库的完整性约束。如果上述例子中第2个操作执行后系统崩溃，保证A和B的金钱总计是不会变的，也称为完整性。
I (isolation) 隔离性。通常来说，事务之间的行为不应该互相影响。然而实际情况中，事务相互影响的程度受到隔离级别的影响。文章后面会详述。
D (durability) 持久性。事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

事务控制语句

开始事务

begin 或 start transaction 或 set autocommit=0 显式开启一个事务

设置保存点

savepoint 保存点名称，在事务中创建一个保存点，一个事务中可以有多个保存点

回滚

rollback to 保存点名称，回滚到某个保存点，并撤销该保存点之后所有未提交的修改（前提是该事务还没有commit 提交）

rollback 回滚到事务的开始位置，事务开始时会自动设置一个保存点（前提是该事务还没有commit 提交）

提交事务

commit 提交事务，并使已对数据库进行的所有修改成为永久性的，事务一旦提交就不能回滚了

事务使用（控制台）

显式事务

//创建测试表  默认就是 innodb 引擎
mysql> create table account(id int primary key,name varchar(20) not null default '',balance decimal(10,2) not null default 0.0);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into account values(100,'宋江',1000);
Query OK, 1 row affected (0.03 sec)
mysql> insert into account values(101,'吴用',80000);
Query OK, 1 row affected (0.03 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//显式开始事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

//胡乱操作1
mysql> update account set balance=2000 where id=100;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  2000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//设置一个保存点 aaa
mysql> savepoint aaa;
Query OK, 0 rows affected (0.00 sec)

//胡乱操作2
mysql> delete from account where id=101;
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
+-----+------+---------+
| id  | name | balance |
+-----+------+---------+
| 100 | 宋江 | 2000.00 |
+-----+------+---------+
1 row in set (0.00 sec)

//回滚到 保存点 aaa  该操作会撤销aaa保存点之后所有未提交的修改
mysql> rollback to aaa;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  2000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//回滚到开始位置  该保存点在aaa保存点前面，所以并没有被之前的回滚撤销
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//回滚到开始位置已经撤销了之后所有未提交的修改  aaa保存点此时已经不存在了  所以aaa不存在
mysql> rollback to aaa;
ERROR 1305 (42000): SAVEPOINT aaa does not exist

隐式事务（autocommit=1）

隐式事务又称自动提交事务，mysql在进行dml 操作时，会默认开启隐式事务，原因是默认autocommit=1
autocommit=1 表示在隐式事务时，开启自动commit提交
隐式事务情况下，autocommit=1 时用户每进行一次操作都会即时提交或即时回滚，此时用户的每一个操作都是一个完整的事务周期。

//autocommit=1 表示 在隐式事务时，开启自动commit提交（显式事务即使autocommit=1也不会自动提交）
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 (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//未显式开启事务 mysql自动隐式开启事务 这里用 //后的语句，来表示系统自动操作的语句

//begin;
mysql> delete from account where id=101;
Query OK, 1 row affected (0.53 sec)
//commit;

mysql> select * from account;
+-----+------+---------+
| id  | name | balance |
+-----+------+---------+
| 100 | 宋江 | 1000.00 |
+-----+------+---------+
1 row in set (0.00 sec)

//回滚无效了  因为已经自动commit提交
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+---------+
| id  | name | balance |
+-----+------+---------+
| 100 | 宋江 | 1000.00 |
+-----+------+---------+
1 row in set (0.00 sec)

//begin;
mysql> delete from account1 where id=101;
ERROR 1146 (42S02): Table 'test1.account1' doesn't exist
//rollback;
//commmit;

显式事务（autocommit=0）

autocommit=0 相当于 begin 或 start transaction，开启显式事务

//autocommit=0 表示 在隐式事务时，关闭自动commit提交（显式事务即使autocommit=1也不会自动提交）
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

//set autocommit=0 相当于 begin 或 start transaction，开启显式事务
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from account where id=100;
Query OK, 1 row affected (0.00 sec)

mysql> delete from account where id=101;
Query OK, 1 row affected (0.00 sec)

mysql> select * from account;
Empty set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

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

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

autocommit 自动提交功能

autocommit=1（默认）

如果没有显式开启事务，用户每进行一次操作都会即时提交或者即时回滚，这种情况下用户的每一个操作都是一个完整的事务周期。

autocommit=0

autocommit=0 相当于 begin 或 start transaction，开启显式事务

临时生效设置方法

SET AUTOCOMMIT=1; 将自动提交功能置为ON
SET AUTOCOMMIT=0; 将自动提交功能置为OFF

永久生效设置方法

通过修改配置文件my.cnf文件，通过vim编辑my.cnf文件，在[mysqld]（服务器选项下）添加: autocommit=0

事务使用（PHP程序）

未使用事务

出现问题，吴用的钱少了500，宋江的钱没变化

$conn = mysqli_connect('localhost','root','');

if (!$conn){
    die('连接失败'.mysqli_error($conn));
}

mysqli_select_db($conn,'use test1') or die(mysqli_error($conn));

mysqli_query($conn,'set names utf8');

$sql1 = 'update account set balance=balance-500 where id=101';
$sql2 = 'update account1 set balance=balance+500 where id=100';

$res1 = mysqli_query($conn,$sql1);
$res2 = mysqli_query($conn,$sql2);

echo 'ok';

使用事务

完美避免了以上恐怖事件的发生，事务就是一组dml语句，这些语句存在逻辑上的相关性，这一组dml语句要么全部成功，要么全部失败

$conn = mysqli_connect('localhost','root','');

if (!$conn){
    die('连接失败'.mysqli_error($conn));
}

mysqli_select_db($conn,'use test1') or die(mysqli_error($conn));

mysqli_query($conn,'set names utf8');

$sql1 = 'update account set balance=balance-500 where id=100';
$sql2 = 'update account1 set balance=balance+500 where id=101';

//开始一个事务
mysqli_query($conn,'begin');

$res1 = mysqli_query($conn,$sql1);
$res2 = mysqli_query($conn,$sql2);

if ($res1 && $res2){
    //当两个dml操作都成功时，我们才提交
    mysqli_query($conn,'commit');
}else{
    //当有一个不成功时，我们就回退
    mysqli_query($conn,'rollback');
}

事务的四大特性（ACID）

原子性（Atomicity）：事务是一个不可分割的整体，事务开始后所有操作，要么全部发生，要么都不发生，对于一个事务来说不可能只执行其中的部分操作，这就是事务的原子性

一致性（Consistency）：数据库总是从一个一致性的状态转移到另一个一致性的状态. 。比如A向B转账，不可能A扣了钱，B却没收到。

隔离性（Isolation）：事物的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离

持久性（Durability）：事务一旦提交，其所作做的修改会永久保存到数据库中，能回滚，此时即使系统崩溃修改的数据也不会丢失

事务的隔离级别

基本概念

当表中数据被多个线程或客户端同时操作时，mysql 提供一种机制，可以让不同的事务在操作数据时，具有隔离性

设置隔离级别

设置当前会话隔离级别（暂时）
set session transaction isolation level 隔离级别名称;

设置当前系统隔离级别（暂时）
set global transaction isolation level 隔离级别名称;

永久修改
mysql.ini 中修改，一般不要改

查询隔离级别

查看当前会话隔离级别
select @@tx_isolation;
默认会话隔离级别是 Repeatable read，一般没有特殊需求，不要修改

查看当前系统隔离级别
select @@global.tx_isolation;

四种隔离级别

隔离级别	脏读（Dirty Read）	不可重复读（NonRepeatable Read）	幻读（Phantom Read）
未提交读（Read uncommitted）	可能	可能	可能
已提交读（Read committed）	不可能	可能	可能
可重复读（Repeatable read）	不可能	不可能	不可能
可串行化（Serializable ）	不可能	不可能	不可能

事务的并发问题

脏读：（同时操作都没提交的读取）脏读又称无效数据读出。事务B读取到事务A还没有提交的数据叫脏读。

不可重复读：（同时操作，事务B分别读取事务A操作时和提交后的数据，读取的记录内容不一致）不可重复读是指在同一个事务内，两个相同的查询返回了不同的结果。

幻读：（幻读是不可重复读的特殊场景，但是事务A的数据操作仅仅是插入和删除，不是修改数据，读取的记录数量前后不一致）在同一个事务操作中先后读取一个范围内的数据，发现两次读取到得数据条数不一样

可串行化：在幻读的基础上，设置可串行化的客户端A在开始事务后，客户端B只能读，无法增删改(需要等待)，当客户端A commit 提交后，客户端B的增删改操作就会立即执行

隔离级别测试代码

先操作A 再操作B，隔离级别是在B中设置，B是否开启事务都可

测试未提交读

第一步

//客户端 A
mysql> use test1;
Database changed
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

//开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

=================================================================

//客户端 B  设置错误级别为 未提交读
mysql> use test1;
Database changed
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)

//开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

第二步：

//客户端A 修改 宋江的存款为 500 但未提交
mysql> update account set balance=500 where id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

=============================================
//客户端 B 可以读到客户端 A 未提交的数据，这就是脏读
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

测试已提交读

第一步

//客户端 A
mysql> use test1;
Database changed
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

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

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

==========================================

//客户端 B  设置错误级别为 已提交读
mysql> use test1;
Database changed
mysql> set  session  transaction  isolation  level read committed;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+----------------+
| @@tx_isolation |
+----------------+
| READ-COMMITTED |
+----------------+
1 row in set (0.00 sec)

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

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

第二步

//客户端A 修改 宋江的存款为 500 但未提交
mysql> update account set balance=500 where id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

客户端A 增加一条数据 但未提交
mysql> insert into account values(102,'武松',200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
| 102 | 武松 |   200.00 |
+-----+------+----------+
3 rows in set (0.00 sec)

=============================================
//客户端 B 不能读到客户端 A 未提交的数据了，解决了脏读问题
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

第三步

//客户端 A 提交commid
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

========================================================

// 客户端 B 执行与上一步相同的查询，结果 与上一步数据不一致，即产生了不可重复读的问题
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

// 客户端 B 执行与上一步相同的查询，结果 与上一步行数不一样，即产生了幻读的问题
mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
| 102 | 武松 |   200.00 |
+-----+------+----------+
3 rows in set (0.00 sec)

测试可重复读

第一步

//客户端 A
mysql> use test1;
Database changed
mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)

//开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

=================================================================

//客户端 B  设置错误级别为 可重复读
mysql> use test1;
Database changed
mysql> set session transaction isolation level repeatable read;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

//开启事务
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

第二步

//客户端A 修改 宋江的存款为 500 但未提交
mysql> update account set balance=500 where id=100;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

客户端A 增加一条数据
mysql> insert into account values(102,'武松',200);
Query OK, 1 row affected (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |   500.00 |
| 101 | 吴用 | 80000.00 |
| 102 | 武松 |   200.00 |
+-----+------+----------+
3 rows in set (0.00 sec)

=============================================
//客户端 B 不能读到客户端 A 未提交的数据了，解决了脏读问题
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

第三步

//客户端 A 提交commid
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

========================================================

// 客户端 B 执行与上一步相同的查询，结果一致，解决了不可重复读的问题
mysql> select * from account;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

// 客户端 B 执行与上一步相同的查询，结果一致，解决了幻读的问题
mysql> select * from account where balance>100;
+-----+------+----------+
| id  | name | balance  |
+-----+------+----------+
| 100 | 宋江 |  1000.00 |
| 101 | 吴用 | 80000.00 |
+-----+------+----------+
2 rows in set (0.00 sec)

路飞小朋友

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
mysql 事务

为什么要有事务？事务广泛的运用于订单系统、银行系统等多种场景。如果有以下一个场景：A用户和B用户是银行的储户。现在A要给B转账500元。那么需要做以下几件事：检查A的账户余额>500元；A账户扣除500元；B账户增加500元；正常的流程走下来，A账户扣了500，B账户加了500，皆大欢喜。那如果A账户扣了钱之后，系统出故障了呢？A白白损失了500，而B也没有收到本该属于他的500。以上的案例中，隐藏着一个前提条件：A扣钱和B加钱，要么同时成功，要么同时失败。事务的需求就在于此。My
复制链接

扫一扫