CODEWITHSUNDEEP
sqlsql-server-2005
NoviceToDotNet
NoviceToDotNet

Reputation: 10815

Dirty read problem, how to solve in sql server 2005

How can i resolve the below dirty read problem in sql server.

There is a customer invoice report, which runs on 1:00 AM in afternoon and after that all invoices are sent to the respective customer for payments. Let us say one of the customer has 1000$ to be paid. Customer pays 1000$ at 1:00 AM and at the same time report is run. Actually, customer has no money pending but is still issued an invoice.

also how to resolve this prblem too, for non repeatable read

For instance, a customer wants to book flight, so the travel agent checks for the flights availability. Travel agent finds a seat and goes ahead to book the seat. While the travel agent is booking the seat, some other travel agent books the seat. When this travel agent goes to update the record, he gets error saying that “Seat is already booked”. In short, the travel agent gets different status at different times for the sea

also this problem also trouble me .....Lost Updates

Suppose that a customer has due of 2000$ to be paid. He pays 1000$ and again buys a product of 500$. Lets say that these two transactions are now been entered from two different counters of the company. Now both the counter user starts making entry at the same time 10:00 AM. Actually speaking at 10:01 AM the customer should have 2000$-1000$+500 = 1500$ pending to be paid. But as said in lost updates the first transaction is not considered and the second transaction overrides it. So the final pending is 2000$+500$ = 2500$.....I hope the company does not loose the customer.

Upvotes: 1

Views: 1249

Answers (1)

John Nicholas
John Nicholas

Reputation: 4836

You are describing logical disjunctions.

in the latter case you need to itemise the invoice and say if the customer hs paid something that hasn;t made it through the system to be aware that it will make it through.

In the first the error is simply stating the obvious. Its the same situation with disk access and file handles. Just because you cna write to a file when you check doesnt mean you can actually write to a file when you try to do so.

You have to handle the errors.

In the first case i don't see why you cannot say whta time the amount was pending. If sending out empty invoices really is a problem then you need to check right before it is issued. If the invoice really has no items on it then there shuold be some logic that says hang on this invoice has no detail items or that the total is zero. But if it because inbetween issuing and receipt that it is paid then you still have a valid invoice.

If you are talking about 2 things updating a record at once then you need to use a locking system to ensure no other process writes whilst another write is taking place.

There are all kinds of concurrency approaches for this - such as a semephore system. sql itself has transactions. Google SQL concurrency, its a common issue with many many solutions.

The common pattern is read, lock, read, write and unlock.

Understanding Concurrency Control

The thing to consider is do you want high performance on reads or writes. The strategy you use will reflect this. Eg you are considering a single field ... but what about multiple fields updating on a single row?

Upvotes: 3

Related Questions