Firstly, we have to understand what is lock and where we put locks in SQL Server and then different types of locks.

What is Lock in SQL Server?

As we all know, multiple users need to access databases concurrently. So locks come into the picture to prevent data from being corrupted or invalidated when multiple users try to do operations such as read, write and update on database.
“Lock is defined as a mechanism to ensure data integrity, consistency while allowing concurrent access to data. It is used to implement concurrency control when multiple users access Database to manipulate its data at the same time”

Where locks are put in Database

Now, we have to understand where locks are actually present in our database, it means on which resource it locks or not.
locks
RID: (Row ID)
RID Used to lock a single row within a table.
Table: Complete table, including all data and indexes.
Key: Row lock within an index. It means primary key, Candidate Key, Secondary key etc.
Page: 8-kilobyte (KB) data page or index page. Lock can be placed on Page Level also, it means if a particular page is locked so another user cannot update data on it.
Extent: Contiguous group of eight data pages which can include index pages also.
Database: Entire Database can be locked for some type of users who have read permission on database.

Different Models of SQL Server locks

Examples of Locks in SQL Server

Shared lock: select balance from tbl_account where acct_number = 25
--shared lock
We can perform multiple select statements on the same table.
Exclusive lock
insert tbl_account values(34, 500)
When we perform insert query in the table then page lock in Exclusive mode. Until recorded it's not inserted in table n other operation perform here. Similarly, delete, Update operation occurs.
delete tbl_account where balance < 0
update tbl_account set balance = 0 where acct_number = 25

Lock Compatibility Matrix

Lock Compatibility Matrix
Read more articles on SQL Server: