Configure isolation level via connection string/options

[mehmetbilen]

In some projects I'm working on, all queries are running in a transaction with READ UNCOMMITTED isolation level. So we had create a transaction for each query.

If we could set isolation level when creating db connection, we wouldn't need to create transactions.

I would like to have an option to set default isolation level with db connection creation.

[roji]

SQL Server itself may have some sort of configuration for changing the default - I'm not sure. If not, I don't believe this should exist in the driver - that would mean implicitly wrapping every user operation with a transaction. It's trivial to write an extension method in your program which would do exactly this.

I'd also generally advise against using READ UNCOMMITTED globally in this way; some people do this without fully understanding the consequences, under the impression that they need it in order to get good performance. That's usually not the case - understanding how (committed) isolation modes work and locking is important, and proper usage of the database can in almost all cases make it more than fast enough without seeing uncommitted data.

[paulmedynski]

READ COMMITTED is the SQL Server default isolation level. I don't believe SQL Server has a configuration option to change this at a server or database level. In other words, as far as I know, each new connection to SQL Server should be in READ COMMITTED.

READ UNCOMMITTED is the default isolation level for Azure Synapse. Are you connecting to an Azure Synapse instance rather than a SQL Server instance?

You can issue a T-SQL command to change the isolation level on a per-connection basis:

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

This might be an improvement over wrapping all of your commands in transactions. This changes the default isolation for the lifetime of the connection.

If you're also using connection pooling, I don't think there is a way to tell MDS to set the per-connection isolation level for you, so you would have to do it yourself each time you pulled a connection from the pool.

[mehmetbilen]

READ UNCOMMITTED was just an example. It would be really helpful if we could set default isolation level while creating connection without needing any transaction.

[paulmedynski]

You can avoid creating a transaction to set the isolation level today using the T-SQL command I noted above:

https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-ver16

Hopefully that will cover your use case.

For the issue with connection pooling, we will keep this issue open for consideration.