Very Computer

    I am baffled can anyone give me some hints on what to do. I can easily
recreate this by running 3 clients looping through inserts in an MTS
component (TRANSACTIONAL).

More info:

SQL Server Version 7.0 SP 6

ADO Connection Errors collection object contained:
Source = (Microsoft OLE DB Provider for SQL Server). Description = (Your
transaction (process ID #29) was deadlocked with another process and has
been chosen as the deadlock victim. Rerun your transaction.). SqlState =
(40001). NativeError = (0x4b5).

Trace produced from SQL Server:

*** Deadlock Detected ***
 ==> Process 29 chosen as deadlock victim
 == Deadlock Detected at: 2000-11-01 19:37:55.51
 == Session participant information:
 SPID: 29 ECID: 0 Statement Type: INSERT Line #: 1
 Input Buf:   s p _ e x e c u t e s q l     c?   ?   I N S E R T   I N T O
" q
 u o t e _ t e s t " . . " C l i e n t "   ( " c l i e n t _ t y p e _ i d
" )

 SPID: 30 ECID: 0 Statement Type: INSERT Line #: 1
 Input Buf:   s p _ e x e c u t e s q l     c?   ?   I N S E R T   I N T O
" q
 u o t e _ t e s t " . . " C l i e n t "   ( " c l i e n t _ t y p e _ i d
" )

 == Deadlock Lock participant information:
 == Lock: KEY: 9:293576084:1 (ffffffffffff)
 Database: quote_test
 Table: Client
 Index: XIF111Client
  - Held by: SPID 29 ECID 0 Mode "IS-S"
  - Requested by: SPID 30 ECID 0 Mode "IIn-Null"
 == Lock: KEY: 9:293576084:1 (ffffffffffff)
 Database: quote_test
 Table: Client
 Index: XIF111Client
  - Held by: SPID 30 ECID 0 Mode "IS-S"
  - Requested by: SPID 29 ECID 0 Mode "IIn-Null"

Thanks

    Tim Miller

Interesting...

There are situations that result in deadlocks that are far from being
obvious where both the table and one of its indexes are involved. E.g., one
process locks a resource in the index and tries to obtain a lock on a
resource in the table, the other process locks a resource in the table and
tries to lock a resource in the index. and you have a deadlock.
In your case, it's hard to figure out the problem without knowing the
table/index structure and the statements involved in the transactions.
Profiler can be very efficient in trapping the chain of events that lead to
the deadlock.
You can trace the Lock:Deadlock, Lock:Deadlock Chain, Lock: Acquired Lock:
Released events besides the default events, and examine the trace.

--
BG

Hi-Tech College, Israel
http://sql.hi-tech.co.il