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
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

merge
Starting Member

1 Posts

Posted - 02/02/2011 :  10:31:10  Show Profile  Reply with Quote
Hi!

I'm studying isolation levels but can not get next snippet

quote:

Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data. This option is the SQL Server default.



from here
http://msdn.microsoft.com/en-us/library/ms173763.aspx

I've tried test changing data from another session but in vain.

Here is session 1


SET TRAN ISOLATION LEVEL READ COMMITTED;
GO
	BEGIN TRAN
	GO
		DECLARE @pop int
		DECLARE curCities CURSOR FOR 
			SELECT Population FROM Cities
		
		OPEN curCities
		
		FETCH NEXT FROM curCities 
		INTO @pop
		
		WHILE @@FETCH_STATUS = 0
		BEGIN
		
		
			UPDATE Cities SET population = 456 WHERE current of curCities
			--go
		
			WAITFOR DELAY '00:00:01'		
			
			FETCH NEXT FROM curCities 
			INTO @pop			
		END
	
		CLOSE curCities
		DEALLOCATE curCities	
		
	
	COMMIT TRAN
	


Here is session 2 which try change data


SET TRAN ISOLATION LEVEL READ UNCOMMITTED;

	UPDATE Cities
	SET Population = 987
	WHere CityID < 5	


SELECT * from  Cities


What is wrong in my code?
Why second session is hung and how to see example of the snippet in work?

nbrezis
Starting Member

Israel
2 Posts

Posted - 02/05/2011 :  07:47:30  Show Profile  Reply with Quote
Hi merge,

The quote: "Data can be changed by other transactions between individual statements within the current transaction..."

is correct but valid only on data that has been read by the current transaction (not data that has been changed). Since in your main Transaction you changed data, these rows are locked and thus your second transaction is locked (until the first has finished).


You can still use your example to see the difference between
READ COMMITTED and READ UNCOMMITTED do the following:

1. Change the update in the main transaction to:

UPDATE Cities WITH (TABLOCK) SET population = 46 WHERE current of curCities

(make sure the population value is different than 46 - for the change to have effect)

2. Run the first Statement
3. Run in a new query window:

SET TRAN ISOLATION LEVEL READ UNCOMMITTED;
SELECT * from Cities

4. Run in a new query window:
SET TRAN ISOLATION LEVEL READ COMMITTED;
SELECT * from Cities

You should see that the statement in READ UNCOMMITTED runs while the statement in READ COMMITTED waits for the first transaction to finish.


Hope this helps,


SQL Server Consultant
Madeira
www.madeira.co.il

Edited by - nbrezis on 02/05/2011 07:50:11
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