We help IT Professionals succeed at work.

Getting the transaction deadlock error

770 Views
Last Modified: 2012-05-08
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

Comment
Watch Question

CEO @ Arkro IT
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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 AriesCEO @ Arkro IT
CERTIFIED EXPERT

Commented:
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)

Author

Commented:
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

Author

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.