MySQL事物详解

事物

MySQL事务（Transaction）是一系列的数据库操作，作为一个不可分割的逻辑单元，要么全部执行成功，要么全部失败回滚。事务的目的是确保数据的一致性和完整性。

MySQL事务具有以下特性（通常使用ACID缩写表示）：

1. 原子性（Atomicity）：事务中的所有操作要么全部成功执行，要么全部回滚。如果发生了错误，事务会回滚到初始状态，保证数据的一致性。

2. 一致性（Consistency）：事务开始之前和结束之后，数据库的完整性约束没有被破坏。在事务执行期间，数据会从一个一致状态转变为另一个一致状态。

3. 隔离性（Isolation）：每个事务在逻辑上是独立执行的，彼此之间互不干扰。一个事务在提交之前对其他事务是不可见的，事务之间的操作是相互隔离的。

4. 持久性（Durability）：一旦事务提交，其对数据库的修改将是永久性的，即使在系统发生故障的情况下也不会丢失。

MySQL中使用以下语句来管理事务：

• START TRANSACTION或BEGIN：标识事务的开始。
• COMMIT：提交事务，将所有的修改永久保存到数据库。
• ROLLBACK：回滚事务，撤销所有的修改。
• SAVEPOINT：在事务中创建一个保存点，可以在后续操作中通过ROLLBACK TO SAVEPOINT回滚到指定的保存点。
• SET AUTOCOMMIT：设置是否自动提交事务。

默认情况下，MySQL处于自动提交模式（AUTOCOMMIT），即每个SQL语句都被视为一个独立的事务并立即提交。要使用事务，可以将数据库连接设置为手动提交模式，然后使用事务控制语句来管理事务的开始、提交和回滚。

例如，以下是一个使用事务的示例：

START TRANSACTION; -- 开始事务

-- 执行一系列SQL操作

COMMIT; -- 提交事务

如果在事务执行期间发生错误，可以使用ROLLBACK回滚事务，将所有的修改撤销到事务开始之前的状态。

事务的使用可以确保复杂的数据库操作的一致性和完整性，对于需要进行多个操作的情况，特别是涉及到数据的更新、插入和删除时，事务是非常有用的。

事物的开始、结束

在MySQL中，DDL（数据定义语言）、DCL（数据控制语言）和DML（数据操作语言）是不同类型的SQL语句，用于定义、控制和操作数据库。它们在事务的开始和结束上有一些不同。

1. DDL（数据定义语言）：

   • DDL语句用于创建、修改和删除数据库对象，如表、索引、视图等。
   • DDL语句包括CREATE、ALTER和DROP等操作。
   • DDL语句在执行时会自动提交事务，并立即生效，无法在事务中回滚。

   例如：

   CREATE TABLE table_name (column1 datatype, column2 datatype, ...); -- 创建表
   
   ALTER TABLE table_name ADD column_name datatype; -- 修改表
   
   DROP TABLE table_name; -- 删除表
   

2. DCL（数据控制语言）：

   • DCL语句用于控制数据库访问和权限，包括授权、回收权限和事务控制等。
   • DCL语句包括GRANT、REVOKE和START TRANSACTION等操作。
   • DCL语句在执行时会自动提交事务，并立即生效，无法在事务中回滚。

   例如：

   GRANT privileges ON database.table TO user@host; -- 授权权限
   
   REVOKE privileges ON database.table FROM user@host; -- 回收权限
   
   START TRANSACTION; -- 开始事务
   

3. DML（数据操作语言）：

   • DML语句用于操作数据库中的数据，包括查询、插入、更新和删除等操作。
   • DML语句包括SELECT、INSERT、UPDATE、DELETE等操作。
   • DML语句可以在事务中使用，并根据事务的提交或回滚来决定其最终效果。

   例如：

   START TRANSACTION; -- 开始事务
   
   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...); -- 插入数据
   
   UPDATE table_name SET column1 = value1 WHERE condition; -- 更新数据
   
   DELETE FROM table_name WHERE condition; -- 删除数据
   
   COMMIT; -- 提交事务
   

注意事项：

• DDL和DCL语句会自动提交事务，并立即生效，无法在事务中回滚。
• DML语句可以在事务中使用，并根据事务的提交或回滚来决定其最终效果。
• 如果想要在DDL或DCL操作中实现事务控制，可以使用START TRANSACTION和COMMIT语句，将其嵌套在事务中。

总结：DDL和DCL语句在执行时会自动提交事务，并立即生效；DML语句可以在事务中使用，并根据事务的提交或回滚来决定其最终效果。

注意

root@(none) 10:08 mysql>show variables like '%autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)

关闭自动提交

set @@autocommit = 0;

开启自动提交

set @@autocommit = 1;

事情背后

在MySQL中，事务的背后是数据库管理系统（DBMS）的一系列机制和技术，用于确保事务的原子性、一致性、隔离性和持久性（ACID属性）。以下是一些与MySQL事务相关的背后实现细节：

1. 事务日志（Transaction Log）：

   • MySQL使用事务日志来记录事务执行期间所做的修改操作，包括插入、更新和删除等。
   • 事务日志是以逻辑方式记录的，即记录哪些数据被修改了，而不是直接记录修改后的数据。
   • 事务日志允许在数据库崩溃或故障恢复后，通过重新执行日志中的操作来恢复事务的一致性。

2. 锁机制（Locking Mechanism）：

   • MySQL使用锁机制来实现事务的隔离性，确保并发事务之间的数据访问不会互相干扰。
   • 锁可以分为共享锁（Shared Lock）和排他锁（Exclusive Lock）。
   • 共享锁允许多个事务同时读取一个数据项，而排他锁则在事务对数据项进行修改时独占该数据项。

3. 多版本并发控制（MVCC，Multi-Version Concurrency Control）：

   • MySQL使用MVCC来处理并发事务，以提高数据库的并发性能。
   • MVCC通过在数据库中维护多个版本的数据来实现事务的隔离性，使每个事务在读取数据时能够看到一致的快照。
   • 当事务需要修改数据时，MVCC会创建一个新的数据版本，并将修改操作应用于新版本，而不是直接修改原始数据。

4. 内部提交（Internal Commit）：

   • 在MySQL中，事务的提交不仅仅是将修改应用到磁盘上的数据文件，还涉及到内部的一系列提交操作。
   • 内部提交包括将事务日志写入磁盘、释放锁资源、更新数据库元数据等操作。

5. 持久性保证（Durability Guarantee）：

   • MySQL通过将事务日志和数据文件存储在持久性存储介质上（如磁盘），以保证事务的持久性。
   • 即使在数据库崩溃或系统故障的情况下，MySQL可以通过重放事务日志来恢复事务的一致性，并将数据恢复到事务提交之后的状态。

并发事物的四个问题

在MySQL中，并发事务可能面临以下四个问题，通常被称为并发控制问题：

1. 脏读（Dirty Read）：

   • 脏读指在一个事务中读取了另一个未提交事务所做的修改数据。
   • 当一个事务读取了另一个事务尚未提交的数据，并在后续发生回滚时，脏读就发生了。
   • 脏读可能导致事务基于不一致的数据做出错误的决策。

2. 不可重复读（Non-repeatable Read）：

   • 不可重复读指在一个事务内，多次读取同一数据时得到的结果不一致。
   • 当一个事务在读取某个数据后，另一个事务对该数据做了修改并提交后，第一个事务再次读取同一数据时，发现数据已经改变。
   • 不可重复读可能导致事务无法依赖于一致的数据状态进行处理。

3. 幻读（Phantom Read）：

   • 幻读指在一个事务内，多次执行相同的查询得到的结果集不一致。
   • 当一个事务在读取某个范围的数据后，另一个事务插入了符合该范围的新数据并提交后，第一个事务再次执行相同的查询时，发现结果集中出现了新插入的数据（幻行）。
   • 幻读可能导致事务无法保证一致的结果集，影响数据的完整性。

4. 丢失更新（Lost Update）：

   • 丢失更新指两个或多个事务同时修改同一数据，但只有一个事务的修改生效，而其他事务的修改被覆盖丢失。
   • 当多个事务同时读取同一数据并进行修改后，后提交的事务的修改可能覆盖先提交的事务的修改，导致先前的修改丢失。
   • 丢失更新可能导致数据的不一致和意外的结果。

为了解决这些并发控制问题，MySQL提供了多种并发控制机制，如锁机制、多版本并发控制（MVCC）和隔离级别等。通过合理设置隔离级别、使用适当的锁粒度和优化事务操作，可以有效地解决并发控制问题，确保数据的一致性和可靠性。

事物的四个隔离级别

在MySQL中，事务的隔离级别定义了事务之间的隔离程度，它决定了一个事务能否看到其他事务未提交的修改，以及是否允许脏读、不可重复读和幻读等问题。MySQL支持四个隔离级别，分别是：读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）和串行化（Serializable）。

1. 读未提交（Read Uncommitted）：

   • 最低的隔离级别，事务可以读取其他事务尚未提交的修改。
   • 允许脏读、不可重复读和幻读、丢失更新等问题的发生。
   • 事务的隔离性最弱，性能较高，但数据的一致性和可靠性较差。

2. 读已提交（Read Committed）：

   • 默认的隔离级别，事务只能读取已经提交的数据。
   • 不允许脏读，即一个事务不能读取另一个事务尚未提交的修改。
   • 可能出现不可重复读和幻读、丢失更新的问题。

3. 可重复读（Repeatable Read）：

   • 在同一个事务中，多次读取相同的数据将得到一致的结果，即使其他事务对数据进行了修改。
   • 保证了事务执行期间不会发生不可重复读的问题。
   • 可能出现丢失更新的问题，即在同一个事务中，多次执行相同的查询可能返回不同的结果集。

4. 可串行化（Serializable）：

   • 最高的隔离级别，通过强制事务串行执行来避免并发问题。
   • 在事务执行期间，其他事务无法对被锁定的数据进行读取或修改。
   • 可以避免脏读、不可重复读、幻读和丢失更新的问题，但并发性能最差。

不同的隔离级别提供不同的数据一致性和并发性能。选择合适的隔离级别取决于应用的需求和对数据的一致性要求。通常情况下，推荐使用默认的读已提交（Read Committed）或可重复读（Repeatable Read）隔离级别，并根据具体情况进行调整。

查看mysql的使用的隔离级别

root@(none) 10:22 mysql>select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.01 sec)

设置隔离级别

在MySQL中，可以使用SET TRANSACTION ISOLATION LEVEL语句设置事务的隔离级别，例如：

SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- 设置隔离级别为读已提交

注意：隔离级别的更改对当前事务有效，需要在事务开始前设置。

事物的流程

MySQL事务的流程如下：

1. 开始事务：

   • 使用START TRANSACTION或BEGIN语句开始一个新的事务。
   • 这将把数据库连接置于事务状态，并在执行后续操作时将其视为事务的一部分。

   例如：

   START TRANSACTION;
   -- 或
   BEGIN;
   

2. 执行一系列操作：

   • 在事务中执行一系列的数据库操作，包括插入、更新、删除等操作。
   • 这些操作会在事务中被记录，但对数据库的实际修改并不会立即生效。
   • 执行期间，可以使用SAVEPOINT语句创建保存点，以便在后续操作中回滚到指定的保存点。

   例如：

   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   UPDATE table_name SET column1 = value1 WHERE condition;
   DELETE FROM table_name WHERE condition;
   

3. 保存点（Savepoint）：

   • 在事务执行过程中，可以使用SAVEPOINT语句创建一个保存点。
   • 保存点允许在后续操作中回滚到特定的保存点，而不需要回滚到事务的开始。

   例如：

   SAVEPOINT savepoint_name;
   

4. 回滚操作：

   • 如果在事务执行期间遇到错误或需要回滚到之前的状态，可以使用回滚操作。
   • 使用ROLLBACK语句回滚到事务的开始，撤销所有的修改。
   • 使用ROLLBACK TO SAVEPOINT语句回滚到指定的保存点，撤销从该保存点之后的修改。

   例如：

   ROLLBACK; -- 回滚到事务的开始
   -- 或
   ROLLBACK TO SAVEPOINT savepoint_name; -- 回滚到指定的保存点
   

5. 提交事务：

   • 如果事务中的操作全部执行成功，可以使用COMMIT语句提交事务，将所有的修改永久保存到数据库中。
   • 提交后，数据库将应用所有修改并释放相关资源，这些修改将对其他会话和连接可见。

   例如：

   COMMIT; -- 提交事务
   

6. 结束事务：

   • 无论是提交事务还是回滚事务，都将结束事务。
   • 结束事务后，数据库连接将返回到自动提交模式，每个SQL语句将被视为一个单独的事务并立即提交。

事务的流程如上所述。在开始事务后，执行一系列操作，并根据需要创建保存点。如果发生错误或需要回滚，可以回滚到事务的开始或指定的保存点。如果所有操作都成功完成，可以提交事务，将修改永久保存到数据库中。无论是提交还是回滚，都会结束事务，并返回到自动提交模式。