Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • what is NOLOCK in SQL Server

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 444
    Comment on it

    Hi guys, I want to tell you the use of NOLOCK with simple example but before this lets discuss some terms

     

    Lock:-

    When multiple users or applications access the non-shareable resource at the same time, Locking allows to access the resource sequentially. Locks are maintained internally by SQL Server. It is automatically acquired and released. 

    There are broadly two types of lock

     

    1) Shared lock

     It's a read lock and it is applied when we execute select statement or any other statement in which no modification of data is happening. 

     

    2) Exclusive lock

    It is applied when modification of data is happening. When this lock is applied only sequential access is allowed on that DB object

     

    Transaction:-

    A unit of work executed to be whole.

     

    Dirty read:-

    Reading the uncommitted data is called dirty read. Lets understand this with example suppose we perform update on table in transaction and before the transaction is committed we accessed the modified data so this data is dirty read because it might be possible that accessed data might not be part of this table if transaction is rolled back.

    Now its ideal time to discuss the NOLOCK

    Please use the below script for table schema

    CREATE TABLE [dbo].[User](
    	[Id] [int] IDENTITY(1,1) NOT NULL,
    	[UserId] [int] NULL,
    	[Name] [varchar](50) NULL,
    	[Phone] [varchar](10) NULL,
    	[Email] [varchar](50) NULL,
    
    )

    I have some records in this table 

     

     

    Now execute the following query in window 1 of SQL Sever. I am updating the User table in transaction but not committing it. This will put the exclusive lock on this table means no operation is allowed until transaction is committed.

     

    now open the another window and try to access the user table. We will see its not returning the result due to exclusive lock on user table

     

    Here NOLOCK will do the trick. Now if we execute the query with NOLOCK it will return the result but it returned the dirty data because transaction is not committed

     

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: