---
title: "How does SQL Server manage concurrency control?"  
description: "How does SQL Server manage concurrency control?"  
author: "Sandra Emily"  
published: 2024-07-15  
updated: 2024-07-16  
canonical: https://www.mindstick.com/forum/160902/how-does-sql-server-manage-concurrency-control  
category: "mssql server"  
tags: ["mssql server", "sql server", "sql"]  
reading_time: 3 minutes  

---

# How does SQL Server manage concurrency control?

How does SQL Server manage [concurrency control](https://answers.mindstick.com/qa/111883/how-does-concurrency-control-work-in-databases-and-what-are-the-various-isolation-levels)?

## Replies

### Reply by Ashutosh Patel

#### Concurrency Control in SQL Server

SQL Server basically manages [concurrency](https://www.mindstick.com/interview/23470/what-is-the-concurrency) [control](https://www.mindstick.com/blog/197/asp-dot-net-repeater-control) through a combination of locking mechanism and isolation layer. Here is how it works in general.

#### Way to Manage Concurrency Control in SQL

Here are several ways that are used to manage the concurrency control by SQL Server,

#### Locking Mechanism

SQL Server uses locks to control access to data during transactions, preventing conflicts that occur when multiple connections access the same data simultaneously

There are several types of locks,

**Shared (S) locks-** Allows multiple services to read objects simultaneously.

```plaintext
SELECT * FROM tableName WITH (TABLOCKX);
```

**Exclusive (X) lock-** Prevents other services from accessing resources.

```plaintext
UPDATE tableName SET column1 = value WHERE condition;
```

**Update (U) Locks-** Used to set the logic of updated functionality.\
**Intent Locks-** Indicates the intent of the transaction to modify the object.

\
SQL Server automatically maintains these locks to ensure data integrity and prevent anomalies such as dirty reads, non-repetitive reads, and phantom reads.

#### Isolation Levels

SQL Server supports different isolation levels that specify the degree to which jobs are isolated from each other. Common separation rates are:

**Read Uncommitted-** Allows connections to read data that has been changed by other services but not yet committed. This layer provides the highest concurrency but can cause dirty reads.

**Read Committed-** Ensures that the statement reads only committed data. It prevents dirty reads but still allows non-repetitive reads and phantom reads.

**Repeatable Read-** Ensures that changes made by other projects after the project has started will not be reflected in the project. It prevents dirty reads and unusual reads but can still allow false reads.

**Serializable-** Provides the highest level of isolation. It ensures complete isolation of connections. It prevents impure reads, atypical reads, and phantom reads but can reduce concurrent reads.

**Syntax-** Setting isolation level,

```plaintext
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
```

This sets the transaction isolation level to `READ COMMITTED`, which ensures that transactions only read committed data.

**Example-** using an isolation level in a transaction

```plaintext
BEGIN TRANSACTION;
SELECT * FROM tableName WITH (UPDLOCK, HOLDLOCK); -- Example of using locking hints
-- Perform operations
COMMIT TRANSACTION;
```

#### Row versioning (for read committed snapshot isolation)

SQL Server also supports the Read Committed Snapshot Isolation (RCSI) feature which uses row versioning instead of providing a lock to provide transaction isolation this can reduce blocking for read operations.

## Syntax-

```plaintext
ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON;
```

#### Lock Escalation.

SQL Server can increase locks from a finer granularity (such as a row or page) to a coarser granularity (such as a table) to save system resources and improve performance.

## Syntax-

```plaintext
ALTER TABLE tableName SET (LOCK_ESCALATION = AUTO);
```

This allows SQL Server to automatically escalate the lock based on its internal criteria.

#### Deadlock Detection and Resolution

SQL Server detects deadlocks, where two or more tasks are waiting for each other to open, and resolves them by automatically selecting one client to roll back as a deadlock victim

## Syntax-

```plaintext
BEGIN TRY
   -- Perform transactional operations
END TRY
BEGIN CATCH
   IF ERROR_NUMBER() = 1205
       -- Handle deadlock (rollback or retry)
END CATCH;
```

**Also, Read:** [Describe the different type of roles in SQL Server.](https://www.mindstick.com/forum/160905/describe-the-different-type-of-roles-in-sql-server)


---

Original Source: https://www.mindstick.com/forum/160902/how-does-sql-server-manage-concurrency-control

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