CODEWITHSUNDEEP
mysqlsqltransactionsmodelingisolation-level
NicolasCanac
NicolasCanac

Reputation: 57

InnoDB Isolation Level for single SELECT query

I know that every single query sent to MySQL (with InnoDB as engine) is made as a separate transaction. However my concerns is about the default isolation level (Repeatable Read).

My question is: as SELECT query are sent one by one, what is the need to made the transaction in repeatable read ? In this case, InnoDB doesn't add overhead for nothing ?

For instance, in my Web Application, I have lot of single read queries but the accuracy doesn't matter: as an example, I can retreive the number of books at a given time, even if some modifications are being processed, because I precisely know that such number can evolve after my HTTP request.

In this case READ UNCOMMITED seems appropriate. Do I need to turn every similar transaction-with-single-request to such ISOLATION LEVEL or InnoDB handle it automatically?

Thanks.

Upvotes: 2

Views: 951

Answers (1)

miko
miko

Reputation: 366

First of all your question is a part of wider topic re performance tuning. It is hard to answer just like that - knowing only this. But i try to give you at least some overview.

The fact that Repeatable Read is good enough for most database, does not mean it is also best for you! That’s holly true!

BTW, I think only in MySQL this is at this level defaultly. In most database this is at Read Committed (e.g. Oracle). In my opinion it is enough for most cases.

My question is: as SELECT query are sent one by one, what is the need to made the transaction in repeatable read ?

Basically no need. Repeatable read level ensure you are not allowing for dirty reads, not repeatable reads and phantom rows (but maybe this is a little different story). And basically these are when you run DMLs. So when query only pure SELECTs one by one -this simply does not apply to.

In this case, InnoDB doesn't add overhead for nothing ?

Another yep. It does not do it for nothing. In general ACID model in InnoDB is at cost of having data consistently stored without any doubts about data reliability. And this is not free of charge. It is simply trade off between performance and data consistency and reliability.

In more details MySQL uses special segments to store snapshots and old row values for rollback purposes. And refers to them if necessary. As I said it costs. But also worth to mention that performance increase/decrease is visible much more when doing INSERT, UPDATE, DELETE. SELECT does not cost so much. But still.

If you do not need to do it, this is theoretically obvious benefit. How big? You need to assess it by yourself, measuring your query performance in your environment.

Cause many depends also on individual incl. scale, how many reads/writes are there, how often, reference application design, database and much, much more .

And with the same problem in different environments the answer could be simply different.

Another alternative here you could consider is to simply change engine to MyISAM (if you do not need any foreign keys for example). From my experience it is very good choice for heavy reads needs. Again all depends- but in many cases is faster than InnoDB. Of course less safer but if you are aware of possible threats - it is good solution.

In this case READ UNCOMMITED seems appropriate. Do I need to turn every similar transaction-with-single-request to such ISOLATION LEVEL or InnoDB handle it automatically?

You can set the isolation level globally, for the current session, or for the next transaction. Set your transaction level globally for next sessions.

SET GLOBAL tx_isolation = 'READ-UNCOMMITTED';

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

Upvotes: 1

Related Questions