Web Development·11,931 discussions

Data loss in MS SQL server

Locked

I use visual Basic and SQL server 7.0 to write a program for data capturing. The program is used by more than 10 operators. When I find a blocking occurs, I kill the blocking process. And it is reported that thousands of records captured are loss. Iam sure there the transaction is COMMIT after every valid transaction. I think only one record can be lost rather than thousand of records.
Why this happen and what is the machanism of SQL server handle the data?

Topic

Data loss in MS SQL server

In reply to Data loss in MS SQL server

Point value changed by question poster.

Data loss in MS SQL server

In reply to Data loss in MS SQL server

How are you handling locking? you may need to LockBatchOptimistic, this way updating the records would only block the other operators during the COMMIT phase. Perhaps rewrite the routine that kills the blocking process and instead incorporate a client-side transaction log that can re-try updating the DB. Remember, transactions in the Microsoft world are ALL OR NOTHING ,this is why you lose thousands instead of just one.

Data loss in MS SQL server

In reply to Data loss in MS SQL server

Poster rated this answer

Data loss in MS SQL server

In reply to Data loss in MS SQL server

As far as I know, pessimistic locking alone locks the underlying record in the data base the moment you change the contents in the associated record set .i.e when you have an intent to update.

When you intend to update/insert/delete use pessimistic locking and operate in a begin trans – commit/rollback cycle.

Don’t worry. Pessimistic wont slow you down by much. Better not to lose data.

But thousands of records. Are you sure this is from an interactive sesssion? I think some backend tuning may also be required. The minimum accepted granularity of locking permitted in your installation will ensure that only a few records are open for update.

Hope this helps.
End of mail.

Hope this helps.
End of mail.

[Author]

Replies