Sqlserver-dba.com

SQL Server deadlocks analysis

Once the  deadlock has occured , it will  remain in a constant deadlock state. The SQL server clean up routine comes along every few seconds and releases one of the victims (the one using the least amount of resources).An error message is returned - error mesaage 1205.
A deadlock is not the same as blocking. Blocking is a normal condition of database applications, although excessive blocking can create service degradation, and it is the aim of the DBA to identify excessive blocking. Blocking is not to be confused with deadlocking.

Lets look at the example below, which illustrates a deadlock scenario. 

Assume there are 2 transaction running simultaneously.

Transaction 1 - holds a lock on "order number 1"
Transaction 2 - holds a lock on "vehichle 1"

The deadlock will occur if Transaction1 - requires a lock "vehicle 1 " and Transaction2 - requires a lock on "order number 1". In other words a  dependancy.

Analysis of how to resolve the deadlocking should include:

1)Consider the order of the transactions within the stord procedure.
2)You can see from the breakdown below , the potential scenario . i.e beacause this is one long running transaction , if multiple transactions are running , a few millisecond transactions would create a situation where deadlocks (not blocking would occur.)
3)Are so many steps required ?
4)Can BEGIN TRANS ...COMMIT TRANS  be placed around more sub steps , to release locks?
5)A way must be found to release the (probably ) exclusive locks created by the UPDATE,DELETES,INSERTS, without upsetting the data integrity.Speak to the developers , and consider the implications of adjusting transaction management.
6)What is going on in usp_SESSORD_Order_WriteCLIENTOrdercar? Is this trying to access resources , which have exclusive locks from processes in the main body?
7)Can you control a queue from the app , that would queue this transaction?

BEGIN TRANSACTION 1
if exists CLIENT
 UPDATE CLIENT
else
 INSERT CLIENT
 FROM sessordCLIENT

 SELECT @custId = origCustId FROM SESSORD_CUSTOMERS WHERE sessionSessionId = @sessionId
 UPDATE SESSORD_CUSTOMERS   SET origCustId = @@IDENTITY   WHERE sessionSessionId = @sessionId

end

IF exits - order sessorder

 
 if exists .....
 SELECT @bookedOrderId = o.orderId, @bookingImage = o.bookingImage + 1     FROM ORDERS o
          INNER JOIN sessord_orders so ON o.BookingRef = so.BookingRef  WHERE so.sessionSessionId = @sessionId

 
 if exists (SELECT...)
 DELETE orderItemPaxFROM orderItemPaxodv  INNER JOIN orderDets od ON od.orderdetId = odv.paxdetorderdetId
              LEFT JOIN sessord_orderItemPaxsodv ON odv.PaxDetId = sodv.origPaxDetid
            WHERE sodv.PaxDetId IS NULL AND od.orderdetOrderId = @bookedOrderId

 

 if exists(SELECT)
 DELETE orderItemCar             FROM orderItemCar odv               INNER JOIN orderDets od      ON od.orderdetId = odv.vehdetorderdetId
              LEFT JOIN sessord_orderItemCar sodv   ON odv.VehDetId = sodv.origorderItemCarid
            WHERE sodv.VehDetId IS NULL  AND od.orderdetOrderId = @bookedOrderId

 
 DELETE .......
      

 

 DELETE .......


 DELETE  ........


       
 DELETE ..........

 

       DELETE ...........


 --notetext
 DELETE ............
 --note
       DELETE ..............
 --OrderDets
 UPDATE ..............

ELSE
 INSERT ORDER ...........
 
 
END

        INSERT INTO orderItemCar from SESSORD_orderItemCar
        INSERT INTO orderItemPaxfrom SESSORD_orderItemPax
        INSERT INTO orderDetJourney FROM SESSORD_orderDetJourney
        INSERT INTO orderAddedValue FROM SESSORD_orderAddedValue
  INSERT INTO orderSupRed (SESSORD_orderSupRed
        INSERT INTO dbo.orderPayments FROM [dbo].[sessord_orderpayments] sop
         INSERT INTO dbo.Note from [sessord_note]
        INSERT INTO Notetext from [SESSORD_NoteText]

if bookingImage > 0

 UPDATE orders
 UPDATE orderItemCar


 UPDATE orderItemPax
     UPDATE OrderDetJourney
 UPDATE orderAddedValue
  UPDATE orderSupRed
 UPDATE orderPayments
 UPDATE dbo.note
 UPDATE dbo.notetext

 EXEC [usp_SESSORD_Order_WriteCLIENTOrderCar] @sessionId, @amendedBy


COMMIT TRANSACTION


 

 

 

 


 


Author: Jack Vamvas (http://www.sqlserver-dba.com)

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact