MySQL中插入后触发器的竞争条件

Question

我有一个mysql触发器，我注意到在几天前将触发器部署到生产站点后，我的数据库中出现了一些“未同步”的数据，并对此表示担忧。

为了在这里发布目的，我已经简化了我的代码。有三个表:

得分表: ID、UserID、Score、GameID（用户每次玩游戏时输入分数，他们可以玩同一款游戏多次）

得分摘要表: ID、UserID、GameID、SummaryValue（此表为每个用户和每个游戏保留了一个运行分数)

游戏摘要表: ID、GameID、SummaryValue（此表为每个游戏保留了一个总分数）

当用户将游戏得分输入得分表中时，触发器会更新得分摘要表中用户的运行总得分（SummaryValue），并更新给定GameID的game_summary表。

CREATE TRIGGER scores_insert_trigger AFTER INSERT ON scores
FOR EACH ROW BEGIN

    UPDATE scores_summary
    SET SummaryValue=SummaryValue + NEW.Score 
    WHERE UserID=NEW.UserID
    SELECT ROW_COUNT() INTO rowCount;
    IF (rowCount=0) THEN
        INSERT INTO scores_summary
        (UserID, GameID, SummaryValue)
        VALUES
        (NEW.UserID, NEW.GameID, NEW.Score);
    END IF;

    UPDATE game_summary
    SET SummaryValue=SummaryValue + NEW.Score 
    WHERE GameID=NEW.GameID
    SELECT ROW_COUNT() INTO rowCount;
    IF (rowCount=0) THEN
        INSERT INTO game_summary
        (GameID, SummaryValue)
        VALUES
        (NEW.GameID, NEW.Score);
    END IF;
END;

我的关注点在于，如果一些用户同时输入分数，触发器会遇到竞争条件 - 特别是对于当特定游戏没有分数时的game_summary更新 - 如果两个用户同时尝试这样做，他们都将得到rowCount = 0，然后都会进行插入？

我的担心是否合理？如果是，我能做些什么呢？

提前感谢大家。

- MirzaP

Answer 1

只需在game_summary.GameID上创建UNIQUE索引或将其设置为主键。

UNIQUE索引将防止第二个用户添加重复行。整个事务将失败并回滚，因此您的应用程序需要处理此异常，例如在失败后再次尝试，直到放弃。这种解决方案的缺点是，在非常繁忙的环境中，这将导致许多冲突和许多回滚，但您可以确保不会有任何重复行。

您还可以使用GET_LOCK(str, timeout)函数来获取给定GameID的独占锁，更新或插入值，最后RELEASE_LOCK(str)，以便下一个用户也可以更新分数。在某些情况下，这可能更有效率，但您需要进行检查。

- Kamil Dziedzic

---

我的问题是，我有其他列（例如categoryId、seasonId等）在里面，我已经排除了这些列以简化我的问题，因此在每个列上创建唯一索引将不起作用。使用Get_lock和Release_lock，如果两个或多个进程尝试对同一值进行操作会发生什么？其中一个锁定它，在其他进程等待时执行插入操作吗？如果第二个进程尝试访问数据，看到它被锁定并移动到另外的地方，是否会丢失任何数据？还是要等待-我必须失败并重试？ - MirzaP

您可以在多个列上添加唯一键，因此如果对于相同的gameId、categoryId和seasonId应该只有一行（例如，gameId=1、categoryId=2和seasonId=3可能恰好有一行），则在所有三个列上添加一个唯一键：ALTER TABLE game_summary ADD UNIQUE (gameId, categoryId, seasonId); 您真的应该考虑添加这样的键，因为即使在应用程序逻辑中出现错误，它也有助于保持数据一致性。 - Kamil Dziedzic

有锁定机制，您仍需要弄清楚在game_summary中使一行成为唯一的原因（可能只有一个gameID=3在game_summary中？或者可能只有一个具有gameId=1、categoryId=2和seasonId=3的行？）这样您就可以创建并传递正确的字符串到“GET_LOCK”。关于“GET_LOCK(str, timeout)” - 如果无法获取给定字符串“str”的锁定，则等待给定的“timeout”后放弃。只需阅读手册即可。 - Kamil Dziedzic

你还没有表明其他进程是否需要轮询锁定的资源直到其被释放。我相信这个问题有更加优雅的解决方案。 - MirzaP

GET_LOCK会在尝试获取锁timeout秒后返回false。所以如果其他进程在timeout之前释放锁，则会再次有机会获取锁。但是首先我仍然建议在一个或多个表列上使用一个唯一的键。 - Kamil Dziedzic

Answer 2

我想为我的特定情况提供一个答案。 我的更新语句是原子更新，这意味着mysql引擎处理任何潜在的竞争条件。 为了避免插入时的竞争条件，我选择了ON DUPLICATE KEY UPDATE，这将再次是原子操作，并且会避免两个或更多线程导致创建多行。 谢谢您的帮助。

- MirzaP

---

如果你最终使用ON DUPLICATE KEY UPDATE，那么你已经添加了唯一键(或者是唯一主键)。如果指定了ON DUPLICATE KEY UPDATE，并且一个被插入的行会导致在唯一索引或主键上出现重复值，MySQL就会更新旧行。更多信息请参考http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html。 - Kamil Dziedzic