logo标签列表

如何正确使用事务和锁来确保数据库完整性？

mysqldatabaseconcurrencytransactionslocking
11

我正在开发一个在线预订系统。简单来说，用户可以预订多个项目，每个项目只能被预订一次。项目首先添加到购物车。

应用程序使用 MySql / InnoDB 数据库。根据 MySql 文档， 默认隔离级别为 可重复读取（Repeatable reads）。

这是目前我想出的结帐过程：

  1. 开始事务
  2. 选择在购物车中的项目（使用带有 for update 锁定的方式）
    在此步骤中，从 cart-item 和 items 表中获取记录。
  3. 检查是否有其他人已经预订了这些项目
    基本上是检查 quantity > 0。在实际应用中，它更加复杂，因此我将其作为单独的步骤放在这里。
  4. 更新项目，将 quantity = 0
    同时执行其他必要的数据库操作。
  5. 进行支付（通过外部 API 如 PayPal 或 Stripe）
    由于支付细节可以在结帐之前收集，因此不需要用户交互。
  6. 如果一切顺利，则提交事务或回滚，否则
  7. 继续进行非关键逻辑
    在成功时发送电子邮件等，在错误时重定向。

我不确定是否足够。我担心是否：

  1. 其他在同时预订相同项目的用户将正确处理。他的事务 T2 会等待直到 T1 完成吗？
  2. 使用 PayPal 或 Stripe 进行付款可能需要一些时间。这在性能方面是否会成为问题？
  3. 项目的可用性是否始终正确显示（项目应该在结帐成功之前一直可用）。这些只读选择是否应使用 共享锁（shared lock）？
  • MySql是否可能自动回滚事务？是自动重试还是显示错误消息并让用户再试一次更好？
  • 我想如果我在"items"表上执行"SELECT ... FOR UPDATE"就足够了。这样，由双击引起的请求和其他用户的请求都将等待事务完成。他们会等待，因为他们也使用"FOR UPDATE"。同时，普通的"SELECT"只会看到事务之前的数据库快照，没有延迟，对吗？
  • 如果我在"SELECT ... FOR UPDATE"中使用"JOIN"，那么两个表中的记录会被锁定吗？
  • 我有点困惑于Willem Renzema答案中关于"SELECT ... FOR UPDATE on non-existent rows"部分。它何时变得重要？你能提供任何例子吗？

    • 以下是我阅读过的一些资源：处理数据库中的并发更新、MySQL：事务 vs 锁定表、数据库事务是否可以防止竞争条件？、隔离（数据库系统）、InnoDB锁定和事务模型、数据库锁定和丢失更新现象初学者指南。

    重新编写我的原始问题，使其更加通用。
    添加了后续问题。

    重要问题：当你使用SELECT ... FOR UPDATE语句时，你是选择已经存在的行，还是选择你计划插入的行？ - Willem Renzema
    @WillemRenzema 我通过 cart-item 透视表仅选择已存在的行。 - Paul
    2个回答
    5
    1. 开始事务
    2. 选择购物车中的商品（带有更新锁）

    到目前为止，这至少可以防止用户在多个会话中进行结算（多次尝试结算相同的卡 - 处理双击很好）。

    1. 检查其他用户是否已经预订了物品

    如何检查？使用标准的 SELECT 还是 SELECT ... FOR UPDATE ？根据步骤5，我猜测您正在检查项目上的保留列或类似内容。

    问题在于，步骤2中的 SELECT ... FOR UPDATE 不会将 FOR UPDATE 锁应用于其他所有内容。它仅适用于所选内容：即 cart-item 表。根据名称，这将是每个购物车/用户的不同记录。这意味着其他事务将无法被阻止。

    1. 付款
    2. 更新项目并将其标记为已预订
    3. 如果一切正常，则提交事务，否则回滚

    根据您提供的信息，按照上述方式，如果在第3步没有使用 SELECT ... FOR UPDATE，则可能会出现多个人购买同一件物品的情况。

    建议的解决方案

    1. 开始事务
    2. SELECT ... FOR UPDATE cart-item 表。

    这将阻止双击运行。在此处选择的内容应该是某种“已订购的购物车”列。如果这样做，第二个事务将在此处暂停并等待第一个完成，然后读取第一个保存到数据库中的结果。

    如果 cart-item 表显示已经下单，请确保在此处结束结帐流程。

    1. SELECT ... FOR UPDATE 记录项目是否已被预订的表。

    这将阻止其他购物车/用户能够读取这些物品。

    根据结果，如果物品未被预订，则继续：

    1. UPDATE ... 步骤3中的表，将项目标记为已预订。进行任何其他所需的 INSERT 和 UPDATE。

    2. 付款。如果支付服务表示付款失败，请发起回滚。

    3. 记录付款成功。

    4. 提交事务

    确保在第5步和第7步之间不要做可能失败的事情（如发送电子邮件），否则在事务回滚时他们可能会进行付款而没有记录。

    第3步是关于确保两个（或更多）人不尝试订购同一物品的重要步骤。如果有两个人尝试，第二个人将会出现网页“挂起”，因为它正在处理第一个人的订单。当第一个人完成时，第二个人将读取“保留”列，您可以向用户返回消息，告诉他们已经有人购买了该商品。

    是否在交易中付款

    这是主观的。通常情况下，您希望尽快关闭交易，以避免多个人同时被锁定无法与数据库交互。

    但是，在这种情况下，您实际上需要让他们等待。这只是时间长短的问题。

    如果您选择在付款之前提交交易，则需要记录您的进度到某个中间表中，运行付款，然后记录结果。请注意，如果付款失败，您将不得不手动撤销已更新的项目预留记录。

    在不存在的行上使用SELECT ... FOR UPDATE

    只是提个醒，如果您的表设计涉及插入需要早期SELECT ... FOR UPDATE的行：如果不存在行，那个事务不会导致其他事务等待，如果他们也SELECT ... FOR UPDATE同样不存在的行。

    因此，请确保始终通过对您知道存在的行进行SELECT ... FOR UPDATE来串行化您的请求。然后您可以SELECT ... FOR UPDATE关于可能存在或不存在的行。（不要尝试只在可能存在或不存在的行上执行SELECT，因为您将读取事务开始时该行的状态，而不是运行SELECT时的状态。因此，在不存在的行上使用SELECT ... FOR UPDATE仍然是为了获取最新的信息，只是请注意它不会导致其他事务等待。）

    谢谢您提供如此详细的答案！ 您指出了一个很好的观点，即我应该避免在付款和提交之间可能失败的任何代码。因此，我改变了步骤4和5的顺序。 我认为我会将付款留在事务内部，否则可能会发生这样的情况，即User 2的结账被拒绝，但过一段时间后，“已取走”的物品重新出现，因为User 1从未完成付款。我更新了我的帖子以澄清这些步骤，并添加了一些后续问题，请看一下！ - Paul
    如果在第5步“进行付款”的成功后，您的进程终止，那么系统整体状态将不一致。您可以使用2pc或其他分布式事务机制来解决这个问题。 - fionbio
    2

    1. 如果有其他用户在同一时间尝试预定相同的物品，系统会正确处理。他的交易T2会等待T1完成吗？

    是的。当一个活动事务在记录上保持FOR UPDATE锁时，使用任何锁（SELECT ... FOR UPDATE、SELECT ... LOCK IN SHARE MODE、UPDATE、DELETE）的其他事务中的语句将被暂停，直到活动事务提交或超过“锁等待超时”。

    2. 使用PayPal或Stripe付款可能需要一些时间。这在性能方面会成为问题吗？

    不会成为问题，因为这正是必要的。结帐事务应该按顺序执行，即后面的结帐不应在前面的完成之前开始。

    3. 商品的可用性将始终正确显示（商品应在结帐成功之前可用）。这些只读选择应该使用共享锁吗？

    可重复读隔离级别确保了事务所做的更改在该事务提交之前不可见。因此，商品的可用性将正确显示。在实际支付之前，不会显示任何不可用的内容。不需要锁。

    SELECT ... LOCK IN SHARE MODE会导致结帐事务等待直到它完成。这可能会减慢结帐速度，而没有任何回报。

    4. MySql是否可能自动回滚事务？通常自动重试还是显示错误消息，让用户再次尝试更好？

    是可能的。当超过“锁等待超时”或发生死锁时，事务可能会被回滚。在这种情况下，自动重试是一个好主意。
    默认情况下，暂停的语句在50秒后失败。

    5. 我想如果在items表上执行SELECT ... FOR UPDATE就足够了。这样由双击引起的请求和其他用户都将等待事务完成。他们会等待，因为他们也使用了FOR UPDATE。同时，简单的SELECT只会看到事务之前的数据库快照，没有延迟，对吗？

    是的，在items表上执行SELECT ... FOR UPDATE应该足够。
    是的，这些选择会等待，因为FOR UPDATE是独占锁。
    是的，简单的SELECT只会立即抓取事务开始之前的值。

    6. 如果在SELECT ... FOR UPDATE中使用JOIN，两个表中的记录是否都会被锁定？

    是的，SELECT ... FOR UPDATE，SELECT ... LOCK IN SHARE MODE，UPDATE，DELETE都会锁定所有读取的记录，所以无论我们使用什么JOIN，它都会被包含在内。请参见MySql文档。

    有趣的是（至少对我来说），在处理SQL语句时扫描的所有内容都被锁定，无论是否选择它们。例如，WHERE id < 10也会锁定具有id = 10的记录！

    如果您没有适合您的语句的索引，并且MySQL必须扫描整个表来处理该语句，则表的每一行都会被锁定，这反过来会阻止其他用户向表中插入数据。重要的是要创建良好的索引，以使您的查询不会不必要地扫描许多行。

    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接