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](http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-transactions.html) * [PostgreSQL Documentation - Transactions Tutorial](http://www.postgresql.org/docs/9.4/static/tutorial-transactions.html) * [TutorialsPoint - PostgeSQL Transactions](http://www.tutorialspoint.com/postgresql/postgresql_transactions.htm) * [Oracle DB Concepts - Transactions](https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT016) * [Oracle Tutorials - JDBC Using Transactions](http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html)