Link to home
Start Free TrialLog in
Avatar of Lexie
LexieFlag for Zambia

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_client @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
Avatar of _ys_
_ys_

> In SPID 61 a SELECT is done
Is this possibly executing in the context of a transaction. If so, it's likely to lock it up - dependant on the isolation level.
Avatar of Lexie

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.
Avatar of Lexie

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_id,
         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_id = vaccommodations.acc_id
         INNER JOIN taccommodation_basis ON taccommodation_basis.aco_code = vaccommodations.acv_code
         INNER JOIN taccommodation_suppliers ON taccommodation_suppliers.aco_id = taccommodation_basis.aco_id
         INNER JOIN taccommodation_products ON
            taccommodation_products.asu_id = taccommodation_suppliers.asu_id AND
            taccommodation_products.acp_code = vaccommodation_items.aiv_code
         LEFT JOIN tview_accommodations_price_table ON
            tview_accommodations_price_table.aco_id = taccommodation_basis.aco_id AND
            tview_accommodations_price_table.acp_code = vaccommodation_items.aiv_code 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_id AND
            aiv1.aia_id = @intAttributeMin
         LEFT JOIN taccommodation_item_values AS aiv2 ON
            aiv2.ait_id = vaccommodation_items.ait_id 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_id,
         taccommodation_values.acv_value,
         vaccommodations.acv_name,
         aiv1.aiv_value,
         aiv2.aiv_value,
         vaccommodation_items.aiv_name
      ORDER BY
         taccommodation_values.acv_value DESC,
         vaccommodations.acv_name,
         aiv1.aiv_value,
         aiv2.aiv_value,
         vaccommodation_items.aiv_name

   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_code = vaccommodations.acv_code
         LEFT JOIN tview_accommodations_price_table ON
            tview_accommodations_price_table.aco_id = taccommodation_basis.aco_id 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
How is this stored procedure invoked? ... through ODBC?
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.
Avatar of Lexie

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.
ASKER CERTIFIED SOLUTION
Avatar of _ys_
_ys_

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lexie

ASKER

... However, I still don't know how this leads to a dead-lock... Well, most other dead-locks in the system seem normal...