IAuthRepository and IDataRepository pattern

ServiceStack Authentication
1

More of a “best practice” question really, although I’m not quite sure how I managed to avoid this for so long,

I use a repository pattern for accessing all my main data, called IDataRepository. It’s setup to be reused per request, e.g

container.RegisterAutoWiredAs<DataRepository, IDataRepository>().ReusedWithin(Funq.ReuseScope.Request);

The class is fairly straightforward and takes the IDbConnectionFactory so it gets a database connection for its first instance per request.

public class DataRepository : IDataRepository
{
  public IDbConnectionFactory DbFactory { get; set; }

  public DataRepository(IDbConnectionFactory dbFactory)
  {
    DbFactory = dbFactory;
  }
  :
}

I’m also using the build-in IUserAuthrepository from OrmLiteAuthRepository

However. I’ve come across a case where I’m getting database timeouts during a REST call. The issue is simply that some SQL updates during some data repository methods are updating tables that also put a lock on the UserAuth table (it’s all in the same MySQL database). A subsequent call to IAuthRepository.SaveUserAuth() is then timing out.

OrmLiteAuthRepository creates its own DbConnection. So while I’m able to share the DatabaseFactory instance per request, I can’t share the connection. The second connection in the request times-out because of the database locks.

There are FOREIGN KEYs across the UserAuth tables and my own tables, which then really requires they use the same connection in an atomic update.

Is this approach just flawed?

Is there a way to make OrmLiteAuthRepository use an existing connection? Or should I not be using it at all?

Suggestions would be very helpful.

2

I’d say the issue is that you’re holding on the db connection for too long, RDBMS connection pools by default so there’s no reason for using Request scope, and should just use transient scope so it’s disposing of the connection when it’s no longer needed and made available sooner for reuse. DB access that needs to be atomic should be in a transaction, I definitely wouldn’t be writing code where different repositories require use of the same open db connection.