---
title: "Locks in SQL Server"  
description: "In this article, I’m explaining the locks in sql server and its types.  Locking helps in providing concurrency within the database. Without locking, S"  
author: "Sumit Kesarwani"  
published: 2013-07-10  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/546/locks-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Locks in SQL Server

In this article, I’m explaining the locks in [sql server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server) and its types.\

Locking helps in providing [concurrency](https://www.mindstick.com/interview/23470/what-is-the-concurrency) within the database. Without locking, SQL server would not be able to prevent [multiple users](https://www.mindstick.com/forum/158182/how-to-handle-session-conflicts-and-concurrency-issues-in-a-web-application-with-multiple-users) from updating the data at the same time.\
Locks helps in [preventing](https://www.mindstick.com/news/2244/issue-preventing-users-from-accessing-facebook-s-social-networking-platforms-has-been-resolved) following situations that would let to [compromise](https://answers.mindstick.com/qa/35808/what-did-the-three-fifths-compromise-state) [transaction](https://www.mindstick.com/blog/175/transactions-in-database) integrity:

##### Lost Updates

An update can get lost when a transaction overwrite the changes from another transaction.

##### Uncommitted Dependency

This problem is also [known as](https://answers.mindstick.com/qa/35703/ricky-ponting-is-also-known-as-what) Dirty Read. This problem occurs when a record is read while someone is still making changes to it and yet not finished with it.

##### Inconsistent Analysis

An inconsistent analysis occurs when a transaction reads the same row more than once and in between these two readings another transaction modifies that row.

##### Phantom Read

This problem occurs when the transaction are not isolated from one another.

##### Types of Locks

##### 1. Shared Lock

##### 2. Exclusive Lock

##### 3. Update Lock

##### 4. Intent Lock

##### 5. Schema Lock

##### Shared Lock

Shared lock is also known as Read Lock. SQL server uses the shared locks for operation that neither change nor [update data](https://www.mindstick.com/forum/380/how-achieve-batch-update-data). No transaction can [modify the data](https://answers.mindstick.com/qa/97470/is-it-possible-to-modify-the-data-once-it-is-written-in-a-block) in a resource while a shared lock is being held on that resource by any other transaction.

##### Exclusive Lock

Exclusive locks or write locks are used for data modification statements such as INSERT, UPDATE or DELETE. Only one transaction can acquire an exclusive lock.

##### Update Lock

Update lock signals that a transaction intends to modify a resource. An update lock must be upgraded to an exclusive lock before the transaction actually makes the modification. Only one transaction at a time can hold an update lock on a particular resource.

##### Intent Lock

SQL server uses the intent lock internally to minimize locking conflicts. It helps in establishing a locking hierarchy so that other [transactions](https://www.mindstick.com/interview/864/explain-acid-rule-of-thumb-for-transactions) cannot acquire locks at a more inclusive level. This means that a transaction has an exclusive row level lock on a particular record then another transaction will be prevented from acquiring an exclusive lock at the table level.

##### Schema Lock

Schema locks ensures that a table or an index is not dropped or its structure modified, when referenced by another session.

---

Original Source: https://www.mindstick.com/blog/546/locks-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.