Internal connection fatal error on SQL Transaction when running multiple threads

Question

I am writing an application which does multiple tests for some devices at the same time. After testing I want to add the results to a database. I have 3 Tables in the database: Testsequence, Maintest, Subtest to link the data I have to know the id of the "parent" table object

But when I run more than 1 task I get: {"Internal connection fatal error."} on sequenceId = Convert.ToInt32(sqlcommand.ExecuteScalar());

At the Moment I am using a local database with Microsoft SQL Server (using System.Data.SqlClient;)

public static void AddResults(Device device)
    {
        using (var connection = new SqlConnection(Settings.DBConnectionString))
        {
            SqlTransaction transaction;
            transaction = _sqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);

            try
            {
                SqlCommand sqlcommand = _sqlConnection.CreateCommand();
                sqlcommand.Connection = _sqlConnection;
                sqlcommand.Transaction = transaction;

                // Add Testsequence
                sqlcommand = new SqlCommand(@"INSERT INTO TestSequence (IdMachine,DateTime) VALUES (@IdDevice,@DateTime) SELECT SCOPE_IDENTITY()", _sqlConnection, transaction);
                {
                    sqlcommand.Parameters.Add(new SqlParameter("@IdDevice", device.DataBaseId));
                    sqlcommand.Parameters.Add(new SqlParameter("@DateTime", device.Progress.ResultActive.Time));
                }
                sequenceId = Convert.ToInt32(sqlcommand.ExecuteScalar());

                foreach (TestCategory category in device.Progress.ResultActive)
                {
                    foreach (MainTest mainTest in category)
                    {
                        // Add MainTest
                        if (mainTest.Time == null) continue;
                        sqlcommand = new SqlCommand(@"INSERT INTO MainTests (IdSequenceMT,IdTestNameMT,DateTimeMT,Passed) VALUES @IdSequenceMT,@IdTestNameMT,@DateTimeMT,@Passed) SELECT SCOPE_IDENTITY()", _sqlConnection, transaction);
                        {
                            sqlcommand.Parameters.Add(new SqlParameter("@IdSequenceMT", sequenceId));
                            sqlcommand.Parameters.Add(new SqlParameter("@IdTestNameMT", mainTest.Id));
                            sqlcommand.Parameters.Add(new SqlParameter("@DateTimeMT", mainTest.Time));
                            sqlcommand.Parameters.Add(new SqlParameter("@Passed", mainTest.Pass));
                        }
                        mainTestId = Convert.ToInt32(sqlcommand.ExecuteScalar());

                        foreach (SubTest subTest in mainTest)
                        {
                            // Add SubTest
                            if (subTest.Pass == null) continue;
                            sqlcommand = new SqlCommand(@"INSERT INTO SubTests
                            (IdMainTestST,IdTestName,DateTimeST,Passed,Log_enUS,Log_deDE) VALUES (@IdMainTestST,@IdTestName,@DateTimeST,@Passed,@Log_enUS,@Log_deDE)",
                                _sqlConnection, transaction);
                            {
                                sqlcommand.Parameters.Add(new SqlParameter("@IdMainTestST", mainTestId));
                                sqlcommand.Parameters.Add(new SqlParameter("@IdTestName", subTest.Id));
                                sqlcommand.Parameters.Add(new SqlParameter("@DateTimeST", subTest.Time));
                                sqlcommand.Parameters.Add(new SqlParameter("@Passed", subTest.Pass));
                                sqlcommand.Parameters.Add(new SqlParameter("@Log_enUS", subTest.Log));
                                sqlcommand.Parameters.Add(new SqlParameter("@Log_deDE", subTest.Log));
                            }
                            sqlcommand.ExecuteNonQuery();
                        }
                    }
                }
                if (connection.State != System.Data.ConnectionState.Open) connection.Open();
                transaction.Commit();
                log.Info($"SQL {device.Serialnumber}|{device.DataBaseId}: Testsequence Id: {sequenceId} added");
            }
            catch (Exception e)
            {
                transaction.Rollback();
                log.Info($"SQL : {device.Serialnumber} Testsequence add: transaction rollback\n{e}");
                Helper.UiMessage($"{device.Serialnumber} {Project.Properties.Resources.results_not_added}", 60);
            }
        }
    }

What I tried:

Before I had a global variable for the connection.. which wasn't the issue
I did not use the using statement... anyhow this wasn't the issue, too

Maybe you see that my code is not the best :D If you have any other ideas how to improve please also write a comment.

I also don't know if the problem resides in multithreading, the query-command or the execution of multiple commands in the transaction.

Thanks in advance :)

As I understood a transaction is to put "more querys" in one batch to transfer to the server => I want to keep the network traffic as low as possible. If I can only use a single command, how can I rewrite my code... and why does it work when executed with a single device? I Also need the id of the parent => Scope_identity which is used in de next command... — Christoph Lange, Aug 17 '21 at 07:55
You are still using your global `_sqlConnection` to create the transaction and execute the commands. Switch it to the local variable `connection`. Also `transaction` should be in a `using` also. See also https://stackoverflow.com/questions/17552829/c-sharp-data-connections-best-practice — Charlieface, Aug 17 '21 at 09:15
Also the first three lines after `try` seem pointless as `sqlCommand` is overwritten. Transactions do not lower network traffic, they ensure a set of statements is made atomically. To minimize traffic, and more importantly make this perform better, you can use a Table Valued Parameter to do a bulk insert — Charlieface, Aug 17 '21 at 11:56

score 0 · Answer 1 · answered Aug 17 '21 at 13:36

0

... The problem was in front of the screen... -.-

The old _sqlConnection was a remain from the previous "version".

changed _sqlConnection to connection
added missing using statement for transaction

Thanks @Charlieface

answered Aug 17 '21 at 13:36

Christoph Lange

37
3

Internal connection fatal error on SQL Transaction when running multiple threads

1 Answers1