General discussion

  • Creator
    Topic
  • #2321603

    Data loss in MS SQL server

    Locked

    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?

All Comments

  • Author
    Replies
    • #3416012

      Data loss in MS SQL server

      by eric_ting ·

      In reply to Data loss in MS SQL server

      Point value changed by question poster.

    • #3416087

      Data loss in MS SQL server

      by hypersoniq mcse ·

      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.

    • #3403409

      Data loss in MS SQL server

      by ananthap ·

      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.

Viewing 2 reply threads