dead lock problem

Version: SQL Server 2000 8.00.818
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
I have been asked to look into a problem in one of the database
at our client site. I have very little idea of the application.
It seems they are facing intermittent deadlock problem. This is the
query of the session which is *always* rolled back.
SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
...
FROM air_itin_price, air_itin_fare_calc
WHERE air_itin_price.air_itin_id = ?
AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_id
order by air_itin_fare_calc.air_itin_price_id
The index on the two tables
ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
(
[air_itin_id],
[psgr_type]
) WITH FILLFACTOR = 50 ON [PRIMARY]
CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
[dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMARY]
CREATE INDEX [air_itin_price_airitinpriceid] ON
[dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
This is a read only query only, even though the isolation level is same for
all sessions (SERIALIZABLE).
Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer wi
ll
use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behavio
r
same with CLUSTERED INDEX also. I also notice that the primary key on the ta
ble
air_itin_price is a composite index on air_itin_id + psgr_type. But the quer
y
is only for air_itin_id. Does that make a difference?
Any pointers will be appreciated."rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id = air_itin_fare_calc.air_itin_price_i
d
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same fo
r
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the behav
ior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the qu
ery
> is only for air_itin_id. Does that make a difference?
> Any pointers will be appreciated.
some more info from trace:=
Deadlock encountered ... Printing deadlock information
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Wait-for graph
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:1
2004-03-19 14:12:34.65 spid4 PAG: 6:1:3120 CleanCnt:1 M
ode: S Flags: 0x2
2004-03-19 14:12:34.65 spid4 Grant List 0::
2004-03-19 14:12:34.65 spid4 Owner:0x42bcba80 Mode: S Flg:0x0
Ref:1 Life:00000000
SPID:178 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 178 ECID: 0 Statement Type: EXECUT
E Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: RPC Event: sp_cursorfetch;1
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: IX SP
ID:76 ECID:0
Ec0x713CF510) Value:0x42bd18e0 Cost0/3F0)
2004-03-19 14:12:34.65 spid4
2004-03-19 14:12:34.65 spid4 Node:2
2004-03-19 14:12:34.65 spid4 PAG: 6:1:10267 CleanCnt:1 M
ode: IX Flags: 0x0
2004-03-19 14:12:34.65 spid4 Grant List 2::
2004-03-19 14:12:34.65 spid4 Owner:0x42bd3080 Mode: IX Flg:0x0
Ref:0 Life:02000000
SPID:76 ECID:0
2004-03-19 14:12:34.65 spid4 SPID: 76 ECID: 0 Statement Type: INSERT
Line #: 1
2004-03-19 14:12:34.65 spid4 Input Buf: Language Event: INSERT INTO a
ir_itin_price
(air_itin_id,psgr_type,quantity,pub_fare
,base_fare,q_charge,other_charges,tt
l_markup,ttl_tax,securit
y_fee,fare_tax_rate, us1_tax) VALUES
(398529,0,1,237.24,183.10,0.00,0.00,0.00,44.14,10.00,0.0000,13.74)
2004-03-19 14:12:34.65 spid4 Requested By:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPI
D:178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
2004-03-19 14:12:34.65 spid4 Victim Resource Owner:
2004-03-19 14:12:34.65 spid4 ResType:LockOwner Stype:'OR' Mode: S SPID:
178 ECID:0
Ec0x716F1548) Value:0x42bca6c0 Cost0/0)
Looks like it is a conversion deadlock.|||both sessions are using READ COMMITTED.|||"rkusenet" <rkusenet@.sympatico.ca> wrote in message
news:c3fkhh$28a94j$1@.ID-75254.news.uni-berlin.de...
> Version: SQL Server 2000 8.00.818
> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
> I have been asked to look into a problem in one of the database
> at our client site. I have very little idea of the application.
> It seems they are facing intermittent deadlock problem. This is the
> query of the session which is *always* rolled back.
>
> SELECT air_itin_fare_calc.air_itin_price_id, air_itin_price.air_itin_id,
> ...
> FROM air_itin_price, air_itin_fare_calc
> WHERE air_itin_price.air_itin_id = ?
> AND air_itin_price.air_itin_price_id =
air_itin_fare_calc.air_itin_price_id
> order by air_itin_fare_calc.air_itin_price_id
> The index on the two tables
> ALTER TABLE [dbo].[air_itin_price] WITH NOCHECK ADD
> CONSTRAINT [PK_air_itin_price] PRIMARY KEY CLUSTERED
> (
> [air_itin_id],
> [psgr_type]
> ) WITH FILLFACTOR = 50 ON [PRIMARY]
> CREATE CLUSTERED INDEX [PK_air_itin_price_id] ON
> [dbo].[air_itin_fare_calc]([air_itin_price_id]) ON [PRIMAR
Y]
> CREATE INDEX [air_itin_price_airitinpriceid] ON
> [dbo].[air_itin_price]([air_itin_price_id]) ON [PRIMARY]
> This is a read only query only, even though the isolation level is same
for
> all sessions (SERIALIZABLE).
> Since the columns in the WHERE CLAUSE is indexed, I assume that SQLServer
will
> use key locks only. I am bit concerned about CLUSTERED INDEX. Is the
behavior
> same with CLUSTERED INDEX also. I also notice that the primary key on the
table
> air_itin_price is a composite index on air_itin_id + psgr_type. But the
query
> is only for air_itin_id. Does that make a difference?
>
Perhaps. If the query used the key, then it's locks would be more narrow.
It looks like the row being inserted by one client might belong in the
resultset of for the other client. If the query specified the full key, it
might be clear to SQL that that is not the case.
Also the query is using
sp_cursorfetch;1
What kind of cursor is the client using?
David