I am testing the concept of concurrent transaction using this table definition:
To simulate multiple users, i'm using threads and having them sleep random amounts of time.
So the system is this:
1. User is looking at the details for an item
2. User clicks "Buy"
3. Clicking buy brings them to a confirmation page. They click "Confirm"
4. From here we have a TRANSACTION that first updates the table to increment the # of locks. (update acquires a row-level lock)
a. If the # of Locks is legit, inform application to start a CC "transaction"
i. Is this transaction approved?
1. Yes, TRANSACTION to decrement Locks and Remaining
2. No, TRANSACTION decrement Locks
b. Otherwise decrement Locks
The reason we need a separate locking mechanism (Locks) is because a credit card transaction takes awhile, and we need to ensure that the user who clicked "Confirm" actually gets their product, and also to not hold a row-level lock while the credit card transaction is taking place.
So my question is, does having the TRANSACTION to increment Locks make sense, or is there a better way to do this? The reason I think this could be bad is that it blocks queries. This maybe just a flawed database design on my part, and we should actually have a different record for every Product, that way row-level locks do not affect queries. But that would majorly inflate the size of the database.