Setting SQL Default Transaction Isolation Level

Question

I want to have an application where the isolation level would ALWAYS be SNAPSHOT. If I want to use stored procedures for the application, how would I go about ensuring that the transaction level stays set to SNAPSHOT?

Every time I closed the connection, would the next connection not default to the MSSQL Server default(READ COMMITTED)?

Basically its seems like I would be best off adding a 'SET TRANSACTION' command at the top off all my stored procedures.

Also, it seems that there is no way to set a 'DEFAULT' isolation level for a database, is this true?

EDIT**

I am aware of the default transaction level! Please re-read the question!

Possible duplicate of [What is the default transaction isolation level for SQL Server with ADO.NET?](http://stackoverflow.com/questions/10003026/what-is-the-default-transaction-isolation-level-for-sql-server-with-ado-net) — Ivan Starostin, Jul 27 '16 at 13:37
@IvanStarostin Please re-read the question. I am fully aware of the default transaction level. — 2b77bee6-5445-4c77-b1eb-4df3e5, Jul 27 '16 at 13:39
Please explain down vote...... help me improve this question — 2b77bee6-5445-4c77-b1eb-4df3e5, Jul 27 '16 at 13:48
I think what you're talking about is in the database properties, e.g. Allow Snapshot Isolation = True and Is Read Committed Snapshot On = True. — Jacob Barnes, Jul 27 '16 at 14:00
@JacobBarnes Yes! And those can be set for the database, however it seems that the transaction isolation level for a new connection defaults to READ COMMITTED, is that true and is there any way around it? — 2b77bee6-5445-4c77-b1eb-4df3e5, Jul 27 '16 at 14:02
@JacobBarnes So it can only be enforced at the transaction level, and therefore would be limited to the open connection? — 2b77bee6-5445-4c77-b1eb-4df3e5, Jul 27 '16 at 14:05
No, READ COMMITTED SNAPSHOT ISOLATION will apply to every single transaction. — Jacob Barnes, Jul 27 '16 at 14:09
http://www.databasejournal.com/features/mssql/snapshot-isolation-level-in-sql-server-what-why-and-how-part-1.html — Jacob Barnes, Jul 27 '16 at 14:09
@JacobBarnes Thank you, that is what i was looking for. If you want to write up an aanswer i will accept it — 2b77bee6-5445-4c77-b1eb-4df3e5, Jul 27 '16 at 14:10

score 1 · Accepted Answer · answered Jul 27 '16 at 14:11

1

Using READ COMMITTED SNAPSHOT ISOLATION will achieve the results you're wanting. You can enable this setting in the database properties.

answered Jul 27 '16 at 14:11

Jacob Barnes

1,480
1
12
29

Setting SQL Default Transaction Isolation Level

1 Answers1