I have two tables (
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
Here is my SQL query:
-- Return any records that are active and have no unsatisfied dependencies. SELECT * FROM SJob 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:
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:
- Scan SJobDependent.
- SJobDependent record(s) inserted.
- Start scan of SJob. SJob.state is 'ready'.
- SJob is updated. This blocks reading of SJob?
- 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
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.