Lexie
asked on
Dead-lock on same object?
I log the deadlocks in my SQL error log, and I have a deadlock I don't understand. This is the dead-lock information:
2004-08-12 04:42:31.06 spid4 Deadlock encountered .... Printing deadlock information
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Wait-for graph
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:1
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Grant List 1::
2004-08-12 04:42:31.07 spid4 Owner:0x35be8fa0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 61 ECID: 0 Statement Type: SELECT Line #: 37
2004-08-12 04:42:31.07 spid4 Input Buf: RPC Event: proc_accommodations_price_ min_select ;1
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x7786B5F8) Value:0x35be9140 Cost:(0/48)
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:2
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Convert List:
2004-08-12 04:42:31.07 spid4 Owner:0x35be9140 Mode: X Flg:0x2 Ref:1 Life:02000000 SPID:57 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE STATISTICS Line #: 363
2004-08-12 04:42:31.07 spid4 Input Buf: Language Event: DECLARE @intError AS INTEGER
EXEC proc_interface_kanteff_cli ent @intError = @intError OUTPUT
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
2004-08-12 04:42:31.07 spid4 Victim Resource Owner:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
The object KEY: 10:2:1 is the table sysindexes, the clustered index. In SPID 61 a SELECT is done, so a lock type S is hold. In SPID 57 a UPDATE STATISTICS is done on the same table, a lock type X is hold. But is seems both SPID's are locking the same object , the one with the hash value (07002126e175). Does the "convert list" has anything to do with it? Why is this is deadlock?
Lex van de Pol
2004-08-12 04:42:31.06 spid4 Deadlock encountered .... Printing deadlock information
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Wait-for graph
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:1
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Grant List 1::
2004-08-12 04:42:31.07 spid4 Owner:0x35be8fa0 Mode: S Flg:0x0 Ref:1 Life:00000000 SPID:61 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 61 ECID: 0 Statement Type: SELECT Line #: 37
2004-08-12 04:42:31.07 spid4 Input Buf: RPC Event: proc_accommodations_price_
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: X SPID:57 ECID:0 Ec:(0x7786B5F8) Value:0x35be9140 Cost:(0/48)
2004-08-12 04:42:31.07 spid4
2004-08-12 04:42:31.07 spid4 Node:2
2004-08-12 04:42:31.07 spid4 KEY: 10:2:1 (07002126e175) CleanCnt:2 Mode: U Flags: 0x0
2004-08-12 04:42:31.07 spid4 Convert List:
2004-08-12 04:42:31.07 spid4 Owner:0x35be9140 Mode: X Flg:0x2 Ref:1 Life:02000000 SPID:57 ECID:0
2004-08-12 04:42:31.07 spid4 SPID: 57 ECID: 0 Statement Type: UPDATE STATISTICS Line #: 363
2004-08-12 04:42:31.07 spid4 Input Buf: Language Event: DECLARE @intError AS INTEGER
EXEC proc_interface_kanteff_cli
2004-08-12 04:42:31.07 spid4 Requested By:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
2004-08-12 04:42:31.07 spid4 Victim Resource Owner:
2004-08-12 04:42:31.07 spid4 ResType:LockOwner Stype:'OR' Mode: U SPID:61 ECID:0 Ec:(0x2FF07598) Value:0x35be8f20 Cost:(1/0)
The object KEY: 10:2:1 is the table sysindexes, the clustered index. In SPID 61 a SELECT is done, so a lock type S is hold. In SPID 57 a UPDATE STATISTICS is done on the same table, a lock type X is hold. But is seems both SPID's are locking the same object , the one with the hash value (07002126e175). Does the "convert list" has anything to do with it? Why is this is deadlock?
Lex van de Pol
ASKER
No, it is not. One statement is a SELECT, the other one is the UPDATE STATISICS. Both are executed in a stored procedure, but not within a transaction. And the isolation level is the default. Both are locking the sysindexes table. If they would be executed in a transaction I would still have the same question, why is this a deadlock on the same object.
> One statement is a SELECT
But it's requesting an UPDATE lock ...
As it's not executing within a transaction, could you possibly post the contents of the stored proc proc_accommodations_price_ min_select . It's not normal for a SELECT to request a lock type U, unless the SELECT is part of a bigger picture.
But it's requesting an UPDATE lock ...
As it's not executing within a transaction, could you possibly post the contents of the stored proc proc_accommodations_price_
ASKER
SPID 57
(part of it - line 363)
UPDATE STATISTICS tview_accommodations_price _table
SPID 61
-- Purpose: select the min price for an accommodation
-- Module: TOUR - accommodations
-- Parameters:
-- @des_id = the destination id
-- @acc_id = the accommodation id
-- @dtmStart = the start of the period
-- @dtmEnd = the end of the period
-- @intAttributePrio = the sales prio attribute
-- @intAttributeMin = the min pax attribute
-- @intAttributeMax = the max pax attribute
-- @intPax = number of pax
-- Returns: output variable price
--
-- Author: Lex van de Pol
-- Date: 10 August 2004
-- Version: 4.03
--
CREATE PROCEDURE proc_accommodations_price_ min_select
@des_id AS INTEGER = NULL,
@acc_id AS INTEGER = NULL,
@dtmStart AS DATETIME,
@dtmEnd AS DATETIME,
@intAttributePrio AS INTEGER = NULL,
@intAttributeMin AS INTEGER = NULL,
@intAttributeMax AS INTEGER = NULL,
@intPax AS INTEGER = NULL
AS
BEGIN
-- The settings
SET NOCOUNT ON
-- Select the minimal price for each room type
IF @des_id IS NOT NULL AND @acc_id IS NULL
BEGIN
SELECT
vaccommodations.des_id,
vaccommodations.acc_id,
vaccommodation_items.ait_i d,
ISNULL(MIN(apr_price + ISNULL(trp_price,0) + ISNULL(apr_discount,0)), -1) AS apr_price
FROM
vaccommodations
INNER JOIN vaccommodation_items ON vaccommodation_items.acc_i d = vaccommodations.acc_id
INNER JOIN taccommodation_basis ON taccommodation_basis.aco_c ode = vaccommodations.acv_code
INNER JOIN taccommodation_suppliers ON taccommodation_suppliers.a co_id = taccommodation_basis.aco_i d
INNER JOIN taccommodation_products ON
taccommodation_products.as u_id = taccommodation_suppliers.a su_id AND
taccommodation_products.ac p_code = vaccommodation_items.aiv_c ode
LEFT JOIN tview_accommodations_price _table ON
tview_accommodations_price _table.aco _id = taccommodation_basis.aco_i d AND
tview_accommodations_price _table.acp _code = vaccommodation_items.aiv_c ode AND
tview_accommodations_price _table.acs _date BETWEEN @dtmStart AND @dtmEnd AND
tview_accommodations_price _table.vpt _active = 'yes' AND
tview_accommodations_price _table.acs _stock >= 0
LEFT JOIN taccommodation_values ON
taccommodation_values.acc_ id = vaccommodations.acc_id AND
taccommodation_values.aca_ id = @intAttributePrio
LEFT JOIN taccommodation_item_values AS aiv1 ON
aiv1.ait_id = vaccommodation_items.ait_i d AND
aiv1.aia_id = @intAttributeMin
LEFT JOIN taccommodation_item_values AS aiv2 ON
aiv2.ait_id = vaccommodation_items.ait_i d AND
aiv2.aia_id = @intAttributeMax
WHERE
des_id = @des_id AND
acv_active = 'Ja' AND
aiv_active = 'Ja' AND
aco_active = 'yes' AND
(@intPax IS NULL OR @intPax BETWEEN acp_pax_min AND acp_pax_max)
GROUP BY
vaccommodations.des_id,
vaccommodations.acc_id,
vaccommodation_items.ait_i d,
taccommodation_values.acv_ value,
vaccommodations.acv_name,
aiv1.aiv_value,
aiv2.aiv_value,
vaccommodation_items.aiv_n ame
ORDER BY
taccommodation_values.acv_ value DESC,
vaccommodations.acv_name,
aiv1.aiv_value,
aiv2.aiv_value,
vaccommodation_items.aiv_n ame
END
-- Select the minimal price for each room type
IF @acc_id IS NOT NULL
BEGIN
SELECT
vaccommodations.des_id,
vaccommodations.acc_id,
NULL AS ait_id,
ISNULL(MIN(apr_price + trp_price + ISNULL(apr_discount,0)), -1) AS apr_price
FROM
vaccommodations
INNER JOIN taccommodation_basis ON taccommodation_basis.aco_c ode = vaccommodations.acv_code
LEFT JOIN tview_accommodations_price _table ON
tview_accommodations_price _table.aco _id = taccommodation_basis.aco_i d AND
tview_accommodations_price _table.acs _date BETWEEN @dtmStart AND @dtmEnd AND
tview_accommodations_price _table.vpt _active = 'yes' AND
tview_accommodations_price _table.acs _stock >= 0
WHERE
vaccommodations.acc_id = @acc_id AND
acv_active = 'Ja' AND
aco_active = 'yes'
GROUP BY
vaccommodations.des_id,
vaccommodations.acc_id
END
END
GO
(part of it - line 363)
UPDATE STATISTICS tview_accommodations_price
SPID 61
-- Purpose: select the min price for an accommodation
-- Module: TOUR - accommodations
-- Parameters:
-- @des_id = the destination id
-- @acc_id = the accommodation id
-- @dtmStart = the start of the period
-- @dtmEnd = the end of the period
-- @intAttributePrio = the sales prio attribute
-- @intAttributeMin = the min pax attribute
-- @intAttributeMax = the max pax attribute
-- @intPax = number of pax
-- Returns: output variable price
--
-- Author: Lex van de Pol
-- Date: 10 August 2004
-- Version: 4.03
--
CREATE PROCEDURE proc_accommodations_price_
@des_id AS INTEGER = NULL,
@acc_id AS INTEGER = NULL,
@dtmStart AS DATETIME,
@dtmEnd AS DATETIME,
@intAttributePrio AS INTEGER = NULL,
@intAttributeMin AS INTEGER = NULL,
@intAttributeMax AS INTEGER = NULL,
@intPax AS INTEGER = NULL
AS
BEGIN
-- The settings
SET NOCOUNT ON
-- Select the minimal price for each room type
IF @des_id IS NOT NULL AND @acc_id IS NULL
BEGIN
SELECT
vaccommodations.des_id,
vaccommodations.acc_id,
vaccommodation_items.ait_i
ISNULL(MIN(apr_price + ISNULL(trp_price,0) + ISNULL(apr_discount,0)), -1) AS apr_price
FROM
vaccommodations
INNER JOIN vaccommodation_items ON vaccommodation_items.acc_i
INNER JOIN taccommodation_basis ON taccommodation_basis.aco_c
INNER JOIN taccommodation_suppliers ON taccommodation_suppliers.a
INNER JOIN taccommodation_products ON
taccommodation_products.as
taccommodation_products.ac
LEFT JOIN tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
LEFT JOIN taccommodation_values ON
taccommodation_values.acc_
taccommodation_values.aca_
LEFT JOIN taccommodation_item_values
aiv1.ait_id = vaccommodation_items.ait_i
aiv1.aia_id = @intAttributeMin
LEFT JOIN taccommodation_item_values
aiv2.ait_id = vaccommodation_items.ait_i
aiv2.aia_id = @intAttributeMax
WHERE
des_id = @des_id AND
acv_active = 'Ja' AND
aiv_active = 'Ja' AND
aco_active = 'yes' AND
(@intPax IS NULL OR @intPax BETWEEN acp_pax_min AND acp_pax_max)
GROUP BY
vaccommodations.des_id,
vaccommodations.acc_id,
vaccommodation_items.ait_i
taccommodation_values.acv_
vaccommodations.acv_name,
aiv1.aiv_value,
aiv2.aiv_value,
vaccommodation_items.aiv_n
ORDER BY
taccommodation_values.acv_
vaccommodations.acv_name,
aiv1.aiv_value,
aiv2.aiv_value,
vaccommodation_items.aiv_n
END
-- Select the minimal price for each room type
IF @acc_id IS NOT NULL
BEGIN
SELECT
vaccommodations.des_id,
vaccommodations.acc_id,
NULL AS ait_id,
ISNULL(MIN(apr_price + trp_price + ISNULL(apr_discount,0)), -1) AS apr_price
FROM
vaccommodations
INNER JOIN taccommodation_basis ON taccommodation_basis.aco_c
LEFT JOIN tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
tview_accommodations_price
WHERE
vaccommodations.acc_id = @acc_id AND
acv_active = 'Ja' AND
aco_active = 'yes'
GROUP BY
vaccommodations.des_id,
vaccommodations.acc_id
END
END
GO
How is this stored procedure invoked? ... through ODBC?
It would seem like your isolation level is set to serializable.
Will have a closer look.
It would seem like your isolation level is set to serializable.
Will have a closer look.
> BETWEEN x AND y
This will result in a lock on the clustered index.
It requires this to guarantee another row doesn't get inserted between this range.
This will result in a lock on the clustered index.
It requires this to guarantee another row doesn't get inserted between this range.
ASKER
In order to resolve deadlocks, often the isolation level is increased. Range searches like BETWEEN x AND y will only lead to a lock on the clustered index if the search criteria are within the fields of the clustered index or bookmarks to the clustered index are required.
However, in this example the tabel itself is not deadlocked but the system table sysindexes is deadlocked. It seems that the UPDATE STATISTICS requires an X-lock on a record. And if the second query is running this places a S-lock on the same record so the index is not changed during it's use.
However, in this example the tabel itself is not deadlocked but the system table sysindexes is deadlocked. It seems that the UPDATE STATISTICS requires an X-lock on a record. And if the second query is running this places a S-lock on the same record so the index is not changed during it's use.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
... However, I still don't know how this leads to a dead-lock... Well, most other dead-locks in the system seem normal...
Is this possibly executing in the context of a transaction. If so, it's likely to lock it up - dependant on the isolation level.