CODEWITHSUNDEEP
sql-server
P. Kurz
P. Kurz

Reputation: 13

dirty reads: Different results within single query?

In SQL Server 2014, when I issue the following SQL:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
WHERE NOT EXISTS (
    SELECT 1 FROM TableA WHERE TableA.ID = TableB.ID
)

Is it possible to read different versions of one table even within a single Statement because of dirty reads?

Example: Reading 2 Rows from TableA in the first part of the union but reading just 1 Row from TableA in the inner select of the second part of the union because one row got deleted by another transaction meanwhile.

Upvotes: 1

Views: 203

Answers (2)

Jeroen Mostert
Jeroen Mostert

Reputation: 28789

Short answer: yes, depending on the execution plan generated. It doesn't matter that you're doing it in a single statement; no special privileges are associated with a statement boundary. READ UNCOMMITTED means no locking on data for any reason, and that's exactly what you'll get. This is also why using that generally is very much not recommended; it's terribly easy to get inconsistent/"impossible" results. Heck, even a single SELECT is not safe: you're not even guaranteed that rows will not be skipped or duplicated!

Upvotes: 1

Andrey Nadezhdin
Andrey Nadezhdin

Reputation: 126

Seems to me its god damn possible.

The query execution plan will look like this: Execution plan example

It looks like there will be two different reads from TableA, so it really depends on time delay between them and amount of CRUD operation made to those table.

READ UNCOMMITTED is really not so great choice for such query.

Upvotes: 1

Related Questions