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

2022-01-06 00:00:00 database concurrency transactions locking mysql

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

App 使用 MySql/InnoDB 数据库.根据 MySql 文档，默认隔离级别是 Repeatable read.

这是我到目前为止想出的结帐程序:

<块引用>

  1. 开始交易
  2. 选择购物车中的商品(带有更新锁)
    在此步骤中获取 cart-item 和 items 表中的记录.
  3. 检查物品是否未被其他人预订
    基本上检查 quantity >0.实际应用中比较复杂，所以单独放在这里.
  4. 更新项目，设置quantity = 0
    还可以执行其他必要的数据库操作.
  5. 付款(通过外部 API，如 PayPal 或 Stripe)
    无需用户交互，因为可以在结帐前收集付款详细信息.
  6. 如果一切顺利提交事务或回滚否则
  7. 继续非必要的逻辑
    成功时发送电子邮件等，错误时重定向.

我不确定这是否足够.我很担心:

  1. 尝试同时预订相同商品的其他用户将得到正确处理.他的交易T2会等到T1完成吗?
  2. 使用 PayPal 或 Stripe 付款可能需要一些时间.这不会成为性能方面的问题吗?
  3. 物品的可用性将始终正确显示(在结帐成功之前，物品应该可用).这些只读选择是否应该使用共享锁?
  4. MySql 是否有可能自己回滚事务?通常是自动重试还是显示错误消息让用户重试更好?
  5. 我想如果我在 items 表上执行 SELECT ... FOR UPDATE 就足够了.这样，双击引起的请求和其他用户都必须等到事务完成.他们会等待，因为他们也使用 FOR UPDATE.同时，vanilla SELECT 只会在事务之前看到 db 的快照，但没有延迟，对吧?
  6. 如果我在SELECT ... FOR UPDATE中使用JOIN，两个表中的记录会被锁定吗?
  7. 我对 Willem Renzema 回答的 SELECT ... FOR UPDATE 对不存在的行 部分有些困惑.什么时候会变得重要?你能举个例子吗?

以下是我读过的一些资源:如何处理数据库并发更新?、MySQL:事务与锁定表、数据库事务是否可以防止竞争条件?、隔离(数据库系统)、InnoDB 锁定和事务模型，数据库锁定和丢失更新现象的初学者指南.

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

解决方案

1.其他尝试同时预订相同项目的用户将被正确处理.他的交易T2会等到T1完成吗?

是的.当活动事务在记录上保持 FOR UPDATE 锁时，其他事务中使用任何锁的语句 (SELECT ... FOR UPDATE, SELECT ... LOCK INSHARE MODE, UPDATE, DELETE) 将被暂停，直到活动事务提交或超过锁定等待超时".

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

这不会有问题，因为这正是必要的.结帐交易应按顺序执行，即.后一个结帐不应在前一个完成之前开始.

3.物品的可用性将始终正确显示(在结帐成功之前，物品应该可用).这些只读选择是否应该使用共享锁?

Repeatable Reading 隔离级别确保事务所做的更改在提交该事务之前不可见.因此，项目可用性将正确显示.在实际支付之前，不会显示不可用的任何内容.不需要锁.

SELECT ... LOCK IN SHARE MODE 会导致结账交易等待完成.这可能会减慢结账速度，而不会带来任何回报.

4.MySql 有可能自己回滚事务吗?通常是自动重试还是显示错误消息让用户重试更好?

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

5.我想如果我在 items 表上执行 SELECT ... FOR UPDATE 就足够了.这样，双击引起的请求和其他用户都必须等到事务完成.他们会等待，因为他们也使用 FOR UPDATE.同时，vanilla SELECT 只会在事务之前看到 db 的快照，但没有延迟，对吧?

是的，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 必须扫描整个表来处理该语句，则该表的每一行都会被锁定，从而阻止其他用户对该表的所有插入.创建好的索引很重要，这样您的查询就不会不必要地扫描很多行.

I develop an online reservation system. To simplify let's say that users can book multiple items and each item can be booked only once. Items are first added to the shopping cart.

App uses MySql / InnoDB database. According to MySql documentation, default isolation level is Repeatable reads.

Here is the checkout procedure I've came up with so far:

  1. Begin transaction
  2. Select items in the shopping cart (with for update lock)
    Records from cart-item and items tables are fetched at this step.
  3. Check if items haven't been booked by anybody else
    Basically check if quantity > 0. It's more complicated in the real application, thus I put it here as a separate step.
  4. Update items, set quantity = 0
    Also perform other essential database manipulations.
  5. Make payment (via external api like PayPal or Stripe)
    No user interaction is necessary as payment details can be collected before checkout.
  6. If everything went fine commit transaction or rollback otherwise
  7. Continue with non-essential logic
    Send e-mail etc in case of success, redirect for error.

I am unsure if that is sufficient. I'm worried whether:

  1. Other user that tries to book same item at the same time will be handled correcly. Will his transaction T2 wait until T1 is done?
  2. Payment using PayPal or Stripe may take some time. Wouldn't this become a problem in terms of performance?
  3. Items availability will be shown correctly all the time (items should be available until checkout succeeds). Should these read-only selects use shared lock?
  4. Is it possible that MySql rollbacks transaction by itself? Is it generally better to retry automatically or display an error message and let user try again?
  5. I guess its enough if I do SELECT ... FOR UPDATE on items table. This way both request caused by double click and other user will have to wait till transaction finishes. They'll wait because they also use FOR UPDATE. Meanwhile vanilla SELECT will just see a snapshot of db before the transaction, with no delay though, right?
  6. If I use JOIN in SELECT ... FOR UPDATE, will records in both tables be locked?
  7. I'm a bit confused about SELECT ... FOR UPDATE on non-existent rows section of Willem Renzema answer. When may it become important? Could you provide any example?

Here are some resources I've read: How to deal with concurrent updates in databases?, MySQL: Transactions vs Locking Tables, Do database transactions prevent race conditions?, Isolation (database systems), InnoDB Locking and Transaction Model, A beginner’s guide to database locking and the lost update phenomena.

Rewrote my original question to make it more general.
Added follow-up questions.

解决方案

1. Other user that tries to book same item at the same time will be handled correcly. Will his transaction T2 wait until T1 is done?

Yes. While active transaction keeps FOR UPDATE lock on a record, statements in other transactions that use any lock (SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, DELETE) will be suspended untill either active transaction commits or "Lock wait timeout" is exceeded.

2. Payment using PayPal or Stripe may take some time. Wouldn't this become a problem in terms of performance?

This will not be a problem, as this is exactly what is necessary. Checkout transactions should be executed sequentially, ie. latter checkout should not start before former finish.

3. Items availability will be shown correctly all the time (items should be available until checkout succeeds). Should these read-only selects use shared lock?

Repeatable reads isolation level ensures that changes made by a transaction are not visible until that transaction is commited. Therefore items availability will be displayed correctly. Nothing will be shown unavailable before it is actually paid for. No locks are necessary.

SELECT ... LOCK IN SHARE MODE would cause checkout transaction to wait until it is finished. This could slow down checkouts without giving any payoff.

4. Is it possible that MySql rollbacks transaction by itself? Is it generally better to retry automatically or display an error message and let user try again?

It is possible. Transaction may be rolled back when "Lock wait timeout" is exceeded or when deadlock happens. In that case it would be a good idea to retry it automatically.
By default suspended statements fail after 50s.

5. I guess its enough if I do SELECT ... FOR UPDATE on items table. This way both request caused by double click and other user will have to wait till transaction finishes. They'll wait because they also use FOR UPDATE. Meanwhile vanilla SELECT will just see a snapshot of db before the transaction, with no delay though, right?

Yes, SELECT ... FOR UPDATE on items table should be enough.
Yes, these selects wait, because FOR UPDATE is an exclusive lock.
Yes, simple SELECT will just grab value as it was before transaction started, this will happen immediately.

6. If I use JOIN in SELECT ... FOR UPDATE, will records in both tables be locked?

Yes, SELECT ... FOR UPDATE, SELECT ... LOCK IN SHARE MODE, UPDATE, DELETE lock all read records, so whatever we JOIN is included. See MySql Docs.

What's interesting (at least for me) everything that is scanned in the processing of the SQL statement gets locked, no matter wheter it is selected or not. For example WHERE id < 10 would lock also the record with 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.

相关文章