CODEWITHSUNDEEP
mysql
Christian
Christian

Reputation: 3972

MySQL cannot execute statement in READ ONLY

How can MySQL allow a READ WRITE transaction to be set to READ ONLY but not back to READ WRITE again?

The following @test causes an SQLException as assertThrow shows and passes.

I've read the manual at https://dev.mysql.com/doc/refman/5.6/en/innodb-performance-ro-txn.html which states the same, but it just seems unreal.

Moving the commit to the end avoids the Exception thrown but I'm curious why this is a problem. Although at the same time, I'm trying to think of an edge case when this would be a problem :)

@Test
void testSeparatedCommitBroken() throws SQLException {
    try (DataConnection connection = ds.getConnection()) {
        connection.setAutoCommit(false);

        // first transaction
        Map<String, Object> vals1 = new HashMap<>();
        vals1.put("name", UUID.randomUUID().toString());

        Long id1 = connection.insertRow("products", vals1);
        assertNotNull(id1);
        connection.commit(); // placing this at the end solves the problem

        // check first transaction
        Map<String, Object> row1 = connection.selectRow("products", new Pair<>("id", id1));
        assertEquals(vals1.get("name"), row1.get("name"));

        // second transaction
        Map<String, Object> vals2 = new HashMap<>();
        vals2.put("name", UUID.randomUUID().toString());

        // the connection was set to read only by SELECT which can't be swapped back whilst in autocommit=false
        assertThrows(SQLException.class, () -> {
            connection.insertRow("products", vals2);
        });

        connection.setAutoCommit(true);
    }
}

General Log snapshot

2017-12-31T13:43:37.290875Z      7556 Connect   root@localhost on  using TCP/IP
2017-12-31T13:43:37.291062Z      7556 Query     set autocommit=1
2017-12-31T13:43:37.296937Z      7556 Query     SET CHARACTER SET utf8
2017-12-31T13:43:37.297194Z      7556 Query     SET NAMES utf8
2017-12-31T13:43:37.298778Z      7556 Query     USE `prod_info_mngr`
2017-12-31T13:43:37.298993Z      7556 Query     set autocommit=1
2017-12-31T13:43:37.301601Z      7556 Query     SET autocommit=0
2017-12-31T13:43:37.303049Z      7556 Query     set session transaction read write
2017-12-31T13:43:37.305546Z      7556 Query     select @@session.tx_read_only
2017-12-31T13:43:37.315099Z      7556 Query     INSERT INTO products (name) VALUES (UUID())
2017-12-31T13:43:37.315814Z      7556 Query     commit
2017-12-31T13:43:37.318989Z      7556 Query     set session transaction read only
2017-12-31T13:43:37.319842Z      7556 Query     select @@session.tx_read_only
2017-12-31T13:43:37.327934Z      7556 Query     SELECT * FROM products WHERE id = 162 LIMIT 1
2017-12-31T13:43:37.336647Z      7556 Query     set session transaction read write
2017-12-31T13:43:37.337431Z      7556 Query     select @@session.tx_read_only
2017-12-31T13:43:37.346483Z      7556 Query     INSERT INTO products (name) VALUES (UUID())

enter image description here

SQL to try it yourself (this design is obviously stupid - if you are committing on each INSERT, then why bother having AutoCommit off):

CREATE DATABASE `prod_info_mngr` /*!40100 DEFAULT CHARACTER SET utf8 */;
use `prod_info_mngr`;

CREATE TABLE `products` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

set autocommit=0;

set session transaction read write;
insert into products (`name`) values (UUID());
commit;

set session transaction read only;
select * from products;

set session transaction read write;
insert into products (`name`) values (UUID());
commit;

set autocommit=1;

select * from products;

Upvotes: 4

Views: 6335

Answers (1)

Solarflare
Solarflare

Reputation: 11096

You cannot change the characteristics of an active transaction, you can only do that for future ones, see SET TRANSACTION:

You can set transaction characteristics globally, for the current session, or for the next transaction:

  • [...]
  • With the SESSION keyword, the statement applies to all subsequent transactions performed within the current session.

So in your case, the first and second set session transaction statements do what you expect: there is no active transaction, so the new characteristic applies for the next query. The third set session transaction statement on the other hand is executed inside a transaction and thus will not become relevant until that transaction ends, so the read-only mode is still active when you try to insert for the second time, giving you the error.

That also explains why it works if you remove the first commit: the read-write mode will be kept active throughout the whole transaction, as set session transaction read only was executed inside a transaction and thus not affecting it. Try adding an insert in the section that is supposedly read-only.

Adding a commit before setting the read-write mode will obviously solve the problem too.

Try set transaction ... without the session keyword. It does not do the same thing (it only applies to the next transaction, not all following ones), but in contrast to using session or global, it will not allow you to execute it inside a transaction:

SET TRANSACTION without GLOBAL or SESSION is not permitted while there is an active transaction.

So it will raise an error for your third set transaction-statement itself (not the following insert). It might clarifying what is going on and which statements actually do what you think they do.

While permitted, it is generally a good idea to avoid setting (global or session) transaction characteristics inside a transaction for exactly such reasons.

Upvotes: 2

Related Questions