none
Dead Lock analysis RRS feed

  • Question

  • Hi Experts,

    I want know if dead lock would happend on this query:

    INSERT table1
    SELECT  a.ServiceRequestID,a.CreationDate,a.XboxCallDriver,a.HossMarket,a.CustomerCountry,a.CustomerID,a.MPGAccountID,a.Email,GETDATE(),GETDATE()  
    FROM ( 
    SELECT DISTINCT a.ServiceRequestID,a.CreationDate,a.XboxCallDriver,a.HossMarket,a.CustomerCountry,a.CustomerID,b.MPGAccountID,c.Email FROM dbo.vueGcsCapSRFlat_withKBMapping AS a 
    INNER JOIN dbo.capCustomer AS b WITH (NOLOCK)  ON a.CustomerID = b.ID 
    AND a.ProblemProduct <> '***'
     INNER JOIN ( SELECT DISTINCT Email,CAST([EventDate] AS DATETIME) AS EventDate 
     FROM RiME_Rejects (NOLOCK) WHERE [EventDate] >= Cast(''  As Datetime) 
     AND [EventDate] <= Cast('' As Datetime) ) AS c ON b.Email = c.Email AND a.CreationDate >= c.EventDate 
     AND a.CreationDate <= c.EventDate + 6 LEFT OUTER JOIN table1 AS gld 
      ON a.ServiceRequestID = gld.ServiceRequestID WHERE gld.ServiceRequestID IS NULL ) AS a

    Thank you!!

    Thursday, February 23, 2012 2:52 PM

Answers

  • Deadlocks happen when two different sessions have an exclusive lock on different records and wait for the record which the other session has lock on.

    Here you are adding new records, so the second session can not have access to the newly added records until they are committed.

    The other part is you are reading the records which takes shared locks, which can be shared. So no deadlock should happen.

    You can read your own record any number of times in your transaction.

    So I don't see any deadlocks.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Marked as answer by SSAS_user Tuesday, February 28, 2012 10:46 AM
    Thursday, February 23, 2012 3:14 PM

All replies

  • I don't think deadlocks should happen in this query. They both are in the same session.

    If this query runs parallely, even then I don't think any should happen.

    Basically you are adding new records into the table. Not sure though. Hang on.

    INSERT table1
    SELECT a.servicerequestid,
           a.creationdate,
           a.xboxcalldriver,
           a.hossmarket,
           a.customercountry,
           a.customerid,
           a.mpgaccountid,
           a.email,
           Getdate(),
           Getdate()
    FROM   (SELECT DISTINCT a.servicerequestid,
                            a.creationdate,
                            a.xboxcalldriver,
                            a.hossmarket,
                            a.customercountry,
                            a.customerid,
                            b.mpgaccountid,
                            c.email
            FROM   dbo.vuegcscapsrflat_withkbmapping AS a
                   INNER JOIN dbo.capcustomer AS b WITH (nolock)
                     ON a.customerid = b.id
                        AND a.problemproduct <> '***'
                   INNER JOIN (SELECT DISTINCT email,
                                               CAST([EventDate] AS DATETIME) AS
                                               eventdate
                               FROM   rime_rejects (nolock)
                               WHERE  [EventDate] >= CAST('' AS DATETIME)
                                      AND [EventDate] <= CAST('' AS DATETIME)) AS c
                     ON b.email = c.email
                        AND a.creationdate >= c.eventdate
                        AND a.creationdate <= c.eventdate + 6
    -- Table1 is used again here.
                   LEFT OUTER JOIN table1 AS gld
                     ON a.servicerequestid = gld.servicerequestid
            WHERE  gld.servicerequestid IS NULL) AS a  


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    Thursday, February 23, 2012 3:09 PM
  • Deadlocks happen when two different sessions have an exclusive lock on different records and wait for the record which the other session has lock on.

    Here you are adding new records, so the second session can not have access to the newly added records until they are committed.

    The other part is you are reading the records which takes shared locks, which can be shared. So no deadlock should happen.

    You can read your own record any number of times in your transaction.

    So I don't see any deadlocks.


    Planet Earth is at risk. Global warming is on a high tide.
    Take Responsibility. Plant Trees. Keep your City Clean and Green.

    Mark all Helping Posts and Close your Threads. Keep the Forum Green.
     - Arun Kumar Allu

    • Marked as answer by SSAS_user Tuesday, February 28, 2012 10:46 AM
    Thursday, February 23, 2012 3:14 PM