Can an update interfere while running a select query

Hello, considering
DROP TABLE #t
CREATE TABLE #t
(
	Field1 INT
)

INSERT INTO #t (Field1) VALUES (1)

SELECT *
FROM (SELECT Field1 FROM #t WHERE Field1>0) a
UNION
SELECT *
FROM (SELECT Field1 FROM #t WHERE Field1<1) b

Open in new window

,
is it possible that while running
UPDATE #t set Field1=0

Open in new window

and the above Select query, could it be that the Select is returning two rows? One with value 0 and one with value 1? Is it possible that the UPDATE query is run between the SELECT queries that are UNIONed?
lefodnesAsked:
Who is Participating?
 
Steve WalesSenior Database AdministratorCommented:
I would disagree with Leo's statement.  In an OLTP system, you're highly likely to have selects and updates running at the same time.

The behavior of the database engine in how it handles these types of concurrent access  is called the isolation level.

You can read about isolation levels here and how SQL Server controls them here:
https://technet.microsoft.com/en-us/library/ms189122%28v=sql.105%29.aspx

Further reading about pessimistic vs optimistic concurrency control can also be read about here:
https://technet.microsoft.com/en-us/library/ms189132%28v=sql.105%29.aspx

Getting back to the core of your question, my answer would be no, the select statements in the union are handled as a part of a single transaction - as long as you have the database isolation level set to something other than READ UNCOMMITTED.

If you have your isolation level set to that (usually not recommended), I guess it's possible (indeed probable if there are updates going on) that you could read strange and unexpected data.

However, if you're running in default modes, it shouldn't be possible.  The UNION'ed select statement is a part of a single transaction and should be seeing a consistent view of the data for the length of the unit of work.

(For complete disclosure, there's also snapshot isolation which is another thing entirely.  Read about that here: https://msdn.microsoft.com/en-us/library/tcbchxcb%28v=vs.110%29.aspx)
0
 
Leo TorresSQL DeveloperCommented:
Never a good idea to run select while update is in progress or vise versa. Deadlocks are bound to happen or the data might get dirty(meaning old not current).

What is your end game here what are you trying to do?
0
 
lefodnesAuthor Commented:
Actually, I'm searching for an error, where it seems two subqueries in one query (select) reads different data based on the same table. This is obviously not the real query. I have several simultaneous processes (aspx files that run queries). Some selects and some updates. I'm thinking, could it be that one update is changing the table while a "complex"select query with subqueries is running, using the same table? I can't avoid them running at the same time, as I see it. Our how could I?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
lefodnesAuthor Commented:
Rephrasing, how can I make sure data is consistent throughout the select query? I really thought it already was by default... If I'm wrong, I've been extremely lucky over the last years...
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
If between the INSERT and SELECT an UPDATE occurs, then yes, your SELECT will work with the updated data.
The only way to not return the updated data is to work with transactions so in your example should be:
BEGIN TRAN

INSERT INTO #t (Field1) VALUES (1)

SELECT *
FROM (SELECT Field1 FROM #t WHERE Field1>0) a
UNION
SELECT *
FROM (SELECT Field1 FROM #t WHERE Field1<1) b

COMMIT

Open in new window

NOTE: I'm assuming the table #t isn't a temporary table in the real situation otherwise you would never have this issue since temporary tables only exists in the current connection even if a different connection works with the sample temporary name.
0
 
lefodnesAuthor Commented:
Oh. Sorry. Never mind the insert. What is recurring, is only the select and the update. Then what? Do I need the begin end transaction?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need transactions just for select's since they aren't change data.
I wouldn't mind with the updates then since that's the way a RDBMS works.
0
 
lefodnesAuthor Commented:
Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.