Dead-lock on same object?

> 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.

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.

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.

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, I still don't know how this leads to a dead-lock... Well, most other dead-locks in the system seem normal...