R2DBC: @Transactional(readOnly) is applied to the connection before the transaction has begun

[hheg]

Affects: <Spring Framework version>
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

spring-framework/spring-r2dbc/src/main/java/org/springframework/r2dbc/connection/R2dbcTransactionManager.java

Line 212 in bc3b3d0

return connectionMono.flatMap(con -> prepareTransactionalConnection(con, definition, transaction)

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)

[jhoeller]

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

[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.