Hello folks! Today we are again back with a super important article on the Most underrated SQL & Database Trick to save your entire application. Maintaining the data integrity of your application is very important in this world of software development. However, even experienced engineers may encounter situations where mistakes happen in changing or updating databases – like accidentally turning all your regular users into admins! In this article, we’ll talk about a neat trick that can save your whole project when you manually update data in your database. So let’s get started.
Imagine this situation
Consider a common scene where you need to make some changes to specific data in your database, such as altering a user’s role. An SQL query written without keeping in mind that an UPDATE query can change all the records in the database can turn out to be a disaster. This is where the importance of carefully performing data manipulation comes into play.
What is a Database Transaction?
Think of it like a bundle of actions you want to do in one go. The BEGIN TRANSACTION command is part of the SQL transaction control language (TCL) and is used to mark the beginning of a transaction. A transaction is a sequence of one or more SQL statements that are executed as a single unit of work.
Trick to save your SQL Database
Whenever you want to update your database manually, use and start with this command:
START TRANSACTION;
Or
BEGIN TRANSACTION;
Now, let’s say you perform a transaction and make a mistake. Mistakes can be small or big, if you are working on real-world applications then any mistake can be a blunder. Now, if you have used this command then you can easily roll back to the state where you started your transaction.
This trick can be a lifesaver because if something goes wrong, you can roll back all your changes – like hitting an undo button. This mechanism becomes invaluable when you need to roll back changes that were done mistakenly, bringing your database back to its initial state.
Let’s Understand with an Example
1. Starting a transaction:
At first, whenever you are doing something important whether it is updation or deletion in a database do let the database know about it. This can be done using a simple command as shown below. It’s like telling the database to pay attention because you’re about to make changes:
BEGIN TRANSACTION;
2. Make required changes:
Now, you can go ahead and make the changes that you want to make in the database. For example, let’s say you want to turn a user into an admin:
UPDATE users SET role = 'admin' WHERE user_id = 1;
We used UPDATE and then used a condition to make changes only for that user whose user_id is equal to 1.
3. Verify and Commit:
Once you’re done with the changes in the database, the final command that we hit to let you complete the transaction is shown below:
COMMIT;
4. Rollback in case of error:
Now that you have made the changes in the database suddenly you remember that you have made a mistake and need to rectify it. Suppose you change the roles of all the users in the database to Admin. Now here comes the advantage of using Transaction.
ROLLBACK;
In the event of an error or unintended consequences, the ability to roll back changes is a lifesaver. Rolling back the transaction undoes the changes, ensuring a quick recovery to the pre-modification state.
Other Examples
To illustrate the practical application of this technique, let’s take a deep dive into real-world scenarios where transactions are used much. From handling bulk updates to managing complex data manipulations, the concept of transactions offers a reliable way to prevent irreversible changes and ensure data integrity.
0 Comments: