Eric,
it was not immediately clear to me what your application
does. Does one connection update the table and
another connection do the SELECT? Then the problem
might be the consistent read. I have copied below a segment
from the InnoDB manual at www.innodb.com :
....
When you issue a consistent read, that is, an ordinary SELECT statement,
InnoDB will give your transaction a timepoint according to which your query
sees the database. Thus, if transaction B deletes a row and commits after
your timepoint was assigned, then you will not see the row deleted.
Similarly with inserts and updates.
You can advance your timepoint by committing your transaction and then doing
another SELECT.
This is called multiversioned concurrency control.
User A User B
set autocommit=0; set autocommit=0;
time
| SELECT * FROM t;
| empty set
| INSERT INTO t VALUES (1, 2);
|
v SELECT * FROM t;
empty set
COMMIT;
SELECT * FROM t;
empty set;
COMMIT;
SELECT * FROM t;
----------------------
| 1 | 2 |
----------------------
Thus user A sees the row inserted by B only when B has committed the insert,
and A has committed his own transaction so that the timepoint is advanced
past the the commit of B.
If you want to see the 'freshest' state of the database, you should use a
locking read:
SELECT * FROM t LOCK IN SHARE MODE;
............
The problem you might have is that ::dbh->commit() really does not do
a commit. Have you tested it? If you do a ::dbh->commit() after inserting
some rows with autocommit switched off, and then do ::dbh->do("ROLLBACK"),
do the rows get inserted or not?
Eric, executing a commit through ::do->("COMMIT") is a perfectly
valid way to do a commit.
By the way, Sinisa, who is responsible for the DBI driver for MySQL?
It might be outdated.
Regards,
Heikki
Copied message:
................
I've got a part of a complex system that isn't behaving as expected. I've
got one perl program running with autocommit off that creates entries in a
table and commits the changes. At some point, the rows have their state
field changed from 'active' to 'closed', and these changes are
thencommitted.
The next step in the process is the part I'm having problems with. The
next step is supposed to pull up a list of the records that have a state
of 'closed' and process them. It works fine the first time through, or if
I limit the number of results in the select that gathers the list that I
use to process them it will loop through as many times as necessary to
process them all, but once it has processed everything that was available
when the program was first run, it stops and won't see any records that
were updated after the first run.
The interesting part is, if I run the program with autocommit on, it
works fine. Unfortunately, transactions are essential. I found that the
program works if I turn autocommit on but wrap the critical table updates
in $dbh->do("BEGIN;") and $dbh->do("COMMIT;"), but I haven't tested it to
make sure there are no side-effects, and I'd rather use the mechanism
it seems that I'm supposed to use with DBI.
Has anyone seen this problem?
Is do'ing the BEGIN and COMMIT valid?
Anyway, here's the table and the second-stage program (trimmed down as far
as possible).
I'm using MySQL 3.23.41, DBI-1.19, andMsql-Mysql-modules-1.2216.
CREATE TABLE LCCer( switchId CHAR(10) BINARY NOT NULL PRIMARY KEY,
activity INT UNSIGNED NOT NULL, dnis CHAR(10),
state ENUM('active','closed','broken','summed','completed',
'archived') NOT NULL, flags
SET('needs_transreq'),
data TEXT NOT NULL, INDEX state (state),
INDEX activity (activity,dnis)) TYPE = INNODB;#!/usr/bin/perluse DBI;
use strict;
my($livedsn)="DBI:mysql:live;mysql_read_default_file=/home/archuser/.my.cnf"
;
my($testdsn)="DBI:mysql:test;mysql_read_default_file=/home/archuser/.my.cnf"
;
$main::livedbh=DBI->connect($livedsn,undef,undef, { RaiseError => 1,
AutoCommit => 0 });
$main::testdbh=DBI->connect($testdsn,undef,undef, { RaiseError => 1,
AutoCommit => 0 });
$main::dbh=$main::testdbh;while(1){ my($rows,$switchId,$dnis,$data,$count
);
my($cmd)="SELECT switchId,dnis,data FROM LCCer WHERE state='closed'
ORDER BY activity;";
my($sth)=$main::dbh->prepare($cmd); $sth->execute();
while(($switchId,$dnis,$data)=$sth->fetchrow_array()) {
$main::dbh->do("UPDATE LCCer SET state='summed' where
switchId='$switchId';");
$main::dbh->commit(); $count++; } $sth->finish(); if($count<1)
{
print("sleeping for 10\n"); sleep(10); }}
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
