read_committed_snapshot on

set transaction isolation level read committed
alter database mydatabase set read_committed_snapshot on
From the Sql Server docs :
"Row versioning is used to present each statement within the transaction
with a transactionally consistent snapshot of the data as it existed at the
start of the statement. Locks are not used to protet the data from updates
by other transactions."
But given that read queries can have complex execution plans, how can the
server know the set of rows that need to be versioned without blocking all
other transactions and figuring it out ( which would be the same as using
shared locks ) ?
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
When you set a database to READ_COMMITTED_SNAPSHOT, all row changes are
versioned independently of select queries. Select queries in the
READ_COMMITTED isolation level use the latest row versions available at the
time the SELECT statement started.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"John A Grandy" <johnagrandy@.gmail.com> wrote in message
news:eU1pJxfjIHA.5504@.TK2MSFTNGP05.phx.gbl...
> set transaction isolation level read committed
> alter database mydatabase set read_committed_snapshot on
> From the Sql Server docs :
> "Row versioning is used to present each statement within the transaction
> with a transactionally consistent snapshot of the data as it existed at
> the start of the statement. Locks are not used to protet the data from
> updates by other transactions."
>
> But given that read queries can have complex execution plans, how can the
> server know the set of rows that need to be versioned without blocking all
> other transactions and figuring it out ( which would be the same as using
> shared locks ) ?
>
>