如何正确使用事务和锁来确保数据库完整性? [英] How to properly use transactions and locks to ensure database integrity?

问题描述

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

应用程序使用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. 如果我在items表上执行SELECT ... FOR UPDATE，我猜就足够了.这样，由双击引起的请求和其他用户都必须等到交易完成.他们将等待，因为他们也使用FOR UPDATE.同时，香草SELECT只会在事务之前看到db的快照，但是没有延迟，对吧?
6. 如果我在SELECT ... FOR UPDATE中使用JOIN，两个表中的记录都将被锁定吗?
7. 我对Willem Renzema答案中的 SELECT ... FOR UPDATE for不存在的行部分有些困惑.什么时候可能变得重要?您能举个例子吗?

以下是我读过的一些资源: 如何处理数据库中的并发更新?，数据库事务是否会阻止竞争状况?， 隔离(数据库系统)，解决方案

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.如果我在items表上执行SELECT ... FOR UPDATE，我猜就足够了.这样，由双击引起的请求和其他用户都必须等到交易完成.他们将等待，因为他们也使用FOR UPDATE.同时，香草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.

这篇关于如何正确使用事务和锁来确保数据库完整性?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！