PostgreSQL’s default isolation level is read commit (RC). PostgreSQL implements only three different isolation levels internally. If the isolation level is set to “read uncommitted,” PostgreSQL does not implement read uncommitted.

Let’s see how to view and set the PostgreSQL isolation level.

View the PostgreSQL isolation level

Method 1: View the default isolation level

To view the default isolation level: show default_transaction_isolation;

postgres=# show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1Rows)Copy the code

Method 2: View the default isolation level

You can view the default isolation level from the system configuration table pg_Settings for PG

postgres=# SELECT name, setting FROM pg_settings WHERE name = 'default_transaction_isolation';
             name              |    setting
-------------------------------+----------------
 default_transaction_isolation | read committed
(1Rows)Copy the code

View the isolation level of the current session

View the isolation level of the current session (connection) :

  • show transaction_isolation;
  • orSELECT current_setting('transaction_isolation');
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1Rows)Copy the code

Configure the PostgreSQL isolation level

Set the isolation level of the current transaction (can only be changed within a transaction block)

In PostgreSQL, SET TRANSACTION can be used to change the isolation level of a TRANSACTION. But it can only be executed in transaction blocks.

SET TRANSACTION ISOLATION LEVEL [ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED ];
Copy the code

Change the isolation level of PgSQL as follows:

postgres=# SETTRANSACTION ISOLATION LEVEL REPEATABLE READ; Warning:SETTRANSACTION can only be used within a TRANSACTION blockSET
postgres=# begin transaction;
BEGIN
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 read committed
(1Line record) postgres=# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1Rows)Copy the code

Starts the transaction with the specified isolation level

You can specify the isolation level directly at the start of a transaction:

postgres=# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN
postgres=# show transaction_isolation;
 transaction_isolation
-----------------------
 repeatable read
(1Rows)Copy the code

Sets the transaction isolation level for the current session

The syntax for setting the isolation level for the current session is a bit more complex. The syntax is as follows:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL [ SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED ]
Copy the code

The SQL statement permanently changes the global transaction isolation level

It is also possible to permanently change the global transaction isolation level on the command line, or by using an SQL statement:ALTER SYSTEM SET default_transaction_isolation TO 'xxx'

After the modification, run the SELECT pg_reload_conf() command. The SQL statement reloads the PostgreSQL configuration for it to take effect. There is no need to restart the PG service.

postgres=# ALTER SYSTEM SET default_transaction_isolation TO 'REPEATABLE READ';
ALTER SYSTEM
postgres=# SELECT pg_reload_conf();
 pg_reload_conf
----------------
 t
(1Line record) postgres=# show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 repeatable read
(1Line record) postgres=# SELECT current_setting('transaction_isolation');
 current_setting
-----------------
 repeatable read
(1Rows)Copy the code

The PostgreSQL configuration is permanent and will remain valid even after a PostgreSQL restart. The exact location of the change is not known because the postgresql.conf configuration file has not been changed. ALTER SYSTEM SET does not affect the configuration file.

The PG profile permanently changes the isolation level of a transaction

The PostgreSQL configuration file postgresql.conf contains a configuration about the isolation level. After the configuration is modified, the postgresql.conf file is reloaded to take effect. This permanently changes the global isolation level.

As follows:

#default_transaction_isolation = 'read committed'
Copy the code