Lock Contentions

Posted on 2009-12-29
Last Modified: 2012-05-08
If there are some locks only two table, what could be solutions to lessen the locks?

Question by:anushahanna
    LVL 26

    Assisted Solution

    check the code and transactions accessing the tables...  if you can give more detail abou lock occurence we may guide you more...
    LVL 6

    Author Comment

    Ok. started with DMV. It suggests couple of index for the tables. Will that help the locking?

    to get transactions hitting the tables, is profiler the tool to go?
    LVL 6

    Author Comment

    sorry for the delay.

    i used the dmv's to get the info. would I use the profiler to get the info you are referring.

    thanks tigin44
    LVL 57

    Accepted Solution

    >> some locks only two table

    I have two approached from my side and would recommend the first approach..

    First Approach:

    1. Use Profiler
    2. Add two Column Filters one for Textdata column LIKE your first table and Second filter for Textdata column LIKE your second table.
    3. Make sure you have included the Lock:Deadlock, Lock:Deadlock Chain, Deadlock Graph and Lock:Timeout under Locks section.
    4. Analyze all the queries or operations that is happening on those two tables which are responsible for this deadlock.
    5. Tune out the queries involved either by rewriting your queries or by creating appropriate indexes

    Second Approach: ( if not able to tune better using first approach)

    1. Find out where those queries are used in your application module.
    2. Check out the transactional level maintained and the code over there..
    3. Check whether any application level tuning or alternative approach can be done to improve the application level.

    This should help you remove lock contentions over those two tables..
    Similar approach can be done on all modules as required..
    LVL 6

    Author Comment

    Thanks for the detailed and clear explanation.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now