深入数据库-事务（Transaction）

更新记录#

转载请注明出处。
2024年4月27日 优化内容。
2022年9月3日 发布。
2022年9月3日 从笔记迁移到博客。

事务概念#

从抽象概念上来说：事务（transaction）一种机制，用于执行成批的MySQL操作。
从具体的语法层面来说：不可分割SQL集合。要么全部成功，要么全部失败。

下文以 MYSQL Innodb 引擎为基础。

事务ACID四大特性#

原子性（Atomicity）： 事务中的所有操作被视为一个单一的逻辑工作单元，要么全部执行，要么全部不执行。如果事务中的任何部分失败，整个事务将被回滚到开始之前的状态，保证了数据的完整性，防止出现部分完成的更新导致的数据不一致。

一致性（Consistency）： 事务执行前后，数据库都必须处于一种合法状态，满足任何已定义的业务规则和完整性约束。一致性确保了即使在并发事务的环境中，当每个事务完成后，数据库依然遵循预定义的完整性约束（如实体完整性、参照完整性和用户自定义完整性），保持数据的逻辑正确性。

隔离性（Isolation）： 在并发环境中，不同的事务之间应相互隔离，即一个事务的内部操作对外部事务应是透明的，互不影响。MySQL 提供了多种事务隔离级别（如读未提交、读已提交、可重复读、串行化），每种级别定义了不同的隔离程度，以防止诸如脏读、不可重复读和幻读等问题的发生。

持久性（Durability）： 一旦事务成功提交，对数据库的更改必须是永久的，即使发生系统故障（如断电、系统崩溃等），已经提交的事务对数据库的修改也不会丢失。持久性通常通过数据库系统的日志记录和恢复机制来保证。

并发情况下事务数据间存在的问题#

脏读 ：一个事务读取到另一个事务还未提交的事务。比如，事务A插入记录 1，此时事务A还未提交，然后另一个事务B可以读取到了记录 1。

不可重复读 ：是指在一个事务内，多次读同一数据。但是2次读取的数据不一致。

幻读：指同一个事务内多次查询返回的结果集不一样。比如同一个事务 A 第一次查询时候有 n 条记录，但是第二次同等条件下查询却有 n+1 条记录，这就好像产生了幻觉。发生幻读的原因也是另外一个事务新增或者删除或者修改了第一个事务结果集里面的数据，同一个记录的数据内容被修改了，所有数据行的记录就变多或者变少了。

隔离级别#

目的#

为了解决并发情况下事务数据间存在的问题。可以根据业务的需要设置不同的隔离级别。

MySQL 支持四种标准的事务隔离级别，按照隔离强度从低到高排列如下：

读未提交（Read Uncommitted）：

说明：在这个级别，一个事务可以读取到其他事务尚未提交（可能最终会被回滚）的数据变更。
问题：可能出现脏读（Dirty Read），即一个事务读取到了另一个事务未提交且后续可能被回滚的数据，导致读取到无效数据。
优点：并发性能较高，因为对数据的访问限制最少。
缺点：数据一致性最弱，因为读取的数据可能是临时的、不完整的或最终被废弃的。

读已提交（Read Committed）：
说明：事务只能看到其他事务已经提交的数据，即每次查询都从最新的已提交快照读取。
问题：解决了脏读问题，但可能出现不可重复读（Non-repeatable Read）和幻读（Phantom Read）。
不可重复读：同一事务内，两次相同的查询返回了不同的结果，因为在此期间其他事务提交了影响查询结果的数据变更。
幻读：同一事务内，对某范围的两次查询返回了不同的行数，因为在此期间其他事务插入或删除了满足查询条件的新行。
优点：相对于读未提交，提高了数据一致性，但仍允许一定程度的并发操作。
缺点：对于需要多次读取相同数据并依赖其不变性的事务来说，可能需要额外的锁定或重试机制来确保一致性。

可重复读（Repeatable Read）：
说明：这是MySQL InnoDB存储引擎的默认隔离级别。在该级别，事务在整个执行期间看到的数据是一致的，即多次读取同一数据始终得到相同的结果，即使其他事务提交了对这些数据的修改。
问题：解决了脏读和不可重复读，但仍然可能出现幻读。
优点：为大多数业务场景提供了良好的数据一致性保障，尤其是在需要多次读取同一数据时。
缺点：对于某些特定查询（如范围查询），可能需要额外的机制（如Next-Key Locks）来防止幻读，这可能增加锁竞争和降低并发性能。

串行化（Serializable）：
说明：这是最高的隔离级别，通过强制事务完全按顺序执行，仿佛是单线程操作一样，实现了最高的数据一致性。
问题：完全避免了脏读、不可重复读和幻读。
优点：提供了最强的数据一致性保证，完全避免了并发事务间的干扰。
缺点：并发性能最差，因为事务之间必须等待彼此完成才能继续执行，可能导致大量的锁等待和阻塞，尤其在高并发环境中可能会严重影响系统的吞吐量。

总结
对于对数据一致性要求严格的系统，如金融交易、会计账务等，可能会选择可重复读或串行化级别。
对于读多写少、对并发性能要求较高的系统，可能倾向于使用读已提交或更低的隔离级别。

语法#

查看事务的隔离级别#

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别#

SET [SESSION | GLOBAL] TRANSACTION 
ISOLATION LEVEL [READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE]
---

MySQL配置文件设置#

MySQL 的事务隔离是在 MySQL. ini 配置文件里添加的，在文件的最后添加：

transaction-isolation = REPEATABLE-READ

可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。

事务基础语法（适用于MySQL）#

自动事务提交#

-- 开启自动事务提交
SET AUTOCOMMIT = 1;
SET @@autocommit = 1;
-- 关闭自动事务提交
SET AUTOCOMMIT = 0;
SET @@autocommit = 0;
SELECT @@autocommit //查看自动事务提交状态

注意：针对每个连接，而不是设置服务器。

开启事务#

START TRANSACTION;

提交事务#

提交并关闭事务

COMMIT;

回滚事务#

回滚并关闭事务

ROLLBACK;

保存暂存点#

SAVEPOINT 保留点名称;

回滚到暂存点#

ROLLBACK TO SAVEPOINT 保存点;

释放暂存点#

RELEASE SAVEPOINT 保留点名称;

注意#

这些SQL命令，执行后将自动提交，是在事务处理范围之外：

DROP DATABASE;
DROP TABLE;
DROP;
ALTER TABLE;

引申思考#

mysql底层是如何保证事务的原子性、一致性、隔离性、持久性的？#

原子性、一致性、持久性 通过redo log、undo log实现.
隔离性通过锁、MVCC实现。

SELECT ... FOR UPDATE#

SELECT ... FOR UPDATE 是 MySQL 中的一个 SQL 语句，用于在事务中锁定查询结果集中的记录，直到当前事务结束（提交或回滚）。这种锁定机制主要用于并发环境下确保数据的一致性和防止竞态条件。

SELECT ...
FROM table_name
WHERE condition
FOR UPDATE;

功能：
锁定记录：当执行带有 FOR UPDATE 子句的 SELECT 语句时，MySQL 将锁定查询结果集中满足 WHERE 条件的所有记录。这意味着其他事务在同一时刻不能对这些记录进行更新（包括 UPDATE、DELETE）或再次使用 SELECT ... FOR UPDATE 锁定，直到当前事务结束。

行级锁定：InnoDB 存储引擎支持行级锁定，这意味着只有被查询结果集中的具体行会被锁定，不会影响到其他未被查询的行，有利于提高并发性能。

事务上下文：SELECT ... FOR UPDATE 必须在事务中使用。在执行该语句前，需要先启动一个事务（BEGIN 或 START TRANSACTION），然后执行 SELECT ... FOR UPDATE，最后根据业务逻辑决定是否提交（COMMIT）或回滚（ROLLBACK）事务。

使用场景：
防止丢失更新：在并发环境中，当两个或多个事务同时尝试更新同一行数据时，如果不采取锁定措施，可能会导致其中一个事务的更新被覆盖，即“丢失更新”。通过 SELECT ... FOR UPDATE，可以确保只有获得锁的事务能够更新数据，其他事务必须等待锁释放后才能进行更新。

乐观锁与悲观锁：SELECT ... FOR UPDATE 通常用于实现悲观锁策略，即假设并发冲突频繁发生，先锁定数据以防止冲突。与之相对的是乐观锁策略，如使用版本号或时间戳检查更新时数据是否已被修改。在预期并发冲突较少且希望最大化并发性能的场景中，可能选择使用乐观锁。

确保数据一致性：在涉及多步骤操作（如转账、扣减库存等）的事务中，需要确保在操作过程中数据不变。首先使用 SELECT ... FOR UPDATE 锁定相关记录，然后执行后续的更新操作，最后提交事务。这样可以确保在事务执行期间，其他事务不能对锁定的记录进行修改，从而保证数据的一致性。

注意事项：
死锁风险：不当使用 SELECT ... FOR UPDATE 可能导致死锁。例如，两个事务分别锁定不同的行并试图更新对方已锁定的行，就会形成循环等待，导致死锁。应合理设计事务逻辑，避免不必要的锁定，及时释放不再需要的锁，并设置合理的死锁检测和超时机制。

锁定范围：SELECT ... FOR UPDATE 默认锁定查询结果集中的所有行。如果只想锁定特定的行，应使用明确的 WHERE 子句来缩小锁定范围。另外，注意避免使用可能导致锁定大量行的操作，如范围查询或全表扫描，以免对并发性能造成严重影响。

隔离级别影响：在不同的事务隔离级别下，SELECT ... FOR UPDATE 的行为可能会有所不同。例如，在 READ COMMITTED 隔离级别下，每次查询都会看到最新的已提交数据，可能会导致同一事务内多次 SELECT ... FOR UPDATE 锁定不同版本的同一行。在 REPEATABLE READ（InnoDB 默认隔离级别）下，事务开始后看到的数据在整个事务期间保持不变，可以避免这个问题。

总结来说，SELECT ... FOR UPDATE 是 MySQL 中用于在事务中锁定查询结果集，以确保数据一致性、防止并发冲突的重要工具。在使用时需注意合理锁定范围、避免死锁风险，并结合实际业务需求和并发环境选择合适的事务隔离级别。

SELECT ... LOCK IN SHARE MODE#

SELECT ... LOCK IN SHARE MODE 是 MySQL 中的一个 SQL 语句，用于在事务中对查询结果集中的记录进行共享锁定（Shared Lock），以支持并发读取和防止数据被其他事务修改，直到当前事务结束（提交或回滚）。与 SELECT ... FOR UPDATE 相比，LOCK IN SHARE MODE 主要应用于读取数据的场景，而非更新。

SELECT ...
FROM table_name
WHERE condition
LOCK IN SHARE MODE;

功能：

共享锁定：当执行带有 LOCK IN SHARE MODE 子句的 SELECT 语句时，MySQL 将对查询结果集中的满足 WHERE 条件的所有记录施加共享锁定（S-lock）。这意味着其他事务可以继续对该记录进行读取（共享锁定），但不能进行更新（需要排他锁定，X-lock）或删除，直到当前事务结束。

行级锁定：InnoDB 存储引擎支持行级锁定，这意味着只有被查询结果集中的具体行会被锁定，不会影响到其他未被查询的行，有利于提高并发性能。

事务上下文：SELECT ... LOCK IN SHARE MODE 必须在事务中使用。在执行该语句前，需要先启动一个事务（BEGIN 或 START TRANSACTION），然后执行 SELECT ... LOCK IN SHARE MODE，最后根据业务逻辑决定是否提交（COMMIT）或回滚（ROLLBACK）事务。

使用场景：

读取一致性：在并发环境中，当多个事务同时读取同一条记录时，如果不想让其他事务在读取过程中修改该记录，可以使用 SELECT ... LOCK IN SHARE MODE 对记录进行共享锁定。这样可以确保在事务执行期间，其他事务不能对锁定的记录进行更新或删除，从而保证读取数据的一致性。

防止幻读：在某些隔离级别（如 READ COMMITTED 或 REPEATABLE READ）下，虽然可以避免脏读和不可重复读，但可能仍会出现幻读现象（同一事务内多次相同查询返回不同的行数，因为其他事务插入了新的满足查询条件的行）。通过使用 SELECT ... LOCK IN SHARE MODE 锁定查询结果集，可以防止其他事务插入新的行，从而避免幻读。

并发读取：相较于 SELECT ... FOR UPDATE，SELECT ... LOCK IN SHARE MODE 允许其他事务对锁定记录进行并发读取，更适合于读取密集型场景。当多个事务需要同时读取同一记录时，使用共享锁定可以提高并发性能，避免因独占锁定导致的读写阻塞。

注意事项：

锁定升级：在某些情况下，如果一个持有共享锁定（S-lock）的事务试图更新锁定记录，InnoDB 存储引擎会自动将共享锁定升级为排他锁定（X-lock）。此时，如果其他事务已经持有该记录的排他锁定，升级操作将被阻塞，直到锁释放。

死锁风险：尽管 SELECT ... LOCK IN SHARE MODE 允许并发读取，但如果事务逻辑设计不当，仍可能导致死锁。例如，两个事务分别持有不同记录的共享锁定，并试图更新对方已锁定的记录，就会形成循环等待，导致死锁。应合理设计事务逻辑，避免不必要的锁定，及时释放不再需要的锁，并设置合理的死锁检测和超时机制。

锁定范围：SELECT ... LOCK IN SHARE MODE 默认锁定查询结果集中的所有行。如果只想锁定特定的行，应使用明确的 WHERE 子句来缩小锁定范围。另外，注意避免使用可能导致锁定大量行的操作，如范围查询或全表扫描，以免对并发性能造成严重影响。

隔离级别影响：在不同的事务隔离级别下，SELECT ... LOCK IN SHARE MODE 的行为可能会有所不同。例如，在 READ COMMITTED 隔离级别下，每次查询都会看到最新的已提交数据，可能会导致同一事务内多次 SELECT ... LOCK IN SHARE MODE 锁定不同版本的同一行。在 REPEATABLE READ（InnoDB 默认隔离级别）下，事务开始后看到的数据在整个事务期间保持不变，可以避免这个问题。

总结来说，SELECT ... LOCK IN SHARE MODE 是 MySQL 中用于在事务中对查询结果集进行共享锁定，以确保读取数据的一致性、防止并发写入的重要工具。在使用时需注意合理锁定范围、避免死锁风险，并结合实际业务需求和并发环境选择合适的事务隔离级别。