CODEWITHSUNDEEP
sqldatabasepostgresqlisolation-leveltransaction-isolation
Ulad Kasach
Ulad Kasach

Reputation: 12908

How to change transaction isolation level globally?

How can I change the default transaction isolation level for the database?

The postgres docs show how to change it per transaction and per session - but not how to alter the default for the database or cluster.

In MySQL the operation is

SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;

Is there an equivalent for PostgreSQL?

Upvotes: 4

Views: 11406

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

By default, read committed is set globally in PostgreSQL as shown below:

postgres=# SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read committed
(1 row)

So, to change it to read uncommitted globally, run the query below first:

            -- Database name
                  ↓
ALTER DATABASE postgres SET DEFAULT_TRANSACTION_ISOLATION TO 'read uncommitted';

Then, you need to log out and log in again otherwise read committed is not changed to read uncommitted globally:

postgres=# exit

psql -U postgres

Now, read committed is changed to read uncommitted globally as shown below:

postgres=# SHOW default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read uncommitted
(1 row)

In addition, read uncommitted has the same characteristics of read committed in PostgreSQL different from other databases so in short, read uncommitted and read committed are the same in PostgreSQL.

PostgreSQL's Read Uncommitted mode behaves like Read Committed.

Upvotes: 3

Laurenz Albe
Laurenz Albe

Reputation: 248215

Just set the default_transaction_isolation parameter appropriately, either in postgresql.conf or with ALTER SYSTEM. After reloading, this will apply to the whole cluster.

You can also use ALTER DATABASE or ALTER ROLE to change the setting for a database or user only.

Upvotes: 4

Ulad Kasach
Ulad Kasach

Reputation: 12908

Per the contents of this book indexed by google

One can change the whole default database isolation level to SERIALIZABLE as follows

ALTER DATABASE <DATABASE NAME> SET DEFAULT_TRANSACTION_ISOLATION TO SERIALIZABLE ;

So for read committed, for example, one can do:

ALTER DATABASE <db name> SET DEFAULT_TRANSACTION_ISOLATION TO 'read committed';

and you can confirm this with

SELECT current_setting('transaction_isolation')

Upvotes: 12

Related Questions