zaterdag 11 november 2006

Nested Transactions in SQL Server

I've been wondering if it would be possible to use 'nested transactions' in SQL Server. To test this, I've set up a little test database and executed a few
T-SQL batches:

USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')

BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
COMMIT TRAN

INSERT INTO tblTest (Name) VALUES ('Name3')

COMMIT TRAN

This is trivial, and it works as expected: 3 records have been added to the table. The next batch looks like this:

USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')

BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
COMMIT TRAN

INSERT INTO tblTest (Name) VALUES ('Name3')

ROLLBACK TRAN

This is no big deal either: as expected, no records have been added to the table. Up to the next one:

USE testdb
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')

BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name2')
ROLLBACK TRAN

INSERT INTO tblTest (Name) VALUES ('Name3')

COMMIT TRAN

This batch fails with the following error message:

Server: Msg 3902, Level 16, State 1, Line 16

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

It turns out that only the last record (Name3) is inserted into the database. That's not what I expected.
Normally, one should expect that Name1 and Name3 are persisted in the database, and only Name2 gets rollbacked.



However, as my collegue Geert pointed out: the BEGIN TRANSACTION statement increments the @@TRANCOUNT Server Variable with 1, and the COMMIT TRANSACTION decrements the @@TRANCOUNT variable with 1.
The ROLLBACK TRAN statement however, decrements the @@TRANCOUNT server variable to 0. That's why the last COMMIT statement gives us the error message: there has been a rollback, and therefore the @@Trancount is set to zero.
Apparently, the ROLLBACK TRANSACTION also rollbacks to the most outer begin transaction, that's why the record 'Name1' is not persisted into the database.

As it turns out, it is not possible to use nested transactions in this way. There is however a way to solve this 'problem':

Savepoints to the rescue

It is possible to use 'savepoints' to solve this problem. As stated in the SQL Server books online:

Savepoints offer a mechanism to roll back portions of transactions.
You use savepoints like this:
BEGIN TRAN
INSERT INTO tblTest (Name) VALUES ('Name1')

SAVE TRANSACTION sp1
INSERT INTO tblTest (Name) VALUES ('Name2')
ROLLBACK TRAN sp1

INSERT INTO tblTest (Name) VALUES ('Name3')

COMMIT TRAN

In this code example, you start a transaction, execute a statement, and save the transaction using the SAVE TRANSACTION sp1 statement.
This statement sets a savepoint with the name 'sp1'. You can then rollback to that savepoint using the ROLLBACK TRAN <savepointname> command.
The result of this batch is as expected: 2 records are inserted into the tblTest table: 'Name1' and 'Name3'

zondag 5 november 2006

Aspect Oriented Programming in .NET

A while ago, there was somebody who asked the question on a programming forum whether it was possible to retrieve the values of the arguments that are passed to a method in .NET. The purpose was to create some kind of a 'logging' system so that he could log which methods have been called, and what values were passed to those methods.
This person had already created a method that retrieved all kinds of information of a certain method, but getting the values of the parameters via reflection was not possible.

The disadvantage of this approach is that your methods are being polluted by this logging method. You always have to add a call to this logging method in your 'business methods'.
For instance:

public void SomeMethod()
{
LogThisMethod (MethodBase.GetCurrentMethod());

// Do the real work here.
}

The call to the LogThisMethod method is not likely a core concern in the application, yet, if you want to log calls to certain methods, you’ll have to write a call to this method in every method that you want to log.
In other words: the logging is a cross-cutting concern because it is an aspect of our program that has nothing to do with the core-problem that is to be solved by our program and it appears in multiple parts of the program.

Luckily, there's a much cleaner approach to solve this problem. Aspect Oriented Programming offers a way to separate cross-cutting concerns like logging in a much cleaner way.
AOP allows you to remove the cross-cutting concerns from your 'business code', and create an 'aspect' for it instead.
This ‘aspect’ will then be weaved into your code at runtime which means that you do not have to call it yourself in the core parts of the application.
In this way, the cross-cutting concerns can be decomposed from the core logic of the application and this will result in more readable and better maintainable software.

In .NET, you can use the Spring.NET framework to apply Aspect Oriented Programming.
In the examples that follow, I’ll be using the Spring.NET framework.

You can solve the logging-problem that I've mentioned earlier using AOP in C# in the following way:

Suppose we have a class 'TestClass' and we want to log every method that is being invoked in this class. Our TestClass looks like this:

public interface ITest
{
void SayHello( string name );
void Shout( string message );
}

public class TestClass : ITest
{
public void Method1( string name )
{
Console.WriteLine ("Hello " + name + " ! ");
}

public void Shout( string message )
{
Console.WriteLine (message + "!!!!!!!");
}
}

These are the steps that have to be taken to create some kind of logging functionality using AOP:


  • Create an Advice that takes care of the logging. An Advice describes a certain ‘procedure’ that must be executed at certain points (joinpoints) in the application. For instance: an Advice can be executed at the entry point of a method.

    If you use Spring.NET, you can create a class which implements the IMethodBeforeAdvice. This will make sure that this Advice is called before a method-call.
    The Advice can look like this:

    public class MethodInvocationLoggingAdvice : IMethodBeforeAdvice
    {

    public void Before( System.Reflection.MethodInfo method,
    object[] args, object target )
    {
    string message = method.Name + " called with ";

    string arguments = string.Empty;

    for( int i = 0; i < args.Length; i++ )
    {
    arguments += args[i] +", ";
    }

    Console.WriteLine (message + arguments.SubString (0, arguments.Length - 2));
    }
    }

    Now, we have separated the logging logic in a separate class.

  • Tell our program to use the Advice
    In our program, we must indicate that our Advice has to be called when we invoke the methods of a certain class.
    Using Spring.NET, we can do this with only 3 lines of code:
    static void Main()
    {
    ProxyFactory f = new ProxyFactory (new TestClass());

    f.AddAdvice (new MethodInvocationLoggingAdvice());

    ITest t = (ITest)f.GetProxy();

    t.SayHello ("Frederik");

    t.Shout ("Watch out");
    }

    The beautiful thing is, that we've kept our 'business methods' clean and every time we invoke a method, the logging functionality is called. If we extend our TestClass with a couple of new methods, we do not have to worry about this logging functionality, since those new methods will also call our MethodInvocationLoggingAdvice as well.

But, what if you only want to log invocations of certain methods, instead of logging every method call? This can also be done rather easily by defining a PointCut. Everytime the pointcut is reached, our Advice will be executed.
.NET attributes provide a great way to define PointCuts.

Building on the previous example, we can extend our code so that the MethodInvocationLoggingAdvice is only called when a method is decorated with a specific Attribute. For instance: only invocations of methods that have the 'Log' attribute, must be logged.
To do this, we must first create this Log attribute:

[AttributeUsage(AttributeTargets.Method)]
public class LogAttribute : Attribute
{
}

We can now change the TestClass to indicate that only method-calls to the Shout method should be logged:
public class TestClass : ITest
{
public void Method1( string name )
{
Console.WriteLine ("Hello " + name + " ! ");
}

[Log]
public void Shout( string message )
{
Console.WriteLine (message + "!!!!!!!");
}
}

All what's left to do, is to make a change to the code that will be responsible of weaving the advice into our code. We must now indicate that our Advice should only be executed on methods that have the Log attribute.
static void Main()
{
ProxyFactory f = new ProxyFactory (new TestClass());

f.AddAdvisor (new DefaultPointCutAdvisor (
new AttributeMatchMethodPointcut (typeof(LogAttribute),
new MethodInvocationLoggingAdvice()));

ITest t = (ITest)f.GetProxy();

t.SayHello ("Frederik");

t.Shout ("Watch out");
}

When you execute this program, you'll see that only the method-call to 'Shout' is being logged.