SQL Transactions - (Commit, Rollback, Savepoint)

There are 3 types of commands in SQL database , DDL commands, DML commands and TCL commands means transactions control language. While transactions in SQL are queries you run to perform a task. the commands of transactions control includes commit, rollback and savepoints.

What is Transaction in SQL:

A set of SQL queries which are use to perform a well defined tasks.or When we run multiple queries or bunch of queries in single run is called transactions in SQL.

For example you are working on library management system or maintaining a database for library. You are about to issue a book to the reader, the criteria would be perform.
  1. You have to reduce a copy of book from the table.
  2. The member table whom you issue the book must be updated.
  3. How many book have been issue to particular member.
  4. We have to update on which date, which member a book issued.
All above 4 will be performed separately via SQL query and become a transaction of queries.



Transaction Commands of SQL:


Commit:

Commit is used to save your SQL queries; commit works only with DML commands. Remember after executing commit command the 3 type of queries will be saved and cannot be undo.
  1. Updating a table row or column.
  2. Delete an entry from the table.
  3. Insert new records in the table.

Rollback:

Rollback is a facility used in database management systems for some sort of security. We can use ROLLBACK command to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction. In simple words, if we made some mistakes in the data while doing DML commands, we can rollback or undo the changes to make the data in its original form.  

Note: After commit anything we cannot rollback.

Example: If a record is inserted in database and you want to revert the changes, writing rollback will undo, it will be deleted you don't need to write delete commands.

SavePoint:

Before learning savepoints you need to know auto commit, it is a state of SQL where you write any query (insert, update, delete) by default SQL automatically commit and save it.

AutoCommit Command: it is turned on by default you don't need to write commit. Rollback will not work if auto commit is turned on.

How to set AutoCommit On and Off

Go to your SQL console and write set autocommit = 0 to turn it off. After that you need to write commit to save changes.

set autocommit = 0


Difference Between Commit and Rollback

In SQL commit is used for saving the changes made in database and roll back is to roll back them , changes will not get saved in the database

Properties of Transaction:

ACID property of transaction:

A = Atomicity:

Either the full transaction will performed or nothing will work. Means we have 4 queries either the 4 will be run or 0 will be run called atomicity.

C = Consistency:

Consistency prevents the data to duplicate or the data will not be ambiguous. Your data will not be inconsistent means nor a value got corrupt or being changed while transaction.

I = Isolation:

Isolation means alone, means if a query is working in your SQL database no other query could perform. It means only single query or transaction could work at a time. Other query will wait for query to complete.

D = Durability:

It stands for the durable query of the SQL means if you write a query to issue a book to customer. Durability means the code or query will work same after a year.

Post a Comment

0 Comments