How Does: ScaleArc work with 'Set Implicit Transaction' ?
How does ScaleArc understand 'Set Implicit Transaction' ON and how does it respond.
SET IMPLICIT_TRANSACTIONS ON
- When an application sends 'SET IMPLICIT_TRANSACTIONS ON' through ScaleArc to the database server
- ScaleArc will check the next query coming in as part of that connection to Check if it's a DML or DDL
- If at that point for that connection if '@@TRANCOUNT=0' than any of the following Transact-SQL statements begins a new transaction. It is equivalent to an unseen BEGIN TRANSACTION (Explicit Transaction) being executed first
- ScaleArc send's the response back to the client for this query, with the transaction descriptor in the response.
- Else if they do not suffice the conditions to start a transaction, then transaction descriptor is not sent in the response.
- 'IF (@@TRANCOUNT > 0) COMMIT;' would commit all queries within this transaction
- If for instance additional 'BEGIN TRANSACTION' were issued within this Transaction then you must 'COMMIT TRANSACTION' until @@TRANCOUNT is decremented back down to 0
- 'SET IMPLICIT TRANSACTION OFF:' would close out transaction which was started with 'SET IMPLICIT TRANSACTION ON:'
- Any queries following 'IF (@@TRANCOUNT > 0) COMMIT;' would start a new transaction until 'SET IMPLICIT TRANSACTION OFF:' is seen
Prepare a table using below commands
Create test_table (col1 int, col2 int);
Insert into test_table values (1,2);
Consider below Traffic pattern
SET IMPLICIT TRANSACTION ON;
SELECT @@servername; -- Since, this query selects a variable It will not start a transaction.
SELECT * from test_table; -- This will start a transaction on the server, but this particular query is not in a transaction.
SELECT * from test_table; -- This query is in a transaction, and will come with a transaction descriptor, and will be detected in ScaleArc.
IF (@@TRANCOUNT > 0) COMMIT; -- Commit if any transaction was started.
SET IMPLICIT TRANSACTION OFF:
Since response parsing is not performed in ScaleArc, it is dependent on the next request to find if the query is in a transaction.
There are several other clarifying points which are explained in Microsoft's SET IMPLICIT TRANSACTION documentation as below