4

I'm using Slim in conjunction with Stripe's PHP Library to process payments in my application.

All is well, however up until recently, I have discovered an alarming fault in my system that I believe may be a much larger issue than I probably think. In my logic, at three separate checkpoints of the payment process I inspect the inventory in my (MySQL) database to ensure a user isn't purchasing more products than is available.

However, when multiple users make a request within approximately 500ms of each other, the payment system seems to process these requests all at once, resulting in a slew of issues ranging from incorrect and unbalanced inventory, to false user confirmation of successful payments.

Through some due diligence, I have narrowed a solution down to two options (although I may be selling myself short):

1) Use a Queueing System that, from my understanding, will queue these request and process them one at a time, creating a sort've first-come, first-serve basis.

2) Attach some middleware on to each request that will act as a queue and attempt to process each request synchronously (although this may be similar to what i already have in place)

Now with that said, any suggestions/opinions on these options? and obviously feel free to totally scrap my idealogy and point me in a different direction.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141
Jared Garcia
  • 751
  • 1
  • 8
  • 17
  • I definitely think a queue-ing system with some kind of real-time acknowledgement that the request was processed is a totally valid way to go about this. If I shared your concerns, I'd go with a queue-ing system combined with websockets that told my front-end that the back-end had handled the request from start to finish. This gives you lots of cool benefits too-- retries, responsiveness, async handling for things like bitcoin, etc. Stripe is synchronous too though, so you could always just do a regular clean-up after you've processed the payments to make sure your inventory is there. – korben Jan 02 '18 at 22:33
  • You should look into using transactions then. Also make sure that your database is properly indexed and possibly having (another) UNIQUE index. Which RDBMS are you using? Remember to @Niner me. – Funk Forty Niner Jan 03 '18 at 03:40
  • Using MySQL @Funk Forty Niner – Jared Garcia Jan 03 '18 at 04:09
  • @JaredGarcia ok thanks. So what do you think about using transactions? have you considered that or did I miss the target entirely? – Funk Forty Niner Jan 03 '18 at 04:11
  • @JaredGarcia So, is your database indeed properly indexed? This question sounds more (to me) like database-related rather than a Stripe-specific one. It doesn't seem as if anyone nailed the question, least not seeing an accepted answer. So in a sense, your question could be opinion-based and too broad, but for me it's unclear. Maybe I'm probably not grasping the question neither and am way outside the ballpark. – Funk Forty Niner Jan 03 '18 at 04:18
  • @FunkFortyNiner forgive me if I'm misinformed but when you speak of `transactions` are you referring to the actual technique/methodology of approaching this or an actual different model? – Jared Garcia Jan 03 '18 at 15:53
  • @JaredGarcia Have a look at https://dev.mysql.com/doc/refman/5.7/en/commit.html and www.mysqltutorial.org/mysql-transaction.aspx - that should give you a better insight as to what I was talking about. Plus, if your db isn't properly indexed and/or does not have some form of a UNIQUE index of sorts, then that would probably the cause of this. Even with milliseconds, there shouldn't be a conflict/failure. – Funk Forty Niner Jan 03 '18 at 15:55
  • my schema for my inventory table is pretty simple, it is as you would imagine - 1 PRIMARY index which is an auto-incremented ID, & the rest of the columns are general info regarding the product. Which column of my table should be Uniquely indexed? @FunkFortyNiner – Jared Garcia Jan 03 '18 at 15:59
  • so after doing some research on the implementation of transactions, i agree this may be the best and most inexpensive way of solving my issue, since its a technique already embedded in mysql and i won't have to go through a third party. However, to your point about having a UNIQUE index, why would that be needed? @FunkFortyNiner – Jared Garcia Jan 03 '18 at 16:56
  • @JaredGarcia well, having a UNIQUE won't hurt, that's for sure. If your db's being queried other than one of the PK's/AI's, then that could have a side effect of sorts. Transactions may be the solution for you here. – Funk Forty Niner Jan 04 '18 at 00:10
  • @JaredGarcia if you feel that the question deserves an answer (on my part) or should be marked as a possible duplicate of https://stackoverflow.com/questions/2364273/how-to-make-sure-there-is-no-race-condition-in-mysql-database-when-incrementing let me know. You could look at that Q&A also; this (also) appears to be a race condition. – Funk Forty Niner Jan 04 '18 at 00:22
  • if you leave an answer ill surely mark as the accepted @FunkFortyNiner – Jared Garcia Jan 04 '18 at 15:59
  • @JaredGarcia Done, as per requested. *Cheers* – Funk Forty Niner Jan 05 '18 at 00:14

5 Answers5

1

So if i understand main problem is that you afraid that someone buy product that already are sold(payment is processing at this time)...

I think that you should leave this queuing system idea - becouse this is not clue here. Clue is your shop logic.

I don't know how your shop works, but from logic point of view product should be locked(allocated) in the moment when client cliking send order, not after payment process. Becouse not each payment may be successfull (what if client want to retry unsuccessfull payment with stripe or other paying method ?) .

Michał G
  • 2,234
  • 19
  • 27
  • I see, well my shop logic is fairly simple. Like I said I check for availability @ three different checkpoints, 1) when the user selects the checkout button, an Ajax request is sent out to inspect availability, 2) after successful payment credentials are entered & 3) right before the requests hits stripe API. – Jared Garcia Jan 02 '18 at 21:28
  • I don’t lock in products until after a payment is successfully captured through stripe, and if it’s not, then user can retry again. – Jared Garcia Jan 02 '18 at 21:29
  • 1
    I agree with @Michal G - you have to flag the item as "reserved" and connect it with an information from the payment. Consider a timestamp "reserved until" and check against it in SQL. If a payment is never made, the lock times out by itself. Just make sure to flag as "sold" once the payment finishes. – Honk der Hase Jan 02 '18 at 21:39
  • @Lars Stegelitz I get that methodology, however wouldn’t that kindve fall into the same issue of havin multiple users lock in an item at once and potentially successfully checking out items simultaneously that may be unavailable – Jared Garcia Jan 02 '18 at 23:20
  • 1
    Not if the locking is implemented properly. Topic is "mutex", "semaphore" or "Critical Section", which are exclusively designed to handle these kind of situations (race conditions). With a common database you can implement something yourself (a table with a unique key for the section. create a record -> you have the lock. if creation fails -> somebody else holds the lock to that section. only act if you hace acquired the lock. freeing the lock -> delete the record). I've implemented something alike myself for a browser game I was working for, where we constantly had these issues. – Honk der Hase Jan 02 '18 at 23:39
  • Wow, that’s sounds like an intriguing implementation. Would you mind a private chat? I’m no newbie so I promise it won’t be a burden, just want to discuss the technique a little further and show some code @Lars Stegelitz – Jared Garcia Jan 03 '18 at 01:15
0

I've heard many horror stories around developers using queuing systems so I would definitely recommend against it. The first-come first-serve basis can often lead to errors that are hard to catch until after the code is in place - one developer I know in specific created a shop using a similar system only to find that many customers ended up with random items in their cart or getting to the end of the payment to figure out no items were in stock.

Instead I would use an "event-listener" using jQuery or Javascript or a PHP library to insure that during the checkout process if something in the database changed then the UI would indicate to the user the stock was different or unavailable.

Perniferous
  • 513
  • 6
  • 19
  • I see, well my shop logic is fairly simple. Like I said I check for availability @ three different checkpoints, 1) when the user selects the checkout button, an Ajax request is sent out to inspect availability, 2) after successful payment credentials are entered & 3) right before the requests hits stripe API. – Jared Garcia Jan 02 '18 at 21:30
  • That definitely works initially for your application but **scalability** could be a huge issue down the road. Even if it slows down your performance a tad it is worth having something always waiting for a database change. Because as you identified, stock could change at any time in between checkpoints. – Perniferous Jan 02 '18 at 21:33
0

I think your problem is independent from your payment provider. It's your shop logic. I would do the following:

  1. User clicks on product to add to cart
  2. Product gets on an "in checkout" list and the number available in your shop get decreased by one
  3. Then we have two possibilities:
    • User finishes the checkout with successful payment => you can keep the total amount of your products
    • User aborts the checkout => you have to increase the total amount of your products after a specific time (eg. 5 minutes)

If you try to buy a seat in the cinema you have to finish the checkout in 5 minutes (count down is shown to user) otherwise the reservation is lost and someone else can book the seats.

Sebastian
  • 31
  • 5
  • That’s a great method, however what if there are only 5 seats and two users are in the process of purchasing all 5 seats simultaneously, wouldn’t that still cause some type of friction? – Jared Garcia Jan 03 '18 at 01:17
  • sure it would. But without knowing what kind of product the shop is selling, I cannot find a way to solve it. You can of course maybe show the second user that there are some seats reserved which will be released in 5 minutes or you can shorten the reservation time. – Sebastian Jan 09 '18 at 13:00
0

I would like to suggest another way to do this, this is the way in which we handle payments and inventory on our store. the main problem arises during high volume sales and we handled Black Friday and Cyber Monday sales with our inventory management solution.

You have to ensure following steps for concurrent payment

  1. You should check available stock just before the user is about to enter payment process. you can do this by keeping this data in Redis instead of querying the database every time. whenever an order is placed it will update the stock entries in Redis to be in sync.
  2. when you are about to call payment gateway for payment check for available stock and process ahead if available otherwise prompt user.
  3. When a user completes payment deduct purchased quantity from inventory in database transaction and using the lock. if transaction not succeed either refund the payment or it's up to you want to make order for out of stock product or not.

Hope this helps you.

Rahul Sharma
  • 1,393
  • 10
  • 19
0

Using TRANSACTIONS would be beneficial for this, since it appears that you've been faced with what is called a race condition.

Pulled from https://www.w3resource.com/mysql/mysql-transaction.php and I quote:

A transaction is a logical unit of work that contains one or more SQL statements. Transactions are atomic units of work that can be committed or rolled back. When a transaction makes multiple changes to the database, either all the changes succeed when the transaction is committed, or all the changes are undone when the transaction is rolled back.

A transaction begins with the first executable SQL statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL (Data Definition Language (DDL) is used to manage table and index structure and CREATE, ALTER, RENAME, DROP and TRUNCATE statements are to name a few data definition elements) statement is issued.

LOCKING the table during a transaction will help prevent against a race condition.

Pulled from the same resource:

LOCK TABLES      
  tbl_name [[AS] alias] lock_type      
  [, tbl_name [[AS] alias] lock_type] ...    

lock_type:      
  READ [LOCAL]    
 | [LOW_PRIORITY] WRITE    

UNLOCK TABLES

More information and syntax can be found on the (official) MySQL website:

"About transactions", pulled from https://www.informit.com/articles/article.aspx?p=29312

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

Funk Forty Niner
  • 74,450
  • 15
  • 68
  • 141