I have two tables (SJob & SJobDependent) that I need to join for some logic in a stored procedure. They both have a column (job) that connects them in a one-to-many relationship - one SJob record for zero or more SJobDependent records.

Here is my SQL query:

-- Return any records that are active and have no unsatisfied dependencies.
LEFT JOIN SJobDependent
    ON SJob.job = SJobDependent.job
    AND SJobDependent.satisfied = 0
WHERE SJobDependent.jobDependentID IS NULL
AND SJob.state = 'active'

Here is the Actual Execution Plan from SQL Server Studio:

SQL Server Studio Actual Execution Plan

Due to the way the code is written:

// Pseudo-code:

// SJob record is added with SJob.state = 'ready'.

// Related SJobDependent record(s) are added.

// SJob record is updated to SJob.state = 'active'.

I fear that this may happen when the SQL query runs:

  1. Scan SJobDependent.
  2. SJobDependent record(s) inserted.
  3. Start scan of SJob. SJob.state is 'ready'.
  4. SJob is updated. This blocks reading of SJob?
  5. End scan of SJob. SJob.state is 'active'.

The problem I fear is that my SQL query returns SJob records found in the "active" state (SJob.state = 'active'), but fails to see the related SJobDependent records.

Is this problem capable of happening, or am I over-analyzing the SQL query?

If this is a legitimate problem to worry about, what can I do to solve it? I'm open to solutions.

One idea I've had is to force the scan of SJobDependent to occur after the scan of SJob. Is this even possible? What are the implications/consequences of doing this?

Do the scans shown in the Actual Execution Plan occur in a particular order or is it always random from call-to-call?

NOTE: As noted in AMtwo's answer, Repeatable Read isolation level will probably not solve my problem, due to the fact that it only takes effect when the read starts.

  • This depends on several interrelated things. 1) Is the pseudo-code from another sql connection/process, wrapped in a transaction? 2) Is your SELECT code above part of a transaction or part of a sql batch that has already executed a DML? 3) What is the isolation levels of both processes (or of the DB if not set anywhere else)? – RBarryYoung Dec 27 '19 at 16:11
  • If you are performing your updates using the same transaction, Then I don't see that kind of problems you described in read committed isolation level – madz Dec 27 '19 at 21:34
  • The code referenced is old legacy code. It unfortunately does not use transactions. – Max Jacob Dec 27 '19 at 23:51
  • ".. repeatable Read isolation level will probably not solve my problem" - then RCSI might not either since it copies the data at the start of the statement i.e. each statement in a batch see data at a different point in time. You many need SNAPSHOT or SERIALIZABLE. – Michael Green Dec 29 '19 at 11:59

If you're using the default isolation level in SQL Server (Read Committed), then you certainly can run into all sorts of issues around inconsistent reads. Paul White describes the problems here.

If you want your read queries to read data which is fully consistent to how it looked at a given point in time, I'd recommend that you consider Read Committed Snapshot Isolation (RCSI). With RCSI, your query will return data that is consistent to a single point in time (the start of your query). If User A starts a SELECT query while User B is concurrently performing updates, User A will read the "old" value because it will read a snapshot of the data, which is consistent to the start of the query.

The catch with RCSI is that it's a database-level setting. Unlike Read Uncommitted, you can't set it as a session-scoped setting. You'll have to consider this change more globally before making the change. However generally speaking, if you require consistent reads for this query, you probably want consistent reads for the entire application.

While the Repeatable Read isolation level may look appealing to solve your problem, but note this detail from the linked post:

The repeatable read isolation level provides a guarantee that data will not change for the life of the transaction once it has been read for the first time.

This means that the data can still be changed prior to being accessed, but during the time your query is running. It is also subject to some of the same inconsistent reads as the Read Committed isolation level--notably phantoms.

  • I believe you are correct in stating that Repeatable Read will not solve my problem. RCSI seems like the most probably solution, although I will have to think over the implications of changing a database-wide setting. – Max Jacob Dec 27 '19 at 21:22

You need to think about how the workload as a whole is running, how your set of statements sees work done by others and how you see theirs. You should also consider the possible timings of actions from two concurrently running streams of work.

It is not clear from your question but I'm going to assume the query ("Here is my SQL query") runs in one session (S1) and the pseudo code runs in another (S2). I'm supposing each pass through the pseudo code inserts only one job. The question is what of S2's work can S1 see since S1 can run, in full or in part, at any point in S2's stream?

I'm going to say "none". I say this because the query specifically looks for SJob.state = 'active'. S2 inserts a value in SJob with state 'ready'. So S1 will not read this row. Yes, the row may be inspected by S1 (depending on S1's isolation level) but it will be rejected because of the status value and will never become part of S1's result set. So even though SJobDependent is the build side of the hash join any rows retrieved will be rejected in the Filter operator and never become part of S1's output.

It is not until rows exist in both tables that S2 sets status to a value that S1 can read. That update is guaranteed to be atomic (it either works fully or is fully rolled back). Since only one value in one row is updated it will be isolated - S1 can never see a value part way between 'ready' and 'active'.

Without the predicate on status there could have been issues. As long as S1 runs at READ COMMITTED or higher, however, there would still be no problem. S2 could put an explicit transaction around the whole block. Then its exclusive locks would be held until the end (until a consistent set of rows exists in both tables). S1 would either have completed before these locks were in place, seeing only consistent data, or would wait until these locks were released again seeing consistent data. If S1 runs with READ UNCOMMITTED (or NOLOCK, same thing) it would see S2's work half way through and errors would ensue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.