Affects: \ At least 5.3.19->5.3.20

I was trying to set a transaction to read only to fake a read only connection. Turns out that the SET TRANSACTION READ ONLY statement is called before there's any transaction going, rendering it useless. This happens in https://github.com/spring-projects/spring-framework/blob/bc3b3d01ee359e4960aa7444d2fec2baedf9a8f0/spring-r2dbc/src/main/java/org/springframework/r2dbc/connection/R2dbcTransactionManager.java#L212 when you run the R2dbcTransactionManger with enforceReadOnly= true. The code reads that it tries to prepareTransactionalConnection where the statement is called but then on the next line .then(Mono.from(doBegin(definition, con))) which calls BEGIN is called after the statement has been called so there's no transaction to set to READ ONLY. This results in the following output in postgres 13.3:

[35] LOG:  statement: SET TRANSACTION READ ONLY
[35] WARNING:  SET TRANSACTION can only be used in transaction blocks
[35] LOG:  statement: BEGIN
[35] LOG:  execute S_0/B_1: INSERT INTO <redacted>
[35] DETAIL:  parameters: $1 = <redacted>
[35] LOG:  statement: COMMIT

The method called is set with @Transactional(readOnly=true)

Comment From: jhoeller

@mp911de I suppose we simply need to invert the order of those operations: doBegin first, prepareTransactionalConnection right afterwards?

Comment From: mp911de

prepareTransactionalConnection handles isolation level and auto-commit updates to the connection. Switching the order of operations would break the usage pattern. I suggest moving the SET TRANSACTION READ ONLY call into a method called after Connection.beginTransaction(). Then we would fully align with JDBC.