MySQL----事务

最新推荐文章于 2024-05-19 03:18:57 发布

暮-夜染

最新推荐文章于 2024-05-19 03:18:57 发布

阅读量75

点赞数 3

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_74259765/article/details/131367368

版权

MySQL 专栏收录该内容

6 篇文章 1 订阅

订阅专栏

一.定义

MySQL事务是一组SQL语句的执行单元，它们被当作一个整体来执行，要么全部执行成功，要么全部执行失败。

具体来说事务是指为了某种目的要对数据库进行的一系列操作。
比方说从小明的账户中转出来1000元汇入小林账户，这个过程涉及两个主要操作分别是将小明的钱转出、向小林转入1000元。操作过程中可能发生转入时候出错，导致小明账户凭空少了1000元。
为了避免这种出错，MySQL提供了事务，一旦某个环节出错，事务回滚会使所有数据恢复。
这里说一下，在不提交之前，MySQL数据库中数据不会更改。即事务相当于暂存数据，确认无误后才可以提交并保存到数据库，减少了错误的发生。

二.特点

事务具有以下四个特性，通常被称为ACID特性：

2.1. 原子性（Atomicity）

：事务中的所有操作要么全部成功，要么全部失败。如果事务中的任何一条语句执行失败，那么整个事务都会被回滚到事务开始前的状态，所有的修改都会被撤销。

2.2. 一致性（Consistency）

：事务执行前后，数据库的状态必须保持一致。这意味着事务中的操作必须满足数据库的约束条件，如主键、外键、唯一性约束等。

2.3. 隔离性（Isolation）

：事务的执行是相互隔离的，一个事务的执行不应该对其他事务产生影响。事务的隔离级别可以通过设置来控制，常见的隔离级别有读未提交、读已提交、可重复读和串行化。

2.4. 持久性（Durability）

：一旦事务提交成功，其所做的修改将永久保存在数据库中，即使系统发生故障也不会丢失。

   事务的使用可以确保数据库操作的一致性和完整性，特别适用于需要多个操作同时成功或同时失败的场景，
   如转账操作、订单处理等。在MySQL中，可以使用BEGIN、COMMIT和ROLLBACK语句来控制事务的开始、提交和回滚。

三.事务控制语句

为了帮助开发者在需要的时候控制事务的开始、提交和回滚，从而实现对数据库操作的一致性和完整性的保证。
MySQL提供了以下几个事务控制语句：

3.1到3.6直接输入在cmd窗口中即可执行

3.1 BEGIN 或 START TRANSACTION

用于开始一个事务。在执行BEGIN语句后，MySQL会自动将后续的SQL语句当作一个事务来执行，直到遇到COMMIT或ROLLBACK语句。

3.2 COMMIT

用于提交一个事务。当执行COMMIT语句时，MySQL会将之前执行的SQL语句所做的修改永久保存到数据库中，同时退出当前事务。

3.3 ROLLBACK

用于回滚一个事务。当执行ROLLBACK语句时，MySQL会撤销之前执行的SQL语句所做的修改，将数据库恢复到事务开始前的状态。

3.4 SAVEPOINT

用于创建一个保存点。保存点可以在事务中的任意位置创建，用于标记一个可以回滚到的位置。在创建保存点后，可以使用ROLLBACK TO语句将事务回滚到保存点。

3.5 RELEASE SAVEPOINT (release savepoint)

用于释放一个保存点。当不再需要一个保存点时，可以使用RELEASE SAVEPOINT语句将其释放

3.6 SET TRANSACTION

用于设置事务隔离等级
SET SESSION TRANSACTION ISOLATION LEVEL <isolation_level>;
<isolation_level>替换为四种隔离等级英文全称

3.7 MySQL 的自动提交:

MySQL默认情况下是自动提交事务的，也就是说每个SQL语句都会被当作一个单独的事务执行。如果要开启事务，需要将自动提交功能关闭，可以使用以下命令：

 SET AUTOCOMMIT=0 禁止自动提交
 SET AUTOCOMMIT=1 开启自动提交

这样，在执行完一系列的SQL语句后，需要手动提交事务或回滚事务。

另外，还可以使用SAVEPOINT和ROLLBACK TO语句来实现更细粒度的事务控制。SAVEPOINT用于创建一个保存点，ROLLBACK TO用于回滚到指定的保存点。

四. 事务的隔离级别

MySQL提供了四个事务隔离级别，分别是读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

4.1 读未提交（Read Uncommitted）

最低的隔离级别，事务中的修改可以被其他事务读取，即一个事务可以读取到另一个事务未提交的数据。这种隔离级别可能导致脏读（Dirty Read）问题，即读取到未提交的数据。

4.2 读已提交（Read Committed）

事务中的修改只能被其他事务在其提交后读取，即一个事务只能读取到另一个事务已提交的数据。这种隔离级别可以避免脏读问题，但可能导致不可重复读（Non-Repeatable Read）问题，即在同一个事务中，多次读取同一行数据时，可能会得到不同的结果

4.3 可重复读（Repeatable Read）

事务中的查询结果保持一致，即在同一个事务中，多次读取同一行数据时，得到的结果是相同的。这种隔离级别可以避免不可重复读问题，但可能导致幻读（Phantom Read）问题，即在同一个事务中，多次查询同一范围的数据时，可能会得到不同的结果。

4.4 串行化（Serializable）

Serializable隔离级别下，MySQL会对所有的读操作加锁，即每个事务都会等待其他事务完成后才能执行，保证每个事务读取的数据都是一致的。这种隔离级别可以避免幻读问题，但会导致并发性能下降。

4.5 相关其他命令

MySQL中查看隔离级别命令：
SELECT @@tx_isolation; //MySQL默认隔离级别为可重复读

在这里插入图片描述

事务隔离级别的作用范围
分为两种:
全局级:对所有的会话有效
会话级:只对当前的会话有效
1、查询全局事务隔离级别
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
2、查询会话事务隔离级别
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
3、设置全局事务隔离级别
set global transaction isolation level read committed;
4、设置会话事务隔离级别
set session transaction isolation level read committed;

设置事务隔离级别

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};

其中，{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}表示要设置的隔离级别。

五. 不同隔离级别可能导致的问题

隔离等级	脏读	不可重复读	幻读
读未提交	YES	YES	YES
读已提交	NO	YES	YES
可重复读	NO	NO	YES
串行化	NO	NO	NO

5.1 脏读（Dirty Reads）

所谓脏读就是对脏数据的读取，而脏数据所指的就是未提交的数据。一个事务正在对一条记录做修改，在这个事务完成并提交之前，这条数据是处于待定状态的（可能提交也可能回滚），这时，第二个事务来读取这条没有提交的数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被称为脏读。

5.2 不可重复读（Non-Repeatable Read）

一个事务先后读取同一条记录，但两次读取的数据不同，我们称之为不可重复读。也就是说，这个事务在两次读取之间该数据被其它事务所修改。

5.3 幻读（Phantom Read）

MySQL的幻读是指在一个事务中，当某个事务在读取某个范围的数据时，另一个事务在该范围内插入了新的数据，导致第一个事务再次读取该范围的数据时，发现有新的数据出现，就好像出现了幻觉一样。

在读未提交的隔离级别下，事务可以读取到其他事务未提交的数据，因此可能会出现幻读的情况。
而在读已提交、可重复读和串行化的隔离级别下，MySQL可以通过锁机制或多版本并发控制（MVCC）来避免幻读的发生。

为了解决幻读问题，
除了调整隔离级别，还可以使用锁机制来解决幻读问题。在MySQL中，可以使用行级锁或表级锁来控制并发访问。行级锁可以在读取或修改某一行数据时对该行加锁，而表级锁则是对整个表加锁。通过合理地使用锁机制，可以避免幻读的发生。

六.锁

在MySQL中，锁是用于控制并发访问的机制，它可以确保多个事务之间的数据一致性和完整性。MySQL中的锁可以分为两种类型：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

6.1 共享锁（Shared Lock）

共享锁也称为读锁，它允许多个事务同时获取同一资源的锁，用于读取数据。共享锁之间不会互相阻塞，多个事务可以同时持有共享锁，但是共享锁和排他锁之间是互斥的，即一个事务持有共享锁时，其他事务无法获取排他锁。

6.2 排他锁（Exclusive Lock）

排他锁也称为写锁，它只允许一个事务获取资源的锁，用于修改数据。排他锁之间和共享锁之间都是互斥的，即一个事务持有排他锁时，其他事务无法获取共享锁或排他锁。

6.3 锁在事务中的使用

MySQL中的锁可以在事务中使用，通过使用BEGIN、COMMIT和ROLLBACK语句来控制锁的获取和释放。在事务中，可以使用以下语句来获取和释放锁：

- 获取共享锁：SELECT ... LOCK IN SHARE MODE;
- 获取排他锁：SELECT ... FOR UPDATE;
- 释放锁：COMMIT或ROLLBACK。

除了事务中的锁，MySQL还提供了其他类型的锁，如表级锁和行级锁。表级锁是对整个表进行锁定，可以用于控制对整个表的并发访问。行级锁是对表中的行进行锁定，可以用于控制对特定行的并发访问。行级锁可以提高并发性能，但会增加锁的开销和复杂性。

七.事务测试

mysql> use edujb51net;
Database changed
mysql> CREATE TABLE edujb51net_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
  
mysql> select * from edujb51net_transaction_test;
Empty set (0.01 sec)
  
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
  
mysql> insert into edujb51net_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
  
mysql> insert into edujb51net_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
  
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
  
mysql>  select * from edujb51net_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
  
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
  
mysql>  insert into edujb51net_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
  
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
  
mysql>   select * from edujb51net_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
  
mysql>

拓展

1.加锁读

在 MySQL 中，可以使用表锁（Table Lock)或行锁（Row Lock)来实现读锁。

行锁：在读取数据时，只锁住被读取的行，其他未被锁住的行仍然可以被其他事务读取或修改。行锁适用于读密集型（Read Intensive）的应用程序。

表锁：在读取数据时，锁住整个表，其他事务无法对表进行任何操作。表锁适用于写密集型（Write Intensive）的应用程序。

加锁读的示例：

-- 行级读锁
SELECT \* FROM users WHERE id = 1 FOR READ;

-- 表级读锁
SELECT \* FROM users FOR READ;

在以上示例中，FOR READ 关键字表示需要对查询结果进行加锁以进行读取。在 MySQL 中，读锁和写锁是互斥的，因此在读取数据时，需要确保没有其他事务正在修改数据。如果正在进行写操作，则需要等待写操作完成，才能进行读操作。

为了避免死锁，MySQL 会自动管理锁的顺序，确保先获取锁的事务先执行提交或回滚操作，让其他事务可以获取锁进行操作。

2. 存储引擎相关

MySQL常用的存储引擎：
1、MyISAM
2、InnoDB
InnoDB: 支持事务，支持4个事务隔离级别（数据不一致问题）MySQL从5.5.5版本开始，默认的存储引擎为 InnoDB5.5 之前是myisam （isam）默认
MyISAM：不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景

详细存储引擎讲解