Occasional data load failure

jsusen · September 2, 2026, 1:19pm

In case there are multiple dependent entities to load, Origam loads all entities into a detasest sequentially and than enables the constraints. No transaction is usually used. This allows for a situation when data is loaded into the first entity, then some other process modifies the dependent table in the database and then the dependent entity is loaded in Origam in inconsistent state (extra rows) causing the following exception:

2026-08-04 10:28:19,280 [23] ERROR Origam.DA.Service.AbstractSqlDataService - Error: ForeignKeyConstraint FK_Inventory requires the child key values (0aa6c0b6-938b-400e-9add-42ce0033c7b2) to exist in the parent table.

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
   at System.Data.DataSet.FailedEnableConstraints()
   at System.Data.DataSet.EnableConstraints()
   at System.Data.DataSet.set_EnforceConstraints(Boolean value)
   at Origam.DA.Service.AbstractSqlDataService.LoadDataSet(DataStructureQuery query, IPrincipal principal, DataSet dataset, String transactionId) in D:\a\1\s\backend\Origam.DA.Service\AbstractSqlDataService.cs:line 426
2026-08-04 10:28:19,281 [23] ERROR SoapCore.SoapEndpointMiddleware - An error occurred processing the message
System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
   at System.Data.DataSet.FailedEnableConstraints()
   at System.Data.DataSet.EnableConstraints()
   at System.Data.DataSet.set_EnforceConstraints(Boolean value)
   at Origam.DA.Service.AbstractSqlDataService.LoadDataSet(DataStructureQuery query, IPrincipal principal, DataSet dataset, String transactionId) in D:\a\1\s\backend\Origam.DA.Service\AbstractSqlDataService.cs:line 426
   at Origam.Workflow.DataServiceAgent.LoadData(DataStructureQuery query, DataSet data) in D:\a\1\s\backend\Origam.Workflow\Service Agents\DataServiceAgent.cs:line 101
   at Origam.Workflow.DataServiceAgent.Run() in D:\a\1\s\backend\Origam.Workflow\Service Agents\DataServiceAgent.cs:line 631
   at Origam.Workbench.Services.CoreServices.DataService.LoadData(Guid dataStructureId, Guid methodId, Guid defaultSetId, Guid sortSetId, String transactionId, QueryParameterCollection parameters, DataSet currentData, String entity, String columnName) in D:\a\1\s\backend\Origam.Workbench.Services\CoreServices\DataService.cs:line 169
   at Origam.Server.DataServiceSoap.LoadData2Async(String dataStructureId, String filterId, String defaultSetId, String sortSetId, String paramName1, String paramValue1, String paramName2, String paramValue2) in D:\a\1\s\backend\Origam.Server\Soap\DataServiceSoap.cs:line 153
   at InvokeStub_IDataServiceSoap.LoadData2Async(Object, Span`1)
   at System.Reflection.MethodBaseInvoker.InvokeWithManyArgs(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

Here is the method that loads the data:

github.com/origam/origam

backend/Origam.DA.Service/AbstractSqlDataService.cs

f497d906b


      
                  loader.Query = query;
                  loader.Timeout = timeout;
                  loader.CurrentProfile = currentProfile;
                  loader.Fill();
              }
          }
          if (query.EnforceConstraints)
          {
              try
              {
                  dataset.EnforceConstraints = enforceConstraints;
              }
              catch (Exception ex)
              {
                  try
                  {
                      log.LogOrigamError(DebugClass.ListRowErrors(dataset), ex);
                      using (
                          var writer = System.IO.File.CreateText(
                              AppDomain.CurrentDomain.BaseDirectory
                                  + @"\debug\"

jsusen · September 2, 2026, 1:25pm

The solution is to run the selects in Origam in a transaction with Snapshot isolation level.

This could be a breaking change that is why running of the load in the transaction should be controlled by a setting in appsettings.json.

Snapshot isolation must also be enabled by setting the ALLOW_SNAPSHOT_ISOLATION ON database option.

@urbanekv what do you think about this solution?

silence · September 3, 2026, 4:32pm

In this case we are already using SNAPSHOT_ISOLATION set ON. With conjunction READ_COMMITED_SNAPSHOT and ReadCommited transaction isolation level for users.

This settings was made a many years ago.

jsusen · September 4, 2026, 8:56am

Okay, but when the data is loaded in the LoadDataSet method, there’s no transaction. null is passed in transactionId in that particular case. So instead of that we have to start a new transaction by calling GetTransaction in LoadDataSet with the isolation level snapshot and commit the transaction after the loading is complete.

github.com/origam/origam

backend/Origam.DA.Service/AbstractSqlDataService.cs

170813118


      
          public override void DiagnoseConnection()
          {
              using IDbConnection connection = GetConnection(ConnectionString);
              connection.Open();
          }
          
          public abstract string CreateSystemRole(string roleName);
          
          public abstract string CreateInsert(int fieldCount);
          
          internal override IDbTransaction GetTransaction(
              string transactionId,
              IsolationLevel isolationLevel
          )
          {
              IDbTransaction transaction;
              if (transactionId == null)
              {
                  var connection = GetConnection(ConnectionString);
                  connection.Open();
                  transaction = connection.BeginTransaction(isolationLevel);

washi · September 4, 2026, 12:24pm

Wouldn’t be better to provide this option by setting flag in datastructure method (filterset) level?

urbanekv · September 4, 2026, 1:23pm

As @silence already mentioned, we run snapshot isolation globally, having it set globally on the database server. So I think there is another problem constraint or other problem with data. The error:

System.Data.ConstraintException: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.
   at System.Data.DataSet.FailedEnableConstraints()

is poor. There is already a static method in DatasetTools GetDatasetErrors that can produce exact reason for the error. It has to be added to the proper place to print the reason to the log. The original problem doesn’t have to be a foreign constraint problem, it’s a DataSet constraint problem. There are many constraints defined on DataSet, e.g. mandatory constraint, length constraint, etc, so it doesn’t have to be changing data problem. It coud be missing mandatory field problem, or something much easier. We are blind.

I’ve already added GetDatsetErrorrs() to several places long time ago. So this is a forgotten place, I think.