AutoCommit does not work

------=_Part_93784_27892697.1156240450447
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

Consider, please, the following piece of code:
____________________________________________________________ _____________________
my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError =>
1,PrintError =>1});

$dbh->{'AutoCommit'} = 0;

my $sth = $dbh->prepare(q{INSERT INTO TRANSACTION(toGroup, fromGroup,
amount) VALUES(?,?,?)});
eval{
$sth->execute("kuku","DADDY","3.14");
print "Going to sleep...\n\n\n";
sleep (60);
};
if ($@) {
print "Error !!!\n\n";
$dbh->rollback();
exit(-2);
}

$dbh->commit();

____________________________________________________________ _____________________

It connects to the database, turns AutoCommit off, prepares the statement,
executes it, waits for 60 seconds and commits the statement.
Although I do not get any errors, it does not work as I expect. The problem
is that I can see the new entry inside the table before $dbh->commit() is
called from the code. The same problem appears also when $dbh->begin_work
is used.

The underlying database is mySQL version 5.0.24 and the version of DBI is
1.50.

Could you please, give any hints how to debug the problem ?
Thanks in advance,
Artem.

------=_Part_93784_27892697.1156240450447--

Re: AutoCommit does not work

------=_Part_11181_31581120.1156263577506
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 8/22/06, Artem Harutyunyan wrote:
>
> Hi,
>
> Consider, please, the following piece of code:
> ____________________________________________________________

{code fragment}
> ____________________________________________________________
> It connects to the database, turns AutoCommit off, prepares the statement,
> executes it, waits for 60 seconds and commits the statement.
> Although I do not get any errors, it does not work as I expect. The
> problem
> is that I can see the new entry inside the table before $dbh->commit() is
> called from the code. The same problem appears also when $dbh->begin_work
> is used.


Hi Artem

I don't have much experience w/MySQL but my understanding with transactions
is that they only allow you roll back records within the transaction
block. A good example is an acct receivable;

BEGIN TRANSACTION
write a sales slip
write a journal record
write a check record
COMMIT
END TRANSACTION

If the "write a check record" operation failed, you'd do a "ROLLBACK" and
there'd be no sales or journal record because otherwise your DB would be all
screwed up.

In short, it souds like you're seeing what would be expected-
if you want the "$dbh->commit" to be some sort of "trigger" maybe you need
to re-think your application a little bit.

Hoping this helps

KC

------=_Part_11181_31581120.1156263577506--

RE: AutoCommit does not work

Artem Harutyunyan wrote:
> Hi,
>=20
> Consider, please, the following piece of code:
[snip]
>=20
> It connects to the database, turns AutoCommit off, prepares the
> statement, executes it, waits for 60 seconds and commits the
> statement.=20
> Although I do not get any errors, it does not work as I expect. The
> problem is that I can see the new entry inside the table before
> $dbh->commit() is called from the code. The same problem appears
> also when $dbh->begin_work is used.
>=20
> The underlying database is mySQL version 5.0.24 and the version of
> DBI is 1.50.

Are you using a table type in mysql that supports transactions?

Philip

Re: Re: AutoCommit does not work

------=_Part_103900_22378264.1156266865315
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hi,

Are you using a table type in mysql that supports transactions?
>
>
Well, I am not sure, how can I check it ?

Artem.

------=_Part_103900_22378264.1156266865315--

RE: AutoCommit does not work

>Artem Harutyunyan wrote:
Hi,
>
> Consider, please, the following piece of code:
>
____________________________________________________________ _______________
______
> my $dbh = DBI->connect($dsn, $user, $pass, {RaiseError =>
> 1,PrintError =>1});
>
> $dbh->{'AutoCommit'} = 0;
>
> my $sth = $dbh->prepare(q{INSERT INTO TRANSACTION(toGroup, fromGroup,
> amount) VALUES(?,?,?)});
> eval{
> $sth->execute("kuku","DADDY","3.14");
> print "Going to sleep...\n\n\n";
> sleep (60);
> };
> if ($@) {
> print "Error !!!\n\n";
> $dbh->rollback();
> exit(-2);
> }
>
> $dbh->commit();
>
>
____________________________________________________________ _______________
______
>
> It connects to the database, turns AutoCommit off, prepares the
> statement,
> executes it, waits for 60 seconds and commits the statement.
> Although I do not get any errors, it does not work as I expect. The
> problem
> is that I can see the new entry inside the table before $dbh->commit() is
> called from the code. The same problem appears also when
> $dbh->begin_work
> is used.
>
> The underlying database is mySQL version 5.0.24 and the version of DBI is
> 1.50.
>
> Could you please, give any hints how to debug the problem ?
> Thanks in advance,
> Artem.

Artem,
In MySQL your table must be of type INNOB for transactions to be
supported.

HTH

Mary>

RE: Re: AutoCommit does not work

Artem Harutyunyan wrote:
> Hi,
>=20
> Are you using a table type in mysql that supports transactions?
>>=20
> Well, I am not sure, how can I check it ?

mysql> show table status like '%transaction%';

The second column (engine) is what you're interested in... needs to be
InnoDB to support transactions.

Regards,
Philip

Re: AutoCommit does not work

------=_Part_109512_20745342.1156280515355
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

On 8/22/06, Kevin Carothers wrote:
>
>
> On 8/22/06, Artem Harutyunyan wrote:
>
> >
> > Hi,
> >
> > >
> > > BEGIN TRANSACTION
> > > write a sales slip
> > > write a journal record
> > >
> > write a check record
> >
> > > COMMIT
> > > END TRANSACTION
> > >
> >
> > But what happens, is that I can see a 'write a sales slip' result
> > before actually doing "commit". Is it normal ?
> >
>
> Yes. Absolutely- otherwise, how would you know the sales slip record was
> written successfully?
>

After doing $sth->execute("kuku","DADDY"," 3.14"); application goes for
sleep for a minute and only after that $dbh->commit() is called. If I
understand everything correctly, during the sleep period the newly added
entry must not be seen in the table, whereas in my case I can see it by
simply doing SELECT from mysql command prompt.
For the test, I have tried to call $dbh->rollback() instead of
$dbh->commit(), but it didn't gave any result (the new entry still was
there).

Maybe your app will be better served with a trigger construct (I think
> this is MySQL 5.0.2).
>

Thanks for the advice, but what I really need is transactions :).

Artem.

KC
>
>

------=_Part_109512_20745342.1156280515355--

Re: AutoCommit does not work

------=_Part_109664_11839679.1156281118138
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Dear Mary,


In MySQL your table must be of type INNOB for transactions to be
> supported.


Thanks a lot !
I have change the database type and now the program works properly.

Thanks to all.

Artem.

------=_Part_109664_11839679.1156281118138--