transaction_isolation | Set transaction isolation level the ActiveRecord | Database library

I tried to replace image: mysql/mysql-server:8.0 with image: mysql:8.0 and this worked.

You're calculating the per-thread memory usage wrong. Those variables (and tmp_table_size which you didn't include) are not all used at the same time. Don't add them up. And even if you were to add them up, at least two might be allocated multiple times for a single query, so you can't just sum them anyway.

Basically, the memory usage calculated by MySQLTuner is totally misleading, and you shouldn't believe it. I have written about this before: What does "MySQL's maximum memory usage is dangerously high" mean by mysqltuner?

If you want to understand actual memory usage, use the PERFORMANCE_SCHEMA, or the slightly easier to read views on it, in the SYS schema.

The documentation for PS or SYS is pretty dense, so instead I'd look for better examples in blogs like this one:

https://www.percona.com/blog/2020/11/02/understanding-mysql-memory-usage-with-performance-schema/

What is happening ? The MYSQL server is reading data from your disk, and loading it into memory (if it is not already in memory) and sending the data to the MYSQL client, who is storing it in memory, before prompting it to the user (you).

What data structures does it build? I do not know, but I am not sure if it really matters.

Is there a way to optimize this? Yes, read less data, or check your configuration and your hardware, (and if you have concurrency issues you may need to change the engine).

• read less data : as you have seen, with a limit 10 the query runs fast, maybe you do not need 22 millions returned for every query, and you can add a WHERE clause.
• check your hardware : the data is on the disk, make sure you have a fast disk (like a SSD), also make sure you have enough memory on the server
• check your configuration : there are better answers on the internet that will explain how to tune your configuration, in a short (and inaccurate way) you want all your data to be able to be stored in memory, if you are only reading data, I would advise looking at this answer : https://dba.stackexchange.com/a/136409/119372 : increase your key_buffer_size to match the size of your index if it is not already done, and try the other suggestions one by one to see if any or all have an effect on your performances
• about concurrency : my guess was that this table was read only, if you are inserting / updating the data while querying it, MyIsam does a table lock, so you could try InnoDB to avoid this table lock

On a side note, as @Barmar pointed you are testing the server and the client at the same time, and you do not say if both are running on the same server. Most likely you are, so the memory can be consumed by your MYSQL server and your MYSQL client.

The goal in an ACID database is that queries in your session have the same result if you try to run them again.

Example: You run an INSERT query that results in a duplicate key error. You would expect if you retry that INSERT query, it would again fail with the same error.

But what if another session updates the row that caused the conflict, and changes the unique value? Then if you retry your INSERT, it would succeed, which is unexpected.

InnoDB has no way to implement true REPEATABLE-READ transactions when your statements are locking. E.g. INSERT/UPDATE/DELETE, or even SELECT with the locking options FOR UPDATE, FOR SHARE, or LOCK IN SHARE MODE. Locking SQL statements in InnoDB always act on the latest committed version of a row, not the version of that row that is visible to your session.

So how can InnoDB simulate REPEATABLE-READ, ensuring that the row affected by a locking statement is the same as the latest committed row?

By locking rows that are indirectly referenced by your locking statement, preventing them from being changed by other concurrent sessions.

The documentation on the web site is for version 2.5.1 which is in Release Candidate stage. You can download a snapshot jar from http://hsqldb.org/download/

There is no workaround for the moment. Issue has been created on https://jira.mariadb.org/browse/CONJ-604 to handle that for next version 2.2.5.

Currently, MySQL 8.0 is not supported (some tests even freeze the server), so waiting version to be more stable (and a working docker image to test properly with CI).

From MySQL 5.7.20 onwards you should changeover to using transaction_isolation. In documentation it states:

Prior to MySQL 5.7.20, use tx_isolation rather than transaction_isolation.

https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

tx_isolation

I don't think your INSERT issue has to do with sql_mode.

Even when Hibernate sets sql_mode to STRICT_TRANS_TABLES which means what?

If a value could not be inserted as given into a transactional table, abort the statement. For a nontransactional table, abort the statement if the value occurs in a single-row statement or the first row of a multiple-row statement.

More Detail here

Coming back to your INSERT issue

If you are using Hibernate then you must be having some Entity say Student something like this

Adding secondary index can be done inplace and permit concurrent DML.