|
|
Posted by Erland Sommarskog on 09/30/09 11:48
pb648174 (google@webpaul.net) writes:
> I didn't realize there were multiple kinds.. We are using ALTER
> DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;
The other one you achieve with ALTER DATABASE db SET
ALLOW_SNAPSHOT_ISOLATION ON. Transactions what want snapshots, then
need to say SET TRANSACTION ISOLATION LEVEL SNAPSHOT.
The two yields slight different results. Pure shapshot isolation, gives
you the state of the database as it looked when the transaction started.
Read Committed Snapshot Isolation (RCSI) is an alternate implementation
of the read committed isolation level. An RCSI transaction can pick up
data that did not exist when the transaction started, but that committed
before the transaction came about to read it.
> My questions is more of a general one - If row versioning is being used
> and a particular record is involved in a transaction, should other
> transactions just get the older version and not have to respect any
> locks? We are seeing blocking happen for normal read operations, which
> seems like it shouldn't happen. A write blocking I could see, but the
> read blocking doesn't make sense to me.
Without any repro it's difficult to comment things out of the blue. However,
note that if you are using alternate isolation level, either by
SET TRANSACTION ISOLATION LEVEL or by query/table hints, the snapshot is
not involved. For instance, run this in one query window:
CREATE TABLE hubba (a int NOT NULL PRIMARY KEY)
go
INSERT hubba(a) VALUES (12)
go
BEGIN TRANSACTION
go
INSERT hubba(a) VALUES (2)
go
Then in another window run:
SELECT MAX(a), MIN(a) FROM hubba
This returns (12, 12). Now try_
SELECT MAX(a), MIN(a) FROM hubba WITH (REPEATABLEREAD)
This blocks, because the isolation level is no longer READ COMMITTED.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|