As I had blogged previously, we have just gone live in certain places in Pakistan with the Computerized Electoral Roll System , a project I have been consulting on in the last couple of months. Conseqently yours truly yesterday (07th March 2011) traveled to Osama bin laden’s backyard – Peshawar in Khyber-Pakhtoonkhwa for a go live test, demo, walk through all wrapped up in one that failed coz the links that connect the provinces’ to the capital were down and we had to settle for the use of a development copy and database for all of the above.
But running towards this go live we had experienced some problems. This is an attempt at logging them and documenting them of all and sundry – most of this is not new nor am I the first to resolve them but I will show you how I resolved them.
The first problem I run into months ago while using transactions was that I seemingly could not execute an insert, update or delete sp (imported function) when in the transaction context.
e.g.
var datactx = ContextHelper<CERSEntities>.GetCurrentContext();
using (var scope = new TransactionScope())
{
List<VOTER> updatedvoters = new List<VOTER>();
foreach (var voter in voterEntity)
{
datactx.UpdateVoters(Param1,Param2,...);
}
scope.Complete();
}
Was causing issues, until I discovered that EF 4.0 was silly and that it somehow expected a
insertedvoter.FirstOrDefault();
After each call to the store procedure more info on this can be found here.
Secondly we run into a problem in that we had been developing had been on SQL Server 2008 but the target database was SQL 2005 - this posed two issues, first a more obvious issue we received a message that
"Type datetime2 is not a defined system type"
Datetime2 is not available in SQL 2005 so I quickly redid the EDMX, and changed its version by opening the edmx file using a text editor and changing the provider manifest to 2008
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
<edmx:Runtime>
<edmx:StorageModels>
<Schema Namespace="CERSModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
Saved it change it to ProviderManifestToken="2005" and recompiled DAL
The second problem was more subtle and only happened in certain screens i.e. the transactions we were running were now becoming distributed. We traced it to the TransactionScope object. It was now all over suddenly escalating transactions to distributed transactions. I have been using the TransactionScope ever since I discovered it, because it’s a nice neat way of providing a neat way to do transactions while also giving your code the ability to reuse ambient transactions (i.e. transactions already created by caller methods) while supporting method level transactions just in case the method is called by a method that does not have a transaction.
But because we were not spanning database nor using multiple connection strings – the obvious things that cause normal transactions to escalate into distributed transactions, this was problematic we later dug up the reason as being that the issue was happening because the target db server was a SQL 2005. This posed a problem that would either need to
1. Upgrade the target database server to 2008 – the cost implications and the time did not make it easy on us (we were quite tempted to run the prod database of 80 million voters on SQL Server 2008 development edition).
2. Replicate all the code that was being used in methods that used ambient transactions – this proved to be quite untidy not to mention unprofessional.
3. Create a customized transaction scope object that would be used to do exactly what the TransactionScope object does but in the context of our use case.
I thought the latter was neater and would be a nice challenge and had a couple of hours to kill so I decided to do my own transaction scoping mechanism by – this is how I did it.
1. I created an extension method that would be attached on a data context and this was it
public class OurStaggeredDBTransaction : MarshalByRefObject, IDbTransaction
{
private const string TransactionCountKey = "TransactionCount";
public IDbTransaction Transaction { get; set; }
private bool CommitCalled = false;
public OurStaggeredDBTransaction(IDbTransaction originaltrans)
{
Transaction = originaltrans;
}
private void ResetCount()
{
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = TransactionCountKey + typeof(OurStaggeredDBTransaction).Name;
if (httpContext.Items[contextTypeKey] != null)
{
httpContext.Items[contextTypeKey] = 0;
return;
}
else
{
throw new ApplicationException("There is no Nested Transaction in Http Context available");
}
}
throw new ApplicationException("There is no Http Context available");
}
public void IncreaseCount()
{
CommitCalled = false;
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = TransactionCountKey + typeof(OurStaggeredDBTransaction).Name;
if (httpContext.Items[contextTypeKey] != null)
{
httpContext.Items[contextTypeKey] = ((int)httpContext.Items[contextTypeKey]) + 1;
}
else
{
httpContext.Items.Add(contextTypeKey, 1);
}
return ;
}
throw new ApplicationException("There is no Http Context available");
}
private void DeductCount()
{
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = TransactionCountKey + typeof(OurStaggeredDBTransaction).Name;
if (httpContext.Items[contextTypeKey] != null)
{
httpContext.Items[contextTypeKey] = ((int)httpContext.Items[contextTypeKey]) - 1;
return;
}
else
{
throw new ApplicationException("There is no Nested Transaction in Http Context available");
}
}
throw new ApplicationException("There is no Http Context available");
}
private int GetCount()
{
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = TransactionCountKey + typeof(OurStaggeredDBTransaction).Name;
if (httpContext.Items[contextTypeKey] != null)
{
return (int)httpContext.Items[contextTypeKey];
}
else
{
throw new ApplicationException("There is no Nested Transaction in Http Context available");
}
}
throw new ApplicationException("There is no Http Context available");
}
public void Commit()
{
CommitCalled = true;
if (GetCount() > 1)
{
DeductCount();
}
else
{
Transaction.Commit();
}
}
public IDbConnection Connection
{
get { return Transaction.Connection; }
}
public IsolationLevel IsolationLevel
{
get { return Transaction.IsolationLevel; }
}
public void Rollback()
{
CommitCalled = false;
ResetCount();
Transaction.Rollback();
}
public void Dispose()
{
if (CommitCalled && GetCount() >0)
{
//do nothing
//Transaction.Dispose();
CommitCalled = false;
}
else
{
Transaction.Dispose();
}
}
}
public static OurStaggeredDBTransaction BeginTransaction(this CERSEntities objcontext)
{
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = ObjectContextKey + typeof(CERSEntities).Name;
if (httpContext.Items[contextTypeKey] == null)
{
if (objcontext.Connection.State != System.Data.ConnectionState.Open)
objcontext.Connection.Open();
httpContext.Items.Add(contextTypeKey, new OurStaggeredDBTransaction(objcontext.Connection.BeginTransaction()));
}
//increase the count for each time requested
((OurStaggeredDBTransaction)httpContext.Items[contextTypeKey]).IncreaseCount();
return (OurStaggeredDBTransaction)httpContext.Items[contextTypeKey];
}
throw new ApplicationException("There is no Http Context available");
}
2. Created an extension method that could be used on a datacontext object (CERSEntities in our case)
public static OurStaggeredDBTransaction BeginTransaction(this CERSEntities objcontext)
{
HttpContext httpContext = HttpContext.Current;
if (httpContext != null)
{
string contextTypeKey = ObjectContextKey + typeof(CERSEntities).Name;
if (httpContext.Items[contextTypeKey] == null)
{
if (objcontext.Connection.State != System.Data.ConnectionState.Open)
objcontext.Connection.Open();
httpContext.Items.Add(contextTypeKey, new OurStaggeredDBTransaction(objcontext.Connection.BeginTransaction()));
}
//increase the count for each time requested
((OurStaggeredDBTransaction)httpContext.Items[contextTypeKey]).IncreaseCount();
return (OurStaggeredDBTransaction)httpContext.Items[contextTypeKey];
}
throw new ApplicationException("There is no Http Context available");
}
3. Changed all transaction scope objects to be as follows
using (var scope = datactx.BeginTransaction())
{
....................
scope.Commit();
}
This worked like a charm, as I reused the same transaction and kept count of callers and failure in one resulted in failure in all but success in on would just reduce the count of callers.
It could do with some more work, but it seems to work as of now – which I am happy with. Feel free to correct, critic the above code.
I will be on my way to Nairobi in a couple of hours….looking forward to holding my wife and baby boy in my hands!!!
