Isolation level in C# or SQL - which one will be used?

Question

I have set isolation level in C# code as readcommitted, and I am calling a stored procedure which is timing out due to some reason. The stored procedure does not have any set isolation level statement.

In SQL Server, database level isolation level is read committed snapshot.

So which isolation level will be used? The one defined in SQL Server, or the one set from C#?

Sunglasses

I don't agree with this duplicate at all... Sure the answer could help guide OP to find the answer, but it is not an answer to this question whatsoever in that it doesn't address the C# aspect at all, unless I'm really misunderstanding the link. – Primogeniture

@BrootsWaymb - The OP wants to know what the isolation level is. The place to figure that out is in the query execution. Adding the code from the accepted answer would provide the answer to the question the OP is asking. Instead of just answering the question (which could be wrong from some dude on the internet) I am teaching them how to find the answer so there is no doubt in which isolation level is being used. – Gresham

@SeanLange - That seems kind of counterintuitive to the duplicate flag. The flag description says "This question has been asked before and already has an answer.". The current dupe is related, but it's certainly not the same question. – Superlative

@CodeStranger how is knowing which isolation being used not answering the question about which isolation level is being used? – Gresham

I would add some code to your query so you can determine the isolation level and remove all doubt. This question has some ideas you can use to provide you the ability to find it. – Gresham

Answer 1

There is no such thing as a 'database isolation level'. What you describe is a database options, called READ_COMMITTED_SNAPSHOT:

READ_COMMITTED_SNAPSHOT { ON | OFF } ON Enables Read-Committed Snapshot option at the database level. When it's enabled, DML statements start generating row versions even when no transaction uses Snapshot Isolation. Once this option is enabled, the transactions specifying the read committed isolation level use row versioning instead of locking.

So when READ_COMMITTED_SNAPSHOT is ON a transaction that specified read committed isolation level will instead see a snapshot isolation level.

It is important to understand that there is another database option: ALLOW_SNAPSHOT_ISOLATION that also must be set to ON for Snapshot isolation to occur. See Snapshot Isolation in SQL Server.

When in doubt, you can always check sys.dm_tran_current_transaction which has a column named transaction_is_snapshot:

Snapshot isolation state. This value is 1 if the transaction is started under snapshot isolation. Otherwise, the value is 0.

Also, there are subtle differences between true snapshot isolation level and read committed isolation that is changed to snapshot by READ_COMMITTED_SNAPSHOT.

Fetation

Better explanation than the one I marked earlier as a duplicate. Says the same thing only a lot clearer. Well done as always!! – Gresham

Answer 2

Commands to set the transaction isolation level are processed in order received. So the last one wins. On SQL Server, you can set the default transaction isolation level, but is just a default.

Ketone