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:
- Begin transaction
- Select items in the shopping cart (with
for update
lock)
Records fromcart-item
anditems
tables are fetched at this step.- Check if items haven't been booked by anybody else
Basically check ifquantity > 0
. It's more complicated in the real application, thus I put it here as a separate step.- Update items, set
quantity = 0
Also perform other essential database manipulations.- Make payment (via external api like PayPal or Stripe)
No user interaction is necessary as payment details can be collected before checkout.- If everything went fine commit transaction or rollback otherwise
- 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:
- Other user that tries to book same item at the same time will be handled correcly. Will his transaction
T2
wait untilT1
is done? - Payment using PayPal or Stripe may take some time. Wouldn't this become a problem in terms of performance?
- Items availability will be shown correctly all the time (items should be available until checkout succeeds). Should these read-only selects use
shared lock
? - 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?
- I guess its enough if I do
SELECT ... FOR UPDATE
onitems
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 useFOR UPDATE
. Meanwhile vanillaSELECT
will just see a snapshot of db before the transaction, with no delay though, right? - If I use
JOIN
inSELECT ... FOR UPDATE
, will records in both tables be locked? - 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.