Dead locks on only an Insert?

Dead locks on only an Insert?

Post by Tim Mille » Fri, 03 Nov 2000 22:58:31



    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

 
 
 

Dead locks on only an Insert?

Post by Itzik Ben-Ga » Sat, 04 Nov 2000 00:51:50


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


>     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