Concurrency in SQL Server

Concurrency is the ability of SQL Server to allow multiple users or transactions to access and modify data simultaneously while maintaining data consistency and integrity. A simple example is an online banking system where one user withdraws money, another transfers money, and a third checks the balance at the same time. SQL Server manages concurrency using locks, isolation levels, and row versioning mechanisms. Without proper concurrency control, issues such as dirty reads, lost updates, blocking, and deadlocks can occur. The goal of concurrency control is to maximize performance and user throughput while ensuring correct and consistent data.

“Many people working on the same data simultaneously”.

SQL Server Manages Concurrency using below mythology to reduce blocking.

Ø  Locks

o   Shared Lock (Read)

o   Exclusive Lock (Update)

o   Update Lock

Ø  Isolation Levels

o   READ UNCOMMITTED

o   READ COMMITTED

o   REPEATABLE READ

o   SNAPSHOT

o   SERIALIZABLE

Ø  Row Versioning

o   SNAPSHOT Isolation