How to shield record selections from other datasets that share similar properties

Question

I wrote a script to update classic header/detail records. I start by getting all the records I want to update, where my dataset is for book orders only.

SELECT *FROM table1 a

INNER JOIN table2 on table2.ID = table1.ID

WHERE table1.Status = 'New' and ((SELECT COUNT(*) from table2 where ID = a.ID) =

(SELECT COUNT(*) from table2 where ID = a.ID and product = 'Book'))

We had a scenario recently where my script was running at 6pm, when another employee inserted new orders for only bicycles, and Status = 'New', also at 6pm.

Can my script pick up the other dataset under some condition?

Someone suggested to me that my WHERE clause actually does two almost different things:

1. Gets records from the header table that are 'New'
2. Gets records from the detail table that have all books on order.

The idea was proposed that if resources are dragging, then step #2 could potentially execute slower than step #1, in this one-to-many relationship.

If that's true, then is it possible for step #1 to pick up other records that are 'New' but for bicycles?

It appears this happened to me recently, and I'm trying to figure out if my script is not optimized.

Answer 1

We had a scenario recently where my script was running at 6pm, when another employee inserted new orders for only bicycles, and Status = 'New', also at 6pm. Can my script pick up the other dataset under some condition?

As they say, it depends. If the database is set to READ_COMMITTED_SNAPSHOT (RCSI), and you are indeed using the isolation level READ COMMITTED, you query will return the data that was committed when the query started running. (This is a little blurred, if your query calls a user-defined function.) So if the new row was submitted after you query started running, you will not see it.

The same is true if you are using true SNAPSHOT isolation level, and this case, there is no blurring with user-defined functions.

But if you are using the default isolation level READ COMMITED and RCSI is not enabled for the database you may or may not see the new row. This depends on the query plan and a few more things. The same is basically true for the higher isolation levels REPEATABLE READ or SERIALIZABLE, although in the latter case, it is possible that the write will be blocked until your query has completed.

As for the query, it appears that you only want to find orders that are all about books, and if there are also bicycles on the order, you don't care. A simpler way to write that condition would be:

WHERE table1.Status = 'New' 
   AND NOT EXISTS (SELECT *
                  FROM    table2 b
                  WHERE   b.ID = a.id
                    AND   b.Product <> 'Book')