Concurrency and reads

[w***@gmail.com]

It's been puzzling to me for a while whether i should address a certain scenario regarding optimistic concurrency.

I'm wondering whether the following scenario can occur.

When reading from the database i read an order and its orderlines in a join statement using optimistic concurrency.

My question is: Could it be that when using optimistic concurrency
Step 1: Sql server reads the orderlines
Step 2: Another transactions changes the orderlines and the order
Step 3: Sql server joins the orderlines on the order (so it effectively joins the old orderlines to the updates order
Step 4: Sql server returns the result.
Step 5: I am presented with data the actually never existed in the database at the same time.

My questions are:
- Can it happen the the result of my query contains data that whas never actually in the database at the same time?, or do i get the guarantee from sql server that data data returned from the query whas at one point in time together in the database?
- If not: What is the features/patterns that sql server uses to prevent this from happening:
- My example uses a join. Perhaps this scenario can never happen on a join, but can happen on a subselect?

Thanks for taking the time to read and possibly answer my question!
Kind regards
Sjoerd

[Erland Sommarskog]

Post by w***@gmail.com
I'm wondering whether the following scenario can occur.
When reading from the database i read an order and its orderlines in a

join statement using optimistic concurrency.

Post by w***@gmail.com
My question is: Could it be that when using optimistic concurrency
Step 1: Sql server reads the orderlines
Step 2: Another transactions changes the orderlines and the order
Step 3: Sql server joins the orderlines on the order (so it effectively
joins the old orderlines to the updates order
Step 4: Sql server returns the result.
Step 5: I am presented with data the actually never existed in the
database at the same time.
- Can it happen the the result of my query contains data that whas never
actually in the database at the same time?, or do i get the guarantee
from sql server that data data returned from the query whas at one point
in time together in the database?

That depends on your isolation level. The only isolation level that
gives you a guarantee is snapshot isolation, because with snapshot
isolation, you read the data from the database as it was in a certain
point in time.

In practice, it will also work this way if you have the isolation level
READ COMMITTED, and the database is configured with
READ_COMMITTED_SNAPSHOT. In this case, every query will read data from
the database as it was at certain point in time, but this is an artifact
of the implementation, and it is not true if the query includes calls
to user-defined function that makes data access on their own.

With READ COMMITTED without the snapshot, you could certainly get data
that never existed in combination. With REPEATABLE READ and SERIALIZABLE it
is less likely, at least if the other process is making changes in both
tables, it's probably possible to work out scenarios where you get
results that never existed as such.