Locking Mechanism -- How locking is intergrated with the ISOLATION Level

Locking Mechanism –

In SQL Server whenever multiple uses are trying to access the piece of data at the same moment locking mechanism provides the data in synchronies ways to all the uses. Specifically Locking mechanism is using in programs at the time of deal with isolation levels.

Locking is specifically integrated with the ISOLATION LEVEL.

Types of mode of Locking –

-          Shared  (S)

Shared locks apply on the read (SELECT) operation. Whenever one transaction is reading the data on same moment other transaction cannot modify the data. Once read operation is completed shared lock released from the transaction.

-          Update (U)–

At a single moment only one transaction can modify the data. Other transaction still waits to complete the current transaction.

-          Exclusive(X) –

No other transaction can’t modify the data and read operation can be take place with the help of NOLOCK hints.

At the time of both the operation read and modify, first read operation access the data before performing the modification operation in exclusive lock.

-          Schema

-          Bulk update

-          Key range

-          Intent