Frans,

If a transaction is already in progress... is there any way to escalate the IsolationLevel for the next operation (using adapter)?

I've read the docs where it states setting adapter.TransactionIsolationLevel during a transaction is ignored.

I could create a StoredProcedure to set an escalated IsolationLevel... would this work or is there a better way?

Marcus

an ADO.NET transaction is in fact a transaction ran in the RDBMS by the RDBMS, thus a BEGIN TRANS name on SqlServer. You can change the isolation level if you want, but that doesn't help much, as you can't start another transaction in ADO.NET, as it doesn't support nested transactions (neither does sqlserver).

Otis wrote:

an ADO.NET transaction is in fact a transaction ran in the RDBMS by the RDBMS, thus a BEGIN TRANS name on SqlServer. You can change the isolation level if you want, but that doesn't help much, as you can't start another transaction in ADO.NET, as it doesn't support nested transactions (neither does sqlserver).

I guess this was my question... Can you escalate a transaction's isolation level, but I guess you're saying it's not possible on SQL Server for the reasons above. Thanks.

I've never heard of increasing the isolation level of a transaction, as you have to state that when you create the transaction (start it)... inside sqlserver, the transaction is not really there, you can't access it and set properties...

I was experimenting with internal reties for handling concurrency errors (linked to http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=2105).

In the event of concurrency errors, lock timeouts etc (within the BL) I wanted to retry a couple of times before reporting errors back to the user.

For concurrency I had the idea that during periods of high update contention I might escalate the Isolation Level of the transaction for the final retry, so that I can guarantee updateability of the rows... The problem is that my BL allows me to pass in an existing transaction and this is where the plan came unstuck...

But... what's wrong with doing something like (this is very raw, but it gets the idea accross )

bool succeeded = false
DataAccessAdapter adapter = new DataAccessAdapter();
for(int i=0;(i<3)&&!succeeded;i++)
{
    adapter.StartTransaction(IsolationLevel.ReadCommitted, "Foo");
    try
    {
        succeeded = adapter.SaveEntity(myRootEntity); // save the graph
        adapter.Commit();
    }
    catch(ORMConcurrencyException)
    {
        // exception caught, we'll have to retry
        adapter.Rollback();
    }
    catch(Exception)
    {
        // other exception, do not continue
        adapter.Rollback();
        throw;
    }
}

if(!succeeded)
{
    // final try, with other isolation level
    adapter.StartTransaction(IsolationLevel.ReadUncommitted, "Foo");
    try
    {
        succeeded = adapter.SaveEntity(myRootEntity); // save the graph
        adapter.Commit();
    }
    catch(ORMConcurrencyException)
    {
        adapter.Rollback();
    }
    catch(Exception)
    {
        // other exception, do not continue
        adapter.Rollback();
        throw;
    }
}

A transaction is an atomic unit, if something goes wrong inside it, it has to be rolled back completely to the initial state so you can either retry or give up and ask the user to alter something.

Yes this is exactly what I wanted to do... Except:

My BL is part of a SOA architecture where messages come in via WSE 2.0. All busniness action implement an interface IBusinessAction:

public interface IBusinessAction
    {
        SoapEnvelope Execute(SoapEnvelope envelope);
    }

They all have the following constructor:

public BABase()
{
    _adapter = CreateDataAccessAdapter();
    _myAdapter = true;
}

public BABase(IDataAccessAdapter adapter)
{
    _adapter = adapter;
    _myAdapter = false;
}

Originally I had the atomic transactions as you coded above... BUT SOA is so "CRAP" that I had to implement reliable messaging layer on top in order to ensure that the BL was receiving messages "Exactly Once".

SoapEnvelopes are queued on the way in and the dequeue operation is part of the transaction. The InProc transport on the other hand does not start a transaction since it calls the BL directly and there is no inbound queue.

Throw into the mix a load of NMock stubs and 500+ NUnit tests and the thought of the refactor makes me want to cry... (even with ReSharper to hand)

Who's idea was SOA anyway!

But in essence you are entirely correct in that it is possible to ensure the transaction is always started on the same layer and the retry logic can be implemented there. This is just a design issue in my code that I will have to sort out shortly...

Hmm, sounds indeed like a lose lose situation .

But you know my opinion about soa

I wouldn't know any other solution. You could move the start of the retry up a bit, so it's restarted on a higher level, after all it doesn't really matter if it's restarted in a lower layer or a higher layer: something failed, which is bad and as a convenience to the user, you retry the query. Where that's done is IMHO not that important. So it could even be in the button event handler of the "Save" button

Otis wrote:

But you know my opinion about soa

Yes, and I'm starting to agree with you on a number of points... Until we have better infrastructure (i.e. Indigo) it's almost a loosing battle.

Otis wrote:

I wouldn't know any other solution. You could move the start of the retry up a bit, so it's restarted on a higher level, after all it doesn't really matter if it's restarted in a lower layer or a higher layer: something failed, which is bad and as a convenience to the user, you retry the query. Where that's done is IMHO not that important.

Agreed

Why couldnt you queue the transaction and let the queue deal with it when it gets around to it? WSE Supports asynchronous transactions.