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
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |