Transaction Scope and Task.When all execution-CodePudding

Intention: speed up Sql insert operation with a transaction commit / rollback support

Have a pretty big amount of data (about 900 k entities)
Would like to put down the data to SQL table in parallel, gonna to split the data per connection (e.g. 3 active connections, thus 300 k per a connection)
Would like to have possibility to rollback if something goes wrong, so try to using Transaction Scope (nested-TransactionScopeOption.Required)

Issue:

It looks like Transaction scope couldn't support such kind of Task.WhenAll thus, if ever there will be an error during execution the intermediate writes won't be rollback

Q: So I've wondered if it can be work around, or it just not appropriate way to leverage the transaction scope ?

   public void Upsert(IEnumerable<IEnumerable<Item>> splitPerConnection, DateTime endDate)
    {
        using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
        {
            try
            {
                UpdateEndDate(endDate);

                var insertTasks = splitPerConnection.Select(ch => Task.Run(() => Insert(ch)));

                Task.WhenAll(insertTasks).GetAwaiter().GetResult();

                scope.Complete();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

    private int Insert(IEnumerable<Item> items)
    {
        int affectedRows;

        using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
        using (var sqlConnection = new SqlConnection(DockerMsSqlServerConnectionString))
        {
            sqlConnection.Open();
            affectedRows = sqlConnection.Execute(ItemsQueries.Insert, items, commandTimeout: 0);

            scope.Complete();
        }

        return affectedRows;
    }

    private int UpdateEndDate(DateTime endDate)
    {
        int affectedRows;

        using (var scope = _transactionScopeFactory.Create(TransactionScopeOption.Required))
        using (var sqlConnection = new SqlConnection(DockerMsSqlServerConnectionString))
        {
            sqlConnection.Open();
            affectedRows = sqlConnection.Execute(ItemsQueries.UpdateEndDate, new { EndDate = endDate }, commandTimeout: 0);

            scope.Complete();
        }

        return affectedRows;
    }

CodePudding user response：

You can try to leverage SqlBulkCopy, instead of splitting the items over different connection objects.

Insert 2 million rows into SQL Server quickly

You can create a dataset by modifying IEnumerable<Item>. All the parallel operations can be applied here to convert from IEnumerable<Item> to dataset. Once dataset is created, SqlBulkCopy will help you. You can create 3-4 sets of datasets and execute the insert query 3-4 times.

In this approach, you can maintain one database connection that also helps to follow ACID compliance of the database transactions.