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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.