MySQL中的事物详解

倾城璧

于 2024-08-25 16:38:43 发布

阅读量504

点赞数 8

分类专栏： MySQL基础知识文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2302_79993788/article/details/141503627

版权

MySQL基础知识专栏收录该内容

4 篇文章 0 订阅

订阅专栏

概念

在Mysql中，事物是一种确保数据库操作序列的原子性，一致性，隔离性和持久性（ACID属性）的机制。事物可以包括一个或者多个数据库操作，例如插入，更新或删除记录。主要用于处理操作量大的，复杂度高的数据

为什么要使用事物

事物要保证成批的SQL语句全部执行，要么全都不执行，这样可以保证数据库的完整性
事物可以用来管理增删改这些改变数据的语句

事物的特性（ACID）

原子性：事物是操作的最小单位，事物中的操作要么都执行要么都不执行，不可拆分
一致性：事物的操作数据库中的数据，只会使数据库从一个一致的状态到另一个一致的状态
隔离性：事物的执行之间互不影响（与隔离级别有关）
持久性：事物一旦执行完毕后，对数据库是永久性的改变，不可恢复。

事物的操作

事物默认是开启的可以通过show variables like 'autocommit’SQL语句查看

事物的创建

隐式事物：事物没有开始和结束的标记，比如一条insert，update，delete。也就是说一条SQL语句默认就有隐式事物。
显式事务：事物有开始和结束的标记。比如在一组sql语句的最前面和最后面添加开始和结束的标记

    #首先关闭自动开启的事物
    set autocommit = 0; 
    #开始事物
    start transaction; #可以不写
    #编写多条SQL语句
    ...
    # 关闭事物提交和回滚都会关闭事物根据情况写一条
    # 提交事物
    commit; # 或者rollback;

为什么会出现事务

MySQL中最开始就有事物的概念吗？实际上并不是的！事务被MySQL编写者设计出来，本质是为了当应用程序访问数据库的时候，事务能够简化我们的编程模型，不需要用户自己去考虑各种各样的潜在错误和并发问题。
如果MySQL只是单纯的提供数据存储服务，那么用户在访问数据库时就需要自行考虑各种潜在问题，包括网络异常、服务器宕机等。因此事务本质是为了应用服务的，而不是伴随着数据库系统天生就有的。

控制事物处理

MySQL使用START TRANSACTION来标识事物的开始。

我们先建立名为user_db的表和插入数据（使用的数据库引擎默认为InnoDB）：

--1.建立名为user_db的表
CREATE TABLE user_db(
	id INT(4) NOT NULL PRIMARY KEY AUTO_INCREMENT,
	username VARCHAR(32) NOT NULL
);
--2.插入两条数据后并查看数据表
INSERT INTO user_db(username) VALUES('张三'),('李四');
mysql> SELECT * FROM user_db;
+----+----------+
| id | username |
+----+----------+
|  1 | 张三     |
|  2 | 李四     |
+----+----------+

使用ROLLBACK

MySQL的ROLLBACK命令用来回退（撤销）MySQL语句，请看下面的语句：

START TRANSACTION;
DELETE FROM user_db;
SELECT * FROM user_db;
ROLLBACK;
SELECT * FROM user_db;

根据上面建的数据表，这里使用START TRANSACTION开启事物，之后执行清空user_db数据表的操作（删除用DROP），再查询数据表确实为空，之后执行ROLLBACK事物回滚操作，再查询数据表数据还是和原来的一样。显然，ROLLBACK只能在一个事务处理内使用（在执行一条START TRANSACTION命令之后）。

CREATE 和 DROP 操作是 DDL（数据定义语言）语句，它们不适用于 ROLLBACK。DDL 语句通常不能被回滚，因为它们会立即更改数据库的结构。而 SELECT 操作是一个只读操作，不会改变数据，因此也不需要回滚。

此外，DML（数据操纵语言）语句，如 INSERT、UPDATE 和 DELETE，在事务提交之前不会更改底层数据。它们只是记录了这些更改，直到事务被提交。如果事务被回滚，这些更改将不会被应用到数据库中。

使用COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是在事务处理块中，提交不会隐含地进行，而是进行明确的提交，使用COMMIT语句，如下所示：

START TRANSACTION;
INSERT INTO user_db(username) VALUES('王五');
INSERT INTO user_db(username) VALUES('麻六');
COMMIT;
SELECT * FROM user_db;

使用事物块来保证两个插入语句要么都成功，要么都失败。不会出现只成功一个的情况。

通过insert，update和delete操作的事务处理，并是用commit提交更改会使得这些更改永久生效。

事物关闭：当COMMIT或ROLLBACK语句执行后，事务会自动关闭（将来的更改会隐含提交）。

使用保留点

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是，只是对简单的事务处理才能这样做，更复杂的事务处理可能需要部分COMMIT或ROLLBACK回退。在事务处理块中合适的位置放置占位符，如果需要回退，可以回退到某个占位符的为准。这些占位符称为保留点。创建占位符使用SAVEPOINT语句：

事例：

START TRANSACTION;
INSERT INTO user_db(username) VALUES('王七');
SAVEPOINT insert1;
INSERT INTO user_db(username) VALUES('王九');#不会被执行
ROLLBACK TO insert1;
COMMIT;
SELECT * FROM user_db;

在事物块中，ROLLBAKC到insert1保留点，使得第二条insert语句得不到提交。

更改默认的提交行为

MySQL行为是自动提交所有更改，即执行一条MySQL语句，该语句实际上都是针对表执行的，而且所做的更改立即生效。为指示MySQL不自动提交更改，需要使用以下语句：

SET AUTOCOMMIT=0;

autocommit标志决定是否自动提交更改，不管有没有COMMIT 语句，设置autocommit为0（假）指示MySQL不自动提交更改（直到autocommit被设置为真为止）。下面使用一个示例帮助理解：

SET AUTOCOMMIT=0;
INSERT INTO user_db(username) VALUES('┗|｀O′|┛ 嗷~~');
EXIT;#关闭数据库

事物的隔离性

事务的隔离性是数据库事务的四个基本属性之一，通常被称为ACID属性中的”I”（Isolation）。隔离性确保并发执行的多个事务之间不会互相干扰，每个事务都好像在独立运行一样。隔离性通过不同的隔离级别来实现，这些级别定义了事务之间可以观察到的并发操作的可见性。

在对多线程情况下，针对于数据安全的问题，我们有一种锁的概念，先了解锁：

锁分类：锁分为共享锁（shared lock）和排他锁（exclusive lock）。其中共享锁也称为只读锁，当一个用户在操作数据库时，其他用户只能读取数据，不能进行数据的增删改；排他锁当执有一个用户在执行增、删、改的指令时，其他用户不能有任何的操作。

锁的粒度：锁对象的大小就是锁的粒度，分为三种：记录锁、表锁和数据库锁。
而数据库如果使用锁的话性能会下降很多，频繁访问不一定合理，则使用事物的隔离级别来表示事务处理之间的影响程度。

事物的隔离级别

假设有两事物，事物A和事物B，它们之间具有一定的隔离性。不同的事物之间具有一定的隔离性

1. 读未提交（Read Uncommitted）:
• 在这个级别上，事务可以读取到其他未提交事务的更改。这可能导致“脏读”（Dirty Read），即读取到其他事务未提交的数据。

2. 读已提交（Read Committed）:
• 事务只能读取到其他事务已经提交的更改。这个级别可以避免脏读，但仍然可能遇到“不可重复读”（Nonrepeatable Read），即在同一事务中，多次读取同一数据集合时可能会得到不同的结果，因为其他事务可能已经修改了这些数据。

3. 可重复读（Repeatable Read）:
• 在这个级别上，事务在整个过程中可以看到一致的快照数据，解决了不可重复读的问题。但是，它不能防止“幻读”（Phantom Read），即在事务执行过程中，其他事务插入了新的行，导致原始事务在执行范围查询时得到不同的结果集。

4. 串行化（Serializable）:
• 这是最高的隔离级别，它通过锁定涉及的所有数据来避免脏读、不可重复读和幻读。在这个级别上，事务将依次顺序执行，从而提供了最严格的隔离，但可能会严重影响并发性能。

脏读

脏读（Dirty Read）是数据库事务中的一个概念，指的是在一个事务中读取到另一个事务未提交的数据更改。如果发生脏读，那么读取的数据可能包含错误或不一致的信息，因为这些数据最终可能不会被提交到数据库中。

脏读的具体情况：
1. 事务A开始并读取了某些数据。
2. 事务B开始并对相同的数据进行了更改，但还没有提交这些更改。
3. 事务A再次读取相同的数据，此时读取到的是事务B所做的更改，即使这些更改还未提交。
4. 如果事务B最终决定不提交更改（例如，因为违反了某些业务规则或出现错误），那么事务A读取的数据就是无效的。

脏读的例子：
假设有两个用户，Alice和Bob，他们都可以访问同一个数据库。
• Alice开始一个事务，查询Bob的账户余额，假设是$100。
• 此时，Bob开始另一个事务，从他的账户中扣除$50，账户余额变为$50，但Bob还没有提交这个事务。
• Alice的事务再次查询Bob的账户余额，由于脏读，她看到的是$50，这是Bob未提交的更改。
• 如果Bob的事务最终因为某些原因被回滚，Bob的账户余额应该还是$100。但Alice的事务可能基于错误的$50余额做出了决策。
避免脏读：
脏读通常在最低的隔离级别“读未提交”（Read Uncommitted）下发生。为了避免脏读，数据库系统通常会使用更高的隔离级别，如“读已提交”（Read Committed）、“可重复读”（Repeatable Read）或“串行化”（Serializable），这些级别通过锁定机制或多版本并发控制（MVCC）等技术来确保数据的一致性和完整性。

不可重复读

不可重复读（Nonrepeatable Read）是数据库事务中的一个现象，指的是在一个事务的执行过程中，多次读取同一数据集合时，由于其他事务的介入和提交，得到的结果集不一致的情况。换句话说，就是在同一个事务中，由于其他事务对数据做了修改并提交，导致你再次读取相同数据时，数据的值发生了变化。

不可重复读的具体情况：
1. 事务A开始并读取了某些数据。
2. 事务B开始并对相同的数据进行了更改，然后提交了这些更改。
3. 事务A再次读取相同的数据，此时读取到的是事务B提交后的更改，与第一次读取的结果不同。

不可重复读的例子：
假设有两个用户，Alice和Bob，他们都可以访问同一个数据库。
• Alice开始一个事务，查询数据库中某个商品的库存数量，假设是100件。
• 此时，Bob开始另一个事务，购买了10件该商品，将库存数量更新为90件，并且提交了这个事务。
• Alice的事务再次查询该商品的库存数量，由于Bob的购买操作已经提交，Alice看到的库存数量变成了90件，与她第一次查询的结果100件不一致。

避免不可重复读：
不可重复读通常在隔离级别为“读已提交”（Read Committed）时发生。为了避免不可重复读，数据库系统通常会使用更高的隔离级别：
• 可重复读（Repeatable Read）：在这个级别，事务在整个过程中可以看到一致的快照数据。即使其他事务提交了更改，当前事务再次读取相同数据时，仍然会看到第一次读取时的值。
• 串行化（Serializable）：这是最高的隔离级别，事务将依次顺序执行，从而提供了最严格的隔离，避免了不可重复读和幻读。
使用这些隔离级别，数据库系统通过锁定机制或多版本并发控制（MVCC）等技术来确保事务在执行过程中读取到的数据保持一致。然而，这些措施可能会影响并发性能，因此在实际应用中需要根据具体需求选择合适的隔离级别。

幻读

幻读（Phantom Read）是数据库事务中的一个现象，与不可重复读类似，但涉及到范围查询（如使用SELECT语句的范围条件）。幻读是指在一个事务中，多次执行相同的范围查询，由于其他事务插入了新的行，导致得到的结果集数量不同。

幻读的具体情况：

1. 事务A开始并执行了一个范围查询，比如查询库存大于某个值的所有商品。
2. 事务B开始并插入了一些新的行，这些行符合事务A的查询条件，但尚未提交。
3. 事务A再次执行相同的范围查询，由于事务B的插入操作，事务A看到了更多的行，即使事务B的更改还未提交。

幻读的例子：

假设有两个用户，Alice和Bob，他们都可以访问同一个数据库。
• Alice开始一个事务，查询库存大于50的所有商品。
• Bob开始另一个事务，向数据库中添加了一些新的商品，这些商品的库存也大于50，但Bob还没有提交这个事务。
• Alice的事务再次执行相同的范围查询，由于Bob的插入操作，Alice看到了比第一次查询更多的商品。

避免幻读：
幻读通常在隔离级别为“可重复读”（Repeatable Read）时发生。要避免幻读，可以使用最高的隔离级别“串行化”（Serializable），在这个级别下，事务将依次顺序执行，从而避免了幻读。
以下是如何在MySQL中设置串行化隔离级别的示例：
-- 设置隔离级别为串行化
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
在串行化隔离级别下，事务将获得必要的行锁和表锁，以确保其他事务不能在当前事务执行期间插入、更新或删除任何可能影响当前事务查询结果的数据。
请注意，虽然串行化隔离级别可以避免幻读，但它会严重影响并发性能，因为它要求对涉及的所有数据进行锁定。在实际应用中，通常需要在数据一致性和系统性能之间找到平衡点。