C# Multithreading transactions causes exceptions

Question

I am trying to simulate a case we're facing where an insert statement is executed in a multi threaded process and causing exceptions,

class Program
{
    private readonly static string[] names = { "name1", "name2", "name3", "name4" };

    private const string CreateQuery = @"DROP TABLE IF EXISTS name_multi_thread_test CASCADE;
                                        CREATE TABLE name_multi_thread_test (name VARCHAR(20));";

    private const string InsertQuery = @"INSERT INTO name_multi_thread_test VALUES('{0}');";

    private const string SelectQuery = "SELECT * FROM name_multi_thread_test;";
    static void Main(string[] args)
    {
        var warehouseHelper = new WarehouseHelper();

        try
        {
            System.Threading.Tasks.Parallel.ForEach(names, name =>
            {
            //foreach(var name in names)
            //{
                for(int i = 0; i < 10; i++)
                {
                    warehouseHelper.BeginTransaction();

                    warehouseHelper.ExecuteNonQuery(CreateQuery);
                    warehouseHelper.ExecuteNonQuery(string.Format(InsertQuery, name));

                    using (var reader = warehouseHelper.ExecuteReader(SelectQuery))
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(reader["name"]);
                        }
                    }

                        warehouseHelper.CommitTranzaction();
                }
            }//;
            );
        }
        catch(Exception ex)
        {
            Console.Write(ex.Message);
        }
    }
}




class WarehouseHelper
{
    private IDbConnection _transactionConnection;
    private IDbTransaction _transaction;
    public void ExecuteNonQuery(string commandText)
    {
        var connection = GetConnection();

        using (var command = connection.CreateCommand())
        {
            command.Transaction = _transaction;
            command.CommandText = commandText;
            command.ExecuteNonQuery();
        }
    }

    public IDataReader ExecuteReader(string commandText)
    {
        var connection = GetConnection();

        using (var command = connection.CreateCommand())
        {
            command.Transaction = _transaction;
            command.CommandText = commandText;
            return command.ExecuteReader();
        }
    }

    public void BeginTransaction()
    {
        _transactionConnection = ConnectionManager.CreateConnection();
        _transactionConnection.Open();
        _transaction = _transactionConnection.BeginTransaction();
    }
    public void CommitTranzaction()
    {
        _transaction.Commit();
        _transactionConnection.Close();
        _transaction = null;
        _transactionConnection = null;
    }

    private IDbConnection GetConnection()
    {
        if(_transactionConnection != null)
        {
            return _transactionConnection;
        }else
        {
            var connection = ConnectionManager.CreateConnection();
            connection.Open();
            return connection;
        }
    }
}



    class ConnectionManager
{
    private static string _connectionStringOdbc = "Driver={Vertica};SERVER=x.x.x.x;PORT=5433;DATABASE=mydb;UID=username;PWD=password;";
    public static OdbcConnection CreateConnection()
    {
        return new OdbcConnection(_connectionStringOdbc);
    }
}

so basically, for every name in names I begin a transaction, create a table, insert some entries, and read them and this is repeated ten times sequentially in a for loop, what happens is that the first iteration runs fine until the execution reaches CommitTranzation, in this example we have 4 threads, once the first thread sets _transaction to null and the next thread tries to execute _transaction.commit() I get a NullReferenceException

Object reference not set to an instance of an object.

        public void CommitTranzaction()
       {
        _transaction.Commit();
        _transactionConnection.Close();
        _transaction = null;
        _transactionConnection = null;
       }

as if it's the same instance of _transaction and _transactionConnection for all the 4 threads, I know we're missing something here in handling Multi threading but what is it, for now to unblock myself I moved the warehouseHelper.BeginTransaction(); statement outside the Parallel execution, so now it's directly after the try{ and the warehouseHelper.CommitTranzaction(); statement is at the end of the try{ block outside the Parallel execution also, what is the right approach here.

Answer 1

Open the database connection inside the thread and start the transaction before the loop and commit after the loop ends.

static void Main(string[] args)
{
    try
    {
        System.Threading.Tasks.Parallel.ForEach(names, name =>
        {
            var warehouseHelper = new WarehouseHelper();
            warehouseHelper.BeginTransaction();

            for(int i = 0; i < 10; i++)
            {
                warehouseHelper.ExecuteNonQuery(CreateQuery);
                warehouseHelper.ExecuteNonQuery(string.Format(InsertQuery, name));                
            }

            using (var reader = warehouseHelper.ExecuteReader(SelectQuery))
            {
                while (reader.Read())
                {
                    Console.WriteLine(reader["name"]);
                }
            }

            warehouseHelper.CommitTranzaction();
        }
        );
    }
    catch(Exception ex)
    {
        Console.Write(ex.Message);
    }
}

Answer 2

The WarehouseHelper instance is common for all the threads and when the first thread call CommitTranzaction then it sets the _transaction variable to null and when the other threads calls _transaction.Commit() they throw NullRefernceException I think warehouseHelper.CommitTranzaction() should be outside Parallel.ForEach loop.