none
Lock problem when inserting and deleting records

    Question

  • Hi all.

     

    I have an application that is using a SQL compact edition database to save/process information.

     

    I run a test that is creating two threads:

    1. one is inserting data in Table1

    2. the other one is deleting records from Table1

     

    When I run the application, I get some exceptions on both threads saying that the insert/delete could not aquire a lock on the table.

    After a while, when I try to connect to the database I get an exception saying that the database file might be corrupted.

     

    Any thoughts?

     

    Thanks.

    Thursday, July 12, 2007 1:20 PM

Answers

  • If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

     

    How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

     

    But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

     

    Thanks

    Raja [MSFT]

     

    P.S: If this solves your problem, please mark it as answered.

     

     

    Wednesday, August 01, 2007 1:42 AM

All replies

  • Hello,

     

        Can you send me the database, and if possible, tell me (or give me) the app, so that, I can see what the issue is? Send it to goteti.udaya.bhanu@gmail.com. And also, let me know which version of SQL CE are you using (3.1 or 3.0 etc...). Are you using ADO.NET or a native app to do this. Can you send me the code.

     

    Thanks

    Udaya.

    Friday, July 13, 2007 12:09 PM
  • The db has a very simple structure:

    2 tables with strings and datetimes as columns data types.

     

    I am using SQLCE 3.1 version and ADO.NET. So, there is no native call from my code.

     

    Thanks.

    Mircea

    Friday, July 13, 2007 10:03 PM
  • Hello,

     

        I have tried to repro the issue, with SQLCE 3.1, but, could not. I need one more detail for that, I think. Which version of .NET are you using? Are you using .NET CF or .NET (on a device or on the desktop)?

     

    Thanks

    Udaya.

     

    Wednesday, July 25, 2007 5:22 PM
  • If your both threads are doing DML operations on the same thread at the same time, there is a good chance that you experience locking issues. It could be that both operations need a lock on the same index page and one of them is denied lock for a long time (2000 msec or some thing like that). This looks okay to me. We are trying to improve the experience.

     

    How to solve it? You might solve the above problem by either handling the error or serializing your actions. In the first approach you might want to just catch the error and retry the operation.

     

    But why file corruption? This should not happen. Unless we look into your code, it's difficult to explain. Can you please try using different connection objects in these two threads?

     

    Thanks

    Raja [MSFT]

     

    P.S: If this solves your problem, please mark it as answered.

     

     

    Wednesday, August 01, 2007 1:42 AM