SQL Transactions

A unit of work performed against a database is known as Transactions.For example, if you are creating a record, updating a record, or deleting a record from a table, you are doing transactions on the table.Controlling transactions is important to handle database errors and data integrity.

Some of the Properties of Transactions are as follows:-
Atomicity:-Atomicity is used to ensure that all operations within the work unit are completed successfully.
Isolation:-In isolation the intermediate status of one transaction is invisible to other transactions. Consequently, transactions that run concurrently appear sequentially.
Consistency:-The data is in a consistent state when the transaction starts and ends.
Durability:-In Durability it ensures that the effect of a committed transaction persists in case of a system failure.


Transaction Control

There are 3 commands that are used for transaction control :−
BEGIN TRANSACTION :-This is used to start the transaction.
COMMIT:-A commit statement is used to commit(save the changes) the current transaction.
ROLLBACK:-This command aborts the current transaction

BEGIN TRANSACTION:

We can use BEGIN or BEGIN TRANSACTION commands to start the transaction.The command usually persist until the next COMMIT or ROLLBACK command is encountered.Transaction will rollback if any error encountered.

BEGIN;
or
BEGIN TRANSACTION;

COMMIT:

A commit statement is used to perform the current transaction. All changes made by the transaction are visible to others and saves all transactions to the database when an accident occurs.

COMMIT;
or
END TRANSACTION;

ROLLBACK:

This command aborts the current transaction and undo all updates made by the transaction.Issuing Rollback when not inside a transaction does no harm, but it will provoke a warning message.

ROLLBACK;

TRANSACTIONS Example :

Consider the STUDENT table is having the following records

Now, let's start a transaction and delete the records from the table being age = 19 and finally we use the rollback command to undo all the changes.

DELETE FROM STUDENT WHERE AGE = 19;
ROLLBACK;

When we look at the student table it still has all the records because the rollback command undo all the transactions.

Now, let's start a another transaction and delete the records from the table being age = 19 and finally we use the commit command for all the changes.

DELETE FROM STUDENT WHERE AGE = 19;
COMMIT;

---

Visit :

---

Discussion