If an instance of SQL 2005 was in use and was using row versioning,
under what circumstances would the below error occur?

Transaction (Process ID 56) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction

We used to get this sort of thing when a large copy process was running
under a transaction, but all it was doing was reading the records and
creating brand new records yet would still lock the entire table. Once
we enabled the row versioning, we stopped having this issue, but it
seems that there are some circumstances in which it still happens, i.e.
the above error.

Any ideas how that might occur?

pb648174 (google@webpaul.net) writes:
> If an instance of SQL 2005 was in use and was using row versioning,
> under what circumstances would the below error occur?
>
> Transaction (Process ID 56) was deadlocked on lock resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction
>
> We used to get this sort of thing when a large copy process was running
> under a transaction, but all it was doing was reading the records and
> creating brand new records yet would still lock the entire table. Once
> we enabled the row versioning, we stopped having this issue, but it
> seems that there are some circumstances in which it still happens, i.e.
> the above error.
>
> Any ideas how that might occur?

Without knowledge of the code, and not have seen the deadlock trace?
Not even knowing which of the two varities of snapshot isolation
you are using. SET TRANSACTION LEVEL SHAPSHOT, or READ COMMITTED
SNAPSHOT?

To get a deadlock trace in the SQL Server error log, enable trace
flags 1222 and 3605. (It used be 1204, but 1222 is a new flag, which
gives better information.)

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

I didn't realize there were multiple kinds.. We are using ALTER
DATABASE DBName SET READ_COMMITTED_SNAPSHOT ON;

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.

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Ahhhhh... Now we are getting somewhere. I think other transactions are
set as serializable, so that would explain it. Thanks for the tip.