General discussion

Locked

Data loss in MS SQL server

By eric_ting ·
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?

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Data loss in MS SQL server

by eric_ting In reply to Data loss in MS SQL serve ...

Point value changed by question poster.

Collapse -

Data loss in MS SQL server

by hypersoniq MCSE In reply to Data loss in MS SQL serve ...

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.

Collapse -

Data loss in MS SQL server

by eric_ting In reply to Data loss in MS SQL serve ...

Poster rated this answer

Collapse -

Data loss in MS SQL server

by ananthap In reply to Data loss in MS SQL serve ...

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.

Back to Web Development Forum
4 total posts (Page 1 of 1)  

Related Discussions

Related Forums