Transactional operations simultaneously mixed with non-transactional ones

Question

I need to perform data import from external source to my database. Because there is a lot of data to download, the import is executing for a long time and I need to persist periodic updates about current importing state to the database (for the user to follow).

Suppose I have 2 tables: Import (storage for imported data) and Status (importing state monitoring table).

The code for data import:

public class Importer
{
    public delegate void ImportHandler(string item);
    public event ImportHandler ImportStarted;

    public void OnStart(string item)
    {            
        ImportStarted(item);
    }

    public void Execute(string[] items)
    {
        foreach (var item  in items)
        {
            OnStart(item);                    
            PersistImportedData(Download(item));
        }
    }

    private void PersistImportedData(object data)
    {
        using (var connection = new SqlConnection()){ /*saving imported data*/ }
    }
}

The starter code - for invoking import task and updating its status:

public class Starter
{
    public void Process(string[] items)
    {
       var importer = new Importer();
       importer.ImportStarted += UpdateImportState;
       importer.Execute(items);
    }

    private void UpdateImportState(string item)
    {
        using (var connection = new SqlConnection()){ /*status updates*/ }
    }
}

Now everything works fine. Import is executing and user is getting status updates (from Status table) as import goes on.

The problem occurs because such logic is not safe. I have to be sure, that import is an atomic operation. I don't want partially downloaded and saved data. I've used transaction approach as a solution for this (I've wrapped importer.Execute with TransactionScope):

importer.ImportStarted += UpdateImportState;
using (var scope = new TransactionScope())
{
    importer.Execute(items);
    scope.Complete();
}

Now I have safety - rollback occurs e.g. in case of process abort.

I faced different problem now - the one I want to resolve. I need status updates information for the user to show, but the Status table is not affected by updates, while transaction is not yet completed. Even if I try to use RequiresNew option for creating separate transaction (not ambient one), nothing changes. The Execute function creates its own connection to database and UpdateImportState does the same. The connection is not shared. I don't know why State table cannot be affected, even if TransactionScope covers only logic connected with Import table.

How to preserve consistent import and allow periodic status updates ?

Answer 1

Use TransactionScopeOption.Suppress in UpdateImportState instead of TransactionScopeOption.RequiresNew