PetaPoco - setting transaction isolation level

Question

Using PetaPoco, you're allowed to do transaction management doing something like this:

var newObject = new NewObject();
var newObjectId = 1;
using (var scope = db.GetTransaction())
{
    newObject = db.SingleOrDefault<NewObject>("SELECT * FROM tblNewObject WHERE Id = @0", newObjectId);
    scope.Complete();
}

While this is great for managing when in a transaction updates get committed, it's a little lacking for controlling the isolation level of a transaction similar to how you'd do it with a traditional SQL connection:

TransactionOptions transOptions = new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted };
using (new TransactionScope(TransactionScopeOption.Required, transOptions))
{
    //here be transactions
}

In PetaPoco, GetTransaction returns a new Transaction, which, using that specific constructor, calls BeginTransaction. BeginTransaction in this case, uses .NET's IDbConnection.BeginTransaction() - which has an overload to provide a transaction isolation level. As far as I can tell, PetaPoco does not provide any way to provide an isolation level to that method. Does anyone know if it's possible to actually modify the isolation level of PetaPoco without having to dig into the source and add an overloaded constructor that takes isolation level in? I'm happy to do that and submit a pull request, but I want to make sure before I do the work, I'm not missing something straightforward.

Pelson

Answer 1

Having taken a quick look at the source code for PetaPoco, it looks like you're absolutely correct. It does not look like there is anything in the source that allows you to modify transaction level, nor have I found any documentation online to support that behavior. I will have to look for your pull request down the road! It would be quite useful!

However, as another alternative, could you not set the Transaction Isolation level explicitly in the query that you pass?

"SET TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT * FROM tblNewObject WHERE Id = @0"

Salzhauer

Yeah, I was thinking of that as a workaround, but I'm really not too happy about that. Going to submit a pull request. – Pelson

It IS a workaround, but it's the only one available I think without a pull request. There just isn't anything hard baked in. – Salzhauer

Agreed. I'm modifying it quick and putting a PR up. – Pelson

I look forward to it! – Salzhauer

Here it is - github.com/toptensoftware/PetaPoco/pull/172 - looks kind of like the project has stagnated a bit so I'm not sure it'll make it in, but I'm using it locally and it seems to be fine. – Pelson

Answer 2

With the latest version of PetaPoco, you can now set the isolation level.

Using fluent configuration

var db = config.Build()
         .UsingConnectionString("cs")
         .UsingProvider<SqlServerDatabaseProvider>()
         .UsingIsolationLevel(IsolationLevel.Chaos)
         .Create();

 db.IsolationLevel.ShouldBe(IsolationLevel.Chaos);

Or traditional constructor

var db = new Database("MyConnectionStringName") { IsolationLevel = IsolationLevel.Chaos };

Highlander

Answer 3

Or in my case, where I want to avoid locking and and I'm not fussed about dirty reads, I'd use:

SELECT * FROM tblNewObject with(nolock) WHERE Id = @0

which, in PetaPoco terms, can still be shortened to:

FROM tblNewObject with(nolock) WHERE Id = @0

This has the benefit of only selecting and mapping the columns that exist in your DTO.

Moccasin