...
...
SQL Server 2005 added two new isolation levels, Read Committed Snapshot and Snapshot, to enhance concurrency for OLTP applications. These isolation levels determine what locks SQL Server takes when accessing data and, therefore, by extension they determine the level of concurrency and consistency that statements and transactions experience. In earlier versions of SQL Server, concurrency was based solely on locking, which caused blocking and deadlocking problems for some applications. Snapshot isolation depends on enhancements to row versioning and is intended to improve performance by avoiding reader-writer blocking scenarios. All of these isolation levels are described in the following Microsoft Development Network article:
...
...
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.
...
Perform this procedure to enable SQL Server snapshot isolation. Ensure that you understand the information in the Understanding SQL Server Snapshot Isolation Levels before going ahead and enabling snapshot isolation.
- Log into SQL server management studio with a sa-level user account.
- Open a new query.
Run the following query to enable the READ_COMMITTED_SNAPSHOT option:
/** Run this statement
to
change the
isolation
level
of
the
database
.
When
the READ_COMMITTED_SNAPSHOT
database
option
is
set
ON
, the mechanisms used
to
support the
option
are activated immediately.
When
setting the READ_COMMITTED_SNAPSHOT
option
,
only
the
connection
executing the
ALTER
DATABASE
command
is
allowed
in
the
database
. There must be
no
other
open
connection
in
the
database
until
ALTER
DATABASE
is
complete. The
database
does
not
have
to
be
in
single-
user
mode.
**/
ALTER
DATABASE
APTIFY
SET
SINGLE_USER
WITH
ROLLBACK
IMMEDIATE;
ALTER
DATABASE
Aptify
SET
READ_COMMITTED_SNAPSHOT
ON
;
ALTER
DATABASE
APTIFY
SET
MULTI_USER;
Run the following query to enable the ALLOW_SNAPSHOT_ISOLATION option:
/** Statement below can be used
to
turn
on
the snapshot
isolation
, so that transactions can
set
the snapshot
isolation
level
. Per our analysis, there
is
no
Aptify code that currently does this, therefore this
is
not
required.**/
ALTER
DATABASE
Aptify
SET
ALLOW_SNAPSHOT_ISOLATION
ON
;
Run the following query to verify the values of these options:
/** Query
to
determine snapshot states **/
SELECT
Name
, snapshot_isolation_state
, snapshot_isolation_state_desc
, is_read_committed_snapshot_on
FROM
sys.databases
WHERE
name
=
'APTIFY'
;