3

I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore.

In fact, I would like to force the isolation level of my database to SERIALIZABLE DEFERRABLE. Is this possible at all? If yes, how?

6
  • 1
    I have never heard of such isolation level. Read about Postgres isolation levels. The strictest (highest) level is the Serializable. Commented Mar 28, 2014 at 22:28
  • 4
    This question appears to be off-topic because it is not possible
    – Philᵀᴹ
    Commented Mar 28, 2014 at 23:22
  • 2
    @GrumpyPhil That's not accurate, you can. You probably shouldn't, though. Commented Mar 29, 2014 at 2:18
  • 1
    @CraigRinger Why not? Serious question.
    – user32234
    Commented Jul 3, 2014 at 23:52
  • 1
    @Gracchus Some apps may assume the default of READ COMMITTED, so they might not cope with SERIALIZABLE tx aborts. Bad coding, but not uncommon. Commented Jul 4, 2014 at 2:25

1 Answer 1

4

You can set SERIALIZABLE as the default isolation level in postgresql.conf:

default_transaction_isolation = 'serializable'

This may confuse clients that assume READ COMMITTED isolation, so IMO it's better to set it explicitly in your sessions.

As for the rest, per the manual:

The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY

... and given the properties of such transactions, I find it hard to imagine you wanting to make it the default. Are you perhaps confusing it with SET CONSTRAINTS DEFERRED?

You can make transactions read-only and deferrable by default with:

transaction_read_only = on
transaction_deferrable = on

in postgresql.conf but it's really a rather bad idea. Instead, just use an appropriate BEGIN, or SET TRANSACTION in your session before your first "real" SQL statement.

3
  • 1
    Ok, thanks for your feedback. I was interested in DEFERRABLE to avoid serialization failures, but may be I am overshooting here. Commented Mar 29, 2014 at 6:44
  • 1
    Setting the default isolation level to serializable implies discipline and extra work but it pays off (read the end of postgresql.org/docs/current/transaction-iso.html). If the question is slightly modified to the properties of a specific transaction, it may make more sense: The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY. From postgresql.org/docs/15/sql-set-transaction.html Commented Feb 23 at 16:55
  • 1
    @PabloAdames In general, it's a good idea to induce random failures in your app's SQL, whether using SERIALIZABLE or not. Adding random failures during commit processing is usually particularly enlightening. But there are corner cases where logic will be correct for READ COMMITTED that would be incorrect in SERIALIZABLE isolation; some careful analysis is usually needed before changing it. Commented Mar 9 at 6:46

Not the answer you're looking for? Browse other questions tagged or ask your own question.