Transaction Isolation Level - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Transaction Isolation Level

Author

Topic

merge
Starting Member

1 Post

Posted - 2011-02-02 : 10:31:10

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
[url]http://msdn.microsoft.com/en-us/library/ms173763.aspx[/url]

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

2 Posts

Posted - 2011-02-05 : 07:47:30

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources