SQL Server Scrapbook: Idera Learning

Idera Learning - SQL Server Blocking

A. Blocking Example:

Run the following query in SQL server session 1:

begin tran

update customers with (rowlock)

set cust_age=100

Run the following query in session 2:

select top 10 * from dbo.customers

The session 2 never completes as the customers table is under a transaction in session 1 and session 2 waits for it to complete.

The session trace shows that which command of the sesion is getting blocked:

B. Indirect Blocking:

Now open a third session (Session 3) and run the following command in it:

select top 10 * from dbo.customers.

This command also never completes. We can see the details of blocking and blocked by in the details window of Idera.

Here we see that Session ID 58 (Session 2) is blocked by Session ID 54 (Session 1)

In Blocking Pane we can see the blocking details:

Session 1: Session ID 54

Session 2: Session ID 58

Session 3: Session ID 62

In the second half plane of the above detail view we can see that there is 1 Lead blocker and 2 Blocked sessions. That is once the session transaction is closed the other two session commands will execute.

Now stop the session 2, we see that there is only 1 lead blocker and 1 blocked session:

Open another session and execute the following command:

select top 10 * from dbo.customers with (nolock)

This session does not get blocked as the “nolock” option has been used. It will read dirty records, i.e., for example if there are records that have been updated in other session transactions but not yet committed.

C. Direct Blocking:

Execute the following commands:

Session1:

begin tran

update customers with (rowlock)

set cust_age=100;

update orders with (rowlock)

set order_qty = 50

Session2:

select top 10 * from dbo.customers

Session3:

select top 10 * from dbo.orders

Here we see that sessions 2 and 3 are directly blocked by session 1 as the tables used in blocked statements of sesions 2 and 3 are different.

In the previous scenario we had seen that session 1 blocked 2 and session 2 blocked 3 that is because sessions 2 and 3 were blocked while trying to access the same table. Since session 2 initiated the read first it stands first in the queue to read the table. Once it completes it only then session 3 can go ahead and complete its execution. In this case we can see that session 3 is blocked by session 2. And session 2 is blocked by session 1. Hence session 3 is indirectly blocked by session 1.