Web Development

Ask something
Discussion Locked

Data loss in MS SQL server

0 helpful
4 replies

by eric_ting

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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?

4 replies

by hypersoniq mcse

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.

by ananthap

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.

This discussion is closed to new replies.