SQL Server - Transaction Isolation Levels

Hi This is Prathap......I want information of transaction isolation levels

When i am fire this query on sql server 2008

select * from sys.dm_exec_requests as req

cross apply sys.dm_exec_sql_text (req.sql_handle)

In result window one column is there name is transaction isolation level. In this column having 2 and some of rows having 3.

Please tell me what is the meaning of transaction isolation level 2 and 3 in this scenario.?

I know theritically like read committed and repetable read,serializable.......

Please give me the clarity any one.....

Thanks advance....

Regards,
Prathap.

hi,

Use following links for understanding Transaction Isolation Levels:
http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/
http://msdn.microsoft.com/en-us/library/ms173763.aspx
http://msdn.microsoft.com/en-us/library/aa259216%28v=sql.80%29.aspxhttp://msdn.microsoft.com/en-us/library/aa259216%28v=sql.80%29.aspx
http://www.sql-server-performance.com/2007/isolation-levels-2005/
http://databases.about.com/od/sqlserver/a/isolationmodels.htm

Regards,

Hi prathap refer to the below link, explained very clearly about Transaction Isolation Levels with examples,

http://www.sqllion.com/2009/07/transaction-isolation-levels-in-sql-server/

Hope it helps you.

Thanks Kalpana......

But i want the meaning of that query transaction isolation level=2 or 3 like
PleASE IN THIS SCENARION...ANY ARTICLE......

In servers where we can see the isolation levels.....

set transaction isolation level 3
 

Applications that use transaction isolation level 3 should set that isolation level at the beginning of each session. However, setting transaction isolation level 3 causes Adaptive Server to hold any read locks for the duration of the transaction. If you also use the chained transaction mode, that isolation level remains in effect for any data retrieval or modification statement that implicitly begins a transaction. In both cases, this can lead to concurrency problems for some applications, since more locks may be held for longer periods of time.

To return your session to the Adaptive Server default isolation level:

set transaction isolation level 2 or set transaction isolation level repeatable read

To enforce transaction isolation level 2 from a query, use:

select title_id, price, advance from titles at isolation 2

or

select title_id, price, advance from titles at isolation repeatable read

Transaction isolation level 2 is supported only at the transaction level. You cannot use the at isolation clause in a select or readtext statement to set the isolation level of a query to 2. http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@ebt-link;pt=53713?target=%25N%15_54131_START_RESTART_N%25