|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.
phrase stands for ensuring all operations were completed gracefully,
else the transaction would be aborted, and a rollback command will
restore the former status.
Transaction must take the database from one consistent state to
another. It is user’s responsibility to insure consistency
Transaction should appear as though it is being executed in
isolation from other transactions.
Changes applied to the database by a committed transaction must
persist, even if the system fail before all changes reflected on
|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
Note eid 15 won't load
|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
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
The PostgreSQL db, has storage manager (non-overwriting) ,
provides similar benefit of Oracle's REDO-log and ROLLBACK.
|The syntax of the VACUUM command is
VACUUM [ VERBOSE ] [ ANALYZE ] [ table ]