See Part 1
Before we can do anything about deadlocks or deal with similar troubles, we first need to be able to tell that we indeed have a deadlock situation. Finding this out is a matter of knowing the respective error codes that your database gives you and a mechanism to bubble that information up to some code that will handle the situation. So before we can think about and write the handling logic for failed/failing but safely repeatable transactions, we need to build a few little things. The first thing we’ll need is an exception class that will wrap the original exception indicating the reason for the transaction failure. The new exception class’s identity will later serve to filter out exceptions in a “catch” statement and take the appropriate actions.
using System;using System.Runtime.Serialization;namespace newtelligence.EnterpriseTools.Data{ [Serializable] public class RepeatableOperationException : Exception { public RepeatableOperationException():base() { } public RepeatableOperationException(Exception innerException) :base(null,innerException) { } public RepeatableOperationException(string message, Exception innerException) :base(message,innerException) { } public RepeatableOperationException(string message):base(message) { } public RepeatableOperationException( SerializationInfo serializationInfo, StreamingContext streamingContext) :base(serializationInfo,streamingContext) { } public override void GetObjectData( System.Runtime.Serialization.SerializationInfo info, System.Runtime.Serialization.StreamingContext context) { base.GetObjectData (info, context); } }}
Having an exception wrapper with the desired semantics, we know need to be able to figure out when to replace the original exception with this wrapper and re-throw it up on the call stack. The idea is that whenever you execute a database operation – or, more generally, any operation that might be repeatable on failure – you will catch the resulting exception and run it through a factory, which will analyze the exception and wrap it with the RepeatableOperationException if the issue at hand can be resolved by re-running the transaction. The (still a little naïve) code below illustrates how to such a factory in the application code. Later we will flesh out the catch block a little more, since we will lose the original call stack if we end up re-throwing the original exception like shown here:
Try{ dbConnection.Open(); sprocUpdateAndQueryStuff.Parameters["@StuffArgument"].Value = argument; result = this.GetResultFromReader( sprocUpdateAndQueryStuff.ExecuteReader() );}catch( Exception exception ){ throw RepeatableOperationExceptionMapper.MapException( exception ); }finally{ dbConnection.Close();}
The factory class itself is rather simple in structure, but a bit tricky to put together, because you have to know the right error codes for all resource managers you will ever run into. In the example below I put in what I believe to be the appropriate codes for SQL Server and Oracle (corrections are welcome) and left the ODBC and OLE DB factories (for which would have to inspect the driver type and the respective driver-specific error codes) blank. The factory will check out the exception data type and delegate mapping to a private method that is specialized for a specific managed provider.
using System;using System.Data.SqlClient;using System.Data.OleDb;using System.Data.Odbc;using System.Data.OracleClient;namespace newtelligence.EnterpriseTools.Data{ public class RepeatableOperationExceptionMapper { /// <summary> /// Maps the exception to a Repeatable exception, if the error code /// indicates that the transaction is repeatable. /// </summary> /// <param name="sqlException"></param> /// <returns></returns> private static Exception MapSqlException( SqlException sqlException ) { switch ( sqlException.Number ) { case -2: /* Client Timeout */ case 701: /* Out of Memory */ case 1204: /* Lock Issue */ case 1205: /* Deadlock Victim */ case 1222: /* Lock Request Timeout */ case 8645: /* Timeout waiting for memory resource */ case 8651: /* Low memory condition */ return new RepeatableOperationException(sqlException); default: return sqlException; } } private static Exception MapOleDbException( OleDbException oledbException ) { switch ( oledbException.ErrorCode ) { default: return oledbException; } } private static Exception MapOdbcException( OdbcException odbcException ) { return odbcException; } private static Exception MapOracleException( OracleException oracleException ) { switch ( oracleException.Code ) { case 104: /* ORA-00104: Deadlock detected; all public servers blocked waiting for resources */ case 1013: /* ORA-01013: User requested cancel of current operation */ case 2087: /* ORA-02087: Object locked by another process in same transaction */ case 60: /* ORA-00060: Deadlock detected while waiting for resource */ return new RepeatableOperationException( oracleException ); default: return oracleException; } } public static Exception MapException( Exception exception ) { if ( exception is SqlException ) { return MapSqlException( exception as SqlException ); } else if ( exception is OleDbException ) { return MapOleDbException( exception as OleDbException ); } else if (exception is OdbcException ) { return MapOdbcException( exception as OdbcException ); } else if (exception is OracleException ) { return MapOracleException( exception as OracleException ); } else { return exception; } } }}
With that little framework of two classes, we can now selectively throw exceptions that convey whether a failed/failing transaction is worth repeating. Next step: How do we do actually run such repeats and make sure we neither lose data nor make the user unhappy in the process? Stay tuned.