AutoCommit 设置为 0 的 DBI 数据库句柄未通过 SELECT 返回正确的数据？

Question

这是一个很难解释的问题（而且很奇怪），所以请耐心等待。我将解释这个问题及其修复方法，但我想看看是否有人可以解释为什么它会这样工作:)

我有一个使用 mod_perl 的 Web 应用程序。它使用MySQL数据库，我定期将数据写入数据库。它是模块化的，因此它也有自己的“数据库”类型的模块，我在其中处理连接、更新等。database::db_connect() 子例程用于连接到数据库，AutoCommit设置为 0。

我制作了另一个 Perl 应用程序（独立守护程序），它定期从数据库获取数据，并根据返回的数据执行各种任务。我在其中包含了database.pm 模块，所以我不必重写/复制所有内容。

我遇到的问题是：

应用程序在启动时连接到数据库，然后永远循环，每 X 秒从数据库获取数据。但是，如果数据库中的数据被更新，我的应用程序仍然会返回我在数据库的初始连接/查询中获得的“旧”数据。

例如 - 我有 3 行，每条记录的“名称”列都有值“a”、“b”和“c”。如果我更新其中一行（例如，从命令行使用 mysql 客户端）并将名称从“c”更改为“x”，我的独立守护程序将无法获取该数据 - 它仍然会获取从返回的 a/b/c MySQL。我使用 tcpdump 捕获了数据库流量，并且我可以肯定地看到 MySQL 确实返回了该数据。我也尝试过将 SQL_NO_CACHE 与 SELECT 一起使用（因为我不确定发生了什么），但这也没有帮助。

然后，我修改了独立守护程序中的数据库连接字符串，并将 AutoCommit 设置为 1。突然，应用程序开始获取正确的数据。

我很困惑，因为我认为AutoCommit只影响INSERT/UPDATE类型的语句，而对SELECT语句没有影响。但似乎确实如此，我不明白为什么。

有谁知道为什么当 AutoCommit 设置为 0 时 SELECT 语句不会从数据库返回“更新”行，以及为什么当 AutoCommit 设置为 1 时它将返回更新的行？

这是我在独立守护程序中使用的简化代码（删除了错误检查等），并且不返回更新的行。

#!/usr/bin/perl

use strict;
use warnings;
use DBI;
use Data::Dumper;
$|=1;

my $dsn = "dbi:mysql:database=mp;mysql_read_default_file=/etc/mysql/database.cnf";
my $dbh = DBI->connect($dsn, undef, undef, {RaiseError => 0, AutoCommit => 0});
$dbh->{mysql_enable_utf8} = 1;

while(1)
{
    my $sql = "SELECT * FROM queue";
    my $stb = $dbh->prepare($sql);
    my $ret_hashref = $dbh->selectall_hashref($sql, "ID");
    print Dumper($ret_hashref);
    sleep(30);
}

exit;

将 AutoCommit 更改为 1 可以修复此问题。为什么？

谢谢:)

PS: 不确定是否有人关心，但 DBI 版本是 1.613，DBD::mysql 是 4.017，perl 是 5.10.1（在 Ubuntu 10.04 上）。

Answer 1

我想您使用的是 InnoDB 表而不是 MyISAM 表。正如 InnoDB 事务模型中所述，< em>所有您的查询（包括 SELECT）都发生在事务内。

当 AutoCommit 启用时，将为每个查询启动一个事务，如果成功，则隐式提交（如果失败，行为可能会有所不同，但事务保证结束）。您可以在 MySQL 的 binlog 中看到隐式提交。通过将 AutoCommit 设置为 false，您需要自行管理事务。

默认事务隔离级别是 REPEATABLE READ，这意味着所有 SELECT 查询都将读取相同的快照（事务启动时建立的快照）。

除了其他答案中给出的解决方案（开始阅读之前的ROLLBACK）之外，这里还有几个解决方案：

您可以选择另一个事务隔离级别，例如READ COMMITTED，这使得您的 SELECT 查询读取每次都是新鲜的快照。

您还可以将 AutoCommit 保留为 true（默认设置），并通过发出 BEGIN WORK 启动您自己的事务。这将暂时禁用 AutoCommit 行为，直到您发出 COMMIT 或 ROLLBACK 语句，之后每个查询再次获得自己的事务（或者您启动另一个查询）与开始工作）。

我个人会选择后一种方法，因为它看起来更优雅。

Answer 2

我认为当你关闭自动提交时，你也会启动一个事务。而且，当您启动事务时，您可能会受到保护，免受其他人的更改，直到您提交或回滚它。因此，如果我的半知情猜测是正确的，并且由于您只是查询数据，请在睡眠操作之前添加回滚（没有必要持有不使用的锁等）：

$dbh->rollback;