10/21/2011 7:12:38 AM
Amit Bansal -
We know that READ UNCOMMITTED Isolation level allows dirty read. That is, neither does it ask for locks nor does it honor locks held by other transactions. This is true in case of reading and writing data. But the story is different if another transaction has issued a DDL statement. Let us see.
In a query window, execute the following code:
-- connection 1
ALTER TABLE Person.Person
ADD Address2 nvarchar(100);
In another query window, read from the table under READ UNCOMMITTED isolation level.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
WHERE LastName = 'BANSAL';
And you will observe that the above transaction will wait indefinitely even though it is running under READ UNCOMMITTED isolation level.
In a new query window, say connection 3, observe the locks.
Following is what you will observe (your session ids might be different)
Apart from number of locks being held by session 57 (which has issued the DDL statement), it has been granted the Sch-M (Schema Modification lock). And session 56 (running under READ UNCOMMITTED) isolation level waits.
ROLLBACK both the transactions.
You can see that READ UNCOMMITTED does honor locks.
If you liked this post, do like us on FaceBook at http://www.FaceBook.com/SQLServerGeeks
Also tweet and pingback if you wish so.
Have a SQL Server question? Join the fastest growing SQL Server facebook group at: http://www.facebook.com/groups/458103987564477/
Regards, Amit Bansal
http://www.twitter.com/A_Bansal http://www.twitter.com/SQLServerGeeks http://www.amitbansal.net/ Visit my FaceBook page at http://www.facebook.com/AmitRSBansal Contribute on SQLServerGeeks.com: visit http://www.sqlservergeeks.com/default-category/write-for-us
Amit Bansal (Member since: 3/12/2011 4:59:54 PM)
Follow Amit at Twitter @A_Bansal : Amit Bansal is the CTO of eDominer Systems & Peopleware India. He is a consultant, trainer, writer, speaker & evangelist on SQL Server & Business Intelligence. A seasoned speaker; he speaks at major summits and conferences of Microsoft worldwide including TechED, MCT Summits, MSDN & TechNet conferences. Over the last 8 years, he has consulted, trained & mentored more than 6000 IT professionals on SQL Server & Business Intelligence and worked with top notch blue chip companies worldwide including Microsoft, Infosys, Wipro, RBS, HCL, HP, Siemens, IBM, Accenture, etc. He has delivered more than 400 workshops on SQL Server & Business Intelligence. Apart from holding many Microsoft credentials, he is also a Microsoft Most Valuable Professional (MVP) awardee on SQL Server. He is also one of the first Microsoft Certified Trainer in India. Amit has worked with Microsoft in India and US as a Subject Matter Expert in various capacities participating in OD sessions, technical reviewing, etc. He is also a very active community lead in Asia Pacific. Visit www.amitbansal.net to know more. Subscribe to Amit’s blog and be a member of his technical forum at www.SQLServerGeeks.com - an exclusive SQL Server portal. Visit www.peoplewareindia.com for Corporate Training solutions.
FaceBook – http://www.facebook.com/people/Amit-Bansal/525339346
LinkedIn – http://www.linkedin.com/pub/amit-bansal/7/121/755
View Amit Bansal 's profile
Leave a comment