Isolation level for single SELECT query with PHP mysqli_query

Question

I have an InnoDB database with one table, book. In addition, I have a PHP script which contains one single query, to display the number of books in such book table:

SELECT COUNT(*) FROM book

As you know, with the mysqli extension, it is possible to create a transaction with mysqli_begin_transaction. Next, the isolation level can be defined.

In my case, I don't need transactions related functions, and I use mysqli_query, because it's only a single SELECT query. However, I know that even a single query is wrapped under a transaction with MySQL, and the default isolation level is REPEATABLE READ.

The problem is here: I don't want REPEATABLE READ overhead for just executing such single query. READ UNCOMMITTED is enough.

Question: is the mysqli_extension auto-detect that I'm using a single SELECT query (because I don't begin any transaction) and automatically set the isolation level to READ UNCOMMITTED (or at most READ COMMITED) or do I need to define a wrap class to always set READ UNCOMMITTED isolation level before executing such transaction-with-only-one-SELECT-query ?

Thank you very much.

I don't know why you think it's an imaginary issue. My example is simplified to show the principle, but of course, it's a real problem. I want the best performance for my web application which made very lot of such SELECT-single-query. So I think that using READ UNCOMMITED instead of REPEATABLE READ when I precisely don't need REPEATABLE READ can be a good start to relieve my MySQL database by deleting any REPEATABLE READ overhead. So, my question is: how PHP/MySQLi extension handle such single-SELECT-query case in terms of isolation level ? Thanks. — NicolasCanac
"I want the best performance" --- that's why I asked about performance. Can you measure the difference between using different isolation levels? — zerkms
I can't measure easily: to know the real global gain, I need to turn all concerned mysqli_query to mysqli_begin_transaction schemes (with correct isolation level) on my PHP sources in productions servers. That's why I just want to know if the mysqli extension automatically set the READ UNCOMMITED isolation level when I call mysqli_query with one SELECT query. In this case, I even don't have to make any change in this area, and I can try to reducing the load by other ways. — NicolasCanac
So if you cannot measure it - how would you know that the optimization was successful? You cannot handle what you cannot measure. That's the performance optimization 101 — zerkms

skarist skarist · Accepted Answer · 2014-08-04T00:04:48

No, mysqli_query will not automatically change the isolation level. Connecting through mysqli is in many ways just like connecting via the mysql cli. In both cases you will get the default isolation level. And just like the cli, mysqli can't make any assumptions about what statements will be coming through the connection.

Anyway, if you have some good reasons to be concerned about the isolation level, I think you should just set it explicitly, e.g.:

$mysqli_connection->query("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

That way you can ensure you have the isolation level you want, and you can comment your reasons in the code.

Isolation level for single SELECT query with PHP mysqli_query

1 Answers