Application That Can Deadlock
- Application That Can Deadlock Control
- Application That Can Deadlock Go
- Application That Can Deadlock
- Application That Can Deadlock Get
- Application That Can Deadlock Meaning
- Application That Can Deadlock 2
Tony Davis demonstrates a deadlock incident, as detected in SQL Monitor, and shows how we can find the cause quickly, using the Extended Events deadlock graph provided in the alert details, plus details of the sessions and queries that were running at the time it occurred.
Deadlocks can occur when two or more sessions inside of the database engine are waiting for access to locked resources held by each other. Technically, a deadlock can be viewed as a circular locking chain, because every process (SPID) in the blocking chain will be waiting for one or more other processes in that same blocking chain, such that none can complete.
By default, the priority set is NORMAL. So, if a deadlock happens and priority is other than default, then the process with less priority will become the victim of deadlock. Deadlock in SQL Server is a situation of resource contention and if it happens, the application tries to handle it itself. On event of deadlock in java application you can do following to investigate it: 1) By pressing Ctrl+Break on Windows machines print thread dump in console of Java application. By analyzing this thread dump you will know cause of deadlock.
- This is a cyclical dependency and results in a deadlock. Deadlocks can involve more than two transactions, but two is the most common scenario. Deadlocks are the result of application code.
- If the same application can open two or more connections which can cooperate with each other, then it would not create deadlocks. That’s why it’s advised to use Bound connections. Deadlocks are not the only hassle you are likely to experience while working on a SQL Server database.
As soon as SQL Server detects a deadlock it will act to resolve it, by killing one of the deadlocked processes, and rolling back whatever transaction it was running; potentially an important business transaction, which would need to check the error code for a deadlock and respond accordingly. Also, while the protagonist processes are deadlocked, other processes may also be blocked from accessing the affected tables.
This article will discuss why deadlocks occur, why they require immediate investigation by the DBA, and the diagnostic data required to troubleshoot them. It then demonstrates a deadlock incident, as detected in SQL Monitor, and shows how we can find the cause quickly, using the Extended Events deadlock graph provided in the alert details, along with supporting diagnostic data on resource usage, and the queries and processes executing, around the time of the alert.
How SQL Server resolves deadlocks
SQL Server has a lock monitor that auto-detects deadlocks, by periodically checking for the existence of any circular locking chains.
For example, let’s say session A holds a lock on one resource, but can’t proceed until session B releases a lock on a resource it needs to access, and Session B can’t proceed till Session C releases a lock, and session C can’t proceed until session A releases its lock.
SQL Server will resolve the deadlock by killing one of these sessions (the deadlock victim), and rolling back its transaction, therefore releasing any locks it held. This allows the other sessions in the blocking chain to continue executing.
The unfortunate deadlock victim, receives the dreaded 1205 error message:
Transaction (Process ID XX) was deadlocked on resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
The DBA needs to ensure proper handling of this 1205 exception, to avoid UnhandledException errors in the application. Whether this is done using a BEGIN TRY/CATCH
block inside the SQL being executed, or within the application code, the most common approach is to pause, and then retry the transaction a set number of times.
So why do we need to diagnose deadlocks?
Just because DBAs don’t need to intervene, manually, to resolve a deadlock, it doesn’t mean they can ignore them. It’s an important error condition, and they should investigate immediately to find out what caused the deadlock, and take steps to prevent it recurring.
Firstly, the deadlock victim may turn out to be an important business process. We can avoid a session being chosen as a deadlock victim, if its running an important process, by assigning it that session a DEADLOCK_PRIORITY
setting of HIGH
. However, if two or more sessions involved in a deadlock have the same deadlock priority, then SQL Server’s only criteria for choosing the deadlock victim is the estimated cost to roll back the session. On that basis, if a session that’s modified a single row of data deadlocks with a complex reporting query that’s been running for 5 hours, the latter will be chosen as the victim.
Secondly, some of the most common causes of deadlocks include poor database design, lack of indexing, poorly-designed queries, transactionitis (unnecessary or overly-long explicit transactions), and inappropriate choice of transaction isolation level. If these problems afflict your databases, it’s likely that your applications are suffering not only from deadlocks, but also slow-running queries, severe blocking, and other performance problems. By monitoring, and diagnosing deadlocks, we can then go on to improve the overall database performance.
Common types of SQL Server deadlock
For an excellent dissection of some of the common types of deadlock, what causes them, and strategies to prevent their recurrence, see Gail Shaw’s article, SQL Server Deadlocks By Example.
Diagnosing deadlocks: the deadlock graph
The key piece of diagnostic data is the deadlock graph, which shows us which processes and resources were involved in a deadlock, which one was chosen as the deadlock victim, and more.
One way to capture the graph is to set up a server-side Profiler trace to capture the Deadlock Graph event, and wait for the deadlock to recur. However, a much more convenient method is to use the Extended Events xml_deadlock_report
event, captured automatically by the system_health
event session, which is running by default on all installations of SQL Server 2008 and later. The xml_report
field of this event holds the deadlock graph. Also, uniquely, the Extended Events deadlock graph allows diagnosis of multi-victim deadlocks, where SQL Server had to kill multiple sessions to resolve the deadlock condition.
If you’re trying to resolve deadlocks on instances that pre-date SQL Server 2008 or for some other reason can’t use Extended Events or Profiler traces then I’ll refer you to Jonathan Kehayias’ article, Handling Deadlocks in SQL Server, for other ways to do this.
Figure 1 shows an Extended Events xml_deadlock_report
event in the event_file
target of the system_health
default event session. You can download the ReaderWriterDeadlock code file to reproduce the deadlock.
Figure 1: Capturing the deadlock graph using Extended Events
You can click on xml_report
to open the full XML deadlock graph, as shown in Figure 2. I’ve added the colored boxes, manually, to highlight the three main sections of the deadlock graph (victim list, process list, resource list).
Figure 2: the XML deadlock graph
Reading an XML Deadlock Graph takes patience, and some of the elements, particularly the <process>
elements, contain many fields, so you’ll find yourself scrolling left and right to find some of the important details, such as the isolation level requested for each of the deadlocked processes.
However, once you’ve mastered the basics, all the information you need to diagnose the deadlock condition is in there. The alternative is to switch to the Deadlock tab, in Figure 1, to see a basic, visual representation of the graph.
Figure 3: Visual representation of the deadlock graph is SSMS
The blue cross indicates clearly which process (SPID 84) was the deadlock victim. Clicking on it brings up a tooltip showing the last SQL batch it issued, a stored procedure execution. We can see that, to execute this batch, process 84 issued a request for an Intent Shared (IS) lock on an object, although which object isn’t immediately obvious (it’s Person.Address
), but was blocked. We can also see that process 84 already owned a IX lock on a page in on the Person.Person
table.
The other process (SPID 82) already owned an Exclusive (X) lock the object on which 84 had requested in IS lock, which is why 84’s request was blocked. We can also see that process 82 made a request for a S lock on a page in Person.Person
, to read it, but was blocked because process 84 held an IX lock on it. So, we can get a feel at what’s happened here, and the processes and resources involved, but the full details are elusive.
Generally, not many developers or DBAs find SSMS’s visual depiction of the deadlock graph easy to read and understand, and so most stick to wading through the XML version.
Monitoring Deadlocks in SQL Monitor
SQL Monitor’s key mode of operation is to raise alerts based on abnormal or undesirable conditions and properties or error, on any of the monitored servers, instances and databases. It provides a range of alerts for specific SQL Server errors and problems, including deadlocks.
FIFA 06, also called as FIFA Soccer 06, is a sports game published by Electronic Arts and developed by EA Canada based around the game of football.It was released on October 4, 2005 in the U.S. For game platform like GameCube, PlayStation 2, Xbox, PlayStation Portable, Windows PC. Fifa 6 download pc. Mar 10, 2017 FIFA 06 Overview. FIFA 06 Free Download for PC, also known as FIFA Soccer 06, is a video game developed by EA Canada and published by Electronic Arts based around the game of football. It was released in the United States on 4 October 2005 for the PlayStation 2, Xbox, GameCube, Windows, and Nintendo DS. Sep 06, 2005 Download FIFA 06. You are good with your feet, let’s see with your hands. FIFA 06 is now available! This game has been on the top for years together with Pro Evolution Soccer. This year FIFA 06 promises to fight for the throne again, with a new graphic engine and lots of animations. FIFA 06 includes. Jul 04, 2018 LAST UPDATE: FIFA 16 Download Crack, Install, Features and PC Requirement FIFA is one of known football simulation series available for PC and other gaming consoles. If you love to play such games, then it is easy to find that FIFA 16 was a big hit. It is still a popular game that is available for PC. Jun 24, 2016 FIFA 06 Game Free Download For PC Full Version. Every single thing is available here but the links will be working and checked by only me some will be in torrents and others are direct links take it now. You need to clear 15 years career mode some matches are very easy in it but others are very hard even you can’t play it.
SQL Monitor 8 includes a built-in alert, called Deadlocks (Extended Events), which will, by default, raise a medium-severity alert whenever a deadlock occurs, and captures the Extended Events Deadlock Graph. This means you get through SQL Monitor the same XML Deadlock Graph as you get from Extended Events, but with some useful enhancements and advantages.
Firstly, SQL Monitor presents a much simpler, clearer, graphical version of the deadlock graph, which includes all the important information you need to diagnose the problem. Secondly, it provides all the important contextual data you need to understand about the activity on the server, and on the affected database, at the time of the deadlock.
Armed with all this diagnostic data, you can build a full picture of why and when the deadlocks occur, what other process might be affected, and you can formulate the best strategy for eradicating the problem.
SQL Monitor’s graphical deadlock graph
In SQL Monitor, we can see immediately that a deadlock alert was raised on one of our SQL Server instances.
Figure 4: A deadlock alert was raised
Clicking on the deadlock alert will, as for any alert, takes us to the Details tab of the Alerts screen. For every alert, the top of the Alerts screen (not shown) will indicate the time the alert was raised (and ended, if relevant) and the status of the alert.
In the body of the Details tab, we see the details that are specific to the alert. In this case, we see graphical depiction of the deadlock graph, as shown in Figure 5.
Figure 5 – The Details tab for a Deadlock alert showing a graphical deadlock graph
The source of the Extended Events XML Deadlock Graph is still fully available; just click on the Output tab to see it. However, in SQL Monitor, the graphical version makes it very easy to see all the critical details.
We can see straight away that processes 84 and 82 were engaged in a deadlock and the red border indicates that SQL Server chose process 84 as the deadlock victim, and rolled it back. For each process, we can see the machine, application and login name for the client who issued the SQL batch.
Process 84 needed to acquire a lock on the Person.Address
table, to read it, but was blocked since process 82 held an X lock on this object. In turn, process 82 needed to acquire a lock on a page in the Person.Person
table, to read it, but was blocked since process 84 held an IX lock on this page. At this point, SQL Server’s lock monitor detects a circular lock chain and kills the suspended thread that is running the process with the lowest rollback cost.
If we click on either of the processes, we further important diagnostic details including the text of the SQL batch issued by that process. In Figure 6, we see that process 82 issued a batch to execute a stored Procedure called Person.UpdateAddressDetails
, and that the last SQL statement executed was a SELECT
*
query on the Person.Person
table.
Figure 6: Details of each process involved in the deadlock
Likewise, if we examine the deadlock victim, we’ll see that it was executing an UpdatePersonDetails
stored procedures, and the last SQL statement it issued was a query on the Person.Address
table.
We also see the transaction isolation level for the SQL Server session, which in this case was the default READ
COMMITTED
level. However, it’s worth knowing if the application specified use of a more restrictive transaction isolation level (such as REPEATABLE
READ
or SERIALIZABLE
), which forces SQL Server to lock resources for longer, and is a common cause of blocking and deadlock issues.
Root cause analysis
The advantage of using a tool like SQL Monitor is that not only do we see the deadlock data, we get to see it within the full context of the activity occurring on the SQL Server instance around the time of the alert.
Server and database-level diagnostic data for the alert
Application That Can Deadlock Control
Scroll down on the alerts screen and you’ll see a host of performance data, arranged by tabs, showing snapshots and summaries of resource usage and activity levels on the host machine, and SQL Server instance, at the time of the alert, as well as details of SQL Server queries and processes, or other server-level processes. For example, the SQL Server tab shows a spike in the average lock wait time metric, around the time of the incident.
Application That Can Deadlock Go
Figure 7: SQL Server instance-level metrics around the time of an alert
On the Top queries tab, we see queries that were executing around the time of the alert. By default, SQL Monitor lists them in order of duration, but by clicking on the desired column (such as Logical reads), we can reorder the results by that column.
Figure 8: Top queries from the time of the deadlock alert.
We recognize a few of these queries to be part of the stored procedures involved in the deadlock. If we click on the SELECT *
query on Person.Person
, we see the text of the query, a link to view its query plan, from the plan cache, and a list of any significant waits associated with the query. In this case, we can see that it was forced to wait when trying to acquire a shared read lock.
Figure 9: Details of one of the top queries.
Figure 10 shows the associated query plan. This query is part of the Person.UpdateAddressDetails
stored procedure (executed by SPID 82), and we see the plans for each statement in the stored procedure. In this case, the stored procedure (written purely for demo purposes!) starts an explicit transaction, updates the Address
table, and then queries the Person
table (before rolling back).
Figure 10: Query plan for one of the stored procedures executed to cause the deadlock.
If we were to examine the sixth query in the list from Figure 9, we’d see that it’s a query from the Person.UpdatePersonDetails
stored procedure (executed by SPID 84, the deadlock victim), which access the same tables in the opposite order, first updating the Person
table and then querying the Address
table.
We can also see details of other processes and queries that may have been affected by the incident. In this case, the longest running query in the list was a separate ad-hoc query that also needed access to the Person
and Address tables, and suffered locking waits, caused by the occurrence of the deadlock.
Figure 11: Other affected queries.
The Interactive Incident timeline
Application That Can Deadlock
Alongside the specific diagnostic data for an alert, it’s also very useful to review the Overview graph for the affected SQL Server instance, which displays CPU, Memory, I/O and Waits over a selected period, along with an “incident timeline” showing the occurrence of alerts. In this case, we see a cluster of alerts, including the deadlock alert we investigated. We can see a ‘bump’ on waits around this period, and a significant peak in disk IO activity.
Figure 12: Interactive Overview graph and incident timeline
Scrolling down the Overviews page, we see top queries and waits that occurred in the set period, along with snapshots of server- and database-level metrics, as well as specific summaries of processes running at the time of the alert, such as Blocking Processes (Top 10 by time), and others.
Figure 13: Blocking Processes (Top 10 by time)
Next Steps
Armed with knowledge of the queries involved in the deadlock, and the with the broader knowledge of concurrent activity on the server, we can take steps to resolve the problem, to ensure it does not recur. This might involve query tuning, adding a new index, or modifying an existing one, or some other standard mitigation for deadlocks. Often the cause is transactionitis, were the developer has unnecessary transactions, or holds them open for too long, or places statements within a transaction that have no need to be in there.
Th deadlock in this article, albeit contrived and simplified for demo purposes, was an example of a reader-writer deadlock, caused by two processes that each updated and then queried the same two tables, in a different order, within an explicit transaction. Questions to ask in resolving this problem would include:
- Is a case of transactionitis? In other words, given that in neither case does the stored procedure include multiple data modification statements that need to form an atomic unit, is the explicit transaction necessary?
- Can we move either
SELECT
statement outside the transaction? - Can we reverse the order of either, or both, sets of statements?
If any of these changes are possible, the deadlock will be prevented, though the third solution on the list relies on use of the default Read Committed isolation level, where shared locks are released no later than the end of the query.
For more general advice on coding techniques to help avoid deadlocks, see the Reducing deadlocks article in the Redgate documentation (reference below).
Summary
With SQL Monitor you will be alerted to deadlocks as they happen, giving you the chance to recover any lost processes and put procedures in place to minimize their occurrence in the future.
SQL Monitor 8 support the extended events deadlock graph, and adds much simpler and clearer visualization than is available in SSMS. With the added benefit of snapshots of historic data, from the time of the alert, you can start to build a full picture of what happened and what was affected as a result.
You can download a free 14-day trial of SQL Monitor to start monitoring deadlocks. And if you’d like any help, just get in touch.
Application That Can Deadlock Get
Further Reading
- SQL Server Deadlocks by Example, Simple-Talk article by Gail Shaw
https://www.red-gate.com/simple-talk/sql/performance/sql-server-deadlocks-by-example/ - Handling Deadlocks in SQL Server, Simple-Talk article by Jonathan Kehayias
https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/ - Reducing deadlocks, Redgate documentation
https://documentation.red-gate.com/sm7/advanced-operations/reducing-deadlocks - Detecting and Ending Deadlocks, Microsoft TechNet resource
https://technet.microsoft.com/en-us/library/ms178104(v=sql.105).aspx
Application That Can Deadlock Meaning
Share this post.
Application That Can Deadlock 2
Article
How to monitor the impact of patching on SQL Server performance
This article uses the Spectre/Meltdown bugs as means to demonstrate how you can use a tool like SQL Monitor to assess the impact of patching on the throughput and performance of your SQL Servers.
Article
Azure SQL Elastic Pools and SQL Monitor
This article explains the Azure-tailored metrics and alerts in SQL Monitor that track use of the limited compute resources available to an Azure SQL Elastic Pool, and to each of the databases in it.
Webinar
Minimizing the impact of data breaches in dev and test databases
Data privacy regulations make it essential to implement controls and processes that protect personal data and guard against data breaches. Join Microsoft MVP Steve Jones for a webinar on how to minimize the impact of data breaches in SQL Server dev and test databases.
Webinar
How to keep your delivery processes secure with Database DevOps
Extending DevOps practices to the database, brings additional advantages, ensuring you protect personal data across your SQL Server estate, while also improving the efficiency and quality of software delivery. We’ll explore the impact database DevOps has on regulatory and compliance requirements and how approaches such as automation, can improve accuracy, transparency, and faster recovery across the entire database development cycle.
University
Take the Getting Started with SQL Monitor course
Forums
SQL Monitor Forum
Real-time SQL Server performance monitoring, with alerts and diagnostics