locked
ChangesApplied - how to avoid record locking? RRS feed

  • Question

  • I have a smart device application which synchronises data using sync services. When the data hits the server, I want to send off SMS alerts, reports etc to business users to show details of the uploaded data (in this case: help-desk tickets). The process that performs SMS/email notifications updates a column in the same table that is being updated by the sync once it has completed its work to say that that the ticket has been processed.

    The issue that I have is that the row that I am trying to update by the notification business-logic is 'locked' inside a sync transaction and I cannot modify the row until the transaction commits. I have tried to use e.Transaction.Commit(); to commit the transaction inside the ChangesApplied event handler and then running my business logic (but this causes an error for subsequent records inside the same transaction "This SqlTransaction has completed; it is no longer usable.")

    Is there any way of hooking into the same transaction as the sync, or changing the isolation level of the sync? It looks like the same transaction is used by all rows within a particular DataTable. It would be good if each row could act as a separate transaction and therefore committing one row wouldn't affect other rows in the same transaction (maybe there's a way of creating a nested transaction which I could then share with my business logic?)

    Snipped version of code to illustrate my problem:

           /// <summary>
            /// After changes have been applied, run business logic
            /// </summary>
            private void ScrutinizeLiveDataCacheServerSyncProvider_ChangesApplied(object sender, ChangesAppliedEventArgs e)
            {
           
                // Loop through tables in DataSet
                foreach (DataTable dt in e.Context.DataSet.Tables)
                {
                    // Issue any new incidents
                    if (dt.TableName == "Incidents")
                    {
                        foreach (DataRow dr in e.Context.DataSet.Tables["Incidents"].Rows)
                        {
                            // Grab attributes from row just uploaded
                            Guid IncidentUID = (Guid)dr["IncidentUID"];
                    
                            // Retrieve the uploaded incident
                            ScrutinizeLiveWeb.CIncident UploadedIncident = new ScrutinizeLiveWeb.CIncident(IncidentUID);

                            try
                            {
                                if (e.Transaction.Connection != null) // transaction connection exists
                                {
                                    e.Transaction.Commit(); // Commit tran (and free up any locks)
                                }
                            }
                            catch (Exception ex)
                            {
                                ScrutinizeLiveWeb.CAppLogger.LogException(ex, ScrutinizeLiveWeb.CAppLogger.Component.ScrutinizeLiveSyncService);
                            }
                           
                            // Issue the uploaded incident
                            ScrutinizeLiveWeb.CIncidentStatusIssue Issue = new ScrutinizeLiveWeb.CIncidentStatusIssue();
                            Issue.Incident_Issue(UploadedIncident, UploadedIncident._AssignedTo_User, UploadedIncident._CreatedBy_User, _OriginalIncident); // this line cannot execute as it is trying to update a row is inside the current sync transaction
                        }
                    }
                }

    Any pointers appreciated...
    • Edited by SunHunter Tuesday, June 30, 2009 5:35 PM
    • Moved by Hengzhe Li Friday, April 22, 2011 2:51 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Tuesday, June 30, 2009 4:15 PM

Answers

  • Ok, just found out how to do this (if it helps anyone else):

    Pass current connection and transaction through to business layer:

    businesslayer.DoSomething( (SqlTransaction)e.Transaction, (SqlConnection)e.Connection );

    Business layer:

    cmd.Transaction = e.Transaction;
    cmd.Connection = e.Connection;
    cmd.ExecuteScalar();

    simple when you know how... (locking issue resolved)
    Wednesday, July 1, 2009 9:55 AM