How to resolve concurrent transaction issues in sales database

Question

My application is storing some data in SQL Table called Transactions. Users are able to sell something from Client1 to Client2 and then Client2 has it. I am storing it in a table the following way:

Client 1 | Buy  | Something | Price  | External |
Client 1 | Sell | Something | Price2 | Client 2 |
Client 2 | Buy  | Something | Price2 | Client 1 |

First Client1 bought it (brought it in or just has it for all it matters). Then he sells it to another client.

And all that is fine, it works but my application has a short time when it doesn't check if Client 1 still has what it claims to have (when data is loaded into gui). So if 2 users would make run for it it's possible that product from Client1 could be sold multiple times. Most likely it won't happen since my users tend to share their work on what they are doing but there's always BUT...

How to prevent this? Would simple select query check just before insert transaction be sufficient or should this be done differently? (I can imagine situation when multiple people make run for it and some would succeed). How is this handled in real world situations on heavy systems? Like when you pick money from one bank account with 2 cards from 2 different CashMachines (although i believe they would just let balance go under 0 in this case even if it wouldn't be allowed).

So what options do I have? What is your take on this?

Answer 1

I assume this is not a real inventory tracking system, where the database merely record real world events, but instead is a some virtual auction or market place sort of application, where 'reality' is whatever the application believe it is.

If you only keep history, you'll never have the current state. W/o a current state, you can't make efficient correctness decisions. So keep a current state. Have a table of items and their current owner. The problem you ask then become a simple problem of 'How to I prevent a lost update?' or 'How do I prevent a blind write'? (ie. a write-write conflict) and the answer is well known in database application: use optimistic concurrency control.

For a detailed discussion of how to use Optimistic Concurrency with C# and SQL, see Optimistic Concurrency (ADO.NET).

Answer 2

I calculate running totals, store them in the same row, and use a constraint to make sure that running totals are non-negative. Described here: Denormalizing to enforce business rules: Running Totals