SQL Server Locking

Posted on 2009-12-21
Last Modified: 2012-05-08
I have read about locking mechanism in sql server 2005. I do understand it, but not able to figure out in real time applications where do we need to use it. For eg : shared, update Lock, Row Lock etc.

SQL Server automatically implements all that. I heard some companies implement transaction locking at granular level, but am really not able to understand on what real time scenarios, we would want to  Override the default SQL Server isolation levels. Can clear examples be given when to use them, in what type of scenarios we need to implement or change the default locking techniques.
Question by:dotnet0824
    LVL 75

    Expert Comment

    by:Aneesh Retnakaran
    playing with the Locks could be dangerous, i think you have to leave it to sql server most of the times.
     - for the queries used for reports, you can put a NOLOCK hint

    Author Comment

    why for queries only (NoLock). can it be explained in detail  aneeesh
    LVL 57

    Expert Comment

    by:Raja Jegan R
    Since you require detailed information, providing these links which should give you a clear idea about Locking..

    Kindly go through these articles and shoot your questions..

    Expert Comment

    To understand the technique of locking you will need to understand the problem of data integrity. Say query A is selecting 100 records and just as it has selected 50 records (out of 100), query B has updated the first 10 records of 100. so when your query A selects 100 records some of the data may not be accurate.

    So when query A is being run, we ask it to lock the table until it is finished. Since the table is locked query B will not be able to update any of the records.

    Locking will make sure the queries run in sequence and avoid any data integrity issues.

    Makes sense?
    LVL 9

    Expert Comment

    LVL 9

    Accepted Solution


    please follow link as Next Page for more details.

    Author Comment

    well thats fine guys. I understand locking, but my question was when sql server implements locking mechanicm automatically like shared, exclusive,updatelocks etc why do we need to modify the default behaviour.

    why is that we need to explicitly change the default mechanism was my question. In what cases do we need to change the default locking mechanism was my question about

    Hope i am clear again .
    LVL 57

    Assisted Solution

    by:Raja Jegan R
    >> why do we need to modify the default behaviour.

    By default, SELECT statements would place a shared lock on the tables involved.
    This would create some load to the tables and based on the transaction isolation level this might get changed..

    And in this case we can modify the SELECT statement not to lock(Shared) the table by using NOLOCK keyword..

    This would get the results faster in times.

    PS: To use these kind of hints or NOLOCK statements, you need to very careful in choosing it. Choosing wrong hints would suffer your query performance.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    760 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

    8 Experts available now in Live!

    Get 1:1 Help Now