Description
I've set the transaction isolation level to read commited at postAllocate for the connection.
private Publisher<Void> initIsolationLevel(Connection conn){
if(conn.getTransactionIsolationLevel() != IsolationLevel.READ_COMMITTED) {
return conn.setTransactionIsolationLevel(IsolationLevel.READ_COMMITTED);
}
return Mono.empty();
}According to the documentation, this should be across the lifetime of the connection. But turn out it is not true, the isolation level switch back to repeatable read immediately after the first transaction is run.
This is observed by using select * from performance_schema.events_transactions_current
I look into the source code. On the r2dbc-mysql project,
override fun setTransactionIsolationLevel(isolationLevel: IsolationLevel): Publisher<Void> {
return executeVoidAfterCurrent("SET TRANSACTION ISOLATION LEVEL ${isolationLevel.asSql()}")
.doOnSuccess { this.isolationLevel = isolationLevel }
}The statement to set the isolation level is not correct. The session keyword should be added to make it effective to all the subsequent transactions. (ref: https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html)
SET SESSION TRANSACTION ISOLATION LEVEL ${isolationLevel.asSql()}
This bug is still there at the master source code
https://github.com/jasync-sql/jasync-sql/blob/master/r2dbc-mysql/src/main/java/JasyncClientConnection.kt
The bug may be also true for other sub-project as well