MySQL的for update skip locked

合集 - 数据库(6)

1.MySQL共享锁，排他锁2024-12-252.乐观锁，悲观锁2024-12-263.造成死锁的原因2024-12-274.MySQL的事务2024-12-315.MySQL数据库索引2024-12-31

6.MySQL的for update skip locked01-06

收起

最近有一个功能要是音频转文字以及翻译，该任务不仅耗时还消耗硬件，在硬件能够支持的情况下可以启动多台电脑一起处理任务加快速度，启动多个程序会出现处理同一个任务的问题，也就是并发的问题，趁机了解了一下mysql的FOR UPDATE SKIP LOCKED

在 MySQL 中，FOR UPDATE SKIP LOCKED 是一个强大的功能，通常用于解决并发环境中的任务分配问题，确保每个任务只被一个进程处理。它允许查询未被锁定的记录，并跳过那些已经被其他事务锁定的记录。

FOR UPDATE SKIP LOCKED 解释

• FOR UPDATE：锁定查询到的行，防止其他事务对这些行进行修改。
• SKIP LOCKED：跳过那些已经被其他事务锁定的行，返回未被锁定的记录。

这种方法非常适用于任务队列场景，可以避免任务被多个进程同时处理。

MySQL 8.0+ 示例

假设我们有一个任务表 tasks，其中包含任务的状态。

任务表结构

CREATE TABLE tasks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    status INT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

状态含义

• status = 1：任务待处理
• status = 2：任务正在处理中
• status = 3：任务已完成

查询并锁定未处理的任务

我们可以使用 FOR UPDATE SKIP LOCKED 查询并锁定待处理的任务。此查询会锁定一行记录，如果该记录已被其他事务锁定，则跳过该记录，返回未被锁定的记录。

查询并锁定任务

START TRANSACTION;
 
SELECT id, name
FROM tasks
WHERE status = 1
FOR UPDATE SKIP LOCKED
LIMIT 1;

这个查询将：

• 锁定一个 status = 1（待处理）的任务。
• 如果该任务已经被其他事务锁定，它将跳过这行记录，返回一个未被锁定的任务。
• 通过 LIMIT 1 限制每次查询一个任务。

更新任务状态

UPDATE tasks
SET status = 2, updated_at = NOW()
WHERE id = <TASK_ID>;
 
COMMIT;

执行完任务后，更新任务的 status 字段为 2，表示任务正在处理中，最后提交事务。

---

Python 使用示例

如果你使用 Python 和 SQLAlchemy 来处理任务，可以像下面这样实现：

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker
 
# 数据库连接
engine = create_engine("mysql+pymysql://user:password@localhost/database")
Session = sessionmaker(bind=engine)
 
def fetch_and_process_task():
    with Session() as session:
        # 查询并锁定任务
        task = session.execute(
            text("""
                SELECT id, name
                FROM tasks
                WHERE status = 1
                FOR UPDATE SKIP LOCKED
                LIMIT 1
            """)
        ).fetchone()
 
        if task:
            task_id = task.id
            # 更新任务状态
            session.execute(
                text("""
                    UPDATE tasks
                    SET status = 2, updated_at = NOW()
                    WHERE id = :task_id
                """),
                {"task_id": task_id}
            )
            session.commit()
 
            # 模拟任务处理
            print(f"Processing task: {task.name}")
        else:
            print("No pending tasks available.")

注意事项

1. 版本要求：FOR UPDATE SKIP LOCKED 是 MySQL 8.0 及以上版本的功能，旧版本的 MySQL 不支持此功能。
2. 锁定行：使用 FOR UPDATE 锁定的行会阻止其他事务修改该行，直到事务提交。
3. 跳过锁定：SKIP LOCKED 允许跳过已被其他事务锁定的行，确保并发的任务处理不会发生冲突。
4. 事务管理：确保在事务内执行 SELECT 和 UPDATE，以保持一致性。

总结

• FOR UPDATE SKIP LOCKED 是 MySQL 中用于并发任务处理的有力工具，可以避免任务重复执行。
• 适用于分布式任务队列和需要高并发的场景，确保任务不会被多个进程同时处理。