Sql-server – How do these snapshot isolation level configurations interact on a SQL Server 2005 instance

isolation-levelsql serversql-server-2005

I started troubleshooting a tempdb issue we are having on SQL Server 2005 Enterprise Edition. A developer is getting an error that tempdb is out of space. Technically, the error is:

Transaction aborted when accessing versioned row in table
'dbo.inserted' in database 'dbname'. Requested versioned row was not
found. Your tempdb is probably out of space. Please refer to BOL on
how to configure tempdb for versioning

I looked at the database configuration in sys.databases and found the following settings:

snapshot_isolation_state: 0

snapshot_isolation_state_desc: OFF

is_read_committed_snapshot_on: 1

I looked up what this meant in BOL and the salient information is as follows:

snapshot_isolation_state

State of snapshot-isolation transactions being allowed, as set by the ALLOW_SNAPSHOT_ISOLATION option:

0 = Snapshot isolation state is OFF (default). Snapshot isolation is disallowed.

1 = Snapshot isolation state ON. Snapshot isolation is allowed.

2 = Snapshot isolation state is in transition to OFF state. All transactions have
their modifications versioned. Cannot start new transactions using
snapshot isolation. The database remains in the transition to OFF
state until all transactions that were active when ALTER DATABASE was
run can be completed.

3 = Snapshot isolation state is in transition to
ON state. New transactions have their modifications versioned.
Transactions cannot use snapshot isolation until the snapshot
isolation state becomes 1 (ON). The database remains in the transition
to ON state until all update transactions that were active when ALTER
DATABASE was run can be completed.

snapshot_isolation_state_desc

Description of state of
snapshot-isolation transactions being allowed, as set by the
ALLOW_SNAPSHOT_ISOLATION option:

  • OFF
  • ON
  • IN_TRANSITION_TO_ON
  • IN_TRANSITION_TO_OFF

is_read_committed_snapshot_on

1 = READ_COMMITTED_SNAPSHOT option is ON. Read operations under the
read-committed isolation level are based on snapshot scans and do not
acquire locks.

0 = READ_COMMITTED_SNAPSHOT option is OFF (default). Read operations
under the read-committed isolation level use share locks.

If I'm understanding this correctly, snapshot_isolation_state is OFF and disallowed. However, the 1 on is_read_committed_snapshot_on indicates that the database is using snapshot isolation. How is this possible if it's disallowed?

Best Answer

As @AlexKuznetsov noted, SNAPSHOT and READ_COMMITTED_SNAPSHOT are two different isolation levels. As such, snapshot_isolation_state indicates the former, while is_read_committed_snapshot_on the latter. Here are a few comments that I found that summarize the differences between the two:

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.

~Bill Paetzke here

and ...

[READ_COMMITTED_SNAPSHOT] differs from the SNAPSHOT isolation level in that instead of providing a reader with the last committed version of the row that was available when the transaction started (SNAPSHOT ISOLATION), a reader gets the last committed version of the row that was available when the statement started (READ_COMMITTED_SNAPSHOT).

~Uri Dimant (emphasis mine), here

Related Question