Prevent Double bookings

[Jon]

Hello All,

Looking for a bit advice I have a site setup which sells spaces, all the spaces are stored in a page as a number field when booking is made the x number of spaces are removed. However on the odd occasion we have ended up with double booking when 2 people book at the exact same time. Wondering what approach anyone would take to deal with this?

Iam thinking something along the lines of locking the page until the booking has been complete with a time stamp the deal with abandoned payment.

 

[Jan Romero]

Store the bookings themselves, then subtract the amount of bookings from the available spaces on the fly. Don’t store a single constantly decremented/incremented number!

[Craig]

When processing a booking, you could include the current number of available spaces that the user was shown. Before finalising or saving the booking, you can check if the number sent through matches the current live value.

If the live value is the same, and you have available spaces for the requested quantity, you are fine to decrease the amount.

If the live value is lower, then someone else has grabbed them, so you can handle this situation as you please.

[Jon]

Thanks for your comments, The problem we have is we are using legacy WorldPay gateway ? so customers are taken away from the website for the payment. The payment is processed before returning to the site at which point the spaces are updated. Its during the payment window when double booking are occurring. 

[Jan Romero]

Ticket vendors often temporarily reserve the space as soon as it’s added to the cart. You could use that to count towards the occupied spaces and remove the reservation if the transaction isn’t finalized in time. For example you could store the time of the reservation and to calculate availabilty subtract completed bookings as well as reservations younger than 15 minutes or so. 

[LostKobrakai]

This is a classical distributed systems problem: Your options are idempotency, reserving limited resources early and implementing compensation actions (e.g. compensating for missed payments by freeing the resource again). At some point it might even be useful to involve humans instead of trying to cater for each error case in code.

The other part as @Jan Romero pointed out: Give whatever you sell identities. Instead of decrementing number of event tickets available create 100 tickets upfront and link them to orders. A ticket not linked is a ticket available. This is a more natural and easy to understand way of dealing with the domain. In the real world you also first print X tickets before they can be sold in retail. Same should apply to your spaces. Take that from someone having build a system similar to like you intended in a not so critical space (a handful of tickets more are not critical) and while not having completely regretted it I could soon see the problems of it.

[BitPoet]

I'm very much with @Jan Romero there. Create reservations and expire them after a while (making sure that any shopping cart entries client side are invalidated as well in that case). I'm not so sure if anything needs to be pre-created, as you'll have a race condition anyway between availability check and actual reservation. There's realy only one solution to that race condition, and that's using database transactions. Thankfully, you can just wrap your relevant PW code in $database->beginTransaction() and $database->commit() as explained here.

[Jon]

Thanks for the input. I am now working on changing the system as per @Jan Romero I am reserving the spaces as soon as possible now by making a pending reservation and calculating that against existing bookings to work out availability. I am running a check before creating the pending reservation to make sure the spaces are still available. I can see the benefit of generating the space in advanced then removing them once booked but for this project is probably easier using the existing booking and calculating it that way.  

@BitPoet Ive had a look at $database transitions and tried the example. I created the page foo but not bar then ran the example which generated foo-1. I was expecting this to roll back rather add the pages. Ill need to have a read up a bit more.

With my pending payment what would be the best way to expire this after a set amount of time? I have timed the Worldpay payment page timeout and its over 20mins so I will need to hold the reservation for a least that. 

[LostKobrakai]

7 hours ago, Jon said:

I have timed the Worldpay payment page timeout and its over 20mins so I will need to hold the reservation for a least that. 

I've tried out mollie a few weeks back and they have their internal timeouts. Unless they're to long for your usecase I'd consider mirroring those of your payment provider.

[BitPoet]

6 hours ago, Jon said:

Ive had a look at $database transitions and tried the example. I created the page foo but not bar then ran the example which generated foo-1. I was expecting this to roll back rather add the pages. Ill need to have a read up a bit more.

I did a bit of digging in the code, and it seems the example in the blog post is outdated. PW nowadays always increments page names for new pages if a page with the same name and parent already exists (that appears to have changed with release 3.0.127). ?