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#?

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.