Solved

Dead-lock on same object?

Posted on 2004-08-12
10
680 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:Lexie
  • 5
  • 4
10 Comments
 
LVL 9

Expert Comment

by:_ys_
ID: 11785239
> 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.
0
 
LVL 3

Author Comment

by:Lexie
ID: 11818052
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.
0
 
LVL 9

Expert Comment

by:_ys_
ID: 11818194
> 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.
0
 
LVL 3

Author Comment

by:Lexie
ID: 11821454
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
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 9

Expert Comment

by:_ys_
ID: 11822055
How is this stored procedure invoked? ... through ODBC?
It would seem like your isolation level is set to serializable.

Will have a closer look.
0
 
LVL 9

Expert Comment

by:_ys_
ID: 11830400
> 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.
0
 
LVL 3

Author Comment

by:Lexie
ID: 11840506
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.
0
 
LVL 9

Accepted Solution

by:
_ys_ earned 50 total points
ID: 11879591
> 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.

Pretty much answered this yourself ...
0
 
LVL 3

Author Comment

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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now