Transactions in SQL
Transactions in SQL, In terms of databases, a single logical operation on data is called a transaction. For example when we perform any CRUD operations like inserting some data into a table or deleting some data from the table. It is called transactions in SQL.
Let us understand it by an example.
Suppose you are asking your friend John is doing to send you some funds in your bank account. When he sends the funds, it should land in your account. Once your account is credited, it will be called a transaction. I hope you get the point.
Let’s talk about the reliability of the data.
This is very important to make sure the data in the database is reliable and does not contain any contamination. You might be thinking what do I mean when I say contamination. I will make it clear for you by an example. Support you are executing an insert operation on a table and suddenly your computer got powered off. You are not sure if your insert operation completed successfully or not. Another example you were running an update operation and the computer suddenly was rebooted.
To ensure data reliability, ACID principles come into the picture. This is the responsibility of the Database handler that provides all 4 principles that are applied to the database to keep the data reliable.
ACID Principles
ACID is a short form. The full form is mentioned below.
- Atomicity
- Consistency
- Isolation
- Durability.
Atomocity
Atomicity requires that all the transactions in SQL should be all or nothing. That means all the parts of a transaction should be successful or fail. If the entire transaction is successful then the data is saved/deleted/updated. If any part of the transaction fails then the whole transaction should be failed and the data should be unchanged. Or in other words, we can say the entire transaction should be rolled back.
Consistency
The consistency ensures the state of the database changes from one valid state to another valid state. When a transaction is successful.
Isolation
Isolation means, if there, are multiple transactions are happening in a database, the Isolation principle ensures that all the transactions happen in a series. All the transactions are isolated from one another.
Durability
The durability principle ensures that once a transaction is successful, the data should stay in the database until deleted or changed by another valid transaction. It should not be like, the transaction was successful and you shut down your computer, the next morning all the data is gone for no reason. Such blunders should not take place.
I hope you have understood the concept of transactions in SQL. In case of any confusion or feedback feel free to comment.
In the last article, we talked about User-defined Functions. You can go through that if you are interested.
Take Care Guys!
