locked
Foreign Key constraint exception intermittent on initial sync - transaction? RRS feed

  • Question

  • On performing an initial sync of our 80 table Oracle 10g database to SQL CE 3.5 RTM, we periodically get a foreign key constraint exception when Oracle is experiencing a high volume of inserts (see below). The problem seems to be that a parent table is populated with rows that exist at an earlier point in sync time, and later during the same sync operation a child table is populated with related rows that were inserted in Oracle after the parent rows were pulled down. I can handle this issue in the DbServerSyncProvider ApplyChangeFailed event, but I would think that if transactions were being handled properly this situation should never occur.

     

    In addition, the CE database seems to be initially built in a non-transactional way - interruption of initial sync does not roll back data for already populated tables.

     

    Is there something more I need to do for sync to be transactional, both on the server and on the client? I would have thought that Synchronization Services would handle this for me. I am planning to do background sync on an automatic schedule for around 10,000 clients, and it would be nice to have the transaction thing nailed down

     

    Dave Michel

     

    Exception:

     

    2008-03-14 11:08:53,567 [1] INFO  OMS.MainForm - Manual Sync started
    2008-03-14 11:09:32,022 [1] ERROR OMS.MainForm - Unknown Manual Sync exception
    System.Exception: Synchronization error applying change to PROJECT for sync stage ApplyingInserts ---> A foreign key value cannot be inserted because a corresponding primary key value does not exist. [ Foreign key constraint name = FK_PRJ_PHS ]
       --- End of inner exception stack trace ---
       at Mqsr.Synchronization.Sync.clientSyncProvider_ApplyChangeFailed(Object sender, ApplyChangeFailedEventArgs e)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.OnApplyChangeFailed(ApplyChangeFailedEventArgs value)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.ResolveConflict(SyncConflict syncConflict, SyncTableMetadata tableMetadata, SyncSession syncSession, Exception error)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.ApplyUpserts(SyncTableMetadata tableMetadata, SyncTableProgress tableProgress, SyncGroupMetadata groupMetadata, DataTable dataTable, DataRowState applicableState, UInt64 lastSentAnchorValue, SyncSession syncSession)
       at Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider.ApplyChanges(SyncGroupMetadata groupMetadata, DataSet dataSet, SyncSession syncSession)
       at Microsoft.Synchronization.Data.Client.InternalSyncAgent.DownloadChanges(SyncGroupMetadata groupMetadata)
       at Microsoft.Synchronization.Data.Client.InternalSyncAgent.Synchronize()
       at Microsoft.Synchronization.SyncAgent.TryDoDataSync(SyncStatistics& statistics)
       at Microsoft.Synchronization.SyncAgent.Synchronize()

     

    The main sync code in question:

     

      

    Code Snippet

    Private Function GetServerProvider() As DbServerSyncProvider
            If Session("ServerProvider") Is Nothing Then
                _log.Info("Initializing GetServerProvider for " & User.Identity.Name)
                _log.Debug("SessionId=" & Session.SessionID)
                serverProvider = New DbServerSyncProvider()
                Dim serverSyncHelper As SynchronizationHelper.Server = GetServerSyncHelper()
                serverProvider.Connection = New OracleConnection(serverSyncHelper.OracleConnectionString)
                Dim tables As List(Of String) = serverSyncHelper.GetSyncableTables()

                For Each tableName As String In tables
                    Dim syncAdapter As SyncAdapter = New SyncAdapter(tableName)

                    If TableShouldDownload(tableName) Then
                        '// select incremental inserts command
                        Dim incInsertCmd As OracleCommand = New OracleCommand()
                        incInsertCmd.CommandType = CommandType.Text
                        incInsertCmd.CommandText = serverSyncHelper.GetSelectIncrementalInsertsCommand(tableName)
                        incInsertCmd.Parameters.Add(":" & SyncSession.SyncOriginatorId, OracleType.Int32)
                        incInsertCmd.Parameters.Add(":" & SyncSession.SyncLastReceivedAnchor, OracleType.Timestamp)
                        incInsertCmd.Parameters.Add(":" & SyncSession.SyncNewReceivedAnchor, OracleType.Timestamp)
                        AddFilterParameters(incInsertCmd, tableName)
                        syncAdapter.SelectIncrementalInsertsCommand = incInsertCmd


                        '// select incremental updates command
                        Dim incUpdateCmd As OracleCommand = New OracleCommand()
                        incUpdateCmd.CommandType = CommandType.Text
                        incUpdateCmd.CommandText = serverSyncHelper.GetSelectIncrementalUpdatesCommand(tableName)
                        incUpdateCmd.Parameters.Add(":" & SyncSession.SyncOriginatorId, OracleType.Int32)
                        incUpdateCmd.Parameters.Add(":" & SyncSession.SyncLastReceivedAnchor, OracleType.Timestamp)
                        incUpdateCmd.Parameters.Add(":" & SyncSession.SyncNewReceivedAnchor, OracleType.Timestamp)
                        syncAdapter.SelectIncrementalUpdatesCommand = incUpdateCmd
                    End If

                    If TableShouldUpload(tableName) Then
                        Dim insertCmd As OracleCommand = New OracleCommand()
                        serverSyncHelper.SetInsertCommand(insertCmd, tableName)
                        insertCmd.Parameters.Add(":" & SyncSession.SyncOriginatorId, OracleType.Int32)
                        insertCmd.Parameters.Add(":" & SyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output
                        syncAdapter.InsertCommand = insertCmd

                        Dim updateCmd As OracleCommand = New OracleCommand()
                        serverSyncHelper.SetUpdateCommand(updateCmd, tableName)
                        updateCmd.Parameters.Add(":" & SyncSession.SyncLastReceivedAnchor, OracleType.Timestamp)
                        updateCmd.Parameters.Add(":" & SyncSession.SyncOriginatorId, OracleType.Int32)
                        updateCmd.Parameters.Add(":" & SyncSession.SyncRowCount, OracleType.Int32).Direction = ParameterDirection.Output
                        syncAdapter.UpdateCommand = updateCmd
                    End If

                    serverProvider.SyncAdapters.Add(syncAdapter)
                Next


                ' select new anchor command               
                Dim anchorCmd As OracleCommand = New OracleCommand()
                anchorCmd.CommandType = CommandType.Text
                anchorCmd.CommandText = _
                        "BEGIN " & _
                        "   SELECT SYSDATE INTO :" & SyncSession.SyncNewReceivedAnchor & " FROM DUAL;" & _
                        "END;"
                anchorCmd.Parameters.Add(":" + SyncSession.SyncNewReceivedAnchor, OracleType.Timestamp).Direction = ParameterDirection.Output
                serverProvider.SelectNewAnchorCommand = anchorCmd

                '' select new ClientId command               
                'Dim clientIdCmd As OracleCommand = New OracleCommand()
                'clientIdCmd.CommandType = CommandType.Text
                'clientIdCmd.CommandText = "SELECT INTO :" & SyncSession.SyncOriginatorId & " FROM (SELECT CLIENT_ORIGINATOR_ID FROM CLIENT WHERE :" & SyncSession.SyncClientId & ")"
                'clientIdCmd.Parameters.Add(":" + SyncSession.SyncClientId, OracleType.Char)
                'clientIdCmd.Parameters.Add(":" + SyncSession.SyncOriginatorId, OracleType.Int32).Direction = ParameterDirection.Output
                'serverProvider.SelectClientIdCommand = clientIdCmd

                serverProvider.Schema = GetSyncSchema(serverSyncHelper)
                Session("ServerProvider") = serverProvider
                Return serverProvider
            Else
                Return Session("ServerProvider")
            End If
        End Function

     

     

    Incremental Inserts code:

    Code Snippet

     

    Public Function GetSelectIncrementalInsertsCommand(ByVal tableName As String) As String
                Dim createdDateColumn As String = GetCreatedDateField(tableName)
                Dim modifiedByColumn As String = GetModifiedByField(tableName)
                If createdDateColumn.Length > 0 Then
                    Dim filterNode As XmlNode = GetFilterNode(tableName)
                    Dim whereClause As String = " WHERE " & createdDateColumn & " > :" & SyncSession.SyncLastReceivedAnchor & " and " & createdDateColumn & " <= :" & SyncSession.SyncNewReceivedAnchor & " and ((" & modifiedByColumn & " <> :" & SyncSession.SyncOriginatorId & " or " & modifiedByColumn & " is null) or " & createdDateColumn & " is null)"
                    Return "SELECT " & GetColumnList(tableName, False) & " FROM " & tableName & whereClause & " ORDER BY " & createdDateColumn
                End If
                Return String.Empty
    End Function

     

     

     

    • Moved by Max Wang_1983 Friday, April 22, 2011 7:43 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Friday, March 28, 2008 7:32 PM

Answers

  • Thanks for the follow up, Yunwen. I've had many conversations with my other development team members, and we've come to the conclusion that this is due to long running client-initiated transactions where Oracle inserts are being made during a sync operation. A problem related to the client owning the transaction is that Oracle is particularly bad at cleaning up broken connections. Using SQL Server 2008 for Change Tracking and much, much better connection management is unfortunately not an option for our current client.

     

    Because of these two issues and the nature of the project we are looking at using MSMQ to provide a feed to Oracle and using a custom binary formatter with WCF for transport rather than Sync Services over a web service. We'll be looking at proofs of concept within the month.

     

    Thanks for your assistance.

     

    Dave

     

    Friday, May 16, 2008 5:06 AM

All replies

  • Any ideas, anyone?

     

    Monday, April 7, 2008 7:18 PM
  • Hi Dave,

     

    the change application phase on both server and client should be gurarded with the transactions. However, if any changes ( rows ) failed to apply, the default action is to continue and the failed row will not be applied. you can change the action to the desired ones by implementing the ApplyChangeFailed event and put you logic there.

     

    regarding with the FK-PK table, are you put them in a single sync group ? noticed that the transaction is per sync group, i.e. if you have two tables, by default they will be added to different sync groups. when applying changes, each of them will be applied under their own transaction scope.

     

    can you check if the FK-PK table were added to the same sync group ?

     

    thanks

    Yunwen

     

    Saturday, May 3, 2008 11:24 PM
    Moderator
  • I know for a fact that I am not using a sync group. I'll add one and see what happens.

     

    Thanks,

    Dave

     

    Saturday, May 3, 2008 11:30 PM
  • Hi Dave,

     

    Just want to follow up on this. does adding the sync group solve this issue ?

     

    Thanks
    Yunwen

    Thursday, May 15, 2008 11:35 PM
    Moderator
  • Thanks for the follow up, Yunwen. I've had many conversations with my other development team members, and we've come to the conclusion that this is due to long running client-initiated transactions where Oracle inserts are being made during a sync operation. A problem related to the client owning the transaction is that Oracle is particularly bad at cleaning up broken connections. Using SQL Server 2008 for Change Tracking and much, much better connection management is unfortunately not an option for our current client.

     

    Because of these two issues and the nature of the project we are looking at using MSMQ to provide a feed to Oracle and using a custom binary formatter with WCF for transport rather than Sync Services over a web service. We'll be looking at proofs of concept within the month.

     

    Thanks for your assistance.

     

    Dave

     

    Friday, May 16, 2008 5:06 AM