The question of blocking comes up frequently when discussing how SQL databases function, programming, and how to troubleshoot issues with a view to optimizing performance.
If you are worried about blocking on your own server, or you are interested in understanding how significant it is in the context of orchestrating concurrent requests, this brief dive into the topic should set you on the right track.
The benefits of blocking
First and foremost, it is worth emphasizing that blocking is generally a good thing, and in practice, is essential to ensuring that the database is able to process requests concurrently without the integrity of any of the information it contains being compromised.
Blocking comes about when a process has a lock on a resource that stops other processes from gaining simultaneous access. This central design feature of SQL databases means that blocking is not only commonplace but also an important positive.
The considerations when composing requests
Just because blocking is important does not mean that it should be either ignored when programming requests or overlooked when considering database maintenance further down the line.
At a certain point, blocking of a problematic nature can arise, leading to longer wait times and potentially even manifesting as full-on deadlocking, which is another aspect worth investigating if you are a developer or DBA.
If blocking occurs frequently and hampers database and site performance, then intervention will be necessary, and making optimizations to the requests that are at fault is sensible.
You can also prevent such a scenario by making sure that requests are written optimally in the first place, which relies on a similar skill set in addition to the accumulation of experience built up over time.
One of the main ways to keep blocking to a minimum is to make sure that queries are not written in a way that grants them exclusive locks over resources in an excessive or aggressive manner. Such queries will both take longer to complete and also create a chain reaction of increased wait times by blocking other processes from reading or altering data.
Another source of blocking may be a transaction that completes successfully but does not release the locks it holds on resources, monopolizing them unnecessarily.
The relevance of monitoring
Regardless of how well you think you have written the concurrent requests for your database, it is likely that in an ecosystem this complex and multifaceted, problems will crop up over time and need to be dealt with.
Blocking, which compromises the smooth running of your server, should be sought out proactively using the various monitoring tools that are at your disposal. This is not just an efficient way of detecting that there are problems in the first place but also a convenient means of pinpointing the precise queries and processes which are at fault, saving you from the hassle of doing this manually.
The best approach is to embrace blocking when it is contributing to the integrity of your database while remembering to write queries optimally and to intervene if complications arise.