Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: how to set default isolation level for the entire stored procedure?

If right after BEGIN I have SET TRANSACTION ISOLATION LEVEL ... statement, will the given transaction level in force for the entire scope of the stored procedure regardless if I use BEGIN TRANSACTION or not? Namely if I have simple SELECT statements, which are atomic/transacted by definition, will the default transaction level for them set to the given one?

BEGIN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 -- will a transaction level for a atomic transaction created by SQL Server for this statement be READ COMMITTED 
SELECT * FROM T
END
like image 205
Schultz9999 Avatar asked Nov 02 '11 04:11

Schultz9999


People also ask

How do I change the default isolation level in SQL Server?

The isolation level of the transactional support is default to READ UNCOMMITTED. You can change it to READ COMMITTED SNAPSHOT ISOLATION by turning ON the READ_COMMITTED_SNAPSHOT database option for a user database when connected to the master database.

What is the default isolation in SQL Server?

READ COMMITTED is the default isolation level for SQL Server. It prevents dirty reads by specifying that statements cannot read data values that have been modified but not yet committed by other transactions.

How do I change the isolation level of a database?

How do I do this? From the tools menu select options. Under Query Execution/SQL Server/Advanced, change the value of SET TRANSACTION ISOLATION LEVEL to READ UNCOMMITTED.


1 Answers

First, the default isolation level in SQL Server is Read Committed, so that statement doesn't really do anything unless you've changed the default isolation level.

But, in general, yes, SET Transaction Isolation Level will change the isolation level for the whole procedure (the duration of the connection, in fact)

Keep in mind that all SQL statements are implicit transactions meaning that if, for example, an update fails 99% through, it will rollback automatically; no BEGIN TRAN/COMMIT is necessary.

To answer your question, yes, your SELECT statements will inherit the isolation level you set (or the default if you do not set one) unless you override the behavior with a query hint like WITH NOLOCK which will make the individual query behave as though you did SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

like image 87
Code Magician Avatar answered Nov 08 '22 22:11

Code Magician