I need the following functionally on my program:

User1 starts order and takes 2 of product1 a into order.

User2 queries inventory for product1 while User1 is still adding products to his order.

I want User2 to see that we have (qty - 2) left before User1 commits his transaction.

I am using PostgreSQL which does not support Read Uncommitted isolation level. Is there a way to get this to work?

Thank you,

Fernando

What happens if User1 leaves his/her order open for 24 hours?

If the DB supports read uncommitted abandoned transactions are rolled back when they time out.

I have found that the solution when the DB does not supports read uncommited, which appearently is not a good thing either, what can be done is have a table that holds the quantity. You call this qty on hold. When user2 checks inventory you check the qty of the product and add the qty on hold and display this to user2. Then you can check the qty on hold table for entries older that your chosen time out and remove those.

I think this is the way to go.

Thank you and I hope this helps someone else.

Fernando

Another way to do this follows. The assumption here is that once a user requests n units of product x, these n units become unavailable for any other user.

At the time the database is started, I would store the current inventory in a hash map with the product_id as the key and the number of units available as the value.

When any user requests n units of product p, I'd look into this hash map and check if there are at least n units of product p.

If yes, then in the hashmap, I'd decrement the available quantity by n and allow the user to order n units.

If no, then I'd tell the user that there are not enough items in stock.

This way, there is only one key for each product in the hashmap. The timestamps do not come into the picture, neither does the question of deleting rows from a table. You do not have to create a table unnecessarily.

Synchronization problems, such as those that occur when two or more users ask for the same product at the same time, can be handled relatively easily.

Hope that helps.

Ravi