<img height="1" width="1" style="display:none;" alt="" src="https://dc.ads.linkedin.com/collect/?pid=204641&amp;fmt=gif">

Are your Database Locks Dead?

Posted by Lukas Vileikis on Oct 27, 2021 6:29:00 AM

If you have found yourself in the shoes of a database administrator or even a very database-savvy developer or a system administrator, you probably know a thing or two about databases. Chances are that you have probably heard about database locks as well – a database lock is usually used to make only one session able to update a particular set of data, and it’s a pretty usual thing in the world of database administration.

However, what you might not have heard about are deadlocks – a deadlock in the database world usually refers to a situation where two or more transactions are in the process of waiting for each other to end, but neither ever does. What do you do in such a scenario and how do you tackle such a problem? Thankfully, you have dbWatch. 

----------------------------------------------------------------------------------------------------------

 1-Aug-24-2021-01-58-15-89-AM

 

Deadlocks in Database Instances – A Big Problem? 

As already noted above, deadlocks refer to a “blocking condition” where two or more locks are waiting for each other to finish, but neither ever does. Those “blocking conditions” are frequently referred to as “Coffman’s conditions” where Coffman’s conditions cover four conditions: 

Mutual exclusion –one process hogging up a resource. 
 
Hold and Wait – a process currently holding a resource and requesting more resources from other processes. 
 
No Pre-emption – if a process is holding a resource, the resource cannot be removed from the process. 
 
Circular wait – a situation where a process holds a resource that is held by another process. 

  1.  

SQL developers (and everybody else too) can see that on the left-hand side, we have your database instances, while on the right-hand side, we have our database jobs and their status. Statuses can be of three types – they can either return a status of “OK” meaning everything related to your specific database job is OK, a status of a “warning” meaning something related to a specific database job needs further attention to work correctly, or a status of “Alarm” indicating that something is very wrong and needs immediate fixing. 

For example, here’s how some of the database jobs look like for SQL Server-based instances: 

 

You could see that for SQL Server, jobs are split across multiple different categories – Availability, Capacity, Clustering and Replication, Maintenance, and, of course, Performance. However, what we’re interested in the most for the purposes of this blog post, is the database deadlocks job. Hover on it: 

 

Above the job you will see what it does – it’s pretty self-explanatory. The job raises the alarm or a warning when it detects a deadlock that can harm your SQL Server instances. The job, obviously, can be configured as well. In this case, “history threshold” defines the number of days before the history record will be removed from the history table, and return status refers to the status that needs to be returned when a deadlock is detected (dbWatch will always remind you of this, but it’s possible to set three values – 0, 1, or 2) – 2, in this case, would raise a “red flag” – an alarm: 

 

 

The importance of avoiding deadlocks should become more and more apparent as time passes – once businesses have more data (for example, more than a billion records when running a data breach search engine), deadlocks will become more harmful because they can waste more of your time and cause more issues to the customers of your business. You can, of course, hire SQL developers to solve these kinds of issues, however, if you’re using dbWatch, you don’t ever need to worry or hire anybody to do database-related things for you – once the “red flag” (an alarm) is raised, you would need to do some thorough inspection of your SQL Server instances to make sure one or more of the Coffman’s conditions is avoided at all costs. Once you do that, you should be good to go! 

We hope this blog post has provided you with some insight on how dbWatch works and how it can be used to tackle locking issues related to SQL Server. If you found it interesting, check out our older piece on monitoring and managing database locks, and If anything isn’t clear, please contact support for assistance, otherwise, we will see you in the next blog! 

 

 

-------------------

Other blogs:

MySQL Connection

Optimizing MySQL Engines with dbWatch

 

-------------------

About the Author:

Lukas Vileikis is an ethical hacker and a frequent conference speaker.

Since 2014, Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania.

He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.

 

Topics: database dashboard, database operations, sql server monitoring, sql monitoring tools, database monitoring, sqlmonitor, sqlperformance, sqlmanager, database farm, farmmanagement