时间戳

首页 > 解决方案 > Mysql并以原子方式执行存储过程或以原子方式选择更新

mysql - Mysql并以原子方式执行存储过程或以原子方式选择更新

问题描述

在 Mysql 中，我有两个并发进程需要读取一些行并根据条件更新标志。

我必须编写一个带有事务的存储过程，但问题是有时这两个进程会更新相同的行。

我有一个表Status，我想读取标志Reserved为真的 15 行，然后更新那些将标志设置Reserved为False.

更新的行必须返回给客户端。

我的存储过程是：

CREATE DEFINER=`user`@`%` PROCEDURE `get_reserved`()
BEGIN
DECLARE tmpProfilePageId bigint;
DECLARE finished INTEGER DEFAULT 0;

DECLARE curProfilePage CURSOR FOR 
    SELECT ProfilePageId 
    FROM Status
    WHERE Reserved is false and ((timestampdiff(HOUR, UpdatedTime, NOW()) >= 23) or UpdatedTime is NULL)
    ORDER BY UpdatedTime ASC
    LIMIT 15;
DECLARE CONTINUE HANDLER 
    FOR NOT FOUND SET finished = 1;
    
DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK;
DECLARE EXIT HANDLER FOR SQLWARNING ROLLBACK;

START TRANSACTION;

DROP TEMPORARY TABLE IF EXISTS TmpAdsProfile;
CREATE TEMPORARY TABLE TmpAdsProfile(Id INT PRIMARY KEY AUTO_INCREMENT, ProfilePageId BIGINT);

OPEN curProfilePage;

getProfilePage: LOOP
    FETCH curProfilePage INTO tmpProfilePageId;
    IF finished = 1 THEN LEAVE getProfilePage;
    END IF;
    UPDATE StatusSET Reserved = true WHERE ProfilePageId = tmpProfilePageId;
    INSERT INTO TmpAdsProfile (ProfilePageId) VALUES (tmpProfilePageId);
END LOOP getProfilePage;

CLOSE curProfilePage;

SELECT ProfilePageId FROM TmpAdsProfile;

COMMIT;

END

无论如何，如果我执行两个调用此存储过程的并发进程，有时它们会更新相同的行。

如何以原子方式执行存储过程？

标签： mysqlsqltransactionsdatabase-concurrency

解决方案

稍微简化一下并使用FOR UPDATE. 这将锁定您要更改的行，直到您提交事务。您可以完全摆脱光标。像这样的东西，没有调试！

START TRANSACTION;

CREATE OR REPLACE TEMPORARY TABLE TmpAdsProfile AS
SELECT ProfilePageId 
  FROM Status
 WHERE Reserved IS false 
   AND ((timestampdiff(HOUR, UpdatedTime, NOW()) >= 23) OR UpdatedTime IS NULL)
 ORDER BY UpdatedTime ASC
 LIMIT 15 
   FOR UPDATE; 

 UPDATE Status SET Reserved = true 
  WHERE ProfilePageId IN (SELECT ProfilePageId FROM TmpAdsProfile);
 
COMMIT;

SELECT ProfilePageId FROM TmpAdsProfile;

该临时表将永远只有十五行。所以索引和 PK 以及所有这些都是不必要的。因此，您可以使用CREATE ... AS SELECT ...它一次性创建和填充表格。

并且，考虑重铸您的 UpdatedTime 过滤器，以便它可以使用索引。

AND (UpdatedTime <= NOW() - INTERVAL 23 HOUR OR UpdatedTime IS NULL)

SELECT 查询的适当索引是

CREATE INDEX status_update ON Status (Reserved, UpdatedTime, ProfilePageId);

您的 SELECT 操作越快，您的事务花费的时间就越少，因此您的整体性能就会越好。

推荐阅读