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

  • I agree, looks like a bug, so I wrote a bug report MDEV-23579. Generally in SQL however you don't ask first, you do and the catch errors. It tends to avoid race conditions though this is a little different in that respect.
    – danblack
    Aug 25, 2020 at 22:49
  • 5.7.26 and 8.0.16 also return 0 (for whichever VARIABLE they allow).
    – Rick James
    Aug 25, 2020 at 23:44
  • @danblack I agree about catching errors for normal application run, this was for diagnostic purposes when we had a suspected "read-only-ness" leak from one pooled connection to the next (on the same physical one) due to SET TRANSACTION READ ONLY usage that (unexpectedly for us) has different semantics on MariaDB than on MySQL. Also thank you for bug report, I wasn't that far to be sure I'm not missing something.
    – virgo47
    Aug 27, 2020 at 7:21
  • Apart from the change of variable name, mariadb-10.4, and mysql-8.0 appear to have the same results as does mysql-5.7.29.
    – danblack
    Aug 27, 2020 at 7:29
  • @danblack You're right, I don't know what happened in my previous tests. Seems like I messed up something on that MySQL session that the insert in the original post worked. I'll edit the post and next time use something like dbfiddle.
    – virgo47
    Aug 27, 2020 at 7:45

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

  • Aha, I understand a bit better now. But for MariaDB mariadb.com/kb/en/start-transaction states that SET TRANSACTION READ ONLY "can also be specified in the SET TRANSACTION statement, in which case the specified mode is valid for all sessions, or for all subsequent transaction used by the current session." I tried this fiddle dbfiddle.uk/… but only "SET TRANSACTION SESSION" changes the variable. Is it right?
    – virgo47
    Aug 27, 2020 at 7:32
  • You cannot observe the result of set transaction read_only in using the system variables, only its effect. set transaction read_only however is only valid for the next transaction as the fiddle also shows. Yes the document needs to be clarified.
    – danblack
    Aug 27, 2020 at 7:49
  • I see, I tried this fiddle with two following START TRANSACTION and clearly without SESSION in SET TRANSACTION it really works the second time. I had a different experience based on very obscure situation described here, when I read JDBC connection metadata in the first transaction it somehow stayed read only for the next transaction - so I looked for reasons why in the wrong places.
    – virgo47
    Aug 27, 2020 at 7:56
  • Ad documentation, when I don't look for wrong answers, it actually links SET TRANSACTION page, where everything is clear. I'm sorry for the confusion and thanks again for the clarification.
    – virgo47
    Aug 27, 2020 at 7:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.