Re: deadlocks

pb648174 (google@xxxxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: Lock confusion
    ... SQL Server has to decide on a deadlock victim. ... DEADLOCK_PRIORITY option in your transaction to designate the victim ... In the 2005 studio manager the "Locks by Object" for the Color table ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Prevent deadlock in select
    ... Transaction was deadlocked on lock resources ... with another process and has been chosen as the deadlock victim. ... > transactions access the objects.Don't allow two processes to access to the ...
    (microsoft.public.sqlserver.programming)
  • Re: concurrent transactions
    ... value I will update before to update it, so I need a transaction that groups ... > As far as I'm aware the operation cannot create a deadlock. ... >> I'm on SQL Server 2000, and I need a pessimistic approach because data ... >>> reason to use optimistic concurrency. ...
    (microsoft.public.dotnet.framework.adonet)
  • Deadlock transaction
    ... I have a customer using our program with SQL server and is ... on lock resources with another process and has been chosen as the ... deadlock victim." ... then showing data in an HTML table for the user. ...
    (comp.databases.ms-sqlserver)
  • Data Tier / Transaction Problems
    ... This SqlTransaction has completed; ... Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim ...
    (microsoft.public.dotnet.framework.aspnet)