Using Db property in SS services; disposed?

ServiceStack.OrmLite
1

Hi

Recently I had issues when using a SS based api under great load. I received an error about DB timeout because of too many connections

Timeout expired.  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.

When investigating this, I checked I had usings everywhere I was using the Database, but then I saw that using the Db property on a Service class was just giving:

return this.db ?? (this.db = HostContext.AppHost.GetDbConnection(this.Request));

and this in turn:

    public virtual IDbConnection GetDbConnection(IRequest req = null)
    {
      IDbConnectionFactory connectionFactory = this.Container.TryResolve<IDbConnectionFactory>();
      ConnectionInfo connectionInfo;
      if (req != null && (connectionInfo = req.GetItem(Keywords.DbInfo) as ConnectionInfo) != null)
      {
        IDbConnectionFactoryExtended connectionFactoryExtended;
        if ((connectionFactoryExtended = connectionFactory as IDbConnectionFactoryExtended) == null)
          throw new NotSupportedException("ConnectionInfo can only be used with IDbConnectionFactoryExtended");
        if (connectionInfo.ConnectionString != null && connectionInfo.ProviderName != null)
          return connectionFactoryExtended.OpenDbConnectionString(connectionInfo.ConnectionString, connectionInfo.ProviderName);
        if (connectionInfo.ConnectionString != null)
          return connectionFactoryExtended.OpenDbConnectionString(connectionInfo.ConnectionString);
        if (connectionInfo.NamedConnection != null)
          return connectionFactoryExtended.OpenDbConnection(connectionInfo.NamedConnection);
      }
      return connectionFactory.OpenDbConnection();
    }

So that means it just gives you an OpenDbConnection of the factory but in no way disposing or closing it (or am I missing something).

The API is sending mails out, but then received all the callbacks; Only in the Callback code I am not using the using (IDisose) pattern so I think it might be the issue.

So my questions is, wouldn’t it be better to use IDbConnectionFactory in the constructor and then just use using (var db = DbConnectionFactory.OpenDbConnection()) ?

2

The db instance created in the Service is disposed of immediately after the Service is executed.

3

Ah ok… strange. Then I have to find further why my pooled connections are all used.

4

Do you have code that is frequently accessed, such as authentication?(Attribute)
If so, the data should be validated after the cache, and then use the data in the cache to verify.

I have had the same problem once more when it comes to verifying the license key.

If your database is MS SQL Server.
You can use the following sql statement to view the occupied information

SELECT
sp.spid
, sp.blocked AS BlockingProcess
, DB_NAME(sp.dbid) AS DatabaseName
, sp.loginame
, CAST(text AS VARCHAR(1000)) AS SqlStatement
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle)
where DB_NAME(sp.dbid) = 'YOUR DATABASE NAME'