Databases usually have a lot of operations happening in parallel throughout the life of an application after it has been deployed. These operations can be as simple as retrieval of information from the database, or can be operations that modify the state of the database by inserting new records, updating existing ones, or deleting others. Most of the databases that are currently being used in production by large organizations have been built with quite a lot of resilience, in terms of dealing with errors and crashes that can happen in the environment to disturb the normal functioning of a database. These methods prevent the corruption of data and downtime.

But this does not relieve the application developer completely of the fact that they still need to be careful about maintaining the consistency of the data inside the database. Let's try to understand this situation.

In an enterprise-grade application, there will be a number of database queries running in parallel at any given point in time. These queries arise from the use of applications from a number of users or internal application maintenance jobs. One major fact or in this is that, not all of the queries can be successfully executed. This could be due to several reasons, such as data in the query not conforming to the schema, an incorrect data type being provided for a column value, and violations of constraints. When this happens, the database engine just blocks the query from executing and returns an error for the query. This is absolutely fine as our incorrect query didn't make any incorrect changes to the database. But the situation gets tricky when this query is part of a larger set of operations creating a new resource in the database. Now we need to make sure that the changes made by other queries before the failed query are reverted.

This kind of behavior can still be fixed with some hard work by the developer of the application by tracking the SQL queries and reverting their changes manually when things go bananas in between.

But what if the database engine crashes due to an error in between, while one of the queries was executing. Now we are in a situation where we cannot predict the state of the database, and dealing with these kinds of situations can become really tiresome, and can be a task that halts the operations of the whole organization for a long time until database consistency is validated. So, what we can do? Is there some way through which we can prevent these types of issues from arising? The answer is a big yes. Let's take a look.