My current app is a website, each client gets their own unique database in order to help prevent possible data access from other clients (and meet various legal requirements too.) One of the features of this application is that there is some core data (such as countries/currencies) which is maintained on behalf of the client. This data will be identical for all clients, and only maintained by a system administrator.
Obviously expecting the admin to log into each client to perform updates is unreasonable, so I have structured my app so that there is a separate Admin website which updates a master database and propagates the changes to all of the clients.
When the master website does an update it performs it within an IUnitOfWork, and then tells the same update command to execute for each client database too. If any of these updates fail for whatever reason then the whole thing needs to be cancelled whether one of the child databases updated or not.
using (var distributedTransaction = new Transaction())
{
var validationErrors = PerformActionInMasterDatabase(action);
if (!validationErrors.Any())
{
foreach(var connectionString in clientDatabases)
{
var clientValidationErrors =
PerformActionInClientDatabase(connectionString, action);
validationErrors.AddRange(clientValidationErrors);
if (validationErrors.Any())
break;
}//for each client
}//No errors on master DB
if (!validationErrors.Any())
distributedTransaction.Complete();
}
The problem was that if an exception was thrown during this process then the master database was still updated whereas the client databases were not. I’ve been tracking this problem down during most of Sunday and this morning I had an eureka moment!
I’ve opted to have Enterprise Core Objects store my mapping information in the database, so the first thing that happens when my application runs is Enterprise Core Objects will connect to the DB and read that mapping information. This is a description of the steps involved…
- Application runs
- Database connection is created
- Mapping information is retrieved
- Connection is returned to the pool
- Distributed transaction is started
- Object updates are made
- Connection is retrieved from the pool
Now because the connection is retrieved from a pool it is not created after the distributed transaction starts, so it is not enlisted in the transaction. SqlConnection’s connection pool does handle this, but because in the past Enterprise Core Objects has had to deal with connections which do not pool the developers added their own pooling and it does not handle this scenario.
Thankfully the solution once you know the problem is very simple. The developers added a way of disabling their own pooling, simply set PersistenceMapperSql1.MaxPoolSize to zero. Now it won’t pool connections itself, and SqlConnection will still provide connection pooling for performance and also ensure that the connection is enlisted into the distributed transaction!