Isolation level for single SELECT query with PHP m

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.

Answer 1

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.