Обработчик базы данных DBI с AutoCommit установлен на 0, не возвращая надлежащие данные с помощью SELECT?

Это сложно понять (и очень странно), так что несите меня. Я объясню проблему и исправление для нее, но я хотел бы узнать, может ли кто-нибудь объяснить, почему она работает так, как она работает:)

У меня есть веб-приложение, которое использует mod_perl. Он использует базу данных MySQL, и я регулярно записываю данные в базу данных. Он модульный, поэтому он также имеет свой собственный тип базы данных, где я обрабатываю соединение, обновления и т.д. Для подключения к базе данных используется подпрограмма database:: db_connect(), а AutoCommit - 0.

Я сделал другое приложение Perl (автономный демон), который периодически извлекает данные из базы данных и выполняет различные задачи в зависимости от того, какие данные возвращаются. Я включаю в него модуль database.pm, поэтому мне не нужно переписывать/дублировать все.

Проблема, которую я испытываю, это:

Приложение подключается к базе данных при запуске, а затем циклически завершает выборку данных из базы данных каждые X секунд. Однако, если данные в базе данных обновляются, моему приложению все еще возвращаются "старые" данные, которые я получил в исходном соединении/запросе в базе данных.

Например, у меня есть 3 строки, а в столбце "Имя" есть значения "a", "b" и "c" - для каждой записи. Если я обновляю одну из строк (например, используя mysql-клиент из командной строки) и измените имя с 'c' на 'x', мой автономный демон не получит эти данные - он все равно получит a/b/c, возвращенный из MySQL. Я захватил db-трафик с помощью tcpdump, и я определенно мог видеть, что MySQL действительно возвращал эти данные. Я также пытался использовать SQL_NO_CACHE с SELECT (так как я не был уверен, что происходит), но это тоже не помогло.

Затем я изменил строку подключения DB в моем автономном демоне и установил AutoCommit в 1. Внезапно приложение начало получать правильные данные.

Я озадачен, потому что я думал, что AutoCommit влияет только на типы операторов INSERT/UPDATE и не влияет на инструкцию SELECT. Но, похоже, это так, и я не понимаю, почему.

Кто-нибудь знает, почему инструкция SELECT не вернет "обновленные" строки из базы данных, когда AutoCommit установлено в 0 и почему она вернет обновленные строки, когда 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 исправляет это. Почему?

Спасибо:)

P.S: Не уверен, что это кому-то нужно, но версия DBI 1.613, DBD:: mysql - 4.017, perl - 5.10.1 (на Ubuntu 10.04).

Ответ 1

Я полагаю, вы используете таблицы InnoDB, а не MyISAM. Как описано в модели транзакций InnoDB , все ваши запросы (включая SELECT) происходят внутри транзакции.

Когда AutoCommit включено, транзакция запускается для каждого запроса, и если она выполнена успешно, она неявно совершена (если она не работает, поведение может отличаться, но транзакция гарантирована). Вы можете увидеть неявные фиксации в MySQL binlog. Установив AutoCommit в false, вам необходимо самостоятельно управлять транзакциями.

Уровень изоляции транзакции по умолчанию REPEATABLE READ, что означает, что все запросы SELECT будут считывать один и тот же снимок (тот, который установлен, когда транзакция началась).

В дополнение к решению, данному в другом ответе (ROLLBACK перед началом чтения), вот несколько решений:

Вы можете выбрать другой уровень изоляции транзакции, например READ COMMITTED, что заставляет ваши запросы SELECT читать свежий снимок каждый раз.

Вы также можете оставить AutoCommit в true (значение по умолчанию) и начать свои собственные транзакции, выпустив BEGIN WORK. Это временно отключит поведение AutoCommit до тех пор, пока вы не выпустите инструкцию COMMIT или ROLLBACK, после которой каждый запрос снова получит свою собственную транзакцию (или вы начнете другую с BEGIN WORK).

Я лично выбрал бы последний метод, поскольку он кажется более элегантным.

Ответ 2

Я думаю, что когда вы выключаете аутокоммит, вы также начинаете транзакцию. И когда вы начинаете транзакцию, вы можете быть защищены от изменений других людей, пока не совершите ее, или откатите ее. Итак, если мое полуоснованное предположение правильно, и поскольку вы только запрашиваете данные, добавьте откат перед операцией спящего режима (нет смысла удерживать блокировки, которые вы не используете, и т.д.):

$dbh->rollback;