postgres mvcc visibility check mechanism - ghdrako/doc_snipets GitHub Wiki
MVCC is implemented by creating multiple versions of individual transaction data with two virtual columns, xmin and xmax. xmin has the identity (transaction ID) of the inserting transaction for this row version and xmax has the identity (transaction ID) of the deleting transaction, or zero for an undeleted row version [1]. With these virtual columns, PostgreSQL tracks changes that occurred during insert, updates, and deletes inside the transaction block for commit as well as for rollback and to provide a consistent view of data for select statements used for long- running reports. The WAL files are used for rollback and recovery, which roll backs aborted transactions and replays the committed transactions. The main advantage of MVCC concurrency control is that its readers don’t block writers and vice versa where user action is not required and is managed by PostgreSQL Server.
The default isolation level is READ COMMITTED.
For the alter system command, the scope is for the entire database, and with the set transaction command, the scope is for individual sessions. Here are examples:
postgres=# ALTER SYSTEM SET default_transaction_isolation TO 'REPEATABLE READ';
postgres=# ALTER SYSTEM SET default_transaction_isolation TO 'SERIALIZABLE';
To set isolation level for the session or for individual transactions, you must use the command inside a translation block of begin and end. Here is an example:
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
– Run transations
END;
If you do not set inside the block, you will receive this error:
postgres=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
WARNING: SET TRANSACTION can only be used in transaction blocks
The default transaction level is displayed with the show command:
postgres=# SHOW default_transaction_isolation;
default_transaction_isolation
-------------------------------
read committed
With Postgres catalog views, pg_locks, pg_blocking_pids(),
pg_blocked_pid(), pgrowlocks, and pg_stat_activity, you can identify
the row level, table level, page level, and block level lock.
You have to determine the age of the long-running transactions and
advise the application team to disconnect or terminate long-running
queries to avoid “oldest x min is far in the past.” You can determine the
long- running query by subtracting the current time with xact_start in the
view pg_stat_activity.
Long-Running Status Check
SELECT substr(query,1,40),pid,backend_start,xact_start,now()-
xact_start seconds from pg_stat_activity where xact_start is
not null;