0

We have a job that must process each new record in a SQL Server table. This table has a column Created with a constraint which defaults this column's value to GETUTCDATE(). The job's procedure is as follows:

  1. The job queries for all records whose Created value is greater than or equal to the last seen Created value
  2. The job processes all returned records
  3. The job updates its last seen Created value to the highest Created value it saw in the records.

This seems to work pretty well, however, we have noticed that the job sometimes skips records. We believe this may be because of a scenario like:

  1. Transaction A starts to insert a new record. SQL Server defaults the Created value to 1
  2. Transaction B starts to insert a new record. SQL Server defaults the Created value to 2
  3. Transaction B commits
  4. Job processes the table and sees the record created by transaction B. The job updates its last seen Created value to 2.
  5. Transaction A commits
  6. Job processes the table but "skips" the record created by transaction A as its Created value is smaller than the last seen Created value.

Is such a scenario possible? Will a read operation not block until the pending insert transactions are completed?

Loic Sharma
  • 32
  • 5
  • 3
    Are you using transactions? If so, what isolation level? - [SQL Server Isolation Levels: A Series - Paul White](https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels) – SqlZim May 15 '17 at 16:51
  • Also check out this answer by Remus Rusanu :http://stackoverflow.com/a/4036063/2975396 – TheGameiswar May 15 '17 at 16:57
  • Interesting! If I understand the provided links correctly, the isolation level only modifies the behaviour of multiple reads within one transaction, right? If so, the isolation level won't affect the behaviour of reads in independent transactions, and as a result, won't fix our issue. – Loic Sharma May 15 '17 at 18:21

1 Answers1

0

You describe situation when processing job use READ UNCOMMITTED isolation level (select * from Table with (nolock) or SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in script). Please check that job select data from table in READ COMMITTED (remove hint with nolock) list.

Alexey Varentsov
  • 376
  • 1
  • 2