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.
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.
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..
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