Rolling multiple database operation in a Transaction

[henda79]

I'm looking for some advice on best practices for rolling multiple DB operations in to a transaction. I would have thought others would already use something like this as it's a common scenario right ?

So if I want to update across several tables, or insert or whatever and something fails for whatever reason, I would like to rollback the changes.

In my case this would be on the client in an offline situation.

What is the best way to go about this ?

[adrianhall]

This is not the right tool for that job. We don't support transactions at all.

[henda79]

There is no API method to return a list of the remote tables, and GetTablesAsync only returns tables that have been previously defined (i.e. exist in the SQL database).

Eventually, you have to define the tables you are using. There is just no way around it.

The tables are present in the offline Database, I just don't explicitly define them as they get defined when they are used, but if the tables are not "used" then the method does not return anything.

Anyway not a major problem for now, I might look at getting tables from the server at a later date which allows us to sync from a fresh install.

Regarding comment above about the batch inserts / updates, I've started on this but there is no way to use it from a higher level with in the app without changes to OfflineTable and SyncContext that is if we still want to use something like

await _client!
    .GetOfflineTable<T>()
    .InsertItemsAsync(items)
    .ConfigureAwait(false);

[adrianhall]

Correct - that's because not all stores have batching and transactions. There will never be transactions at the higher level.

[henda79]

I've been thinking about this and its a lot of work to get it to work, however a quick fix would be to update the code in the UpsertAsync method.

            using (operationLock.AcquireLock())
            {
                string savePoint = Guid.NewGuid().ToString("N");
                ExecuteNonQueryInternal($"SAVEPOINT '{savePoint}'");
                BatchInsert(tableName, items, columns.Where(c => c.IsIdColumn).Take(1).ToList());
                BatchUpdate(tableName, items, columns);
                ExecuteNonQueryInternal($"RELEASE '{savePoint}'");
            }

Using this would allow me to create a transaction higher up using ExecuteNonQueryInternal without hitting nesting issues.

What do you think ?

[adrianhall]

Savepoints have to be used within the context of a transaction. There is also no point to a savepoint if your batch doesn't rollback at some point. We'd have to thread the savepoint through the entire system (batch insert and batch update as well). I'm having a hard time understanding how this would be a useful change higher up in the code.

[henda79]

I was under the impression a savepoint created a transaction if none was explicitly created.

In my code, data is collected and is entered across several tables, so there is a risk of having orphaned data if something goes wrong. So what I do is:

List<Func<Task>> actions = new();

actions.Add(() => DataService.Insert(myDataForTable1));
actions.Add(() => DataService.Insert(myDataForTable2));
...

await DataService.RunInTransaction(actions).ConfigureAwait(false);
...
        public class OfflineSqlStore : OfflineSQLiteStore
        {
            public OfflineSqlStore(string connectionString) : base(connectionString)
            {
            }

            public async Task RunInTransaction(List<Func<Task>> actions)
            {
                ExecuteNonQueryInternal("BEGIN");

                try
                {
                    foreach (Func<Task> item in actions)
                    {
                        await item.Invoke().ConfigureAwait(false);
                    }

                    ExecuteNonQueryInternal("COMMIT");
                }
                catch
                {
                    ExecuteNonQueryInternal("ROLLBACK");
                    throw;
                }
            }
    }

This code did not work as you were also creating an explicit transaction in UpsertAsync however it seems to work when using SAVEPOINT.

I don't wrap everything in a transaction as some changes only hit one table, but in cases where I am inserting a lot of data over several tables then it would be mad not to use a transaction.

[amertlich]

@henda79 did you end up finding a workaround for this problem?

There is also no point to a savepoint if your batch doesn't rollback at some point

@adrianhall I think ☝️is true even with begin/commit transactions, which was addressed here: https://github.com/Azure/azure-mobile-apps/issues/749

I'm not seeing a reason why you couldn't substitute the BEGIN TRANSACTION here instead with a SAVEPOINT data_sync_upsert and switch COMMIT to RELEASE data_sync_upsert and then in the exceptional case you ROLLBACK TO SAVEPOINT data_sync_upsert rather than just ROLLBACK -- i.e like what @henda79 mentions above?

[henda79]

I maintain a local copy of the repository and made changes to the code as I outlined above, I don't see any sense in the way it was implemented originally but implementing as above allows me to rollback when things inevitably go wrong.