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?
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 place on Page Level also, it means if particular page is locks so another user cannot update data on it.
Extent: Contiguous group of eight data pages which can includes index pages also.
Database: Entire Database can be lock for some type of users which have read permission on database.
Different Models of locks:
- Shared(S)
a. Used for select operations
b. Enable other sessions to perform select operations but prevent updates
c. read-only operations
d. Operation with SELECT statement Generally use in Shared mode .
- Exclusive(X)
a. Used for DML operations
b. Prevents other users from accessing the resource.
c. Operations, such as INSERT, UPDATE, or DELETE means DML query. Ensures that multiple updates cannot be made to the same resource at the same time.
- Update(U)
a. Preliminary stage for exclusive lock. Used by the server when filtering the records to be modified
b. Prevents other update locks
c. A solution to the cycle deadlock problem
- Intent
a. Intent Locks are used for establish a lock Hierarchy.
b. The types of intent locks are:
1.intent shared (IS),
2.intent exclusive (IX)
3.shared with intent exclusive (SIX).
- Schema
a. Schema locks are used when an operation dependent on the schema of a table is executing.
b. The types of schema locks are:
c. Schema modification (Sch-M) and
d. Schema stability (Sch-S).
- Bulk Update (BU)
a. Bulk Update used when bulk-copying data into a table and the TABLOCK hint is specified. Generally use when user want to insert huge data in database/
Examples of Locks:
Shared lock: select balance from tbl_account where acct_number = 25
--shared lock
We can perform multiple select statement on same table.
Exclusive lock:
insert tbl_account values(34, 500)
When we perform insert query in 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
![table]()
Read more articles on SQL Server: