Transactions Control in SQL & PostgreSQL

Transactions control is the most important thing for the Data Integrity.
Transaction record is one or more related changes to the database that are made at once.

Properties of Transactions

• Atomicity:
  ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  
  
• Consistency:
  ensures that the database properly changes states upon a successfully committed transaction.
  
  
• Isolation:
  enables transactions to operate independently of and transparent to each other.
  
  
• Durability:
  ensures that the result or effect of a committed transaction persists in case of a system failure.
  
  

---

SQL Transaction Control

• COMMIT: to save the changes invoked by transaction to database.

• ROLLBACK: to rollback either all transaction changes or to rollback to some particular savepoint.

• SAVEPOINT: creates points within groups of transactions in which to ROLLBACK.

To remove created savepoint RELEASE SAVEPOINT command is used.
Once SAVEPOINT has been released u can no longer ROLLBACK to this SAVEPOINT.

• SET TRANSACTION: places a name on transaction.

Transactional control commands are only used with the DML commands INSERT, UPDATE and DELETE only.
They can not be used while creating tables or dropping them, because these operations are automatically committed in the database.

By default, embedded SQL programs are not in autocommit mode, so COMMIT needs to be issued explicitly when desired.

However, if your session in autocommit mode, where each individual statement is committed implicitly, turn it OFF via using SET AUTOCOMMIT command.

Whatever it's always better to explicitly open & use transaction in such cases:

BEGIN;
  INSERT ...;
  UPDATE ...;
COMMIT; -- when you are done

---

PostgreSQL Transaction Control

• BEGIN TRANSACTION or simply BEGIN: to start transaction.

I prefer to use simply BEGIN cause it's also suitable for MySQL unlike BEGIN TRANSACTION command.

• SAVEPOINT: create savepoint to be able to rollback to it.

• RELEASE SAVEPOINT: destroy a previously defined savepoint
  without discarding the effects of commands executed after it was established.

• ROLLBACK or ROLLBACK TO SAVEPOINT: to rollback the all transaction changes
  or to rollback to the specific savepoint.

• COMMIT or END TRANSACTION command: to save the changes,
  invoked by transaction to database since last COMMIT or ROLLBACK.

Transaction example with establishing and later destroying a savepoint:

BEGIN;
    INSERT INTO table1 VALUES (3);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (4);
    RELEASE SAVEPOINT my_savepoint;
COMMIT;

Transaction example with establishing and rollback to savepoint
to undo the effects of all commands executed after it was established:

BEGIN;
    INSERT INTO table1 VALUES (1);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (2); 
    ROLLBACK TO SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3);  
    -- value 2 won't be inserted because of rollback. 
COMMIT;

In PostgreSQL opened transaction will also ROLLBACK if the database is closed or if an error occurs.

---

see Also

• MySQL Transactional & Locking Statements
• PostgreSQL Documentation - Transactions Tutorial
• TutorialsPoint - PostgeSQL Transactions
• Oracle DB Concepts - Transactions
• Oracle Tutorials - JDBC Using Transactions