Isolation level is still SERIALIZABLE after change to READ COMMITTED

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi, I have a transaction scope in my codes in which I did not set any isolation level(by default should be serializable?). After some process and parameters is passed into databse/stored procedure(default is read committed in SQL), the isolation level is still serializable.

My question is how can I set the isolation level to read committed for the whole process(from codes until stored procedure execution)? I have tried many methods but the isolation level is still being stuck as serializable.

I have checked the function and it is not nested function calling. It is a direct function call and there is no any other function that is calling my function.

Any help would be appreciated. Thank you.

What I have tried:

1. Set the isolation level as READ COMMITTED in codes as below:

C#

TransactionOptions transOps = new TransactionOptions();
transOps.IsolationLevel = IsolationLevel.ReadCommitted;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, TransactionUtil.GetTransactionOptions()))
{
   //do something here
}

2. Set isolation level in stored procedure to read committed as below:

SQL

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Posted 20-Mar-16 20:21pm

Jamie888

Updated 20-Mar-16 21:39pm

VR Karthikeyan

v2

Add a Solution

Comments

Tomas Takac 21-Mar-16 3:37am

You should update your question with the code you use to check the current isolation level setting so the sequence of the events is clear. Also please note that in you first example the transOps variable is never used.

Jamie888 21-Mar-16 3:44am

Yes sir, I would love to but my DBA would not allow me to get a hand onto those codes for isolation checking. It has make my troubleshooting harder :(

Tomas Takac 21-Mar-16 5:09am

I mean, how do you know the isolation level is set to serializable?

Jamie888 21-Mar-16 22:07pm

My DBA has a SQL script that is used to check for isolation level each time the stored procedure is called.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sinisa Hajnal · Answer 1 · 2016-03-20T21:39:00

What does your GetTransactionOptions() do? It seems to me you're setting transOps to ReadCommited, but then call Get method to return some other instance of TransactionOptions.

Try this variation:

C#

TransactionOptions transOps = TransactionUtil.GetTransactionOptions();
transOps.IsolationLevel = IsolationLevel.ReadCommitted;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, transOps))
{
   //do something here
}

Set a breakpoint at GetTransactionOptions and check the returned value.

As an alternative, remove GetTransactionOptions completely:

C#

TransactionOptions transOps = new TransactionOptions();
transOps.IsolationLevel = IsolationLevel.ReadCommitted;
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew, transOps))
{
   //do something here
}

I hope this helps.