Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PetaPoco - setting transaction isolation level

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.

like image 407
antinescience Avatar asked Nov 08 '13 15:11

antinescience


People also ask

Which of the following is used to set transaction isolation level?

To set the transaction isolation level, use an ISOLATION LEVEL level clause. It is not permitted to specify multiple ISOLATION LEVEL clauses in the same SET TRANSACTION statement. The default isolation level is REPEATABLE READ . Other permitted values are READ COMMITTED , READ UNCOMMITTED , and SERIALIZABLE .

What is transactional isolation?

Transaction isolation levels are a measure of the extent to which transaction isolation succeeds. In particular, transaction isolation levels are defined by the presence or absence of the following phenomena: Dirty Reads A dirty read occurs when a transaction reads data that has not yet been committed.

What is isolation level in C#?

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side effects, such as dirty reads or phantom reads, are allowed.

What is uncommitted read isolation level?

Uncommitted read (UR) The uncommitted read isolation level allows an application to access the uncommitted changes of other transactions. Moreover, UR does not prevent another application from accessing a row that is being read, unless that application is attempting to alter or drop the table.


2 Answers

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"
like image 54
David L Avatar answered Oct 22 '22 20:10

David L


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 };
like image 29
Plebsori Avatar answered Oct 22 '22 20:10

Plebsori