AutoQuery seems to use autocommit in transaction(MSSQL)

ServiceStack Auto Query
1

I have wrapped an AutoQuery.Create and AutoQuery.Update inside a transaction as shown below (simplified):

 using var db = AutoQuery!.GetDb<Currency>(base.Request);   

 using (var trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
    {
        try
        {
          foreach (var newValue in newCurrencies)
          {
            AutoQuery.Create<Currency>(newValue, base.Request);
          }

          foreach (var value in activeCurrencies)
          {
            AutoQuery.Update<Currency>(value, base.Request);
          }
          
          trans.Commit();             // all Create and Update should be committed
        }
        catch (Exception)
        {
            trans.Rollback();         // all Create and Update should be rolled back
            throw;
        }
    }

When I simulate an ServiceStack.Data.OptimisticConcurrencyException, I would like all db changes to be rolled back. However, each AutoQuery execution in the foreach loop is being auto committed, up to the point when the exception is thrown.

Is this correct behaviour? Or have I made a mistake?

2

If you’re not passing the db connection down to a lower API, that API will not be able to use the same db connection (or transaction), so AutoQuery is resolving and using its own db connection.

If you’re not using a named connection AutoQuery will resolve its db connection from AppHost.GetDbConnection() which you can override in your AppHost, so if you populate a DB connection in your request context items, e.g:

req.Items["DB"] = db;

You could get all db connections in that request to use the same db connection with something like:

public override IDbConnection GetDbConnection(IRequest req = null) =>
    req.GetItem("DB") ?? base.GetDbConnection(req);

You’ll likely want to call req.Items.Remove("DB") after you’ve finished with it so anything else that tries to use a db in that request uses the normal resolution rules.

3

In my service implementation, I use the line below to get the db (I updated my previous code snippet):

using var db = AutoQuery!.GetDb<Currency>(base.Request);

Does that not “link” the transaction and db for the AutoQuery commands?

4

No there is no magic, the db connection you resolve is only accessible to anything using it, which the APIs you’re calling (without it) aren’t.

My previous comment describes how you can override the AppHost.GetDbConnection resolution behavior to return the same connection.

5

FYI in the latest 5.10.3 now available on MyGet each AutoQuery CRUD API has an optional db param you can use to pass your current db connection which should make this easier.

6

Brilliant, thank you.

7

I updated my previous code and now pass the db as a parameter to the AutoQuery commands:

using var db = AutoQuery!.GetDb<Currency>(base.Request);   

using (var trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
  {
    try
    {
      foreach (var newValue in newCurrencies)
      {
        AutoQuery.Create<Currency>(newValue, base.Request, db);
      }

      foreach (var value in activeCurrencies)
      {
        AutoQuery.Update<Currency>(value, base.Request, db);
      }
      
      trans.Commit();             // all Create and Update should be committed
    }
    catch (Exception)
    {
        trans.Rollback();         // all Create and Update should be rolled back
        throw;
    }
  }

However, when testing (with the creation of 3 new currencies), I get an exception on the first call of AutoQuery.Create

"SqlConnection does not support parallel transactions."

as it appears that a transactions is being opened.

I thought that allowing me to pass my db as a parameter, would allow my initial transaction to be used in the AutoQuery commands. Am I misunderstanding the concept?

I need all of the foreach calls to AutoQuery.Create and AutoQuery.Update to be inside a single transaction, because the set of changes are all or nothing.

8

Can you try with the latest 5.10.3 that’s now on MyGet, you’ll need to clear your NuGet packages cache:

$ nuget locals all -clear
9

I will try that now, thank you for taking the time.

10

I see no difference in the generated TS, how can I tell I have the latest 5.10.3?

11

Okay, I have the latest 5.10.3 (I had to restart VS for some reason.

The latest ServiceStack no longer gives the parallel transactions exception and the rollback/commit of the initial transaction seems good. I will perform some additional testing, but it looks great to me.

Thank you very much for the excellent support.

12

Performing multiple create and updates is working fine, with all being committed or rolled back as expected.

When I expand my code to also perform deletes (soft-delete followed by hard-delete), I get an exception:

{"Data modification failed on system-versioned table 'QQL.Cryptyo.Currency' because transaction time was earlier than period start time for affected records.\r\nThe statement has been terminated."}

The deletion is done as follows:

foreach (var id in missingIds)
{
    // step 1.1: soft delete prerequiste 
    var softDeleteResponse = (DeleteResponse)AutoQuery.PartialUpdate<Currency>(new DeleteCurrency { Id = id }, base.Request, db);

    // step 1.2: continue with requested hard delete
    var useForDelete = new HardDeleteCurrency { Id = id };
    var hardDeleteResponse = (DeleteResponse)AutoQuery.Delete<Currency>(useForDelete, base.Request, db);                            
}

I am using MSSQL’s System Versioned tables, so this might be unrelated, although performing soft/hard delete has been working when dealing with a single row.

13

Okay, my delete problem seems to be related to how MSSQL handle temporal (System Versioned) tables.

10/10 to ServiceStack.

For reference, the link below explains the use of the transaction time in the SysStartTime column when updating is the

I have solved the issue, by performing any required hard-deletes after committing the initial transaction containing the soft-deletes. This way, the temporal tables do not complain about the transaction start time being earlier.