SQL Server - transaction isolation level range
What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality code.
Obviously, the scope begins when you use a command like this:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
But where does it end? If I set the iso level in a stored procedure and then that proc calls another, does the nested proc inherit it? Even better, if I escalate the iso level inside the nested proc is it going to carry back out into the calling proc? Do transaction commands like BEGIN TRAN, ROLLBACK, and COMMIT make any differences?
When a stored proc is called by an application or an agent job do the isolation level changes persist in some way? Do I always have to revert to the default READ COMMITTED at the end of each proc?
I would test it in different situations but I don't know how to read what the current isolation level is set to.
Leave a Reply
- belt ADO.NET What is the default transaction isolation level of SQL server for?
- sql server - Minimum transaction isolation level to avoid "missing updates"
- sql - Nolock and transaction isolation level
- SQL Server 2008 - transaction scope and isolation level
- tsql - How to cancel / reset the transaction isolation level of SQL Server?
- More similar questions >>