Blocking Vs Deadlocking

Recently I took a session with Roji on Blocking Vs Deadlocking. Couple of notes I am sharing it here..

Blocking - Blocking is a state when a transaction require a lock on a resource on which an incompatible lock is already placed by another transaction. Decreases transactional throughput, increases wait time

Locking - SQL Server ensures Transactional Consistency using two ways.
1. Versioning – Using Snapshot Isolation Levels
2. Locking – Implementing short lived transactional locks on resources. Provides consistency. Prevents conflict
Lock Types

• Shared(S) – Indicates resource is being read by process
• Update (U) – Indicates process is going to update resource. Only one update lock can exist at a time for a resource. It gets converted to X Lock while updating
• Exclusive(X) – Prevent multiple process attempting on same resource
• Intent – Used to Establish Lock Hierarchy.
• Schema – Prevent DML when DDL is executing
• Key Range – Protects range of key during Serializable operations

Lock Hierarchy
To update a particular row below is the lock hierarchy

• Database - Shared Lock
• Table - IX
• Page - IX
• ROW  - X

Lock Escalation Results as below

• Database - Shared Lock
• Table - X Lock

Deadlocking

• Deadlock is a blocking situation where two transactions are holding locks and waiting to acquire lock on the resource held by the other transaction
• Deadlocks are automatically caught by SQL Server Database Engine. Once detected, the Database Engine chooses one of the transactions as the deadlock victim and lets the other transaction complete its process.
• Default search interval is 5 seconds.