In database transaction is an atomic unit of work that will consist of one or more related SQL statements. This is so called because the databases are modified are brought by the SQL statements, since this constitutes a transaction can collectively that to be either committed, that can be made permanent to that of the database or rolled back from the database. When the SQL statement is executed successfully and the committed transactions are not same. Transactions can be rolled back and the changes made by the statements can be undone, when SQL statements are executed successfully, unless the transactions are said to be committed. 

Starting and Ending a Transaction

All the transactions are said to be having a beginning and an end. Transactions begins when one of the following events take place:

1. The first SQL statement is said to be performed after connecting to the database.
2. At each and every new SQL statement are issued after the transactions are completed.

Transactions ends when one of the following events take place:

1. When commit or rollback statements are issued.
2. DDL statement, like CREATE TABLE statements are issued, since in this case COMMIT is automatically performed.
3. DCL statement, like GRANT statement are issued, since in this case the COMMIT is automatically performed.
4. When user gets disconnected from the database.
5. When user exits from the SQL*PLUS by issuing the EXIT command and then the COMMIT is automatically performed.
6. When SQL*Plus terminates abnormally, a ROLLBACK is automatically performed.
7. When a DML statement fails, an in this case ROLLBACK is automatically performing the undoing that DML statement.

Committing a Transaction

Transactions can be made permanent when we issuing the SQL command COMMIT.

Syntax:
COMMIT;
Example:
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'ranjitha', 34, 'bangalore', 2000.00 ); 
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'sai', 24, 'hyderabad', 3000.00 ); 
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'shakthi', 32, 'pune', 4500.00 ); 
COMMIT;

Rolling Back Transactions

Changes can be made to the database without COMMIT which can be undone by using the ROLLBACK command.

Syntax:

ROLLBACK [TO SAVEPOINT < savepoint_name>];

 When the transactions are aborted because of some unprecedent situation may be like system failure, and then the entire transaction since the commit is automatically rolled back. In case we are not using the save point, we can use ROLLBACK statement.

ROLLBACK;

Savepoints

these are said to be the sort of makers that helps in splitting a long transaction into the smaller units by setting some of the checkpoints. When we set this savepoints within a long transaction, we can also rollback to the checkpoint if required. 

Syntax:
SAVEPOINT < savepoint_name>;
For Example:
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (1, 'ranjitha', 34, 'bangalore', 2000.00 ); 
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (2, 'sai', 24, 'hyderabad', 3000.00 ); 
INSERT INTO teachers (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (3, 'shakthi', 32, 'pune', 4500.00 ); 
SAVEPOINT sav1;
UPDATE TEACHERS
SET SALARY = SALARY +1000;
ROLLBACK TO sav1;
UPDATE TEACHERS
SET SALARY = SALARY +1000
WHERE ID =2;
UPDATE TEACHERS
SET SALARY = SALARY +1000
WHERE ID =3;
COMMIT;

The statement to ROLLBACK TO sav1 rolls back to all the changes up to the point, where we have marked savepoint sav1. And then after this all the new changes that we will make will start.

Automatic Transaction Control

For executing COMMIT automatically whenever the INSERT, UPDATE, or DELLETE commands are executed, we can set this AUTOCOMMIT environment variable.

SET AUTOCOMMIT ON;

SET AUTOCOMMIT OFF;