none
Lock on index page RRS feed

  • Question

  • Getting SqlCeException when performing DB Sync (batchwise) and data insertion one after another on a SQL CE Database. Details below:

    ð            => We have implemented batching in DB Sync for both upload and download.

    ð ð     => We are inserting records on the database after every batch of db sync.

    ð  ð     => After ApplyChanges (data download) when we insert data into the Database, we get the following exception:

     

    System.Data.SqlServerCe.SqlCeException sqlCeException = {"SQL Server Compact timed out waiting for a lock. The default lock time is 2000ms for devices and 5000ms for desktops. The default lock timeout can be increased in the connection string using the ssce: default lock timeout property. [ Session id = 2,Thread id = 1797796850,Process id = -1952971814,Table name = TableName,Conflict type = x lock (x blocks),Resource = PAG (idx): 1035 ]"})

     

    Observations:

    ð  The exception means that DB Sync APIs have an exclusive lock on the same Index page on which we are trying to insert data.

    ð  While debugging we checked the contents of __SysLocks table and noticed that the lock is not always present. This means that sometimes insert will be successful and sometimes not.

     

    Current Fix:

    What we have done now is to catch the exception, log it and carry on with the subsequent batch syncing. We try inserting data again after the subsequent batch completion, if the data insert had failed in the previous attempt it will be inserted now.

     

    The above fix is not a good one, in the sense that we get an exception and ignore it. Can we identify the lock OR put a delay till the lock is released OR... ? Please help.

    Monday, February 6, 2012 10:37 AM

All replies

  • would you mind sharing which provider are you using? is this a custom batching solution?
    Monday, February 6, 2012 10:41 AM
    Moderator
  • Hi JuneT,

    <I am LalitSRana's colleague>

    There is no major change in the code for downloading data changes which we are using for DB Sync.

    We wanted to insert records in the same table which is being synced while the changes are being downloaded from server. Since the batching for data download is done from the server side. All that we are doing here is that we have registered for ChangesApplied event of SyncAgent.LocalProvider and we are trying to insert records from inside of the callback registered for this event, each time this callback is fired.

    Following describes the version info about the SqlCE assemblies which we are using as per VS:

     

    Assembly

    Runtime Version

    Version

    Microsoft.Synchronization.Data.dll

    v2.0.50727

    1.0.6000.0

    Microsoft.Synchronization.Data.SqlServerCe.dll

    v2.0.50727

    3.5.6000.0

    System.Data.SqlServerCe.dll

    v2.0.50727

    3.5.0.0

    We can not upgrade to latest version of SQLCE since we are developing for WinCE.


    • Edited by rajpreet Tuesday, February 7, 2012 3:49 PM edit
    Tuesday, February 7, 2012 3:45 PM
  • do you need these additional inserts to be part of the same transaction as the sync?

    do you get the error only when there is additional batch to be downloaded?

    Wednesday, February 8, 2012 1:49 AM
    Moderator
  • do you need these additional inserts to be part of the same transaction as the sync?:

    "Using the same transaction as used by sync" is not an option with me, becuase if a rollback happens (due to some error/network problem) while sync is going on, I will loose my inserts and I can not recover my data.

    do you get the error only when there is additional batch to be downloaded?:

    I get this error when the sync framework is downloading the changes and I am inserting records from within the ChangesApplied event and that is fired after each batch of changes is downloaded and applied in the database.

    I almost always get this error when there are additional batches to be downloaded. But I need to move data to database more frequently and do not want to wait untill the sync is complete, this is why I try to insert records in database after each sync batch.

    Tuesday, February 14, 2012 3:39 PM
  • afaik, even if you enabled batching, all the batches are grouped in the same transaction by Sync Framework. unless of course you wrote your own custom provider.

    so am guessing, the Sync Framework process still has an open transaction (and locks) while you're trying to insert the other rows.

    am assuming you do the insert inside the Sync events because you need information from the rows being synched? can you not temporarily store the inserts you want to do in some variable and do the inserts after the sync? you dont need them to be in the same transaction anyway.

    Wednesday, February 15, 2012 12:30 AM
    Moderator
  • Thanks for sharing your views JuneT.

    am assuming you do the insert inside the Sync events because you need information from the rows being synched?

    No, I do so because I want to insert my data in db ASAP. I do not need data from rows being synched.

    can you not temporarily store the inserts you want to do in some variable and do the inserts after the sync?

    I am afraid I can not add another db or something (I can not afford to loose my data due to device restart) for holding the records to be inserted untill the sync is completed.

    I was just thinking if we can interpret the data in __sysLocks table (while downloading changes) to see if my insert will pass or not. And do not attempt it.

    Wednesday, February 15, 2012 2:31 PM
  • if the records you are inserting has nothing to do with the rows being synched, then the more reason you shouldnt be doing it inside the Sync Framework events.

    can you not do inserts just before or after the call to Synchronize?

    Thursday, February 16, 2012 1:36 AM
    Moderator