How to resolve concurrent transaction problems in a sales database

Question

My application stores some data in an SQL table called transactions. Users can sell something from Client1 to Client2, and then Client2 has it. I store it in a table as follows:

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

The first client1 bought it (brought it or just got it all matters to him). He then sells it to another customer.

And everything is fine, but it works, but my application has a short time when it does not check if client 1 has everything that it claims (when the data is loaded into gui). Thus, if 2 users started it, it is possible that the product from Client1 can be sold several times. Most likely, this will not happen, because my users, as a rule, share their work on what they do, but there is always BUT ...

How to prevent this? Will a simple check of the request before starting the insert suffice or should it be done differently? (I can imagine a situation where several people make her run, and some succeed). How is this handled in real situations on heavy systems? For example, when you take money from one bank account with two cards from two different CashMachines (although I believe that in this case the balance will be 0, even if it is not allowed).

So what are my options? What is your attitude to this?

Answer 1

I suppose this is not a real inventory tracking system where the database simply captures events in the real world, but instead represents a virtual auction or a place in the market where “reality” is what the application thinks is. / p>

If you save a story, you will never have the current state. Without the current state, you cannot make the right decisions about the correctness. Therefore, keep the current state. You have a table of items and their current owner. The task you are setting becomes a simple problem: "How to prevent a lost update?" or "How to prevent blind recording"? (i.e. write-write conflict) and the answer is well known in the database application: use optimistic concurrency control.

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

Answer 2

I calculate the current totals, save them on one line, and use a constraint to make sure the current totals are non-negative. Described here: Denormalization to Enforce Business Rules: Delivering Summary