SQL - With (NOLOCK)

Lock it allows different type of resource to be lock by the transaction.

When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

A transaction is unit of work submitted as hole to database for processing.

Dirty Read is a process of reading database record without locking the record being read.

Stand the risk of reading an uncommitted row that is subsequently rolled back. Resulting in reading of local copy of record that is not consistent with what is stored in database.

Non repeatable read is a situation where a session finds itself in when it perform multiple read.

It is possible to perform the query in the same transaction more than one and show different result.

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

This occur when user insert a record while transaction is underway.

Shared Lock(s) is read lock it is occur when SQL server perform select statement or any other statement to read the data tell to read but do not modify the data.

When one user is reading the data he/she issue read lock so that other user do not modify data.

Exclusive Lock (x) are generally use during modification activity use to lock data being modified by one transaction.it prevent modification by another concurrent transaction.

Update Lock (u) update lock a mix of shared and exclusive lock.

Update Lock is kind of Exclusive Lock except it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of row which has Shared Lock, as soon as Update Lock is ready to change the data it converts itself to Exclusive Lock.  

WITH (NOLOCK) hit

Do not issue shared locks and do not honour exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. 

To check if the query is locked during the execution of not. Do not prefer to use to NOLOCK hints in SQL, as it will read the dirty data and often reading uncommitted data creates problems with database integrity. There are many different ways to tune your query rather than using NOLOCK hint or using read uncommitted transaction isolation.

SELECT *

FROM [Application].[Cities] ct WITH (NOLOCK)

INNER JOIN [Application].[StateProvinces] sp WITH (NOLOCK)

ON ct.StateProvinceID = sp.StateProvinceID

GO

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

SELECT *

FROM [Application].[Cities] ct

INNER JOIN [Application].[StateProvinces] sp

ON ct.StateProvinceID = sp.StateProvinceID

GO

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

Read Uncommitted

Read Uncommitted means exactly that, it allows you to read data from other transactions that haven't been committed to the database yet.

So, let’s say that person A starts a transaction and inserts some records into table X. Nothing done inside this transaction actually exists in the database until the whole transaction is committed (at a very basic level, that’s what transactions do).

During this same time, person B queries table X using a with (NOLOCK), because that puts the query into read uncommitted isolation, person B gets to see all the data in table X plus the data that is still in flight (i.e. not actually committed to the database yet) from person A's insert.

The reason NOLOCK (read uncommitted) "makes queries quicker" is mainly because of the lack of locks that queries in this isolation level take out on the target tables and its lack of observation of exclusive locks taken out by other transactions.

In a nutshell, NOLOCK (read uncommitted) takes no shared locks to prevent other transactions from modifying data read by this transaction. It also effectively ignores exclusive locks taken by other transactions when they have added or changed data but not committed it yet.

So, this does reduce contention in your database (different connections / people / apps trying to read from and write to the same table) and removes a large amount of the overhead of having to hold locks. This is why you'll see an increase in the speed of your queries.

What "read committed snapshot" isolation level does is to use row versioning (which is a whole blog post in of itself), to give you the last committed version of the record as it looked when the statement started. This is a very simplistic explanation and I'd recommend having a look at row versioning in more detail.

Use [master]

go

Create database Demo

go

ALTER DATABASE Demo
SET READ_COMMITTED_SNAPSHOT ON;

ALTER DATABASE Demo
SET ALLOW_SNAPSHOT_ISOLATION ON;

Use [Demo]

go

Drop Table if exists MyReallyImportantFinanceTable

go

Create Table MyReallyImportantFinanceTable
(
   ID int not null identity primary key
   ,FullName varchar(200) not null
   ,ReallyImportantFinancialValue numeric(10,2) not null
);

Insert Into MyReallyImportantFinanceTable (FullName, ReallyImportantFinancialValue)

VALUES ('Bill', 10000.00),('Ben', 50000.50)

Begin Tran Ins

Insert Into MyReallyImportantFinanceTable (FullName, ReallyImportantFinancialValue)

values ('Jack',70000.50),('Jill', 100000.00)

Select SUM(ReallyImportantFinancialValue)
From MyReallyImportantFinanceTable With(NOLOCK)

Select SUM (ReallyImportantFinancialValue)
From MyReallyImportantFinanceTable

rollback tran Ins

So, What Happened There!?

The "Ins" transaction took out a lock on the table due to the insert. When we ran the queries in the second connection, the first select statement, which was the one with the NOLOCK, ignored the lock taken out by the "Ins" transaction and read the table as is which included the two new records that were still in flight inside the "Ins" transaction

The second select statement didn't have the NOLOCK, so it honoured the lock taken out by the "Ins" transaction and thus was redirected to the version store, because the database was in read committed snapshot isolation. SQL Server then used row versioning (as explained briefly above) to give you the last committed version of the rows, to avoid blocking.