asked on

Getting the transaction deadlock error

I am getting following error which looks like a deadlock on the table I am not sure how to fix this issue

I am not sure if its caused by the any processes which is running select query on the same table or something else

Should I use the No lock in the select query? or something else

Error:
system.data.sqlclient.sqlexception; transaction (process id 465) was deadlock on lock resources with another process and has been chosen as the deadlock victim


//Code on which error is occuring
 
        private static int UpdateDeliveryCapacity(int deliveryCapacityId, int requiredCapacity)
        {
            int rowsAffected = 0;
            SqlDatabase db = DatabaseFactory.CreateDatabase(TransactionConfigDatabase) as SqlDatabase;
            DbCommand command = db.GetStoredProcCommand("UpdateDeliveryCapacity", deliveryCapacityId, requiredCapacity);
 
            rowsAffected = db.ExecuteNonQuery(command);
            return rowsAffected;
        }
 
 
//stored procedure
Create PROCEDURE [dbo].[UpdateDeliveryCapacity]  
 @capacityId AS INT,  
 @capacityRequired AS INT  
  
AS  
BEGIN  
  
 UPDATE  
  DeliveryCapacity  
 SET  
  Capacity = Capacity - @capacityRequired  
 WHERE   
  DeliveryCapacityId = @capacityId  
  AND  
  Capacity - @capacityRequired >= 0  
END

Open in new window

ASKER CERTIFIED SOLUTION

Dennis Aries

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shieldguy

ASKER

What I have done now is changed the view which is reading from this table

is it the right thing to do

My code is already running under the .net transaction do I still need to implement it also on the stored procedure level

ALTER VIEW [dbo].[ProductCapacityView]
AS
SELECT     dbo.DeliveryOption.ShippingMethodId, dbo.DeliveryOptionAvailability.ProductId, dbo.DeliveryOptionAvailability.AvailabilityDate, 
                      dbo.DeliveryCapacity.DeliveryCapacityId, dbo.DeliveryCapacity.Capacity, dbo.DeliveryOptionAvailability.OrderCapacity
FROM         dbo.DeliveryOptionAvailability with (nolock) LEFT OUTER JOIN
                      dbo.DeliveryOption ON dbo.DeliveryOptionAvailability.DeliveryOptionId = dbo.DeliveryOption.DeliveryOptionId INNER JOIN
                      dbo.DeliveryCapacity with (nolock) ON dbo.DeliveryOptionAvailability.DeliveryCapacityId = dbo.DeliveryCapacity.DeliveryCapacityId
WHERE     (dbo.DeliveryCapacity.Capacity > 0) OR (ISNULL(dbo.DeliveryOptionAvailability.OrderCapacity,0) > 0)

Open in new window

Dennis Aries

if you are sure that this is the view that is causing the problems, yes.
If you are running under the .NET transaction and you do not commit the data frequently enough, you will have a rising number of locks, resulting in your problem.
Only use transactions when needed, and then no longer than you really need to. You can have nested transactions, so specifying them in the procedure can never hurt (providing you commit them at the end of the procedure)

shieldguy

ASKER

If I use the RowLock on the Update query is it qoing to help me?
If now what type of lock should I use
I would really appreciate if someone can also show me how to apply the lock on the following query

UPDATE  
  DeliveryCapacity  
 SET  
  Capacity = Capacity - @capacityRequired  
 WHERE   
  DeliveryCapacityId = @capacityId  
  AND  
  Capacity - @capacityRequired >= 0

Open in new window

shieldguy

ASKER

I had added the following code in the update stored procedure which is using the ROWLOCK is it the right thing to do

Thanks

   ALTER PROCEDURE [dbo].[UpdateDeliveryCapacity]  
 @capacityId AS INT,  
 @capacityRequired AS INT  
  
AS  
BEGIN  
  
BEGIN TRANSACTION 
UPDATE  
  DeliveryCapacity with (rowlock)
 SET  
  Capacity = Capacity - @capacityRequired  
 WHERE   
  DeliveryCapacityId = @capacityId  
  AND  
  Capacity - @capacityRequired >= 0
COMMIT TRANSACTION

Open in new window