Maintaining a database involves a lot of complex challenges and complexities, with both blocking and deadlocks sitting high on the priority list for experienced DBAs.
While they sound somewhat similar, these two facets of how SQL Server operates are actually distinct from one another and it is necessary to gain an understanding of these differences if you want to keep the database running smoothly.
Here is a look at what deadlocks and blocks mean in this context and what problems they can present from a database maintenance perspective.
Brief intro to Blocking
If you are looking for a thorough rundown on the ins and outs of SQL blocking, this article tells you all you need to know. A quick explanation is that a database has a finite number of resources at its disposal, and blocking arises when two or more processes need to access a given resource at the same time.
Each process can apply a ‘lock’ to a resource, which will block other processes from barging in while it is using it. Because of this, blocking can be seen as a fundamental part of how databases function and a means by which they are able to ensure that data integrity is unshakably maintained.
Explanation of Deadlocks
Like blocking, database deadlocks can also be seen as a core asset of database operations, even if they are a little more problematic.
Deadlocks arise when two processes have exclusive locks on resources and are unable to switch places because of this conflict.
Deadlocks are once again about maintaining data integrity, but in this case the software will pick one of the offending processes and terminate it, because without doing this the deadlock would persist indefinitely.
It is worth pointing out that while deadlocks may be suboptimal, they are not always a cause for concern, especially if they only occur sporadically. For example, they may crop up more frequently when a server is being burdened with an unusual level of activity in a short span of time, since in this context more processes will be vying for access to resources than would normally be expected.
Of course if a particular deadlock between two or more processes keeps cropping up, it may need to be investigated more thoroughly and dealt with if necessary.
Troubleshooting Options
As you may have gathered by this point, both blocking and deadlocks are not out of the ordinary to encounter on an SQL database. Indeed without them, pandemonium would reign and bigger problems would arise.
Even so, you should still be on the lookout for these issues, using server maintenance software to automatically flag any conflicts. If blocks are held for too long or if deadlocks occur too frequently, server performance could suffer and certain processes may fail more regularly than is helpful.
There are a whole host of other variables to take into account, such as the lock modes that are used by processes to hold onto resources, so further reading and experimentation are necessary. With experience, you should be perfectly positioned to keep your database running like clockwork, wrangling everything from resource problems to security deftly.
