SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 transaction isolation level is set to Read Committ
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nomikhanxyz
Starting Member

6 Posts

Posted - 06/27/2013 :  08:42:47  Show Profile  Reply with Quote
What, if any, locks does a SELECT statement place on tables, assuming the transaction isolation level is set to Read Committed. If it does place a lock, what is the effect of that lock? please if can someone Explain in detail with an example. Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/27/2013 :  11:01:34  Show Profile  Reply with Quote
SELECT places shared locks on objects and holds it until reading of data is over. it may block other DML statement as well as select statements until it completes read. By using SNAPSHOT ISOLATION you can avoid it from locking other select queries though.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4612 Posts

Posted - 06/27/2013 :  11:06:49  Show Profile  Reply with Quote
quote:
Originally posted by visakh16

By using SNAPSHOT ISOLATION you can avoid it from locking other select queries though.

At a cost of higher IO. :)
Go to Top of Page

nomikhanxyz
Starting Member

6 Posts

Posted - 06/27/2013 :  11:21:45  Show Profile  Reply with Quote
First of all thank you very much for your response
I am still confuse or in the middle ,so in case What is the effect of setting the transaction isolation level to Read Uncommitted? if its is higher IO cost..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/27/2013 :  11:56:33  Show Profile  Reply with Quote
Read Uncommitted is different from SNAPSHOT ISOLATION.Read Uncommited is least restrictive of locking modes where you will even have access to data used by uncommited transactions which may cause dirty reads (if transaction rollsback later). SNAPSHOT ISOLATION will give you a read only copy of data as that existed before start of current open transactions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000