SQL Server - Locks, Blocked Processes and easy way to find it

Indeed, I am very much sure that one thing a database professional will certainly run into at a point or another when ‘blocked processes caused by locks on database objects’.

But what are database locks, why can they sometime cause one process to block another?

Let’s get into more detail

The ACID

Locking is an integral part of any successful processing of transactions in any RDBMS. The reason of this is the need for data integrity within the system. The RDBMS keeps the integrity of the data stored by making every transaction pass the ACID test that means transaction has to meet the following criteria:

·         Atomic – the transaction performs in an all-or-nothing fashion

·         Consistent – transactions are processed in a uniform manner

·         Isolated – transactions are properly isolated until they are finished

·         Durable – maintain a record of uncompleted transactions in the event of recovery during a failure

The isolation part of the ACID test is addressed by locking objects (in most cases one or more rows of data) until the associated transactions are completed. The locking of objects stop all other processes from being able to change these objects until the lock is removed.

Blocking

Blocking is the logical outcome of locks being issued against various objects in a database. If a request is made against an objects which has a lock issued for it, then the request is delayed until the lock is removed. The delaying or stopping a transaction is referred as a block.

There will always be some level of blocking in an active SQL Server database. This is the natural outcome of the goal of 100% data integrity and the resulting need for locks. High levels of blocking or blocks that are not quickly resolved can become a major concern. One of the most common symptoms of excessive blocking is high SQL Server wait counts, which in turn can cause slow response times to requests made on a database.

Finding Blocks

Open SQL Server Management Studio and connect to the SQL Server instance you wish to monitor. After you have connected, right click on the instance name and select 'Activity Monitor' from the menu.

Once Activity Monitor has loaded, expand the ‘Processes’ section. Any processes which are currently in a blocked state will display the SPID of the processes blocking it in the ‘Bloc By’ column:

Understanding which queries and processes are causing blocks is important in helping make sure your database is running smoothly and providing the best experience for its users.