MySQL死锁问题排查方式

什么是MySQL死锁

死锁（Deadlock）是指两个或多个事务互相持有对方需要的资源，谁都不肯松手，导致“僵局”的现象。举个栗子：
    事务A锁定了行1，想更新行2；
    事务B锁定了行2，想更新行1；
    俩事务都在等对方释放锁，谁都动不了，这就形成了死锁。

InnoDB虽然自带死锁检测机制（默认开启），会自动回滚其中一个事务，但频繁的死锁会严重影响系统性能，甚至导致接口超时、用户体验下降。

# 饮用：https://blog.csdn.net/weixin_42148384/article/details/149079784

死锁示例

示例一 product和order 库存和订单相互修改同一张表数据

数据准备

-- 订单表
CREATE TABLE `deathLockTest`.`orders` (
  `id` int(11) NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  `quantity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `deathLockTest`.`orders` (`id`, `product_id`, `quantity`) VALUES (1, 1, 8);
INSERT INTO `deathLockTest`.`orders` (`id`, `product_id`, `quantity`) VALUES (2, 2, 18);
-- 产品表
CREATE TABLE `deathLockTest`.`products` (
  `id` int(11) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
INSERT INTO `deathLockTest`.`products` (`id`, `name`, `stock`) VALUES (1, 'PA', 100);
INSERT INTO `deathLockTest`.`products` (`id`, `name`, `stock`) VALUES (2, 'PB', 200);

死锁产生

# 事物 一
  START TRANSACTION;
  update products set stock = stock -5 where id = 1;
  -- 模拟延迟
  DO SLEEP(10);
  update orders set quantity = quantity +5 where id =1;
  COMMIT;

# 事物 二
  START TRANSACTION;
  update orders set quantity = quantity -5 where id =1;

  -- 模拟延迟
  DO SLEEP(10);
  update products set stock = stock + 5 where id = 1;
  COMMIT;

示例二 Insert 操作时`违反唯一性约束`

场景: Insert 插入100w条数据时，使用多线程可能造成死锁

数据准备

CREATE TABLE `trans` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `trans_amount` float DEFAULT NULL,
  `trans_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci;

死锁产生

-- 事物1 ⬇️
BEGIN;INSERT INTO trans VALUES(1,399,'2025-08-31');
-- 事物2 ⬇️
BEGIN;INSERT INTO trans VALUES(1,399,'2025-08-31');
-- 事物3 ⬇️
BEGIN;INSERT INTO trans VALUES(1,399,'2025-08-31');

-- 在回滚事物1
ROLLBACK


# 流程说明
  - 当`事物1` insert 插入数据前，首先获得一个（x锁，即排他锁）, 事物二和三无法获取排他锁。
  - 当`事物2`和`事物3` 插入数据时，发现`事物1`违反了唯一性约束原则，会在等待执行队列里面`申请排他锁（x锁，即排他锁）` 转 `申请共享锁（s锁，即共享锁）`。 但是事物2和事物3并没有拿到共享锁
  - 当`事物1`释放掉排他锁（x锁）时，事物2和事物3就拿到共享锁（s锁）之前先获取`插入意向锁`（IX锁，即插入意向锁），此时事物2和事物3都在等待对方释放排他锁（s锁）

查看事物信息

# 1. 查看进行进行中的事物
SELECT * FROM information_schema.INNODB_TRX;
# 2. 查看正在锁的事物
SELECT * FROM information_schema.INNODB_LOCKS;
# 3. 查看等待锁的事物
SELECT * FROM information_schema.INNODB_LOCK_WAITS;
# 4. 查询是否缩表
SHOW OPEN TABLES WHERE In_use > 0 ;

如何排查MySQL死锁

死锁日志查看

-- 查看 Status 内的结果，可以借助AI分析
SHOW ENGINE INNODB STATUS;

查看错误日志

-- 查看死锁 错误日志路径，使用tail 或者 cat 查看
SHOW VARIABLES LIKE 'log_error';

`show engine innodb status 日志分析`

好的，我们来详细解读一下这段 SHOW ENGINE INNODB STATUS 的输出日志。这份报告的核心是记录了一个死锁（Deadlock）事件。

核心问题：死锁（Deadlock）
这是日志中最关键的部分，它告诉我们数据库在 2025-08-31 22:20:48 检测并处理了一次死锁。

死锁是如何发生的？
两个事务（Transaction 281 和 Transaction 280）在操作时形成了循环等待，每个事务都持有另一个事务需要的锁。

事务 1 (TRANSACTION 281)

正在执行: update products set stock = stock + 5 where id = 1

已持有: 它已经成功获取了 orders 表中 id=1 那条记录的排他锁（X lock）。（在“CONFLICTING WITH”部分指出）

正在等待: 它正在尝试获取 products 表中 id=1 那条记录的排他锁（X lock），但这个锁正被事务2持有，所以它必须等待。（在“WAITING FOR”部分）

事务 2 (TRANSACTION 280)

正在执行: update orders set quantity = quantity +5 where id =1

已持有: 它已经成功获取了 products 表中 id=1 那条记录的排他锁（X lock）。（在“CONFLICTING WITH”部分指出）

正在等待: 它正在尝试获取 orders 表中 id=1 那条记录的排他锁（X lock），但这个锁正被事务1持有，所以它必须等待。（在“WAITING FOR”部分）

形成循环等待（死锁）：

事务1 握着 orders 的锁，等着 事务2 释放 products 的锁。

事务2 握着 products 的锁，等着 事务1 释放 orders 的锁。

两者互相等待，谁也进行不下去，这就形成了死锁。

数据库的解决方案
InnoDB 引擎的死锁检测机制立即发现了这个问题。它选择回滚了其中一个事务（WE ROLL BACK TRANSACTION (2)） 来打破这个死循环。

事务 (2) 也就是 TRANSACTION 280 被回滚了。

一旦事务280被回滚，它释放了持有的 products 表上的锁。

事务281（正在等待 products 的锁）立即获得了这个锁，并得以继续执行完成。

被回滚的事务（280）会向应用程序返回一个错误，应用程序需要处理这个错误（例如，重新发起这个事务）。

Web层级，ORM如何防止MYSQL死锁

# flask 为例
从下列：
    事务管理：保持事务简短，按固定顺序访问资源: with_for_update
    	- 保持事务简短
			- 使用固定顺序访问资源

    锁优化：选择合适的隔离级别，使用行级锁 : isolation_level 事物隔离级别
    	- 选择合适的隔离级别
    	- 使用行级锁而不是表级锁。with_for_update 获取行级锁

    重试机制：实现死锁检测和自动重试
    	- 实现死锁重试装饰器

    ORM优化：使用乐观锁，批量操作
    	- 使用乐观锁控制并发，失败了就失败了（乐观锁）
    	- 批量操作优化 

    并发控制：配置合适的连接池，使用信号量控制并发
    	- 配置合适的连接池：pool_recycle，pool_timeout，max_overflow，pool_size
    	- 使用信号量控制并发

    应用层解决方案：使用队列串行化冲突操作
    	- 使用队列串行化冲突操作

    监控日志：记录死锁事件以便分析和优化
    	- 添加死锁监控和日志

DeepSeek详细输出

# 在 Flask 中防止 MySQL 死锁的代码级策略

在 Flask 应用中防止 MySQL 死锁需要从多个层面考虑，包括事务管理、查询优化、并发控制和重试机制。以下是一些实用的代码级策略：

## 1. 事务管理策略

### 保持事务简短
```python
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import exc

db = SQLAlchemy()

def update_inventory(product_id, quantity):
    try:
        # 开始事务
        product = Product.query.with_for_update().filter_by(id=product_id).first()
        
        # 执行业务逻辑 - 保持简短
        product.stock -= quantity
        
        # 立即提交
        db.session.commit()
        
    except Exception as e:
        db.session.rollback()
        raise e
```

### 使用固定顺序访问资源
```python
def update_resources_in_order(resource_ids):
    # 按固定顺序排序资源ID，避免死锁
    sorted_ids = sorted(resource_ids)
    
    for resource_id in sorted_ids:
        resource = Resource.query.with_for_update().filter_by(id=resource_id).first()
        # 更新操作
        resource.value += 1
        
    db.session.commit()
```

## 2. 隔离级别和锁优化

### 选择合适的隔离级别
```python
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# 设置连接池和隔离级别
engine = create_engine(
    'mysql+pymysql://user:pass@localhost/db',
    isolation_level="READ_COMMITTED",  # 使用读已提交隔离级别
    pool_size=20,
    max_overflow=0
)

Session = sessionmaker(bind=engine)
```

### 使用行级锁而不是表级锁
```python
from sqlalchemy import select, update

# 使用 SELECT...FOR UPDATE 获取行级锁
def update_with_row_lock(product_id, quantity):
    # 使用 with_for_update 获取行级锁
    product = db.session.query(Product).filter(
        Product.id == product_id
    ).with_for_update().first()
    
    if product:
        product.stock -= quantity
        db.session.commit()
```

## 3. 重试机制

### 实现死锁重试装饰器
```python
import time
from functools import wraps
from sqlalchemy.exc import OperationalError

def retry_on_deadlock(retries=3, delay=0.1):
    def decorator(func):
        @wraps(func)
        def wrapper(*args, **kwargs):
            attempt = 0
            while attempt < retries:
                try:
                    return func(*args, **kwargs)
                except OperationalError as e:
                    if 'Deadlock' in str(e) or '1213' in str(e):
                        attempt += 1
                        if attempt >= retries:
                            raise
                        time.sleep(delay * attempt)  # 指数退避
                    else:
                        raise
            return None
        return wrapper
    return decorator

# 使用重试装饰器
@retry_on_deadlock(retries=3, delay=0.1)
def update_product_safely(product_id, quantity):
    product = Product.query.with_for_update().filter_by(id=product_id).first()
    product.stock -= quantity
    db.session.commit()
```

## 4. ORM 查询优化

### 使用乐观锁控制并发
```python
from sqlalchemy import Column, Integer, DateTime
from sqlalchemy.ext.declarative import declarative_base
import datetime

Base = declarative_base()

class Product(Base):
    __tablename__ = 'products'
    
    id = Column(Integer, primary_key=True)
    stock = Column(Integer)
    version_id = Column(Integer, default=0)  # 版本号字段
    updated_at = Column(DateTime, default=datetime.datetime.utcnow)
    
    __mapper_args__ = {
        'version_id_col': version_id  # 启用乐观并发控制
    }

# 使用乐观锁
def update_with_optimistic_lock(product_id, quantity):
    try:
        product = Product.query.filter_by(id=product_id).first()
        product.stock -= quantity
        db.session.commit()
        return True
    except StaleDataError:
        db.session.rollback()
        # 处理版本冲突
        return False
```

### 批量操作优化
```python
from sqlalchemy.orm import Query

# 使用批量更新代替逐行更新
def bulk_update_products(product_updates):
    # product_updates: [(product_id, new_stock), ...]
    update_stmt = update(Product).where(Product.id == bindparam('p_id')).values(
        stock=bindparam('new_stock')
    )
    
    params = [{'p_id': pid, 'new_stock': stock} for pid, stock in product_updates]
    db.session.execute(update_stmt, params)
    db.session.commit()
```

## 5. 连接池和并发控制

### 配置合适的连接池
```python
from flask import Flask
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://user:pass@localhost/db'
app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {
    'pool_size': 10,
    'max_overflow': 5,
    'pool_timeout': 30,
    'pool_recycle': 1800,  # 30分钟回收连接
}
db = SQLAlchemy(app)
```

### 使用信号量控制并发
```python
import threading

# 使用信号量控制对特定资源的并发访问
resource_semaphores = {}

def get_resource_semaphore(resource_id, max_concurrent=5):
    if resource_id not in resource_semaphores:
        resource_semaphores[resource_id] = threading.Semaphore(max_concurrent)
    return resource_semaphores[resource_id]

def update_with_concurrency_control(product_id, quantity):
    semaphore = get_resource_semaphore(product_id)
    
    with semaphore:
        product = Product.query.with_for_update().filter_by(id=product_id).first()
        product.stock -= quantity
        db.session.commit()
```

## 6. 应用层解决方案

### 使用队列串行化冲突操作
```python
from queue import Queue
import threading

# 为每个资源创建一个处理队列
resource_queues = {}
queue_workers = {}

def get_resource_queue(resource_id):
    if resource_id not in resource_queues:
        resource_queues[resource_id] = Queue()
        # 为每个队列启动一个工作线程
        worker = threading.Thread(target=process_resource_queue, args=(resource_id,))
        worker.daemon = True
        worker.start()
        queue_workers[resource_id] = worker
    return resource_queues[resource_id]

def process_resource_queue(resource_id):
    queue = resource_queues[resource_id]
    while True:
        task = queue.get()
        try:
            task()
        except Exception as e:
            print(f"Error processing task for resource {resource_id}: {e}")
        finally:
            queue.task_done()

def enqueue_product_update(product_id, quantity, callback):
    queue = get_resource_queue(product_id)
    queue.put(lambda: update_product_and_callback(product_id, quantity, callback))

def update_product_and_callback(product_id, quantity, callback):
    try:
        product = Product.query.with_for_update().filter_by(id=product_id).first()
        product.stock -= quantity
        db.session.commit()
        callback(success=True)
    except Exception as e:
        db.session.rollback()
        callback(success=False, error=str(e))
```

## 7. 监控和日志

### 添加死锁监控和日志
```python
import logging
from sqlalchemy import event
from sqlalchemy.engine import Engine

logger = logging.getLogger(__name__)

@event.listens_for(Engine, "handle_error")
def handle_exception(context):
    # 检查是否是死锁错误
    if 'deadlock' in str(context.original_exception).lower() or '1213' in str(context.original_exception):
        logger.warning(
            "Deadlock detected",
            extra={
                'sql': context.statement,
                'params': context.parameters,
                'original_exception': str(context.original_exception)
            }
        )
    # 继续传播异常
    context.is_disconnect = True
```

## 总结

在 Flask 应用中防止 MySQL 死锁需要综合运用多种策略：

1. **事务管理**：保持事务简短，按固定顺序访问资源
2. **锁优化**：选择合适的隔离级别，使用行级锁
3. **重试机制**：实现死锁检测和自动重试
4. **ORM优化**：使用乐观锁，批量操作
5. **并发控制**：配置合适的连接池，使用信号量控制并发
6. **应用层解决方案**：使用队列串行化冲突操作
7. **监控日志**：记录死锁事件以便分析和优化

根据应用的具体情况，选择适合的策略组合。在高并发场景下，通常需要结合多种策略才能有效减少死锁的发生。

posted @ 2025-08-31 23:43 染指未来阅读(19) 评论(0) 收藏举报

刷新页面返回顶部

染指未来

MySQL死锁问题排查方式

MySQL死锁问题排查方式

什么是MySQL死锁

死锁示例

示例一 product和order 库存和订单 相互修改同一张表数据

示例二 Insert 操作时违反唯一性约束

如何排查MySQL死锁

死锁日志查看

查看错误日志

show engine innodb status 日志分析

Web层级，ORM如何防止MYSQL死锁

DeepSeek详细输出

公告

示例一 product和order 库存和订单相互修改同一张表数据

示例二 Insert 操作时`违反唯一性约束`

`show engine innodb status 日志分析`