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
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;
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?