Problem with SET TRANSACTION ISOLATION LEVEL

I found that SET TRANSACTION ISOLATION LEVEL only sets the isolation level for the current CONNECTION.

1. What if I want to change the isolation level of the whole database? Which command is it ?

2. What if I want to use IsolationLevel of Serializable only with a transaction ( a part within a stored procedure)?

for ex. this is from a stored procedure

/* Blah Blah Blah Blah the T-SQL before the transaction */

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

/* Blah Blah Blah Blah the T-SQL after the transaction */

Thanks every body!!!

Pi

Hi Pi.

Answers:

1. No way to change the default isolation level for an entire database (more accurately, all connections to a database). With Sql 2005 you have the option of specifying that the usual default isolation level (read committed) use row-versioning instead of a lock-based isolation by enabling the READ_COMMITTED_SNAPSHOT database option, but that is all you can do there.

2. To achieve what you are trying to do, simply run the appropriate SET TRANSACTION ISOLATION LEVEL statements before and after you begin/end you transaction, as follows:

/* Blah Blah Blah Blah the T-SQL before the transaction */

-- ADD THIS HERE

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRANSACTION myTran ; /*I want IsolationLevel = Serializable being used here*/

/* Do something Blah Blah Blah in this transaction */

COMMIT TRANSACTION myTran ; /* And here go back to default Isolation level */

-- AND THIS HERE

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

/* Blah Blah Blah Blah the T-SQL after the transaction */

Hope that helps,

|||

Is there a way to set the TRANSACTION ISOLATION LEVEL SNAPSHOT for view. So all SQL for the View would run under SNAPSHOT isolation mode.

|||

In SQL Server, the transaction isolation is not tied to a specific transaction, it is a session property. So if you want do a query under a given isolation level, then simply set the session transaction isolation level to that one before you run your query. In your case, if you want the view to be accessed using SNAPSHOT isolation level, then simply set it before accessing the view, and change the isolation back to your default isolation level after the access to the viwe if you want.

There is no way currently in SQL Server to tie up a isolation level to a given object, like table, queue or a database either.

Thanks!