使用PHP mysqli_query进行单个SELECT查询的隔离级别 [英] Isolation level for single SELECT query with PHP mysqli_query

问题描述

我有一个带有一个表 book 的InnoDB数据库.另外，我有一个PHP脚本，其中包含一个查询，以显示此类 book 表中的书籍数量:

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

您知道，使用 mysqli 扩展名，可以使用 mysqli_begin_transaction 创建事务.接下来，可以定义隔离级别.

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.

就我而言，我不需要与事务相关的功能，而我使用的是 mysqli_query ，因为它只是单个SELECT查询.但是，我知道在MySQL的事务下，甚至一个查询都被包装了，默认的隔离级别是REPEATABLE READ.

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.

问题在这里:我不希望仅执行这样的单个查询就具有可重复读取的开销. READ UNCOMMITTED就足够了.

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

问题:是mysqli_extension自动检测到我正在使用单个SELECT查询(因为我没有开始任何事务)并自动将隔离级别设置为READ UNCOMMITTED(或至多READ COMMITED)还是我需要定义一个包装类，以始终在执行此类仅带有一个SELECT的事务查询之前设置READ UNCOMMITTED隔离级别?

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 ?

非常感谢您.

推荐答案

否，mysqli_query不会自动更改隔离级别.通过mysqli进行连接的方式与通过mysql cli进行连接的方式很多.在这两种情况下，您都将获得默认的隔离级别.就像cli一样，mysqli不能对连接中将要传递的语句做出任何假设.

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.

这篇关于使用PHP mysqli_query进行单个SELECT查询的隔离级别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！