Database_intro3 : Transaction
Transaction: is a process associated with the success (committed) or failure (rollback) of an operation. It starts with Begin and ends with either COMMIT or ROLLBACK.

Atomicity:  phrase stands for ensuring all operations were completed gracefully, else the transaction would be aborted, and a rollback command will restore the former status.

Consistency: Transaction must take the database from one consistent state to another. It is user’s responsibility to insure consistency

Isolation: Transaction should appear as though it is being executed in isolation from other transactions.
 

Durability: Changes applied to the database by a committed transaction must persist, even if the system fail before all changes reflected on disk
 
DML commands controlling transaction: SELECT, INSERT, UPDATE, FETCH, and COPY are DML commands

Commit: A commit marks the successful end of a transaction, saves all data during transactions.

SQL>UPDATE EMP SET SAL = SAL * 0.25 WHERE EMPID = 7789
SQL> COMMIT OR ROLLBACK

Rollback: A rollback marks the unsuccessful end of a transaction.

Savepoint :When you ROLLBACK to a SAVEPOINT, changes made since the SAVEPOINT are discarded, but not changes made before the SAVEPOINT.

Note eid 15 won't load

Set Transaction
The MVCC transaction model provides for much higher concurrency than most other models. Even though PostgreSQL uses multiple versions to isolate transactions, it is still necessary to lock data in some circumstances.

The script below will not compile, due to atomicity and consistency, even we try with different session, due to Primary Key constraints , the transaction (total) won't commit.
In a MVCC system, if you modify a row it would create a new copy of the row, and the changes won't be seen by other users until you commit your changes—they don't have to wait until you finish. Once you commit the changes, the old/original row would be marked as OBSOLETE.
  • READ COMMITTED ISOLATION: The changes will be visible to the transactions with READ COMMITTED isolation.
  • SERIALIZABLE ISOLATION : The transactions with SERIALIZABLE level , would see the original data, as the changes were hidden to this level of isolation. A command "VACCUM" removes the obsolete rows, in PostgreSQL database. In oracle there is no vacuum, operation, oracle update a row by overwriting the existing data 

 The PostgreSQL db, has storage manager (non-overwriting) , provides similar benefit of Oracle's REDO-log and ROLLBACK.
 

Ref: http://www.postgresql.org/docs/9.1/static/transaction-iso.html

The syntax of the VACUUM command is
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]