How to avoid blocking in high concurrency system

Blocking cannot be completely eliminated in SQL Server because locks are required to maintain data consistency. However, blocking can be significantly reduced by using proper indexing, keeping transactions short, enabling RCSI, processing data in batches, optimizing queries, and reducing lock escalation.

Below are the few points to avoid the blocking

Ø  Enable Read Committed Snapshot Isolation (RCSI)

Ø  Keep Transactions Short

Ø  Create proper index

Ø  Avoid Large Batch Updates. Keep it short

Ø  Access Objects in Consistent Order: Deadlocks often occur due to inconsistent access order.

Ø  Avoid User Interaction Inside Transactions

Ø  Use Snapshot Isolation for Long Reports

Ø  Avoid Unnecessary Cursors

Ø  Monitor and Fix Blocking Queries

Ø  Reduce Lock Escalation

Ø  Use Appropriate Isolation Level

Ø  Avoid SELECT *