Dapper is crashing when load is increased

Question

I am using dapper in a payment processing application built on .net 5.0. In unit testing, everything was working fine, but when I used JMeter to put the load of 100 users, after processing few transactions, the dapper started crashing with the below error:

Exception[System.InvalidOperationException: Connection must be open for this operation at Oracle.ManagedDataAccess.Client.OracleCommand.ValidateStatePriorToExecution() at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteNonQuery()
at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action2 paramReader) in /_/Dapper/SqlMapper.cs:line 2822 at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in /_/Dapper/SqlMapper.cs:line 572 at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable`1 commandType) in /_/Dapper/SqlMapper.cs:line 443

The code for SP execution is below:

public static int ExecuteSP(string spName, object parameters)
{
    IDbConnection connection = ConnectionManager.GetConnection(_ConnectionString);

    int result = connection.Execute(spName, parameters, null, null, CommandType.StoredProcedure);

    ConnectionManager.CloseConnection();
    return result;
}

public static IDbConnection GetConnection(string connectionString)
{
     DefaultTypeMap.MatchNamesWithUnderscores = true;

     if (_oracleConnection == null)
     {
         _oracleConnection = new OracleConnection(GetConnectionString(connectionString));
     }

     if (_oracleConnection.State == ConnectionState.Closed)
     {
         _oracleConnection.Open();
     }
     return _oracleConnection;
}

Is there anything I am missing in configuration or use of Dapper?

Answer 1

The error here is using a static - and therefore shared - connection (even though it isn't shown, we can assume that _oracleConnection is static because it is accessed from the static method GetConnection).

Connections are not thread-safe, and by making it static, all your code is sharing a single connection. This tends to scale to exactly one user, i.e. one concurrent request - above that: failure is pretty much guaranteed.

Connections should be scoped for a logical piece of code - which could be anywhere between "a single method" to "an entire request", but they must only be accessed by one thread at a time. You should also ensure that connections are correctly disposed as they leave that scope, to prevent leaking connections.

(BTW: the problem isn't really related to Dapper; the exact same problems would occur for any similar usage - whether ADO.NET, Dapper, EF, or anything else)