Tag : deadlock


NOLOCK table hint 9

A lot of people use NOLOCK table hint in their SELECT queries, some of them are expert and well aware of its pros and cons, but some are not. So, In this post we are going to focus on this table hint and its impact.

What is NOLOCK

NOLOCK and READUNCOMMITTED hints used with SELECT queries to get the data without issuing shared lock (which prevents modification of data by other transactions during read operation) on the data, and also allows to read the data acquired with exclusive lock by other transactions. NOLOCK hint is equivalent to READUNCOMMITTED isolation level which is least restrictive in SQL Server. It does not restrict the dirty reads and increases the concurrency by reducing blocking.

It holds a Sch-S (Schema stability) lock during its execution scope which blocks the transactions requesting for Sch-M (Schema modification) lock, required by DDL operations. Reverse is also true … More


Tracing deadlock with Extended events

In last two articles, we have learned how to capture deadlock using SQL Server Profiler and Tracing deadlock with Trace Flag 1222 and Trace Flag 1204. Now in this post we are going to learn a very useful method of tracing deadlock with Extended Events. These events are less resource intensive and light weight in comparison of SQL Server Profiler. We use these events to capture interested information to be analyzed now or later from heavy load servers (Like production server).

If we don’t have any clue about deadlock being occurred in midnight at our production server and because of heavy load on server, we can not use SQL Server Profiler their. Extended events are very useful because of very few resource utilization. We can create a session and start it to capture only those events in which we are interested. Extended events are very useful in such types … More


Tracing deadlock with Trace Flag 1222 and Trace Flag 1204

In previous article, we have already learned that how we can trace a deadlock situation with SQL Server Profiler. In case you don’t read, click here. And to understand locking, blocking and deadlocking in sql server, click here. In this post we will learn how we can use “trace flag 1204 and trace flag 1222” to trace a deadlock.

In this article we are going to learn how we can trace SQL Server deadlock and extract the deadlock information from captured data. We will use DBCC TRACEON command, Trace Flag 1204 and Trace Flag 1222 to capture the details.

What is DBCC (Database Console Commands)?

DBCC statements act as Database Console Commands for SQL Server. We can run it using TSQL programming language. It provides commands related to various categories like Maintenance, Miscellaneous,  Informational and Validation.

Deadlock

As we know that Deadlock in SQL Server is a situation … More


Tracing deadlock with sql profiler 3

Hi folks, In this article “Trace deadlock sql profiler”, we are going to learn, how we can capture a deadlock in a graphical form in sql server using an inbuilt tool of sql server named “SQL Server Profiler”.

First of all note that, don’t try to reproduce this sample deadlock and start SQL Server Profiler trace on your production server unless you are well versed of Profiler and event tracing. I recommend to do all these stuffs on you local machine to learn capturing deadlocks using SQL Server Profiler.

We are going to cover below points in this article;

  1. What is a deadlock?
  2. How to create a demo purpose deadlock?
  3. Using SQL Server Profiler to capture deadlock details and graphs.
  4. Understanding the deadlock important events in sql server profiler.

What is a deadlock?

A short note on deadlock:

Deadlock occurs when two sessions are waiting for a lock to clear … More


Locking, Blocking and Deadlocking in sql server 1

Lock vs Block vs Deadlock

Have you ever asked to differentiate lock, block and deadlock in sql server during your interview session? In this article we are going to differentiate these frequently confused terms – Lock, Block and Deadlock. So lets have a look on “Lock vs Block vs Deadlock”

These terms sound the same but are different in their meanings. Have a look on below definition to understand it.

Lock

When any session needs access to a piece of data from Database, a lock of some type is placed on that data to maintain the isolation property of database. Locking is different from blocking.

Block

When a session needs to wait for a resource being locked by another session, this process is called as blocking. e.g. If session “A” is waiting for a resource “R” being used by session “B”, you can say that session “B” is blocking session … More