• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Concurrent users and updates on a record going out of control

I am working on a site that has a huge load of traffic just for a couple of seconds, 100's of users trying to buy a discounted low quantity product and simultaneous transactions occur almost exactly at the same time. Before I allow a transaction, I check the record for the available quantity, and if so, allow the transaction and update the available quantity with the lower number.

The problem is that I am tending to "oversell" since this very fast transactions occur simultaneously. On every case I check for availability, by the time I update the record, I am over the amount I wanted to sell. This is because vs transactions occur at the exact same time.

I tried different things and EVEN changed the host for a faster dedicated server to see if it was a hard problem. No luck

I am sure many of you have seen this problem on a high traffic site that has updates at the same time on the same record, but I haven't found how to solve this in CodeIgniter 1.7 nor anything related to locking records or handling this with some kind of queue.

Anyone knows how to solve this ? Thank you.

[eluser]Rick Jolly[/eluser]
You say you are making "transactions", but are you using real database transactions?

No, I'm sorry. I call transactions to every "sale" that happens. As I mentioneed, I am having to many sales compared to the quantity available.

CodeIgniter User Guide - Transactions


Thank you, but I am not sure this is what I really need ( Correct me if I am wrong ), here's why:

I don't need to correct and update on my record, I need to be able to STOP the update before it happens. If I do allow the update, then I am allowing the sale to occur, and the user to get charged. This is a huge mess. I need to let the update occur ONLY if available units, and as I said I am missing it because of simultaneous requests at the same time.

It might be more an issue of how to LOCK the record by one of the users while it is been updated. But then I also need to kind of delay the rest of the users somehow, and if possible in order. LOCK exists in php & mysql, but I haven't seen hwo to use it in CodeIgniter , and also, don' know what technique to use to handle the rest of the users without entering a dead lock .

Thank you .

MySQL Locking


[eluser]Rick Jolly[/eluser]
Transactions are for problems like yours. Let me try to explain.

Every single query is a transaction implicitly. There is an implicit "begin transaction" before the query and an implicit "commit" after the query. If that weren't true, 2 simultaneous update queries on the same row of the same table would corrupt the data in that row.

When you use a transaction, you ensure that all queries are executed in one isolated action. In your case, you can check the inventory and create the order in one action. Maybe think of it as one query.

query('begin transaction');
$affected_rows = query('update inventory set counter = (counter – 1) where counter > 0');

if ($affected_rows == 0)
    query("insert into orders (data) values ('data'))";
You'll need innodb tables if using mysql.

I'm pretty sure you already thought of this but it doesn't hurt mentioning it.

Think of your application as a real store and lets say there are 10 ipods on the shelf and nothing in stock well if someone is shopping around with their cart they will grab 1 ipod and keep on shopping the shelf will now have 9 ipods for anyone else to view.

In your case what you want to do is update the quantity on the database when a person adds an item to their cart making the item quantities exact to whats available at that moment or at least that is the theory. And then if the person doesn't buy the item and this is where the coding will be a little complex add back the items not sold from either inactivity or any other none sale actions. This is the part to give it a little thought.

Most shopping carts out there don't have this real time item tracking feature but giving a little thought you can integrated.

You can also do a last check at checkout for quantities and then give the buyer more options to either go through with the transaction or to remove out of stock items.

I my self have sunshop shopping cart and I have run into this problems in the past all I was able to do was notify the customer about the availability and hope they will wait for their merchandise.

Sorry for no coding examples but i'm pretty sure you will get the idea if you're using CI cart class, which uses the sessions class to keep track of items and not on stock quantities.

As far as locking tables for what I know, I don't think it will be possible to implement to what you want since locking the table will prevent any updates to that table for any amount of time is being lock.

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  

  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.