Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.server > #18810

Re: Concurrency and reads

Show all headers | View raw

 (westerhofsjoerd@gmail.com) writes:
> 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? 

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.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to microsoft.public.sqlserver.server | Previous | Next — Previous in thread | Find similar

Thread

Concurrency and reads westerhofsjoerd@gmail.com - 2017-10-30 02:21 -0700
  Re: Concurrency and reads Erland Sommarskog <esquel@sommarskog.se> - 2017-10-30 10:30 +0100

csiph-web