Shared Data Problem

Question

I am working on an ASP.NET Online Shop. every products has an entity and even a user sells it, its entity should be decreased. because this field is shared between all users, it may be negative (because of the shared data problem). Now, how can I prevent this problem? Can I use transaction for this? If I can, what the isolation level should I use? And if not, what should I do?

Answer 1

I would recommend to make some design changes if possible. Instead of decreasing/increasing, in other words updating a field, I would insert a record every time a sell is made. Then I would create a stored procedure and schedule it to run it on regular basis.

This will assure that only one process at a time updates the counter.

This is the only way I know to guarantee 100% the field will be consistent.

Some people may suggest pessimistic lock - I would never use it under any circumstances.