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

Question

我开发了一个在线预订系统。为了简化起见，假设用户可以预订多个项目，而每个项目只能预订一次。物品首先添加到购物车中。

应用使用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？
4. MySql本身是否可以回滚事务？通常，自动重试或显示错误消息并让用户重试通常更好吗？
5. 我猜如果SELECT ... FOR UPDATE在items桌子上做就足够了。这样，双击引起的请求和其他用户都必须等到交易完成。他们将等待，因为他们也使用FOR UPDATE。同时，vanilla SELECT只会在事务之前看到db的快照，但是没有延迟，对吗？
6. 如果使用JOINin SELECT ... FOR UPDATE，两个表中的记录都将被锁定吗？
7. 我对Willem Renzema答案的 不存在行 部分 上的SELECT … FOR UPDATE 感到有些困惑。什么时候可能变得重要？你能提供任何例子吗？

这是我已阅读的一些资源：
如何处理数据库中的并发更新？，MySQL：事务与锁定表，数据库事务是否防止竞争情况？，
隔离（数据库系统），InnoDB锁定和事务模型，数据库锁定和丢失更新现象的初学者指南。

改写了我原来的问题，使其更笼统。
添加了后续问题。

Answer 1

1.试图同时预订同一项目的其他用户将得到正确处理。 他的交易会T2等到T1完成吗？

是。虽然活动事务保持FOR UPDATE在记录上的锁，在使用任何锁其他交易报表（SELECT ... FOR UPDATE，SELECT ... LOCK IN SHARE MODE，UPDATE，DELETE）将暂停，直到无论是主动事务提交或“锁定等待超时”超过。

2.使用PayPal或Stripe付款可能需要一些时间。 就性能而言，这不会成为问题吗？

这将不是问题，因为这正是必需的。结帐交易应按顺序执行，即 后一个结帐不应在前一个结帐之前开始。

3.项目可用性将始终正确显示（项目应该可用，直到结帐成功）。 这些只读选择是否应该使用shared lock？

Repeatable reads隔离级别可确保在提交事务之前不可见事务所做的更改。因此，物品可用性将正确显示。在实际付款之前，不会显示不可用的任何内容。无需锁。

SELECT ... LOCK IN SHARE MODE会导致结帐交易等待完成。这可能会使结帐速度变慢而没有任何回报。

4. MySql本身是否可以回滚事务？ 通常，自动重试或显示错误消息并让用户重试通常更好吗？

有可能的。当超过“锁定等待超时”或发生死锁时，事务可能会回滚。在这种情况下，最好自动重试。
默认情况下，挂起的语句在50秒后失败。

5.如果我SELECT ... FOR UPDATE在items桌子上做，我猜就足够了。这样，双击引起的请求和其他用户都必须等到交易完成。他们将等待，因为他们也使用FOR UPDATE。同时，vanilla SELECT只会在事务之前看到db的快照，但是没有延迟，对吗？

是的，SELECT ... FOR UPDATE在items桌子上就足够了。
是的，这些选择等待，因为FOR UPDATE是排他锁。
是的，简单的SELECT交易会像交易开始前那样获取价值，这将立即发生。

6.如果使用JOINin SELECT ... FOR UPDATE，两个表中的记录都将被锁定吗？

是的，SELECT ... FOR UPDATE，SELECT ... LOCK IN SHARE MODE，UPDATE，DELETE锁定所有读取记录，所以无论我们JOIN是包括在内。参见MySql
Docs。

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

If you have no indexes suitable for your statement and MySQL must scan the
entire table to process the statement, every row of the table becomes
locked, which in turn blocks all inserts by other users to the table. It is
important to create good indexes so that your queries do not unnecessarily
scan many rows.