CODEWITHSUNDEEP
mysqltransactionslockinginnodbdatabase-engine
Iva
Iva

Reputation: 55

Using table locking to prevent multiple users from updating at a given time

I am building a simple shopping cart. Currently, to ensure that a customer can never purchase a product that is out of stock, when processing the order I have a loop for each product in their cart:

-- Begin a transaction -- Loop through each product in the cart and

  1. Select the stock count from the products table

If it is in stock:

Otherwise, I call a rollback and return an error

-- (If there isn't a call for rollback, everything ends off with a commit --

However, if at any given time, the stock count for a product is updated AFTER it has checked for that particular product, there may be inconsistencies.

Question: would it be a good idea to lock the table from writes whenever I am processing an order? So that when the 'loop' above occurs, I can be assured that no one else is able to alter the product count and it will always be accurate.

The idea is that the product count/availability will always be consistent, and there will never be an instance where the stock count goes to -1 (which would be unfulfillable).

However, I have seen so many posts on locks being inefficient/having bad effects. If so, what is the best way to accomplish this?

I have seen alternatives like handling it in an update + select query, but have seen that it may also not be suitable in some cases.

Upvotes: 1

Views: 919

Answers (1)

The Impaler
The Impaler

Reputation: 48770

You have at least three strategies:

1. Pessimistic Locking

If your application will experience low activity then you can lock the tables (or single rows) to make sure no other thread changes the values during the processing of a purchase. It works, but it has performance limitations.

2. Optimistic Locking

If your application/web site must serve a high load then you can opt for the "optimistic locking" strategy. In this case you add a version number column to your critical tables and then you use it when reading/writing it.

When updating stock you check the version number you are updating must be the same that you read. If it's not the case anymore (another thread modified it) you roll back the transaction and can retry again a couple of times until you succeed.

It requires more development effor since you need to identify the bad case and implement retry logic (if you want to).

3. Processing Queues

You can implement processing queues. When a thread wants to "purchase an order" it can submit it to a processing queue for purchase orders. This queue can be implemented by one or more threads dedicated to this task; if you choose multiple threads they can be divided by order types, regions, categories, etc. to distribute the load.

This requires more programming effort since you need to manage asynchronous processing, but can sustain much higher levels of load.

You can use this strategy for multiple different tasks: purchasing orders, refilling stock, sending notifications, processing promotions, etc.

Upvotes: 2

Related Questions