MariaDB or MySQL @@SESSION.tx_read_only always returns 0

Question

I'm trying to figure out possible "read only" transaction setup leak between pooled connections (on the same physical connection) in Java application which lead me to a question "How can I find out what the current transaction read-only-ness really is?"

I'm using versions 10.0.28-MariaDB-wsrep and 5.7.31-0ubuntu0.18.04.1 and whatever I do, when I issue the statement:

SELECT @@GLOBAL.tx_read_only, @@SESSION.tx_read_only;

It always returns 0 for both values. (I've tried also the non-deprecated variable on MySql, the same result.)

I've read how to start transaction for both databases and even when I get the error Cannot execute statement in a READ ONLY transaction. which is haunting me in the application, the tx_read_only is still 0.

I tried the following in IDEA SQL console:

• turn of auto-commit
• set TRANSACTION READ ONLY;
• START TRANSACTION;
• INSERT ... - Originally: no problem on MySQL, failed on MariaDB, I understand the reason, it is documented. EDIT: I tried this fiddle and this one and both fail on the first INSERT. My previous experience might have been caused by unclear session.
• start TRANSACTION READ ONLY; -- EDIT: probably not relevant now.
• INSERT ... - ended with Cannot execute statement in a READ ONLY transaction. on both DBs.

Any step of the way, tx_read_only was always 0. What may be the problem? What is the right way to ask the DB whether INSERT is allowed before actually trying to do it?

Answer 1

another fiddle test

So both MariaDB on tx_read_only and mysql on transaction_read_only have the session variable set to the current session value. The "next transaction access mode" remains hidden.

Catching insert errors seems like the only option.