网站导航
> 文章列表 > 【Linux】Mysql事务

【Linux】Mysql事务

网友: 文章列表 2024-03-22 01:25:01

【Linux】Mysql事务

一、什么是事务

Mysql 数据库中不是所有的存储引擎都实现了事务处理。

  • 支持事务的存储引擎有：
    • InnoDB
    • NDB Cluster 。
  • 不支持事务的存储引擎代表有：
    • MyISAM 

事务简单来说：一个 Session 中所进行所有的操作，要么同时成功，要么同时失败。进一步说，事务指的是满足 ACID 特性的一组操作，可以通过 Commit 提交一个事务，也可以使用 Rollback 进行回滚。

【 语法 】

mysqlbinlog -v 数据库的二进制日志

mysqlbinlog -v mysql-bin.000001

该命令可以用来查看数据库二进制日志的详情，二进制日志里存放的就是数据库进行的操作也就是事务，事务由BEGIN开启，由COMMIT提交；

  • 事务就是一组原子性的 SQL 语句。具体来说，事务指的是满足 ACID 特性的一组操作。
  • 事务内的 SQL 语句，要么全执行成功，要么全执行失败。
  • 通过加锁的方式，可以实现不同的事务隔离机制。
  • 想象一下，如果没有事务，在并发环境下，就可能出现丢失修改的问题。
    • T1 和 T2 两个线程都对一个数据进行修改，T1 先修改，T2 随后修改，T2 的修改覆盖了 T1 的修改。

二、事务用法

1）事务处理指令

Mysql 中，使用 START TRANSACTION 语句开始一个事务；使用 COMMIT 语句提交所有的修改；使用 ROLLBACK 语句撤销所有的修改。不能回退 SELECT 语句，回退SELECT 语句也没意义；也不能回退 CREATE 和 DROP 语句。只能回退INSERT语句

  • START TRANSACTION - 指令用于标记事务的起始点。
  • SAVEPOINT - 指令用于创建保留点。
  • ROLLBACK TO - 指令用于回滚到指定的保留点；如果没有设置保留点，则回退START TRANSACTION 语句处。
  • COMMIT - 提交事务。

2）事务处理示例：

（1）创建一张示例表
-- 创建表 user

CREATE TABLE user (
id int(10) unsigned NOT NULL COMMENT 'Id',
username varchar(64) NOT NULL DEFAULT 'default' COMMENT '用户
名',
password varchar(64) NOT NULL DEFAULT 'default' COMMENT '密码',
email varchar(64) NOT NULL DEFAULT 'default' COMMENT '邮箱') COMMENT='用户表';

（2）执行事务操作
-- 开始事务

START TRANSACTION;


-- 插入操作 A

INSERT INTO `user`
VALUES (1, 'root1', 'root1', 'xxxx@qq.com');


-- 创建保留点 updateA

SAVEPOINT updateA;


-- 插入操作 B
 

INSERT INTO `user`
VALUES (2, 'root2', 'root2', 'xxxx@qq.com');


-- 回滚到保留点 updateA
 

ROLLBACK TO updateA;


-- 提交事务，只有操作 A 生效
 

COMMIT;

 （3）执行结果
 

SELECT * FROM user;


 

 【注意，想要ROLLBACK不创建保留点也能ROLLBACK】

3）自动提交（AUTOCOMMIT）

MySQL 默认采用隐式提交策略（ autocommit ）。每执行一条语句就把这条语句当成一个事务然后进行提交。也就是使用BEGIN开启事务的话默认为隐式提交策略，当出现 START TRANSACTION 语句时，会关闭隐式提交；当COMMIT 或 ROLLBACK 语句执行后，事务会自动关闭，重新恢复隐式提交。

通过 set autocommit=0 可以取消自动提交，直到 set autocommit=1 才会提交； autocommit 标记是针对每个连接而不是针对服务器的。

-- 查看 AUTOCOMMIT
SHOW VARIABLES LIKE 'AUTOCOMMIT';-- 关闭 AUTOCOMMIT
SET autocommit = 0;-- 开启 AUTOCOMMIT
SET autocommit = 1;

三、事务特性

ACID 是数据库事务正确执行的四个基本要素。

  • 原子性（Atomicity）
    • 事务被视为不可分割的最小单元，事务中的所有操作要么全部提交成功，要么全部失败回滚。
    • 回滚可以用日志来实现，日志记录着事务所执行的修改操作，在回滚时反向执行这些修改操作即可。
  • 一致性（Consistency）
    • 数据库在事务执行前后都保持一致性状态。
    • 在一致性状态下，所有事务对一个数据的读取结果都是相同的。
  • 隔离性（Isolation）
    • 一个事务所做的修改在最终提交以前，对其它事务是不可见的。
  • 持久性（Durability）
    • 一旦事务提交，则其所做的修改将会永远保存到数据库中。即使系统发生崩溃，事务执行的结果也不能丢失。
    • 可以通过数据库备份和恢复来实现，在系统发生奔溃时，使用备份的数据库进行数据恢复。

一个支持事务（Transaction）中的数据库系统，必需要具有这四种特性，否则在事 务过程（Transaction processing）当中无法保证数据的正确性，交易过程极可能达 不到交易。

  • 只有满足一致性，事务的执行结果才是正确的。
  • 在无并发的情况下，事务串行执行，隔离性一定能够满足。此时只要能满足原子 性，就一定能满足一致性。
  • 在并发的情况下，多个事务并行执行，事务不仅要满足原子性，还需要满足隔离 性，才能满足一致性。
  • 事务满足持久化是为了能应对系统崩溃的情况。

MySQL 默认采用自动提交模式（ AUTO COMMIT ）。也就是说，如果不显式使用 START TRANSACTION 语句来开始一个事务，那么每个查询操作都会被当做一个事务 并自动提交。 

四、事务隔离级别

1）事务隔离简介

在并发环境下，事务的隔离性很难保证，因此会出现很多并发一致性问题：

  • 丢失修改
  • 脏读
  • 不可重复读
  • 幻读

在 SQL 标准中，定义了四种事务隔离级别（级别由低到高）：

  • 未提交读（READ UNCOMMITTED）
  • 提交读（READ COMMITTED）
  • 可重复读（REPEATABLE READ）
  • 串行化（SERIALIZABLE）

Mysql 中查看和设置事务隔离级别：

-- 查看事务隔离级别
SHOW VARIABLES LIKE 'transaction_isolation';-- 设置事务隔离级别为 READ UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;-- 设置事务隔离级别为 READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;-- 设置事务隔离级别为 REPEATABLE READ
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;-- 设置事务隔离级别为 SERIALIZABLE
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

1、读未提交（READ UNCOMMITTED）

读未提交是指：事务中的修改，即使没有提交，对其它事务
也是可见的。
读未提交的问题：事务可以读取未提交的数据，也被称为 脏读（Dirty Read）。
T1 修改一个数据，T2 随后读取这个数据。如果 T1 撤销了这次修改，那么 T2 读取的
数据是脏数据。

脏读

脏读是指一个事务未提交前，另一个事务就读取了它所修改的数据。这种读取的数据 是未提交的，也就是 脏 的，因此被称作是脏读。如果第一个事务在后续操作中回滚 了事务，那么第二个事务读取到的脏数据也就是无效的，这样会导致数据不一致。 

在Mysql中可以使用如下命令修改当前会话的事务隔离级别，

SET TRANSACTION ISOLATION LEVEL 隔离级别;

其中隔离级别可以为：READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 或 SERIALIZABLE。该方式只是将当前会话的隔离级别设置为指定值。

如果需要修改所有连接默认的事务隔离级别，可以通过配置文件中修改：

transaction-isolation = 隔离级别

2、读已提交

提已交读（READ COMMITTED） 是指：事务提交后，其他事务才能看到它的修改。换
句话说，一个事务所做的修改在提交之前对其它事务是不可见的。提交读解决了脏读的问题。

提已交读是大多数数据库的默认事务隔离级别。

提已交读有时也叫不可重复读，它的问题是：执行两次相同的查询，得到的结果可能不一致。

T2 读取一个数据，T1 对该数据做了修改。如果 T2 再次读取这个数据，此时读取的
结果和第一次读取的结果不同。

 3、不可重复读

不可重复读是指在同一个事务中，多次读取同一行数据，但是读取的结果不一致的现 象。例如事务A在读取一行数据时，事务B对该行数据进行了修改并提交了事务。如 果事务A再次读取该行数据，将会读取到修改后的数据。

4、可重复读

可重复读（REPEATABLE READ） 是指：保证在同一个事务中多次读取同样数据的结果
是一样的。可重复读解决了不可重复读问题。

可重复读是 Mysql 的默认事务隔离级别。

可重复读的问题：当某个事务读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务又再次读取该范围的记录时，会产生 幻读（Phantom Read）。

T1 读取某个范围的数据，T2 在这个范围内插入新的数据，T1 再次读取这个范围的
数据，此时读取的结果和和第一次读取的结果不同。
 

5、幻读

幻读是指在一个事务中，多次执行相同的查询，但是每次查询返回的结果集却不一致
的现象。通常情况下，幻读是由于其他事务在该事务执行期间插入或者删除了满足该
查询条件的行，导致查询的结果集发生了变化。

（相当于你查看这个数据，它现在为A，他去对这个数据进行了修改，你再查看发现数据变了，你很纳闷：我明明没改数据，为啥我两次查看速度数据不一致？这就是幻读）

假设事务A在查询某个表中所有符合条件的行，得到了结果集X，然后再该事务执行
期间，事务B查询了一条符合条件的记录，当事务A再次执行同样的查询时，得到的
结果集就包含了新插入的记录。

假设事务A在查询某个表中所有符合条件的行，得到了结果集X，然后再该事务执行
期间，事务B查询了一条符合条件的记录，当事务A再次执行同样的查询时，得到的
结果集就包含了新插入的记录。

 

6、串行化 

 串行化（SERIALIXABLE） 是指：强制事务串行执行。

在Mysql的事务隔离级别中，最高的级别就是SERIALIZABLE。它提供了最高的数据
一致性和安全性，但也是性能最低的隔离级别。

在该隔离级别下，事务之间是完全隔离的，每个事务只能按照顺序逐个执行，任何两
个事务不能并发执行。这就相当于所有事务都是串行化执行的，因此也被称为串行
化。在该隔离级别下，前面提到的脏读、不可重复读和幻读等问题都被解决了。

强制事务串行执行，则避免了所有的并发问题。串行化策略会在读取的每一行数据上
都加锁，这可能导致大量的超时和锁竞争。这对于高并发应用基本上是不可接受的，
所以一般不会采用这个级别。

2）隔离级别小结

  • 读未提交（READ UNCOMMITTED） - 事务中的修改，即使没有提交，对其它事务也是可见的。
  • 读已提交（READ COMMITTED） - 一个事务只能读取已经提交的事务所做的修改。换句话说，一个事务所做的修改在提交之前对其它事务是不可见的。
  • 可重复读（REPEATABLE READ） - 保证在同一个事务中多次读取同样数据的结果是一样的。
  • 串行化（SERIALIXABLE） - 强制事务串行执行。

数据库隔离级别解决的问题：

网络标签：

相关问题