Atomicity − This property states that a transaction must be treated as an atomic unit, that is, either all of its operations are executed or none.

There is no midway i.e. transactions do not occur partially. Each transaction is considered as one unit and either runs to completion or is not executed at all.

It involves the following two operations:

—Abort: If a transaction aborts, changes made to database are not visible.
—Commit: If a transaction commits, changes made are visible.

Consistency The database must remain in a consistent state after any transaction. If the database was in a consistent state before the execution of a transaction, it must remain consistent after the execution of the transaction as well.

Isolation
This property ensures that multiple transactions can occur concurrently without leading to inconsistency of database state.

Durability
This property ensures that once the transaction has completed execution, the updates and modifications to the database are stored in and written to disk and they persist even if a system failure occurs.

PostgreSQL Isolation Levels

Read Phenomena:

dirty read

A transaction reads data written by a concurrent uncommitted transaction.

nonrepeatable read

A transaction re-reads data it has previously read and finds that data has been modified by another transaction (that committed since the initial read).

phantom read

A transaction re-executes a query returning a set of rows that satisfy a search condition and finds that the set of rows satisfying the condition has changed due to another recently-committed transaction.

In summary,

Dirty reads: read UNCOMMITED data from another transaction’

Non-repeatable reads: read COMMITTED data from an UPDATE query from another transaction

Phantom reads: read COMMITTED data from an INSERT or DELETE query from another transaction

Isolation Levels in PostgreSQL

Read uncommitted

This is the lowest isolation level. In this level, dirty reads are allowed, so one transaction may see not-yet-committed changes made by other transactions.

Read Committed

This is the default isolation level in PostgreSQL.

When a transaction uses this isolation level, a SELECT query (without a FOR UPDATE/SHARE clause) sees only data committed before the query began; it never sees either uncommitted data or changes committed during query execution by concurrent transactions.

In this isolation level, a lock-based concurrency control DBMS implementation keeps write locks (acquired on selected data) until the end of the transaction, but read locks are released as soon as the SELECT operation is performed (so the non-repeatable reads phenomenon can occur in this isolation level).

Repeatable reads

In this isolation level, a lock-based concurrency control DBMS implementation keeps read and write locks (acquired on selected data) until the end of the transaction. However, range-locks are not managed, so phantom reads can occur.

Violation in SERIALIZABLE may lead to below error.

ERROR:  could not serialize access due to concurrent update

Serializable

This is the highest isolation level.

With a lock-based concurrency control DBMS implementation, serializability requires read and write locks (acquired on selected data) to be released at the end of the transaction. Also range-locks must be acquired when a SELECT query uses a ranged WHERE clause, especially to avoid the phantom reads phenomenon.

Example.,
BEGIN TRANSACTION;
SELECT * FROM T;
WAIT FOR DELAY ’00:01:00′;
SELECT * FROM T;
COMMIT;

That is a simple task that issue two reads from table T, with a delay of 1 minute between them.

Under READ COMITTED, the second SELECT may return any data. A concurrent transaction may update the record, delete it, and insert new records. The second select will always see the new data.

Under REPEATABLE READ the second SELECT is guaranteed to see the rows that has seen at first select unchanged. New rows may be added by a concurrent transaction in that one minute, but the existing rows cannot be deleted nor changed.

Under SERIALIZABLE reads the second select is guaranteed to see exactly the same rows as the first. No row can change, nor deleted, nor new rows could be inserted by a concurrent transaction.

Violation in SERIALIZABLE may lead to below error

ERROR:  could not serialize access due to read/write dependencies among transactions

How it works?

To set the transaction isolation level of a transaction, use the command SET TRANSACTION.

Syntax.

SET TRANSACTION transaction_mode [, …]
SET TRANSACTION SNAPSHOT snapshot_id
SET SESSION CHARACTERISTICS AS TRANSACTION transaction_mode [, …]
Where transaction_mode is one of:
ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY
[ NOT ] DEFERRABLE

Example, REPEATABLE READ

a repeatable read transaction cannot modify or lock rows changed by other transactions after the repeatable read transaction began.

Example.,  SERIALIZABLE

Let us create a table and insert some records into it.

postgres=# create table myt(class bigint, value bigint);
CREATE TABLE
postgres=# insert into myt values(‘1′,’10’);
INSERT 0 1
postgres=# insert into myt values(‘1′,’20’);
INSERT 0 1
postgres=# insert into myt values(‘2′,’100’);
INSERT 0 1
postgres=# insert into myt values(‘2′,’200’);
INSERT 0 1

Now both transactions try to commit. If either transaction were running at the Repeatable Read isolation level, both would be allowed to commit; but since there is no serial order of execution consistent with the result, using Serializable transactions will allow one transaction to commit and will roll the other back with this message:

could not serialize access due to read/write dependencies among transactions

This is because if A had executed before B, B would have computed the sum 330, not 300, and similarly, the other order would have resulted in a different sum computed by A.