[Cgi-session-user] More transactions needed?

[Ron S. <ro...@sa...>]

Hi Folks

J=F6rg Prante has initiated a discussion about some things, and would lik=
e=20
others to comment on the issues. Here are our emails:

(1) J=F6rg to me:
Hello,

thank you for your Perl Module CGI::Session::Driver Oracle 1.00.
I thought I just drop you a small note about it.

It doesn't work if the DBI connection used is set up with 'AutoCommit'
=3D> 0. A session->flush will hang forever.

My suggestion for your code in _run_sql: the insert/update $sth->execute
statement should be followed by a $dbh->commit() statement.
(It is possible that other CGI::Session DBI drivers will suffer from
this too, but I'm using only Oracle).

You give advice to use a 'long' column data type at table creation. Is
it possible to enhance the documentation so that Oracle users will know
that their DBI connection has to be parameterized by two more
parameters, such as:

    'LongReadLen' =3D> (32 * 1024), # take care for a reasonable size for=

CGI::Session oracle backend, here 32 KB
    'LongTruncOk' =3D> 0, # do not truncate session data, bail out with
error

Best regards,

J=F6rg

(2) My reply (advocating laziness):
J=F6rg Prante wrote:

Hi J=F6rg

 > It doesn't work if the DBI connection used is set up with 'AutoCommit'=

 > =3D> 0. A session->flush will hang forever.

If you display the docs for CGI::Session V 4.20, say with:

http://search.cpan.org/~markstos/CGI-Session-4.20/lib/CGI/Session.pm

and scroll down to the notes on the flush() method, you'll see flush()=20
is known to be unreliable (for unknown reasons). This comment applies to =

all drivers, not just my Oracle driver.

 > My suggestion for your code in _run_sql: the insert/update $sth->execu=
te
 > statement should be followed by a $dbh->commit() statement.
 > (It is possible that other CGI::Session DBI drivers will suffer from
 > this too, but I'm using only Oracle).

Understood, but I won't change the code.
If the user had AutoCommit =3D> 0, I would not do a commit against their =

wished, even if it was documented I was doing it.
If the user had AutoCommit =3D> 1, an extra commit should not have any ef=
fect.
Either way, the known problem with flush() tells me the flush() problem=20
should be fixed, either somewhere in CGI::Session, or that the docs give =

the user sufficient warning. I don't agree that it's the role of my=20
module to offer another work-around (besides the flush() warning).

 > You give advice to use a 'long' column data type at table creation. Is=

 > it possible to enhance the documentation so that Oracle users will kno=
w
 > that their DBI connection has to be parameterized by two more
 > parameters, such as:
 >
 >    'LongReadLen' =3D> (32 * 1024), # take care for a reasonable size f=
or
 > CGI::Session oracle backend, here 32 KB
 >    'LongTruncOk' =3D> 0, # do not truncate session data, bail out with=

 > error

This is a good idea. I'm reluctant to release a new version just for=20
this change, but I'll think about it for a while  :-) .

(3) His reply (the last msg in the series):
Hi Ron,

thank you for your quick response.

Am Montag, den 23.04.2007, 05:11 +0000 schrieb Ron Savage:
 > > J=F6rg Prante wrote:
 > >
 > > Hi J=F6rg
 > >
 >> > > It doesn't work if the DBI connection used is set up with=20
'AutoCommit'
 >> > > =3D> 0. A session->flush will hang forever.
 > >
 > > If you display the docs for CGI::Session V 4.20, say with:
 > >
 > > http://search.cpan.org/~markstos/CGI-Session-4.20/lib/CGI/Session.pm=

 > >
 > > and scroll down to the notes on the flush() method, you'll see flush=
()
 > > is known to be unreliable (for unknown reasons). This comment=20
applies to
 > > all drivers, not just my Oracle driver.

The automatic flush() was unreliable and still is, but the explicit call
is reliable. Knowing this, I always flush() explicitly.

 >> > > My suggestion for your code in _run_sql: the insert/update=20
$sth->execute
 >> > > statement should be followed by a $dbh->commit() statement.
 >> > > (It is possible that other CGI::Session DBI drivers will suffer f=
rom
 >> > > this too, but I'm using only Oracle).
 > >
 > > Understood, but I won't change the code.
 > > If the user had AutoCommit =3D> 0, I would not do a commit against t=
heir
 > > wished, even if it was documented I was doing it.

The CGI:Session flush() method *requires* a synchronization. Please
refer to the documentation of the CGI::Session flush() method:
"Synchronizes data in memory with the copy serialized by the driver.
Call flush() if you need to access the session from outside the current
session object."

How can you access the Oracle session data without having committed the
session object to the database?

Right now, you rely on AutoCommit =3D> 1. But default values for
"AutoCommit" flags are not mentioned at all in CGI::Session (and for
some CGI::Session drivers, example file driver, not applicable at all).
So, how to make a transactional DB scenario more reliable? Can you
please give me some advice how to use your module with any DBI handle,
whether AutoCommit is on or off, so that it is guaranteed that the data
made it to the DB?

I think - and that is my suggestion - the most clever way is to create a
transaction for the session data and to commit the data (and, if an
error occurs, to rollback). Maybe:

eval {
    ...
     insert/update session data
    ...
    $sth->execute;
    $dbh->commit;
}
if ($@ {
    $dbh->rollback;
}


This kind of fix might cover other DB modules as well which operate in
transactional manner. An explanation for why this issue did not appear
yet could be that most CGI::Session users (MySQL etc) are not using
transactions, and, even more, AutoCommit is on by default (e.g. in DBD
mysql).

Another "fix" would be to die at initialization time, if the handed over
DB handle does not have set AutoCommit =3D> 1. This would be very
unfriendly.

I would be glad if you could find a way how to work out the best method
for fixing this issue, which is related to DB users with transactional
connections, Oracle for instance.

 > > If the user had AutoCommit =3D> 1, an extra commit should not have=20
any effect.

Correct.

 > > Either way, the known problem with flush() tells me the flush()=20
problem
 > > should be fixed, either somewhere in CGI::Session, or that the docs =

give
 > > the user sufficient warning. I don't agree that it's the role of my
 > > module to offer another work-around (besides the flush() warning).

Correct, but the flush() bugs are different. They seem only relate to
people who thought they could rely on automatic flushes, e.g. when the
Perl script exits. This kind of bug does not belong to your module.

 >> > > You give advice to use a 'long' column data type at table=20
creation. Is
 >> > > it possible to enhance the documentation so that Oracle users=20
will know
 >> > > that their DBI connection has to be parameterized by two more
 >> > > parameters, such as:
 >> > >
 >> > >    'LongReadLen' =3D> (32 * 1024), # take care for a reasonable=20
size for
 >> > > CGI::Session oracle backend, here 32 KB
 >> > >    'LongTruncOk' =3D> 0, # do not truncate session data, bail out=
 with
 >> > > error
 > >
 > > This is a good idea. I'm reluctant to release a new version just for=

 > > this change, but I'll think about it for a while  :-) .

Thank you for your good work, and best regards,

J=F6rg

Any ideas?
--=20
Ron Savage
ro...@sa...
http://savage.net.au/