9

Inside a stored procedure, I have the following : ( sql server 2008 )

 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 BEGIN TRANSACTION getStuff
 BEGIN TRY 
    /*  some selects, updates, etc, etc. */
    ....
    COMMIT TRANSACTION getStuff
 END TRY
 BEGIN CATCH 
   ...
 END CATCH

Since this is transaction based, my thought was the rest of the database connections will not be affected by the SERIALIZABLE.

Do I need to implicitly set isolation level to read committed after my commit? Will this adversely effect other connections between my application server and database server?

6

The command is TRANSACTION ISOLATION LEVEL, not SERVER ISOLATION LEVEL. It only changes the isolation level for the transaction scope.

5

No you do not have to implicitly set isolation level back to read committed.

From books online.

If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

3

The command that you posted just run the transaction in the SP with TRANSACTION ISOLATION LEVEL SERIALIZABLE. Other connections will default to the default isolation level - read committed (unless specified a different isolation level explicitly).

There is an excellent series by Paul White - SQL Server Isolation Levels : A Series which will help you understand in depth what different isolation levels mean and do when they are used.

-3

A fairly new algorithm that avoids most of the downsides of the previous approaches. It uses an optimistic approach, allowing transactions to proceed without blocking. When a transaction wants to commit, it is checked, and it is aborted if the execution was not serializable. More details of all appproaches at : http://writeulearn.com/relational-database-transactions/

  • This doesn't seem to be an answer to the question.... – RDFozz Mar 29 '18 at 19:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.