SQL SERVER: What is Locking, Types and Examples

Question: What is Locking?

Answer: Locking is a method which is used by the Microsoft SQL Server Database Engine to. It’s provide the synchronize access of Data by one or more than one users to the same piece of data at the same time.

Question: Why we use locking?

Answer: Generally user writing the query without Lock. But in the huge amount of database we should not write query or SQL script without Locking. Because When transaction running, Locking prevent the Dirty Read, it’s avoid the deadlock. We can utilize the resource of database

Question: Types of Locking?

 These are the types of locking which we can use in the database

1. ·        ROWLOCK
2. ·        PAGLOCK
3. ·        TABLOCK
4. ·        DBLOCK
5. ·        UPDLOCK
6. ·        XLOCK
7. ·        HOLDLOCK
8. ·        NOLOCK

Here I am Sharing Some Locking Examples are as follows

Examples:
SELECT OrderID
FROM Orders WITH (ROWLOCK)
WHERE OrderID BETWEEN 300
AND 5000

UPDATE Products WITH (NOLOCK)
SET ProductCat = 'Books'
WHERE ProductSubCat = 'Story'