Databases
StackOverflow2013
Postsdbo
POIncorrect results from READ UNCOMMITTED transaction level beyond expected behavior
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POIncorrect results from READ UNCOMMITTED transaction level beyond expected behavior
    primarykey
    data
    text
    <p><strong>Preface:</strong> I'm looking to move towards using snapshot isolation in the future, but for now, I'd like to understand what's going on with my query.</p> <p>I understand that dirty reads (READ UNCOMMITTED, NOLOCK) don't just ignore locks, but that they can also be subject to inconsistent results due to things like page splits occurring <em>during</em> a dirty read. However, I'm seeing the same incorrect results returned every single time which I can't associate with any of the consistency pitfalls I've read about. </p> <p>I have a large table called RUN which has millions of rows in it. We never UPDATE rows, we only INSERT. I have one query that's trying to retrieve data coming in within the last few days. This query is running with READ UNCOMMITTED, and it always returns the same incorrect results.</p> <h2>Here's an example of my table structure:</h2> <pre><code>CREATE TABLE run { id INT IDENTITY(1,1) NOT NULL , someTableID INT NOT NULL , locationID INT NOT NULL , statusID INT NOT NULL , value INT NULL , date_time DATETIME NOT NULL , CONSTRAINT runs_pk PRIMARY KEY CLUSTERED (id asc) } -- My queries automatically use this index, and I only get correct results when READ COMMITTED CREATE NONCLUSTERED INDEX run_ix ON run ( someTableID, locationID, date_time) INCLUDE (statusID) -- When I force my queries to use this index, I get correct results even when READ UNCOMMITTED CREATE NONCLUSTERED INDEX run_uk ON run ( date_time, locationID, statusID, someTableID, id) INCLUDE (value) </code></pre> <h2>Here's the problem queries:</h2> <p>I expect 60 results to come back, with dates between 2013-05-31 to 2013-06-05:</p> <pre><code>CREATE VIEW testView AS SELECT * FROM ( SELECT *, RANK() over (PARTITION BY someTableID, locationID ORDER BY date_time ASC) rnk FROM run WHERE statusID = 1 AND date_time BETWEEN '2013-05-31 00:00:00' AND '2013-06-08 00:00:00' ) a WHERE rnk = 1 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO -- Always returns 15 SELECT COUNT(*) FROM testView GO -- Always returns 15, 2013-06-05, 2013-06-05 SELECT COUNT(*), MIN(date_time), MAX(date_time) FROM testView GO -- Always returns 60 rows SELECT * FROM testView GO </code></pre> <p>Notice that selecting all the results actually returns all the rows, where as queries with aggregates return incorrect results.</p> <p>I was able to determine that switching to READ COMMITTED returned the correct results. Also, I tried rewriting the query in a way that caused it to use a different index, and that gave me the correct results as well (I don't have an example of this here).</p> <h2>Here are working queries:</h2> <pre><code>-- Always returns 60 SELECT COUNT(*) FROM testView WITH (INDEX( SET TRANSACTION ISOLATION LEVEL READ COMMITTED GO -- Always returns 60 SELECT COUNT(*) FROM testView GO -- Always returns 60, 2013-05-31 , 2013-06-05 SELECT COUNT(*), MIN(date_time), MAX(date_time) FROM testView GO -- Always returns 60 rows SELECT * FROM testView GO </code></pre> <p>The index in question had about 6% fragmentation. I'm not sure if this is representative of its normal fragmentation level, as we have a weekly job that rebuilds indexes over 15% or so. REORGANIZEing the index used by the query would temporarily cause the query to return the correct result even in a dirty read, but after a minute or two I'd go back to getting incorrect results. </p> <p>I noticed that immediately after reorganizing the index, its fragmentation was ~0.68% and would creep closer to 0.8% after a few minutes, probably from new data coming in. I'm not sure if the fragmentation is responsible for the incorrect results or not, but It's all I've got to go on right now.</p> <p>We only have around 500 inserts an hour. There's no way page splits are occurring every single time I run the query, resulting in incorrect data, and even if there were, it wouldn't explain to me why I always get the same results back.</p> <p>Note: we have hundreds of other dirty read queries, and have for years. I can't say this is the first and only time there's ever been a problem, bu it's the first time we've ever identified this issue.</p> <p>Does anyone have any insight as to what might be going on? Could it be related to the fragmentation level of the index? </p> <h2>Update:</h2> <p>I think the problem has something to do with IAM's and the fact that my queries are performing an Allocation Order Scan. </p> <p>The value column isn't contained in an index. Running this query causes the index scan to be set to ORDERED: TRUE, and I get the proper results </p> <pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT value FROM testView </code></pre> <p>The date_time column is contained in an index. Running this query causes the index scan to be set to ORDERED: FALSE, and I get the wrong results</p> <pre><code>SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED GO SELECT date_time FROM testView </code></pre>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjhsheets
    1. USjhsheets
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COWhat happens if you force no parallelism `WITH (MAXDOP 1)`?
      singulars
      1. POIncorrect results from READ UNCOMMITTED transaction level beyond expected behavior
      1. USErikE
    2. COI'll try that tomorrow. Any reason why you think that could be a problem?
      singulars
      1. POIncorrect results from READ UNCOMMITTED transaction level beyond expected behavior
      1. USjhsheets
    3. COIt's just a hunch--may not be anything. It's good to get rid of parallelism when trying to understand SQL Server strange behavior, because it *has* caused problems in the past.
      singulars
      1. POIncorrect results from READ UNCOMMITTED transaction level beyond expected behavior
      1. USErikE
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload