Why are my transactions running in SERIALIZABLE isolation level?

Question

Why are my transactions running in SERIALIZABLE isolation level?

Innerise

Ten Centuries

Points: 1020
More actions
November 18, 2014 at 9:12 am

#148773

Hi All
We have an in-house application that has frequent deadlocks. When I inspect the deadlock reports, I see plenty of the following statements:
isolationlevel="serializable (4)"
When I trace the blocking sessions, I also see that the isolation level for these transactions is SERIALIZABLE.
However, when I use DBCC USEROPTIONS, I find that my SQL instance's isolation level is set to READ COMMITTED. Inspecting the stored procedures that cause the deadlocks show no sign of setting the isolation level to SERIALIZABLE.
So, my question is: Why is SERIALIZABLE being used for these transactions? What other factors would cause a transaction to run SERIALIZABLE?
Apparently, the .NET Framework TransactionScope object can request an isolation level of SERIALIZABLE when connecting to SQL Server, as per this link. However, our Developers have confirmed that this isn't he case here.
Thanks very much!
- Innerise

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Innerise (11/18/2014)
So, my question is: Why is SERIALIZABLE being used for these transactions? What other factors would cause a transaction to run SERIALIZABLE?

A SET TRANSACTION ISOLATION LEVEL statement or a request from the application to run in serialisable. May want to go back to the devs....

Check the connection strings that .net is using, check the session options that it sets (you may need to run some targeted sql traces)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

You can also look in sys.dm_exec_sessions and the transaction_isolation_level column to find out if the devs know what they are doing. Here's the mapping from BOL:

0 = Unspecified

1 = ReadUncomitted

2 = ReadCommitted

3 = Repeatable

4 = Serializable

5 = Snapshot

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Innerise Ten Centuries Points: 1020 More actions · Answer 3

Thanks very much for the helpful replies.

I've used SYS.DM_EXEC_SESSIONS to look for running sessions using a SERIALIZABLE isolation level and we have many. I've looked into the query text for these sessions (all SPs) and the majority of them don't include a SET TRANSACTION ISOLATION LEVEL statement.

Strangely, one of the SPs includes a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED statement, but was still running as SERIALIZABLE :unsure:

So, I guess that the only other possibility is a request from the application to run in SERIALIZABLE. I'll go back to the Developers with this new information and see what they say.

Another quick question: When using SYS.DM_EXEC_CONNECTIONS, I see multiple rows for the same session ID, all of them SERIALIZABLE. Some of the rows have different CONNECT_TIMEs and different QUERY_TEXTs, so is this simply the same session running multiple queries/transactions?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

My thought would be that you're spending a whole lot of time on something that might not help. IMHO, it would be much more fruitful to find the cause of the deadlocks in the code rather than trying to find the cause at a system level that probably shouldn't be changed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Jeff Moden (11/19/2014)
My thought would be that you're spending a whole lot of time on something that might not help.

In this case I'd disagree. Serialisable, when used unnecessarily, can easily cause deadlocks that would not occur in any other isolation level

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass