Tuesday 17 April 2018

Table Hint NOLOCK In SQL Server

Resources on which lock apply:
  1. Pages: The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level.
  2. Extends: Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents.
  3. Table
  4. Partition
  5. Row

Types:
  1. Share Lock
    1. Whenever use Select statement
  2. Update Lock
    1. Whenever use Update statement
    2. Whenever use Delete statement
  3. Exclusive
    1. Update locks provide exclusive lock on objects so no other resources can use it.
  4. Intent
    1. Hierarchy based lock, and apply whenever we use perform operation on any hierarchical based objects (like: Row > Table> Page > Extend)
  5. Schema
    1. Whenever use DDL statement
    2. Types
      1. Schema m - one perform alter statement.
      2. Schema s - When execution plan creating.


Row level lock
  1. RID : Row Identifier, without index work with heap table.
  2. Key : Having Index.


Serial
SSMS Editor 1
SSMS Editor 2
Summary
1
CREATE TABLE dbo.Demo
(
    id INT IDENTITY NOT NULL,
    details VARCHAR(MAX) NULL
);

INSERT INTO dbo.Demo
(
    details
)
VALUES
('Record 1'),
('Record 2');

Just Created a table and inserted few records.
2

SELECT * FROM DB2016.dbo.Demo
No lock
3
BEGIN TRANSACTION TRAN1;
UPDATE dbo.Demo
SET details = 'Updated Record 2'
WHERE id = 2;


4

SELECT * FROM DB2016.dbo.Demo;

SELECT * FROM DB2016.dbo.Demo
 where id=1;
Locked, not able to get result. Because data lock applied on id=2 while searching one by one.
5

SELECT Top 5 * FROM DB2016.dbo.Demo WITH (NOLOCK)
Locked but still we are able to get data as explicitly  READUNCOMMITTED level.
6
ROLLBACK TRANSACTION


7

SELECT * FROM DB2016.dbo.Demo
Lock released and we got result.
8
CREATE CLUSTERED INDEX CID_ID ON dbo.Demo (id);

Index created.
9
BEGIN TRANSACTION TRAN1;
UPDATE dbo.Demo
SET details = 'Updated Record 2'
WHERE id = 2;


10

SELECT * FROM DB2016.dbo.Demo;
Locked because of id=2
11

SELECT * FROM DB2016.dbo.Demo
 where id=1;
Got result, because searched on index and id=1 is no locked.
12
COMMIT TRANSACTION;


13

SELECT * FROM DB2016.dbo.Demo;
Got data, lock released.

See what Microsoft and other say about uses of Table Hints like NOLOCK

Because the SQL Server query optimizer typically selects the best execution plan for a query, we recommend that hints be used only as a last resort by experienced developers and database administrators.

Support for use of the READUNCOMMITTED and NOLOCK hints in the FROM clause that apply to the target table of an UPDATE or DELETE statement will be removed in a future version of SQL Server. Avoid using these hints in this context in new development work, and plan to modify applications that currently use them.

Reference