(SQL tutorials for Business Analyst)
In this end-to-end example, you will learn – SQL Tutorials for Business Analyst: SQL | TRANSACTIONS.
SQL | TRANSACTIONS
What are Transactions?
Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.
Incomplete steps result in the failure of the transaction. A database transaction, by definition, must be atomic, consistent, isolated and durable. These are popularly known as ACID properties.
How to implement Transactions using SQL?
Following commands are used to control transactions. It is important to note that these statements cannot be used while creating tables and are only used with the DML Commands such as – INSERT, UPDATE and DELETE.
-
- SET TRANSACTION: Places a name on a transaction.
Syntax:SET TRANSACTION [ READ WRITE | READ ONLY ];
- COMMIT: If everything is in order with all statements within a single transaction, all changes are recorded together in the database is called committed. The COMMIT command saves all the transactions to the database since the last COMMIT or ROLLBACK command.
Syntax:COMMIT;
Example: Sample table 1
Following is an example which would delete those records from the table which have age = 20 and then COMMIT the changes in the database.
Queries:DELETE FROM Student WHERE AGE = 20; COMMIT;
Output:
Thus, two rows from the table would be deleted and the SELECT statement would look like, - ROLLBACK: If any error occurs with any of the SQL grouped statements, all changes need to be aborted. The process of reversing changes is called rollback. This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
Syntax:ROLLBACK;
Example:
From the above example Sample table1,
Delete those records from the table which have age = 20 and then ROLLBACK the changes in the database.
Queries:DELETE FROM Student WHERE AGE = 20; ROLLBACK;
Output:
- SAVEPOINT: creates points within the groups of transactions in which to ROLLBACK.
A SAVEPOINT is a point in a transaction in which you can roll the transaction back to a certain point without rolling back the entire transaction.
Syntax for Savepoint command:SAVEPOINT SAVEPOINT_NAME;
This command is used only in the creation of SAVEPOINT among all the transactions.
In general ROLLBACK is used to undo a group of transactions.
Syntax for rolling back to Savepoint command:ROLLBACK TO SAVEPOINT_NAME;
you can ROLLBACK to any SAVEPOINT at any time to return the appropriate data to its original state.
Example:
From the above example Sample table1,
Delete those records from the table which have age = 20 and then ROLLBACK the changes in the database by keeping Savepoints.
Queries:SAVEPOINT SP1; //Savepoint created. DELETE FROM Student WHERE AGE = 20; //deleted SAVEPOINT SP2; //Savepoint created.
Here SP1 is first SAVEPOINT created before deletion.In this example one deletion have taken place.
After deletion again SAVEPOINT SP2 is created.
Output:Deletion have been taken place, let us assume that you have changed your mind and decided to ROLLBACK to the SAVEPOINT that you identified as SP1 which is before deletion.
deletion is undone by this statement ,ROLLBACK TO SP1; //Rollback completed.
- SET TRANSACTION: Places a name on a transaction.
Notice that first deletion took place even though you rolled back to SP1 which is first SAVEPOINT.
- RELEASE SAVEPOINT:- This command is used to remove a SAVEPOINT that you have created.
Syntax:RELEASE SAVEPOINT SAVEPOINT_NAME
Once a SAVEPOINT has been released, you can no longer use the ROLLBACK command to undo transactions performed since the last SAVEPOINT.
Disclaimer: The information and code presented within this recipe/tutorial is only for educational and coaching purposes for beginners and developers. Anyone can practice and apply the recipe/tutorial presented here, but the reader is taking full responsibility for his/her actions. The author (content curator) of this recipe (code / program) has made every effort to ensure the accuracy of the information was correct at time of publication. The author (content curator) does not assume and hereby disclaims any liability to any party for any loss, damage, or disruption caused by errors or omissions, whether such errors or omissions result from accident, negligence, or any other cause. The information presented here could also be found in public knowledge domains.
Learn by Coding: v-Tutorials on Applied Machine Learning and Data Science for Beginners
Latest end-to-end Learn by Coding Projects (Jupyter Notebooks) in Python and R:
All Notebooks in One Bundle: Data Science Recipes and Examples in Python & R.
End-to-End Python Machine Learning Recipes & Examples.
End-to-End R Machine Learning Recipes & Examples.
Applied Statistics with R for Beginners and Business Professionals
Data Science and Machine Learning Projects in Python: Tabular Data Analytics
Data Science and Machine Learning Projects in R: Tabular Data Analytics
Python Machine Learning & Data Science Recipes: Learn by Coding
R Machine Learning & Data Science Recipes: Learn by Coding
Comparing Different Machine Learning Algorithms in Python for Classification (FREE)
There are 2000+ End-to-End Python & R Notebooks are available to build Professional Portfolio as a Data Scientist and/or Machine Learning Specialist. All Notebooks are only $29.95. We would like to request you to have a look at the website for FREE the end-to-end notebooks, and then decide whether you would like to purchase or not.
MySQL Tutorials for Business Analyst: How to use Alter, Drop and Rename function in MySQL