I am using Aurora Serverless V2 Postgres edition having 1 writer and 1 reader. I am using Apache DB utils query runner and Hikari as data source to update data.
I am able to write data using cluster writer endpoint to write in DB but I am little confused about writing a transactional query.
I want to execute a read+write query in a single transaction. Now to Begin transaction using query runner, we simply get the connection in auto commit off mode, execute the sql query using that connection and commit and close the connection to complete the transaction. This works well if we have just write or just read query in a transaction. as connection depends on reader and writer endpoint in url. To execute a combination of read and write in a single transaction, I have to use same reader/writer endpoint which I am not sure is the best way to do.
What are some other ways to do it?
