State of the Data Before COMMIT or ROLLBACK

• The previous state of the data can be recovered.
• The current user can review the results of the DML operations by using the SELECT statement.
• Other users cannot view the results of the DML statements by the current user.
• The affected rows are locked; other users cannotchange the data in the affected rows.

State of the Data After COMMIT :

• Data changes are made permanent in the database.
• The previous state of the data is permanently lost.
• All users can view the results.
• Locks on the affected rows are released; those rows are available for other users to
  manipulate.
• All savepoints are erased.

Committing Data :

Make the changes :

• BEGIN
  
• DELETE FROM emp WHERE empno = 7090;
• INSERT INTO dept VALUES (290, 'Corporate Tax', NULL);

Commit the changes :

• COMMIT;

State of the Data After ROLLBACK :
Discard all pending changes by using the ROLLBACK statement :

• Data changes are undone.
• Previous state of the data is restored.
• Locks on the affected rows are released.

• BEGIN;
• DELETE FROM copy_emp;
• DELETE 20
• ROLLBACK ;
• Rollback

Implicit Transaction Processing

Controlling Transactions :



An automatic commit occurs under the following circumstances :

• DML is issued
• DDL statement is issued
• DCL statement is issued
• Normal exit from psql, without explicitly issuing COMMIT or ROLLBACK statements
• An automatic rollback occurs under an abnormal termination of psql or a system failure.

System Failures :

• When a transaction is interrupted by a system failure, the entire transaction is automatically rolled back. This prevents the error from causing unwanted changes to the data and returns the tables to their state at the time of the last commit. In this way, the PostgreSQL server protects the integrity of the tables.