Would it be advantagous to have MySQL default transaction mode on READ ONLY and READ UNCOMMITTED and only override when needed?

Question

The following strategy seems to be working, but am I overlooking something? Why not do this always for read-intensive systems that for most queries don't need data consistency? Like for instance blogging / publication systems. Seems to me

• Set the database-default to read-only mode. This will catch programming errors and probably give better read performance

  -- MySQL / MariaDB schema creation example
  -- in a read intensive system, default to read only for security and speed
  SET GLOBAL TRANSACTION READ ONLY;
  

• Set the database-default to the least strict transaction isolation level. This will prevent locking and probably increase read performance

  -- MySQL / MariaDB schema creation example
  -- if data integrity is not critical for most queries, choose maximum performance
  SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  

• Override only for queries that do need write-access and / or higher transaction isolation levels. In a read intensive system these extra query-statements around write-statements won't be noticeable

  -- MySQL / MariaDB example
  ...
  SET SESSION TRANSACTION READ WRITE;
  UPDATE table_x SET field_y=10;
  SET SESSION TRANSACTION READ ONLY;
  ...
  

Answer 1

I have never heard of a situation where READ UNCOMMITTED was the right thing to do. It seems clear that reading changes from other transactions that aren't committed yet — and may be rolled back — is likely to lead to a lot of phantom reads and other logic problems.

I would be in favor of setting the default transaction isolation level to READ COMMITTED, although this also depends on the application needs. Some applications require that they use REPEATABLE READ semantics by default. We can't say that one or the other of these is "better" because it depends what the application needs.

I am not aware that there's any performance advantage to setting a transaction access mode to READ ONLY. It prevents that transaction from changing any data, but in my opinion if you have code bugs such that you don't know if a given transaction is going to change data, then you have bigger problems.

InnoDB has a read-only mode that affects the whole instance, not individual transactions. Read about it here: https://dev.mysql.com/doc/refman/8.0/en/innodb-read-only-instance.html I doubt this would give significant performance benefits for an average application. It would have to be a very high-traffic application, so you have some bottleneck caused by some of the background threads that are disabled in InnoDB read-only mode. But in that type of application, you'd naturally need read-write mode.