Thread: transaction_isolation vs. default_transaction_isolation
I found a discussion with the same title as this emails’s subject here: https://postgrespro.com/list/thread-id/1741835 It dates from 2009. But it seems to be unresolved. The current PG doc here: 20.11. Client Connection Defaults https://www.postgresql.org/docs/15/runtime-config-client.html has an entry for each setting thus: « default_transaction_isolation (enum): Each SQL transaction has an isolation level, which can be either “read uncommitted”,“read committed”, “repeatable read”, or “serializable”. This parameter controls the default isolation levelof each new transaction. The default is “read committed”. transaction_isolation (enum): This parameter reflects the current transaction's isolation level. At the beginning of eachtransaction, it is set to the current value of default_transaction_isolation. Any subsequent attempt to change it isequivalent to a SET TRANSACTION command. » The first ("default") notion makes sense because its account uses the word "new". The implication is that it's legal to setit during an ongoing txn; but that doing this won't have any effect until it has been ended. On the other hand, the "settransaction" SQL statement is legal only during an ongoing txn. (An attempt when there is none causes the 25P01 error,"SET TRANSACTION can only be used in transaction blocks". Moreover, if you do this: begin; insert into s.t(v) values(19); set transaction isolation level serializable; then you get the 25001 error "SET TRANSACTION ISOLATION LEVEL must be called before any query". This implies that if, as the doc says, "set transaction_isolation: is equivalent to "SET TRANSACTION ISOLATION LEVEL", thenyou should get the same errors at the same moments. But you don't. This works fine when there's no ongoing txn: set transaction_isolation = serializable; But this: begin; insert into s.t(v) values(19); set transaction_isolation = serializable; causes the 25001 error. This suggests that the semantics of "set transaction_isolation" is context-sensitive: - when there's no ongoing txn. it has the same effect as "set default_transaction_isolation". - when you're in a txn, it has the same effect as ""SET TRANSACTION ISOLATION LEVEL" Is my hypothesis right?
On Tue, Feb 21, 2023 at 12:32 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
I found a discussion with the same title as this emails’s subject here:
https://postgrespro.com/list/thread-id/1741835
It dates from 2009. But it seems to be unresolved. The current PG doc here:
20.11. Client Connection Defaults
https://www.postgresql.org/docs/15/runtime-config-client.html
has an entry for each setting thus:
«
default_transaction_isolation (enum): Each SQL transaction has an isolation level, which can be either “read uncommitted”, “read committed”, “repeatable read”, or “serializable”. This parameter controls the default isolation level of each new transaction. The default is “read committed”.
transaction_isolation (enum): This parameter reflects the current transaction's isolation level. At the beginning of each transaction, it is set to the current value of default_transaction_isolation. Any subsequent attempt to change it is equivalent to a SET TRANSACTION command.
»
The first ("default") notion makes sense because its account uses the word "new". The implication is that it's legal to set it during an ongoing txn; but that doing this won't have any effect until it has been ended. On the other hand, the "set transaction" SQL statement is legal only during an ongoing txn.
(An attempt when there is none causes the 25P01 error, "SET TRANSACTION can only be used in transaction blocks". Moreover, if you do this:
This implies that if, as the doc says, "set transaction_isolation: is equivalent to "SET TRANSACTION ISOLATION LEVEL", then you should get the same errors at the same moments. But you don't. This works fine when there's no ongoing txn:
set transaction_isolation = serializable;
This suggests that the semantics of "set transaction_isolation" is context-sensitive:
- when there's no ongoing txn. it has the same effect as "set default_transaction_isolation".
- when you're in a txn, it has the same effect as ""SET TRANSACTION ISOLATION LEVEL"
Is my hypothesis right?
I don't think so. There is a transaction when the SET is executed, it just immediately commits and so doing that is basically pointless.
I suppose the difference in behavior when using SET TRANSACTION versus changing this configuration variable might be surprising but all that is happening is the one is giving you an error when you do something pointless and the other just does the pointless thing without complaint.
Frankly, the non-default versions are simply the one and only way you can see what the current value is. That you can then SET them to change it is I suppose convenient but since there is an actual SQL command to do the same one should use that command, not the setting.
David J.
> david.g.johnston@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> I found a discussion with the same title as this emails’s subject here: >> >> https://postgrespro.com/list/thread-id/1741835 >> >> It dates from 2009. But it seems to be unresolved. The current PG doc here: >> >> 20.11. Client Connection Defaults >> https://www.postgresql.org/docs/15/runtime-config-client.html >> >> has an entry for each setting thus: >> >> « >> default_transaction_isolation (enum): Each SQL transaction has an isolation level, which can be either “read uncommitted”,“read committed”, “repeatable read”, or “serializable”. This parameter controls the default isolation levelof each new transaction. The default is “read committed”. >> >> transaction_isolation (enum): This parameter reflects the current transaction's isolation level. At the beginning of eachtransaction, it is set to the current value of default_transaction_isolation. Any subsequent attempt to change it isequivalent to a SET TRANSACTION command. >> » >> >> ...Is my hypothesis right? > > I don't think so. There is a transaction when the SET is executed, it just immediately commits and so doing that is basicallypointless. I suppose the difference in behavior when using SET TRANSACTION versus changing this configuration variablemight be surprising but all that is happening is the one is giving you an error when you do something pointless andthe other just does the pointless thing without complaint. > > Frankly, the non-default versions are simply the one and only way you can see what the current value is. That you canthen SET them to change it is I suppose convenient but since there is an actual SQL command to do the same one shoulduse that command, not the setting. I believe that I see it now. Thanks. There's a Heisenberg effect lurking here. You can't observe anything, or change anything,except by using SQL. And most SQL statements have the effect that, if there's no transaction ongoing at the momentthat such a statement is issued, then what I've decided to call "single statement automatic transaction mode" is startedand then immediately ended. Otherwise, such a statement has its effect within the ongoing transaction—which continuesto be ongoing after its completion. Just a few statements, like "start transaction", "commit", "set transaction", and the like don't have the capacity to initiatesingle statement automatic transaction mode but, rather, have meanings and possible error outcomes according to whetheror not a transaction is ongoing. Things would be a lot simpler if the "transaction_isolation" run-time parameter wereread-only and the attempt to set it caused 55P02: parameter "transaction_isolation" cannot be changed now Oh well, I'll simply adopt the practice never to set it and to read it only when (by virtue of the session history that Iknow) my session is currently in an ongoing explicitly started transaction.