Transactions in SQL Server Database

  

Hello guys,


I was checking the topic "Transactions" in the Outsystems forum and I found the following topic that was created some time ago:


https://www.outsystems.com/forums/discussion/2830/transactions-in-the-outsystems-platform/


The latest documentation version from Outsystems clarifies that, taking into account SQL Server Database, for each write within the same request (excluding background processes, emails, external DB, etc (different requests)) there is one transaction which ends in a commit / rollback - making possible to commit or rollback all DML changes. It also clarifies that, for each read, there is one transaction, not making any distinction regarding Aggregates / Advance queries.


"You have multiple transactions per web request: one for writes, one for each read."


However, in the previous post, Acácio said:

"An additional clarification regarding SQL Server / MySQL. When Rodrigo indicates "One transaction is dedicated for the writes to ensure commit and rollback atomicity. Additional transactions are opened for the reads, depending on what is needed by the current request", instead of reads you should interpret as primitives built for reading data: Get<Entity> and Get<Entity>ForUpdate; Simple Queries/Aggregates; Advanced Queries.

A particularly relevant scenario is advanced queries - which you might be using to do inserts or updates - these will run in a separate transaction. In particular scenarios, this might cause locking : imagine you run a Get<Entity>forUpdate and then update the record in an advanced query; this would cause a lock and not be possible."


As I found this behavior critical and strange (E.g. We want to rollback the total write transaction, and there are multiple "write" transactions in the same flow that will not be affected), We decided to implement a quick test related to the transactions topic.


The following images shows the complete Transaction, with the respective flow in Outsystems (there is a process in the middle associated with the same table but it's not relevant).


Outsystems Server actions


Profiler (Attached)


The conclusions are:

 - The transaction opened is only one for Writes and Reads (within the same request); - If it depends, please provide some explanation so we can have a clear technical decision;

- Advanced queries uses the same transaction as the previous (Write and Read, and doesn't act  differently between gets/aggregates/adv queries); If it depends, please provide some explanation so we can have a clear technical decision;


I believe this is an important topic to clarify the actual implementation status.

Hope to have some updated feedbacks.


Thank you in advance.


Kind regards

Miguel Sousa

Hi Miguel,

Thanks for the effort in providing all that context!

I asked around and no one knows "by heart" in which scenarios we use different read and write transactions, so I'll try to clarify the implementation status and reply back when I have an answer.

Regards,

Paulo Ferreira

Hi Paulo,


Thanks for your feedback and effort in trying to clarify the topic.

Take your time. 


Kind regards,

Miguel

Solution

Hi Miguel,

Now that I've gathered more context, let me try to answer your questions.

As a rule of thumb, the same transaction is used for all the platform generated queries during a request, regardless if they are performing reads or writes.

However, for Aggregates and Advanced Queries whose results are iterated once (i.e. used in a For Each, Table Records, etc.), a separate transaction is used. The motivation behind it is to optimize the retrieval of the results, by doing it lazily instead of reading the full result upfront, and that requires using a separate transaction in SQL Server.

In the case of Advanced Queries (and here's the catch), this occurs regardless if the query is performing reads or writes. As long as the result is iterated once, the platform assumes we're using the query to read something and applies the optimization. Consequently, changes performed by that specific Advanced Query cannot be rolled back afterwards.

In the case of your test example, adding a For Each that iterates the TestTable2_Insert query will force a separate transaction to be used.

So, in terms of transactionality, the important take away is: results of Advanced Queries which perform writes shouldn't be iterated. You can still access List.Current, List.Empty, and the remaining list properties safely.

For reference, the platform doesn't perform this optimization for small numbers in Max Records, or if we detect the query is iterated multiple times. I wouldn't recommend keeping these rules in mind, as they are quite specific, but they could be useful to explain a few exceptions during a profiling session.

Regards,

Paulo Ferreira

Solution

Hey!


This is very important to understand! It's easy to fall into the pitfalls explained above and having this information readily available is awesome.

I was left wondering if there's any difference to Oracle or MySQL, since only SQL Server is mentioned. Can you clarify Paulo?

@PauloFerreira, when you mention same or new "transaction" you're talking about a "connection" + "transaction", since, AFAIK, SQL Server's nested transactions basically don't work.

That means that for every new iteration of a query with no max records, of a new connection to the db is established. Am I correct?

If that's the case I have the same question as @PedroCardoso. Is there a change if we are talking of Oracle for example? Again, AFAIK, In Oracle nested transaction are actually a working thing, so I'm wondering if that changes the behavior.

Thnks. 




Hi,

For Oracle everything always runs in the same transaction, as the driver supports having multiple active queries. But it's the exception, as MySQL has the same behavior as SQL Server.

Carlos, regarding your first question, yes I meant a transaction + connection. But the criteria to trigger a new connection is more fine grained:

  • The query result is iterated exactly once
  • Max Records is either unspecified or isn't a small number of records

Still, I'll reinforce that I wouldn't recommend having these specific rules in mind, as they are difficult to track. For example, a query with unspecified Max Records in design-time may actually have it inferred by the compiler from a Table Record's Line Count (if it's only used there, etc.), and thus not trigger a new connection.

Regards,

Paulo Ferreira

Awesome Paulo, thanks for the reply

Hi Paulo,


Thank you so much for your reply.


Important conclusions

- Choosing between Advance Query or Aggregate, is irrelevant in terms of Platform Transactions decisions.

- Reads and Writes are executed in the same Transaction. Exceptions can be made in some uncommon situations that I will try to expose later. 

The sentences above are true for SQL Server or other DB's.


It's important to update the documentation about SQL Server transactions:

"When using a MySQL or SQL Server database, you are working at Read Uncommitted isolation level. You have multiple transactions per web request: One for writes, one for each read." - From https://success.outsystems.com/Documentation/10/Reference/Data/Database_Reference/Handling_Transactions"


OutSystems Platform - Transactions

We also explored a little bit more about the topic in the last days and will add some extra detailed information for the ones that want to go a little bit deeper on how it works.


OutSystems takes the decision to choose between the Main transaction or a different one regarding two conditions:

  • The Max records -  Property present in the Query (Aggregate or Advance Query);
  • The Multiplicity - Options can be: Never, Single or Multiple. Like Paulo said, this is related to Iteration: 'Never' iterated, one time (single) iterated or iterated more then 1 time (multiple).


Using Main transaction

The main transaction will be used when the query executed has:

  • MaxRecords Between 1 and 150 
  • OR 
  • Multiplicity Different from Single;

bool useMainTransaction = (multiplicity != IterationMultiplicity.Single || (maxRecords > 0 && maxRecords <= 150));

If the Multiplicity is "Never" (Never iterated), the Max Records will be assigned to 1.

Affecting the MaxRecord with 1, the Platform will optimize the Aggregates including a TOP 1 in the executed SQL.

If (multiplicity == IterationMultiplicity.Never) {     maxRecords = 1;}

Assign List: Assigning a List Variable with the List from the Query, will cause a "Multiple" multiplicity. 

Assign Current: Assigning a Current Record from the Query, will be considered "Never" multiplicity.

ListAppendAll: Considered Multiplicity 'Multiple' (will use the main transaction).

ListAppend: Considered Multiplicity 'Never' (will use same transaction).


The final decision to use the main transaction or a new one (the "read" transaction) depends on the following code line:

Transaction trans = useMainTransaction ? DatabaseAccess.ForCurrentDatabase.GetRequestTransaction() : DatabaseAccess.ForCurrentDatabase.GetReadOnlyTransaction();


Quick Example:
Preparation Screen Action (Page Load)



In this example, Multiplicity will be Single, because the Table Record is iterating the result from the Aggregate present in the Preparation. Because the Linecount Property from the Table Record is filled with 50 (default), the following condition will be true and the main transaction will still be used:

(maxRecords > 0 && maxRecords <= 150)

If, in the same example, the Max Record (TR LineCount) is not filled or is filled with more then 150, another transaction will be used because none of the conditions are true.


Using Different Transaction

Imagine that, for some reason, TestTable1_UpdateAndGetById has an Update and a Select inside of it (Example below).


In this case, a new Transaction will be created because it uses a Single Multiplicity and no Max Records Property assigned. Everything that was done inside this transaction will not be possible to rollback (only if the query fails to execute). The 'AbortTransaction' action will only rollback the main transaction. But, as you can see, this is not a common scenario and also not an OutSystems best practice that someone should follow. 


Another important thing is that everything inside the loop (of course not iterated Objects) will use the main transaction and not the new one.


Why this doesn't have almost impact?

Most of the times we Iterate Queries that only Selects data. New transactions will not have any impact in terms of reads because we are using Dirty Reads that will access different and not only committed transactions. Having that in mind, we can access the last (even if not committed) data. 


Oracle

Oracle, has an "optimistic" view of concurrency by default. When in the middle of a transaction, Oracle will have readers see a version of the row as it existed at the start of the update transaction, thus not blocking readers. Because of this, it would not be possible to create different transactions to read data from the main transaction since you would not get last DML changes made on the same flow.


Hope I could also help :)


Kind regards,

Miguel Sousa

Now that's a clear answer that really opened my mind.

Thanks Miguel Sousa.

Thanks for your explanations Paulo and Miguel, but I actually still have a question regarding those new transactions (SQL Server). 

So, let's say that I have an aggregate with max records = 10.000, this will open a new transaction, right? Based on

"Using Main transaction

The main transaction will be used when the query executed has:

  • MaxRecords Between 1 and 150"

So my question is: When this newer transaction will be closed? After reaching the END node (closes the main + newer transaction)? 

Thanks.

Best regards,
Fernando Branco