A Transaction is an ordered multiple SQL statements which are performed on database as a single unit. If we DELETE a row or UPDATE a row or INSERT a row in database by combining all those SQL statements and execute them together as a transaction. If the SQL statements are executed successfully the changes on table are committed or if any one statement was not executed all statement changes are ROLLBACK that is the changes are undone. Transaction have the ACID properties are explained as follows –

• Atomic - A transaction is a logical unit of work which is completed when all the SQL statements are executes successfully by allowing new changes otherwise it ROLLBACK to previous values in the table if any one of the statement failed.
• Consistent - In the beginning and ending of transaction, Database is in the consistent state.
• Isolated - Modifications of data performed by a transaction must be independent of another transaction.
• Durable - Once changes happens in database it must be Permanent.

Points to remember while using Transactions are as follows –

• Every Transaction must start with BEGIN TRANSACTION.
• Every Transaction end with either COMMIT or ROLLBACK.

Commands which are used by Transactions are as follows –

• COMMIT – It is used to save changes in table permanently.
• ROLLBACK – It is used to undo the changes in table.
• SAVEPOINT – It is used to create save point on used transactions. we can rollback changes on savepoint.
• SET TRANSACTION – It is used to define the characteristics of the transaction.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Getting rows by using COMMIT command.

Requirement – Getting rows from employee_details table by using COMMIT command. The query was as follows –

BEGIN TRANSACTION
	DELETE FROM employee_details WHERE emp_id = 001;
	UPDATE employee_details SET salary = 35000 WHERE emp_id = 002;
COMMIT;  

By executing above query we can get output like as shown in below –

Scenario – Getting rows from table by AUTO ROLLBACK.

Requirement - Getting rows from employee_details table by AUTO ROLLBACK. The query was as follows –

BEGIN TRANSACTION
  INSERT INTO employee_details(emp_id,emp_name,designation,manager_id,
  date_of_hire,salary,dept_id) VALUES (001,'Employee1','Director',
  '2019-07-11',45000.00,1000); 
  UPDATE employee_details SET salary = 35000 WHERE emp_id = 002;
COMMIT; 

By executing above query we can get output like as shown in below –

Scenario – Getting rows by using ROLLBACK command.

Requirement – Getting rows from employee_details table by using ROLLBACK command. The query was as follows –

BEGIN TRANSACTION
	DELETE FROM employee_details WHERE emp_id = 002;
ROLLBACK; 

By executing above query we can get output like as shown in below –

Scenario – Getting rows by using SAVEPOINT command.

Requirement – Getting rows from employee_details table by using SAVEPOINT command. The query was as follows –

BEGIN TRANSACTION
DELETE FROM employee_details WHERE emp_id = 002;
SAVEPOINT A;
DELETE FROM employee_details WHERE emp_id = 006; 
SAVEPOINT B; 
SELECT * FROM employee_details; 
END;

By executing above query we can get output like as shown in below –

ROLLBACK A;
SELECT * FROM employee_details; 

By executing above query we can get output like as shown in below –

Scenario – Deleting existing save point on current transaction by using RELEASE command.

Requirement - Deleting existing save point B on current transaction by using RELEASE command. The query was as follows -

RELEASE SAVEPOINT  B;

By executing above query we can delete existing save point B.

Scenario - Defining characteristics of transaction by using SET TRANSACTION command.

Requirement - Defining characteristics of employee transaction by using SET TRANSACTION command.

SET   TRANSACTION employee  READ  WRITE;

By executing above query we can define characteristics of employee transaction by using SET TRANSACTION as READ WRITE.