10. mysql 学习笔记 - 事务管理与并发控制

3 阅读6分钟

在关系型数据库中,事务管理与并发控制 是确保数据一致性、完整性和可靠性的关键内容。MySQL 提供了丰富的事务控制机制,包括 ACID 属性事务隔离级别锁机制 等,用来管理多个并发事务的执行过程。

1. 事物管理

事务必须遵循 ACID 属性,以保证事务的完整性和数据的一致性:

  • A(Atomicity,原子性) :事务中的操作要么全部成功,要么全部失败,不能中途停止。即使发生系统崩溃,已经提交的事务不会影响未提交的事务。
  • C(Consistency,一致性) :事务开始前和结束后,数据库的状态必须是一致的。即使在事务过程中发生故障,数据库不会进入不一致的状态。
  • I(Isolation,隔离性) :多个并发事务的执行相互独立。每个事务的操作都必须在其他事务的操作完成之前完成,且每个事务只能看到自己已提交的数据。
  • D(Durability,持久性) :一旦事务提交,事务对数据库的更改将永久保存,不会因为系统崩溃而丢失。

1.2 事务隔离级别

事务隔离级别决定了不同事务之间的可见性以及它们如何影响对同一数据的并发访问。MySQL 支持四种事务隔离级别:

  • READ UNCOMMITTED:允许读取其他事务未提交的数据,即 脏读(Dirty Read)。这种隔离级别会带来数据的不一致性,但能提供最高的并发性能。
  • READ COMMITTED:只允许读取已提交事务的数据,避免脏读,但仍然可能会产生 不可重复读(Non-repeatable Read)。即,一个事务在执行过程中读取的数据在后续操作中可能会发生变化。
  • REPEATABLE READ:事务在开始时读取的所有数据,整个事务过程中都保持一致,不会发生 不可重复读。MySQL InnoDB 引擎的默认隔离级别。虽然避免了不可重复读,但仍然存在 幻读(Phantom Read)的问题,即同一个查询在不同时间可能会读取到不同的行。
  • SERIALIZABLE:最高的隔离级别,强制事务串行执行,避免了脏读、不可重复读和幻读的问题。虽然保证了数据一致性,但性能较差,容易造成阻塞。

1.3 设置事务隔离级别

MySQL 可以通过以下语句设置事务隔离级别:

  • 查看当前隔离级别
SELECT @@global.tx_isolation;

设置事务隔离级别

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

或者设置 会话级别

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

2. 锁机制

2.1 行锁 vs. 表锁

MySQL 提供了 行锁表锁 两种锁机制,分别适用于不同的场景。

  • 行锁:锁定某一行数据,允许其他事务操作其他行。行锁是更细粒度的锁定,能够提高并发性。行锁适用于 InnoDB 存储引擎,通常是通过 索引 来实现的。

    • 优点:能高效地处理并发操作,减少了锁的竞争。

    • 缺点:锁粒度较小,可能会产生死锁。

  • 表锁:锁定整张表,只有当前事务完成,其他事务才能操作该表的任何数据。表锁是更粗粒度的锁,适用于 MyISAM 存储引擎。

    • 优点:实现简单,性能开销小。

    • 缺点:并发性较低,可能导致性能瓶颈。

2.2 死锁

死锁是指两个或多个事务在执行过程中互相等待对方释放锁,导致无法继续执行。死锁通常发生在 行锁 情况下。

  • 死锁的检测与处理:MySQL 使用 死锁检测算法 来识别死锁。当检测到死锁时,MySQL 会回滚某个事务,以解除死锁状态。

  • 死锁避免:可以通过合理设计事务顺序、减小事务的锁粒度、减少长时间持有锁的操作来避免死锁。

2.3 锁等待

  • 锁等待:当一个事务请求获取某行数据的锁时,如果该行数据已被其他事务锁定,当前事务会进入等待状态。锁等待可能会导致性能下降,尤其是在高并发场景下。

  • 锁超时:MySQL 允许设置锁等待超时时间,当事务等待时间超过设定值时,会自动回滚并报错。可以通过 innodb_lock_wait_timeout 参数设置锁等待超时时间:

SET GLOBAL innodb_lock_wait_timeout = 50;

3. 自动提交与手动提交

3.1 自动提交

MySQL 默认启用 自动提交 模式,即每执行一条 SQL 语句,系统会自动提交事务。这意味着每条单独的 SQL 语句都被当作一个独立的事务执行。

开启自动提交

SET AUTOCOMMIT = 1;

关闭自动提交

SET AUTOCOMMIT = 0;

3.2 手动提交

在手动提交模式下,事务不会自动提交,直到明确调用 COMMIT 语句,事务中的所有操作才会被持久化到数据库中。

开始事务:MySQL 会话默认开启自动提交模式,但你可以显式开启事务:

START TRANSACTION;

提交事务:当事务中的所有操作都执行完毕时,调用 COMMIT 来提交事务,保存数据更改:

COMMIT;

回滚事务:如果事务中出现错误或需要取消,调用 ROLLBACK 来回滚事务,撤销所有更改:

ROLLBACK;

4. 案例与应用

案例 1:事务与隔离级别

假设你有一个银行账户管理系统,其中每个账户的余额需要进行修改。我们需要确保每个转账操作的正确性,因此使用事务进行管理。

START TRANSACTION;  -- 开始事务

-- 查询账户余额
SELECT balance FROM accounts WHERE account_id = 1001;

-- 更新账户余额
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1001;

-- 查询对方账户余额
SELECT balance FROM accounts WHERE account_id = 1002;

-- 更新对方账户余额
UPDATE accounts SET balance = balance + 100 WHERE account_id = 1002;

COMMIT;  -- 提交事务

如果事务使用了 READ UNCOMMITTED 隔离级别,可能会出现脏读问题,例如在第一个查询和第二个查询之间,另一个事务可能已经修改了余额,导致余额不一致。

案例 2:死锁示例

假设两个事务分别更新两个不同的表。由于它们的顺序不一致,可能会导致死锁:

事务 1

START TRANSACTION;
UPDATE users SET name = 'Alice' WHERE id = 1;
UPDATE orders SET status = 'paid' WHERE user_id = 1;
COMMIT;

事务 2

START TRANSACTION;
UPDATE orders SET status = 'paid' WHERE user_id = 1;
UPDATE users SET name = 'Bob' WHERE id = 1;
COMMIT;

如果两个事务同时运行,它们会互相等待对方释放锁,导致死锁。MySQL 会自动回滚其中一个事务以解除死锁。