MySQL Race Conditions

MySQL Race Conditions

少女净妖师 发布于 2021-11-30 字数 597 浏览 970 回复 4 原文

Does this cause a race condition with MySQL (InnoDB):

  1. Start Transaction.

  2. Try to get record.

  3. If record doesn't exist, return.

  4. If record exists, delete it and add a log entry saying that is was deleted.

  5. End Transaction (commit/rollback).

Is it possible for another process to start just before the delete step in 2b, detect the presence of the record and then have both processes enter item delete entries into the log?

Are there any precautions that I need to take?

Thanks.

如果你对这篇文章有疑问,欢迎到本站 社区 发帖提问或使用手Q扫描下方二维码加群参与讨论,获取更多帮助。

扫码加入群聊

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

梦归所梦 2022-06-07 4 楼

Yes, it's possible for another transaction to check the table after you've read it.

Worse yet, because of how transactions work, even after you delete the row, any new transactions that start will see the row because you haven't yet committed the delete.

SELECT ... FOR UPDATE is one way to prevent it.

LOCK TABLE tablename is another.

Unfortunately, since you're using an ORM, I couldn't say whether it has the ability to do either of these.

千笙结 2022-06-07 3 楼

Start Transaction.

Delete record /* using the very same criteria you use for 'try to get record' */

if response indicates record was indeed deleted, add a log entry.

End Transaction (commit/rollback).

No more race condition.

执手闯天涯 2022-06-07 2 楼

Journeyman Programmer, I believe, has the correct solution. Since you've indicated you are using a broken ORM tool (one that will not allow you to query for update) I'd suggest that you move your INSERT into the log table into a trigger on the delete operation so you will avoid the duplicate entry.

[旋木] 2022-06-07 1 楼

Use 'select for update' at step 2. Only one process will be able to get a lock on the row thus avoiding the scenario you described.