自学内容网 自学内容网
自学内容网 > 正文

【MySQL 进阶之路】事务并发情况分析

🕗 发布于 2024-12-10 12:41 mysql  数据库  sql  性能优化  

MySQL事务并发控制分析笔记

在数据库系统中，事务并发控制至关重要，能够确保多个事务并发执行时的数据一致性、隔离性和正确性。MySQL通过不同的锁机制控制并发操作，以确保事务的隔离性。以下是对事务A和事务B并发行为的详细分析，并对锁粒度和事务行为进行了优化解读。

认识事务

在这里插入图片描述

场景一：事务A和事务B的并发行为（阻塞）

事务操作锁定情况结果
事务ASELECT FOR UPDATE 查询数据行级排他锁（X锁）锁定查询结果集中的行，其他事务无法修改这些行
事务BSELECT FOR UPDATE 查询相同数据被阻塞事务B被阻塞，直到事务A提交或回滚
事务BSELECT 查询数据无锁事务B可以继续执行读取操作，但无法修改数据
事务ACOMMIT 提交事务释放锁事务A提交后，行锁被释放，事务B可以继续执行操作
事务BUPDATE 修改数据行级排他锁（X锁）事务B可以更新数据（前提是事务A已经提交，锁已释放）
解释：
  1. 事务A执行SELECT FOR UPDATE：此操作会对查询结果集中的每一行加上行级排他锁（X锁），这意味着其他事务无法修改这些行，直到事务A提交或回滚。
  2. 事务B执行SELECT FOR UPDATE：事务B会被阻塞，因事务A已对相同的行加了排他锁。事务B需要等待事务A提交或回滚后才能继续执行。
  3. 事务B执行SELECT查询：在没有加锁的情况下，事务B可以继续执行读取操作，但无法进行修改操作。此时没有锁定数据，其他事务仍可修改数据。
  4. 事务A提交COMMIT事务：事务A提交后，事务A持有的排他锁被释放，事务B可以获取锁并继续执行操作。
  5. 事务B执行UPDATE：事务B可以在事务A提交后修改数据，前提是事务A已释放锁。

场景二：事务A和事务B的死锁场景

事务操作锁定情况结果
事务ASELECT FOR UPDATE 查询数据行级排他锁（X锁）锁定查询结果集中的行，其他事务无法修改这些行
事务BSELECT FOR UPDATE 查询相同数据行级排他锁（X锁）事务B被阻塞，等待事务A提交或回滚
事务AUPDATE 修改数据行级排他锁（X锁）事务A继续持有对该行的排他锁，进行更新操作
事务BUPDATE 修改数据行级排他锁（X锁）事务B也在等待事务A释放锁，但事务A同样在等待事务B释放锁，导致死锁
事务ACOMMIT 提交事务释放锁事务A提交后，死锁解除，行锁被释放
事务BCOMMIT 提交事务释放锁事务B提交并释放锁，系统恢复正常
死锁分析：
  1. 事务A和事务B各自对查询到的行执行SELECT FOR UPDATE，并分别对相同的行加了排他锁。
  2. 事务A在执行UPDATE时继续持有行级排他锁，试图修改数据。
  3. 事务B也执行了UPDATE，但它等待事务A释放锁，而事务A又在等待事务B释放它持有的锁。
  4. 这种互相等待的情况形成了死锁。数据库系统通过死锁检测机制（通常采用回滚策略）来解决死锁，系统会选择回滚其中一个事务，从而解除死锁。
结论：

在此场景中，事务A和事务B因为相互等待对方释放锁，最终导致了死锁。数据库会自动检测到这种情况并回滚其中一个事务，确保系统能够继续运行。

锁粒度与事务行为分析

锁的粒度决定了数据库如何限制多个事务之间的并发操作。MySQL支持多种类型的锁，常见的有行级锁和表级锁。不同的操作会使用不同粒度的锁，影响事务的并发性和执行效率。

操作类型锁粒度锁的行为说明
SELECT FOR UPDATE（查询单行）行级排他锁锁定查询到的行其他事务无法修改或删除该行，直到事务提交或回滚。
SELECT FOR UPDATE（查询多行）行级排他锁锁定查询到的所有行锁定所有符合查询条件的行，防止其他事务修改这些行。
SELECT（普通查询）无锁允许其他事务修改数据事务可以并发执行，但不能修改数据，适用于只需要读取的情况。
UPDATE行级排他锁锁定要更新的行在更新时获取排他锁，防止其他事务修改相同的行。
COMMIT 提交事务释放锁释放对数据行的锁提交时释放锁，其他事务可以获得锁并继续执行。
解释：
  1. SELECT FOR UPDATE：对查询结果集中的每一行加行级排他锁（X锁）。其他事务无法修改这些行，直到当前事务提交或回滚。
  2. SELECT查询：普通查询操作不加锁，多个事务可以并发执行读取操作。此时数据不会被锁定，其他事务可以对数据进行修改。
  3. UPDATE：在执行UPDATE时，数据库会对更新的行加排他锁，确保在当前事务提交前，其他事务无法修改相同的数据。
  4. COMMIT：提交事务时会释放所有加的锁。这样，其他事务可以获取到这些锁并继续操作。

认识死锁

在这里插入图片描述

如何避免死锁？

1. 锁的顺序控制

假设你有两个事务，分别操作相同的资源：A行和B行。为了避免死锁，你可以保证两个事务总是按照相同的顺序来获取锁。例如：

  • 事务A：先锁定A行，再锁定B行。
  • 事务B：同样先锁定A行，再锁定B行。

通过这种方式，即便两个事务同时请求A行和B行的锁，也不会发生交叉等待。

2. 设置锁等待超时
SET innodb_lock_wait_timeout = 50;  -- 设置最大锁等待时间为50秒

在这个例子中，如果事务等待某个锁超过50秒，就会被回滚，从而避免无限期的等待。

3. 尽量缩短事务的执行时间

确保事务在获取锁之后，尽量快速完成操作，并尽早提交，以减少锁的持有时间。例如，避免在事务中执行长时间的计算或等待外部服务的操作。

START TRANSACTION;
SELECT * FROM my_table WHERE id = 1 FOR UPDATE;
UPDATE my_table SET value = value + 1 WHERE id = 1;
-- 做一些简单的计算和更新操作
COMMIT;  -- 提交事务

为了避免死锁，关键在于：

  • 保证锁的获取顺序一致，避免交叉等待。
  • 使用锁等待超时机制，避免因死锁导致事务无限期阻塞。
  • 减少锁持有的时间，尽量缩短事务执行的时间，避免长时间占用锁。
  • 使用合适的事务隔离级别和锁粒度，减少不必要的锁冲突。
  • 死锁检测和回滚机制，及时捕捉并解决死锁问题。

总结

在MySQL的事务并发控制中，不同的事务操作对数据产生不同的锁定效果。通过合理的锁机制控制，MySQL能够有效保证事务的隔离性，避免数据冲突和不一致。理解事务的并发行为和锁粒度，有助于开发人员在设计数据库操作时做出更好的决策，尤其是在处理高并发和复杂事务时，能够提升系统的效率和稳定性。


原文地址：https://blog.csdn.net/ajsbxi/article/details/144354569

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

相关文章

自学内容网
Copyright © 2015-2024