[Regression][Perf][Linux] TransactionScope and Connection pool problems on Linux

[Ghyath-Serhal]

I have a business application that uses TransactionScope. The application runs on Windows and we are migrating it to dotnet core in order to work Linux.

I have 2 problems on Linux environment:
1- TransactionScope is not supported
2- The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

We tried to use dotnet core 2.0 and system.Data.SqlClient Version 4.5.0-preview1-26216-02. The Connection pool problem is solved but the TransactionScope problem is not solved. (MSDTC problems, this platform does not support Distributed Transaction)

We tried to use dotnet core 2.1 and System.Data.SqlClient Version 4.5.0-preview2-26228-08. The TransactionScope is solved but the connection pool problem is not solved.

Thank you for your support.

[jimcarley]

Ghyath-Serhal,

When you say "TransactionScope is not supported", you are referring to the fact that SqlClient does not utilize Transaction.Current, correct?

Also, I am not sure what you mean by the "connection pool problem". When you say that System.Data.SqlClient Version 4.5.0-preview1-26216-02 solved the connection pool problem, do you mean that you are no longer seeing timeouts, even when the all the pooled connections were in use and the max pool size was reached? Or are pooled connections being "released" when they weren't before?

Both of these issues are really with System.Data, not System.Transactions. I have added the tag for area-System.Data.SqlClient.

Jim

[saurabh500]

What is the connection pool problem? Promotion to MSDTC is not supported by Sys.Transactions, so SqlClient cannot promote either.

Can you post a repro for the connection pool problem?

[jimcarley]

It is definitely true that transaction promotion to MSDTC is not supported in .NET core. However, it is my understanding that there was a time where SqlClient did not utilize Transaction.Current, leading to the "TransactionScope is not supported" issue. I made the assumption that this is what was being referenced because Ghyath-Serhal indicated that "dotnet core 2.0 and system.Data.SqlClient Version 4.5.0-preview1-26216-02" solved the TransactionScope problem.

If the "TransactionScope problem" really is that distributed/promoted transactions are not supported, then no version is yet available to solve that problem.

[atiato]

Hi,
I will support as well in this as well:
Transaction scope problem (As per my understanding it is not supported but the version -dotnet core 2.1 and System.Data.SqlClient Version 4.5.0-preview2-26228-08 don't show below exception).

Time: 10:17:38.6822967 ------- Message: MSDTC on server 'GHAYATHSERHAL' is unavailable. ------- Inner Exception: ---------- Stack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest) at System.Data.SqlClient.TdsParser.GetDTCAddress(Int32 timeout, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.GetDTCAddress() at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx) at System.Data.ProviderBase.DbConnectionPool.PrepareConnection(DbConnection owningObject, DbConnectionInternal obj, Transaction transaction) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)

for pooling we have WebAPI that open and close connection to MSSQL , then we execute performance testing on that WebAPI while monitoring the DB , it shows that it opens only one connection and raise the following error after twenty hit to WebAPI:

Message: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections we
re in use and max pool size was reached. ------- Inner Exception: ---------- Stack Trace: at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection,
TaskCompletionSource1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource1 retry, DbConnection
Options userOptions)
at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)

Note that the correct behavior of the SQL Client is to open connection per instance of the Web API . the version of the SQL Client 4.5.0-preview1-26216-02 is working fine and we didn't see that pooling exception and it opens a connection per instance of the Application.

thanks,

[saurabh500]

SqlClient support TransactionScope in the 4.5.0-preview* versions. It was not supported in 4.4.0 which came out the same time as .Net Core 2.0

@atiato

As per my understanding it is not supported but the version -dotnet core 2.1 and System.Data.SqlClient Version 4.5.0-preview2-26228-08 don't show below exception)

TransactionScope for SqlClient is supported, but it cannot transaction promotion to MSDTC. Are you opening multiple connections in the same transaction scope in the repro that you have?

From what I understand you are not facing any problems with connection pool and while using transaction scope you are getting an exception related to MSDTC promotion. Is this correct?

[atiato]

@saurabh500
When we are using dotnet core 2.1 + System.Data.SqlClient Version 4.5.0-preview2-26228-08 , the transaction scope is fine but we are facing the pooling issue after performing a performance test.

while dotnet core 2.0 + SQL Client 4.5.0-preview1-26216-02 raise the MSDTC issue while pooling is working fine we didn't see above pooling exception.

all using same code.

if we are using same connection string to same DB in many places in the code while using transaction scope , do the SQL Client and Dotnet Core escalate it to MSDTC ?

[saurabh500]

@atiato Can you give me a repro for Connection Pool issue? I would like to understand this better.

if we are using same connection string to same DB in many places in the code while using transaction scope , do the SQL Client and Dotnet Core escalate it to MSDTC ?

If you have multiple connections being opened to the same database under the same transaction scope, that is considered a promotion and will need to us MSDTC and hence cause a failure in .Net Core.
Refer:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/system-transactions-integration-with-sql-server

Snippet

The benefit of using promotable transactions is that when a connection is opened by using an active TransactionScope transaction, and no other connections are opened, the transaction commits as a lightweight transaction, instead of incurring the additional overhead of a full distributed transaction.

[atiato]

when using

I got

the following code is used

    string connectionString = "Password=sasql1$$;Persist Security Info=True;User ID=sa;Initial Catalog=mea;Data Source=tcp:192.168.0.6";

    public void GetData()
    {
        TransactionOptions transOption = new TransactionOptions();
        transOption.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;

        using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, transOption))
        {
            try
            {
                ReadData();
                InsertData();
                ReadData();
            }
            catch (Exception ex)
            {
                FileWriter.LogException(ex);
                scope.Dispose();
            }
            scope.Complete();
        }
    }

but when using :

we got :

[saurabh500]

@atiato I am more interested in knowing what is going on inside

                ReadData();
                InsertData();
                ReadData();

[saurabh500]

Basically I want to see the code from System.Data.SqlClient that is being executed.

[atiato]

public void ReadData()
{
    try
    {
        List<EmployeeWorkHour> lst = new List<EmployeeWorkHour>();

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT TOP 50 * FROM dbo.EmployeeWorkHour WHERE PersonID = '81d8e09f-ce98-45fa-a311-ca7d7deccb7d'", conn))
            {
                conn.Open();
                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        EmployeeWorkHour employeeWorkHour = new EmployeeWorkHour();
                        employeeWorkHour.EmployeeWorkHourID = reader["EmployeeWorkHourID"].ToString();
                        employeeWorkHour.PersonID = reader["PersonID"].ToString();
                        employeeWorkHour.AttendanceTypeID = reader["AttendanceTypeID"].ToString();

                        lst.Add(employeeWorkHour);
                    }

                    FileWriter.WriteToFile("Success Read...");
                }
            }
            conn.Close();
        }
    }
    catch (Exception ex)
    {
        FileWriter.LogException(ex);
    }
}



public void InsertData()
{
    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand("insert into AccessGroup values(NEWID(), 'TestAccessGroup')", conn))
            {
                conn.Open();
                cmd.ExecuteNonQuery();
                conn.Close();

                FileWriter.WriteToFile("Read success...");
            }
        }
    }
    catch (Exception ex)
    {
        FileWriter.LogException(ex);
    }
}

[saurabh500]

Do you have any Connection Pool related connection string parameters? Like Min Pool Size, Max Pool Size ?
Can you provide your connection string without the server name, uid and pwd?

[atiato]

string connectionString = "Password=XXXXXXX;Persist Security Info=True;User ID=XXXX;Initial Catalog=mea;Data Source=tcp:XXXXXXXXX";

[atiato]

after 30 hits it raises the pool issue while SQL Client 4.5.0-preview1-26216-02 has no issue in that.

[saurabh500]

Thanks for all the information. I will investigate the issue and get back to you.