麦克斯仇
Think different
160
文章
42174
阅读
首页
INDEX
文章
ARTICLE
关于
ABOUT
MySQL事务详解
创建日期:
2021/03/05
修改日期:
2023/03/28
MySQL
> 视频教程:[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) # 概述 ## 简介 事务由单独单元的**一个或多个SQL语句组成**,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。 > 案例:转账要么都成功,要么都不成功 update 表 set 余额=余额-500 where name='A' update 表 set 余额=余额+500 where name='B' ## 支持的存储引擎 `SHOW ENGINES` 命令来查看当前 `MySQL` 支持的存储引擎都有哪些,以及这些存储引擎是否支持事务,在 `MySQL` 中,只有 `InnoDB` 是支持事务的。 ``` mysql> SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.01 sec) ``` ## 事务的ACID特性 - 原子性(Atomicity):事务是一个不可分割的工作单位,要么全部提交,要么全部失败回滚。 - 一致性(Consistency):事务执行前后,数据从一个 **合法性状态** 变换到另外一个 **合法性状态** 。 - 隔离性(Isolation):一个事务的执行不受其他事务的干扰 - 持久性(Durability):一个事务一旦提交,则会永久的改变数据库的数据. # 事务的使用 使用事务有两种方式,分别为 **显式事务** 和 **隐式事务** 。 ## 显式事务 执行步骤如下: 1. 开始事务:`START TRANSACTION` 或者 `BEGIN` ,作用是显式开启一个事务。<br>`START TRANSACTION` 语句相较于 `BEGIN` 特别之处在于,后边能跟随几个修饰符: 1. `READ ONLY` :标识当前事务是一个 **只读事务** ,也就是属于该事务的数据库操作只能读取数据,而不能修改数据。 2. `READ WRITE` :标识当前事务是一个 **读写事务** ,也就是属于该事务的数据库操作既可以读取数据,也可以修改数据。 3. `WITH CONSISTENT SNAPSHOT` :启动一致性读。 2. 执行语句:一系列事务中的操作(主要是 `DML` ,不含 `DDL` ) 3. 添加保存点(可选): 1. `SAVEPOINT xxx` :在事务中创建保存点,方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。 2. `RELEASE SAVEPOINT xxx` :删除某个保存点。 4. 提交事务 或 中止事务(即回滚事务) 1. `COMMIT` :提交事务,当提交事务后,对数据库的修改是永久性的。 2. `ROLLBACK` :回滚事务,即撤销正在进行的所有没有提交的修改 3. `ROLLBACK TO [SAVEPOINT]` :将事务回滚到某个保存点。 ## 隐式事务 ### 事务的自动提交 `MySQL` 中有一个系统变量 `autocommit` : ``` mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.02 sec) ``` - 默认情况下,`autocommit`自动提交的值为`ON` - 当开启自动提交时,若不开启事务,则所有修改立即生效,无法回滚。 - 当关闭自动提交时,所有的 `DML` 操作都需要手动提交。 - 如果想关闭 自动提交 的功能,可以使用下边两种方法之一: - 显式的的使用 `START TRANSACTION` 或者 `BEGIN` 语句开启一个事务。这样在本次事务提交或者回滚前会暂时关闭掉自动提交的功能。 - 把系统变量 `autocommit` 的值设置为 `OFF` ,就像这样: 修改自动提交 - 开启:`SET autocommit = ON;` 或 `SET autocommit = 1;` - 关闭:`SET autocommit = OFF;` 或 `SET autocommit = 0;` ### 隐式提交数据的情况 在关闭自动提交的情况下,有一些语句也会自动提交事务 - 数据定义语言( `Data definition language` ,缩写为: `DDL` )<br>数据库对象,指的就是数据库、表、视图、存储过程等结构。当使用 `CREATE` 、 `ALTER` 、 `DROP` 等语句修改数据库对象时,就会隐式的提交之前语句所属的事务。 - 隐式使用或修改 `MySQL` 数据库中的表<br>使用 `ALTER USER` 、 `CREATE USER` 、 `DROP USER` 、 `GRANT` 、 `RENAME USER` 、 `REVOKE` 、 `SET PASSWORD` 等语句时也会隐式的提交之前语句所属的事务 - 事务控制或关于锁定的语句 1. 在一个事务还没提交或者回滚时就又使用 `START TRANSACTION` 或者 `BEGIN` 语句开启了另一个事务时,会隐式的提交之前语句所属的事务 2. 当前的 `autocommit` 系统变量的值为 `OFF` ,手动调为 `ON` 时,也会隐式的提交之前语句所属的事务 3. 使用 `LOCK TABLES` 、 `UNLOCK TABLES` 等关于锁定的语句也会隐式的提交之前语句所属的事务 - 加载数据的语句<br>使用 `LOAD DATA` 语句来批量往数据库中导入数据时,也会隐式的提交之前语句所属的事务 - 关于 `MySQL` 复制的一些语句<br>使用 `START SLAVE` 、 `STOP SLAVE` 、 `RESET SLAVE` 、 `CHANGE MASTER TO` 等语句时会隐式的提交之前语句所属的事务 - 其它的一些语句<br>使用 `ANALYZE TABLE` 、 `CACHE INDEX` 、 `CHECK TABLE` 、 `FLUSH` 、 `LOAD INDEX INTO CACHE` 、 `OPTIMIZE TABLE` 、 `REPAIR TABLE` 、 `RESET` 等语句也会隐式的提交前边语句所属的事务。 # 隔离级别 ## 并发问题 对于同时运行的多个事务,当这些事务访问数据库中**相同的数据时**,如果没有采取必要的隔离机制,就会导致各种**并发问题**: - **脏读**:对于两个事务`T1`、`T2`,`T1`可以读取到`T2`更新但还没有被提交的字段。之后若`T2`回滚,`T1`读取的内容就是临时且无效的。 - **不可重复读**:对于两个事务`T1`、`T2`,`T1`读取了一个字段,然后`T2`更新了该字段。之后`T1`再次读取同一个字段,值就不同了。 - **幻读**:对于两个事务`T1`、`T2`,`T1`从一个表中读取了数据,然后`T2`在该表中插入了一些新的行。之后如果`T1`操作全表时就会多出几行,且再次读取时行数不同。 ## 四种隔离级别 一个事务与其他事务隔离的程度称为隔离级别。数据库规定了多种事务隔离级别,不同隔离级别对应不同的干扰程度,隔离级别越高,数据一致性就越好,但并发性越弱。 隔离级别 | 描述 | 脏读 | 不可<br>重复读 | 幻读 ---|---|---|---|--- READ UNCOMMITTED<br>读未提交 | 允许事务读取未被其他事物提交的变更 | √ | √ | √ READ COMMITED<br>读已提交 | 只允许事务读取已经被其它事务提交的变更 | × | √ | √ REPEATABLE READ<br>可重复读 | 确保事务可以多次从一个字段中读取相同的值。<br>事务持续期间禁止其他事务对这个字段进行更新 | × | × | √ SERIALIZABLE<br>串行化 | 确保事务可以从一个表中读取相同的行。<br>事务持续期间禁止其他事务对该表执行插入、更新和删除操作。 | × | × | × > PS:打`√`代表会出现问题,`x`代表不会出现 ## 查询与修改隔离级别 MySQL默认隔离级别:`REPEATABLE-READ` ### 查询: - 详细查询:`SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;` - 仅查询当前会话:`SELECT @@transaction_isolation;` ### 修改 #### 配置文件 MySQL默认隔离级别为`REPEATABLE READ`,可以通过`my.ini`配置文件进行修改 在`[mysqld]`标签下添加如下内容: `transaction-isolation = [级别]` 级别如下(注意:使用配置文件时单词之间有`-`): - READ-UNCOMMITTED - READ-COMMITTED - REPEATABLE-READ - SERIALIZABLE 修改后重启服务生效 #### 语句 若不重启服务,则可以通过`SQL`进行修改,语法如下: `SET [范围] TRANSACTION ISOLATION LEVEL [级别]`; > 范围如下: - GLOBAL - SESSION - 若`[范围]`不填写,则仅对**下一个事务**有效 > 级别如下: - READ UNCOMMITTED - READ COMMITTED - REPEATABLE READ - SERIALIZABLE ### 示例 > 当前会话 ```bash # 修改前查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) # 修改 mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) # 修改后查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | REPEATABLE-READ | READ-UNCOMMITTED | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) ``` > 全局会话 - 该操作仅对后面新的会话生效,不对当前会话和已连接的会话生效 - 若服务器重启,则恢复默认的`REPEATABLE READ` ```bash # 修改前查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) # 修改 mysql> SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; Query OK, 0 rows affected (0.00 sec) # 修改后查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | READ-UNCOMMITTED | REPEATABLE-READ | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) # 重新登录后再次查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | READ-UNCOMMITTED | READ-UNCOMMITTED | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) # 重启MySQL后登录再次查询 mysql> SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation; +--------------------------------+---------------------------------+ | @@GLOBAL.transaction_isolation | @@SESSION.transaction_isolation | +--------------------------------+---------------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +--------------------------------+---------------------------------+ 1 row in set (0.00 sec) ``` > 下一个事务 略。。。。。。 # 事务的使用 ## 步骤 - 步骤1:开启事务:`START TRANSACTION;` - 步骤2:执行SQL语句:`SELECT | INSERT | UPDATE | DELETE` - 步骤3:结束事务: - 提交事务:`COMMIT;` - 回滚事务:`ROLLBACK;` ## 简单演示 > 表结构与数据准备 ```sql CREATE TABLE `account` ( `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', `balance` DOUBLE NOT NULL DEFAULT '0' COMMENT '余额', PRIMARY KEY (`id`) USING BTREE ) ENGINE=INNODB COMMENT='账户表'; INSERT INTO `account`(`id`,`balance`) VALUES (1,3500), (2,1500); ``` ### 正常示例 ```bash mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 2000 | | 2 | 2000 | +----+---------+ 2 rows in set (0.00 sec) mysql> UPDATE `account` SET `balance` = `balance` + 500 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE `account` SET `balance` = `balance` - 500 WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 2500 | | 2 | 1500 | +----+---------+ 2 rows in set (0.00 sec) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 2500 | | 2 | 1500 | +----+---------+ 2 rows in set (0.00 sec) ``` ### 回滚示例 ```bash mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 2500 | | 2 | 1500 | +----+---------+ 2 rows in set (0.00 sec) mysql> UPDATE `account` SET `balance` = `balance` + 500 WHERE id = 1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE `account` SET `balance` = `balance` - 500 WHERE id = 2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 3000 | | 2 | 1000 | +----+---------+ 2 rows in set (0.00 sec) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM `account`; +----+---------+ | id | balance | +----+---------+ | 1 | 2500 | | 2 | 1500 | +----+---------+ 2 rows in set (0.00 sec) ``` # 不同隔离级别并发问题演示 - 准备两个会话`T1`、`T2` - `T1`修改隔离级别后开启事务 - `T2`直接开启事务 > 下文的截图中`T1`为黑色框、`T2`为绿色框,表结构和数据仍为上文的表 ## 读未提交 READ UNCOMMITTED  如图:`T1`在第6步读取到了`T2`在第5补更新但未提交的值,当`T2`在第7步回滚时,`T1`第6步读取的值就是无效的。出现***脏读*** ## 读已提交 READ COMMITTED  如图:`T1`在第6步读取不到`T2`在第5步更新但未提交的值,说明解决了***脏读***;但是当`T2`在第7步提交后,`T1`在第8步读取到了`T2`已提交的数据。出现***不可重复读*** ## 可重复读 REPEATABLE READ  如图:`T1`在第7步读取不到`T2`在第6步**更新并提交**的值,说明解决了***不可重复读***;当`T2`在第8步执行了插入后,`T1`在第9步依旧查询不到`T2`插入的值;但是却在第10步更新了三条记录,并且再次查询时读出三条记录,与第9步行数不符。。出现***幻读*** ## 串行化 SERIALIZABLE  如图:当`T2`在第5步执行插入时,会被暂停,当`T1`在第6步执行提交结束事务后`T2`才可以完成插入(注意观察插入的完成时间)。因为不允许其他事务执行修改操作,解决了***幻读***
11
全部评论
插翅虎-雷横
2021/03/17 15:58:58
666!步骤相当的详细!