February 5, 2010 at 5:04 am
Dear colleagues, please help me to understand whether I’m hallucinating, having some unique trouble with environment, or just making some stupid mistake.
Please perform test described below before replying that it is not possible to happen. After couple of months wondering whether it is true or I’m nuts I’ve even tried to contact MS consultant yesterday (through another person) and his answer was no, it is possible to happen only in dirty reads isolation level. However, I’m afraid that he just did not take his time to perform suggested testing.
My statement is as following:
It is possible to get mix of old and new values in default (read committed) transaction isolation level with SQL 2005 and 2008 in following scenario:
1) Both connections run out of explicit begin/end transaction context (transaction did not change detected behavior as far as i remember, problem occurs on atomic level)
1) Connection 1 performs long-running select (long run is simulated by calling of slow scalar function)
2) Connection 2 performs unconditional update of some field in whole table
Resulted select of Connection 1 contains mix of old and new values.
Behavior is different in SQL 2000 - update gets blocked until the end of select.
Draft of code used for testing:
drop function dbo.f
go
create function dbo.f () returns int as begin
declare @i bigint, @txt nvarchar(4000)
select @i = 1, @txt = ''
--900000 has to be corrected according to server's speed
while (@i < 900000) select @txt = @txt + convert(varchar(30), @i), @i = @i + 1
return 1
end
go
drop table t
go
create table t (a varchar(10))
go
insert into t values ('sfsdf')
insert into t values ('sfsdf')
insert into t values ('sfsdf')
insert into t values ('sfsdf')
insert into t values ('sfsdf')
go
--1st connection (starts first)
select dbo.f(), * from t
--2nd connection (starts second)
update t set a = 'aaaaa'
Thank you, Victor
February 5, 2010 at 6:46 am
That's not a dirty read. You're not taking out an exclusive lock on the entire table when you start reading from it. Instead you're getting locks on each row and then releasing them. So what you're seeing is the update statement goes through for the rows that don't have a lock (all but the first one) and then, when the processing of the function completes, it gets the committed values on the rest of the rows. It sounds like you'd like the function to behave in a serial fashion, which is different.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 5, 2010 at 8:05 am
Thank you Grant,
Your answer gave me an insight to the roots of my misunderstanding. I was living with an idea of SELECT statement being an atomic operation.
But it appears that it is not. Author of link below is trying to explain this fact in more details: http://www.sqlservercentral.com/blogs/michael_coles/archive/2008/03/08/why-select-queries-are-not-dml.aspx
Additionally I was confused with “correct” behavior of SQL Server 2000 in same circumstances, and that I could not find any mention on its changing in SQL 2005 BOL’s “What’s new”.
In its own turn, definition of READ COMMITTED in SQL 2005 BOL states “Data can be changed by other transactions between individual statements within the current transaction, resulting in nonrepeatable reads or phantom data”, which somehow implies that possibility of data changes during runtime of individual statement should not be possible.
If statement of first connection is changed to “select into”, i.e. an operation causing data modification, then second connections obediently waits for its completeness.
Regards,
Victor.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply