does disconnect() actually commits transaction even if the connectis made using autocommit=0

Hi,

If I initiate a connection using autocommit=0 in DBI and I don't do a
explicit connection->commit(), should transactions automatically commit
after I do an explicit connection->disconnect()? I was expecting an
automatica rollback. But the testing result is just the opposite.

For example:
my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
{AutoCommit => 0}, "Oracle" )
deleteRecord;
$databaseHandle->disconnect();

Should the delete query be commited?

thanks,

Jie

RE: does disconnect() actually commits transaction even if the connect is made using autocommit=0

On 07-Apr-2006 Jie Zhang wrote:
> Hi,
>
> If I initiate a connection using autocommit=0 in DBI and I don't do a
> explicit connection->commit(), should transactions automatically commit
> after I do an explicit connection->disconnect()? I was expecting an
> automatica rollback. But the testing result is just the opposite.
>
> For example:
> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
> {AutoCommit => 0}, "Oracle" )
> deleteRecord;
> $databaseHandle->disconnect();
>
> Should the delete query be commited?
>
> thanks,
>
> Jie

According to the DBI docs:

The transaction behaviour of the "disconnect" method
is, sadly, undefined. Some database systems (such as
Oracle and Ingres) will automatically commit any out-
standing changes, but others (such as Informix) will
rollback any outstanding changes. Applications not
using "AutoCommit" should explicitly call "commit" or
"rollback" before calling "disconnect".

I was a bit surprised to see the comment on Oracle automatically committing. I
believe there is a way to stop this happening at the oci level.

Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

--------------enig31DBFE27228AD4158E07B471
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

man DBI:
---------------8<---------------
....
The transaction behaviour of the "disconnect" method is, sadly,
undefined. Some database systems (such as Oracle and Ingres) will
automatically commit any outstanding changes, but others (such as
Informix) will rollback any outstanding changes. Applications not
using "AutoCommit" should explicitly call "commit" or "rollback"
before calling "disconnect".
....
---------------8<---------------

Jie Zhang wrote:
> Hi,
>
> If I initiate a connection using autocommit=0 in DBI and I don't do a
> explicit connection->commit(), should transactions automatically commit
> after I do an explicit connection->disconnect()? I was expecting an
> automatica rollback. But the testing result is just the opposite.
>
> For example:
> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
> {AutoCommit => 0}, "Oracle" )
> deleteRecord;
> $databaseHandle->disconnect();
>
> Should the delete query be commited?
>
> thanks,
>
> Jie
>
>


--------------enig31DBFE27228AD4158E07B471
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mandriva - http://enigmail.mozdev.org

iD8DBQFENiwAkVPeOFLgZFIRAhjoAJ4sD7dCfHzy31r9xnMoyl3ibUf/2gCf T14d
YQU4qtOzdPa1hkr9FEAP7ME=
=9ZB8
-----END PGP SIGNATURE-----

--------------enig31DBFE27228AD4158E07B471--

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

--------------enigD7FC7F078D8DAB8439703471
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

Well I was already familiar with this behaviour because it's the same
with JDBC-Drivers.

Tom

Martin J. Evans wrote:
> On 07-Apr-2006 Jie Zhang wrote:
>
>>Hi,
>>
>>If I initiate a connection using autocommit=0 in DBI and I don't do a
>>explicit connection->commit(), should transactions automatically commit
>>after I do an explicit connection->disconnect()? I was expecting an
>>automatica rollback. But the testing result is just the opposite.
>>
>>For example:
>> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
>>{AutoCommit => 0}, "Oracle" )
>> deleteRecord;
>>$databaseHandle->disconnect();
>>
>>Should the delete query be commited?
>>
>>thanks,
>>
>>Jie
>
>
> According to the DBI docs:
>
> The transaction behaviour of the "disconnect" method
> is, sadly, undefined. Some database systems (such as
> Oracle and Ingres) will automatically commit any out-
> standing changes, but others (such as Informix) will
> rollback any outstanding changes. Applications not
> using "AutoCommit" should explicitly call "commit" or
> "rollback" before calling "disconnect".
>
> I was a bit surprised to see the comment on Oracle automatically committing. I
> believe there is a way to stop this happening at the oci level.
>
> Martin
> --
> Martin J. Evans
> Easysoft Ltd, UK
> http://www.easysoft.com
>
>


--------------enigD7FC7F078D8DAB8439703471
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mandriva - http://enigmail.mozdev.org

iD8DBQFENjAIkVPeOFLgZFIRAjLcAKCXP4jp97J2vXY5XGHL2qKYGwZCjQCg htg2
b7nKrZo8iA7hEziCH1bR/DE=
=tAmM
-----END PGP SIGNATURE-----

--------------enigD7FC7F078D8DAB8439703471--

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

Tom and Martin,

Thank you both!

It is a bit counter intuitive.

Jie

Tom Schindl wrote:

>Well I was already familiar with this behaviour because it's the same
>with JDBC-Drivers.
>
>Tom
>
>Martin J. Evans wrote:
>
>
>>On 07-Apr-2006 Jie Zhang wrote:
>>
>>
>>
>>>Hi,
>>>
>>>If I initiate a connection using autocommit=0 in DBI and I don't do a
>>>explicit connection->commit(), should transactions automatically commit
>>>after I do an explicit connection->disconnect()? I was expecting an
>>>automatica rollback. But the testing result is just the opposite.
>>>
>>>For example:
>>>my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
>>>{AutoCommit => 0}, "Oracle" )
>>>deleteRecord;
>>>$databaseHandle->disconnect();
>>>
>>>Should the delete query be commited?
>>>
>>>thanks,
>>>
>>>Jie
>>>
>>>
>>According to the DBI docs:
>>
>> The transaction behaviour of the "disconnect" method
>> is, sadly, undefined. Some database systems (such as
>> Oracle and Ingres) will automatically commit any out-
>> standing changes, but others (such as Informix) will
>> rollback any outstanding changes. Applications not
>> using "AutoCommit" should explicitly call "commit" or
>> "rollback" before calling "disconnect".
>>
>>I was a bit surprised to see the comment on Oracle automatically committing. I
>>believe there is a way to stop this happening at the oci level.
>>
>>Martin
>>--
>>Martin J. Evans
>>Easysoft Ltd, UK
>>http://www.easysoft.com
>>
>>
>>
>>
>
>
>

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

Jie Zhang wrote:
> Tom and Martin,
>
> Thank you both!
> It is a bit counter intuitive.

If by that you mean that you expected disconnect with a commit to
rollback the changes then I agree. We had an Oracle ODBC driver which
used oci which did this and ended up changing it to NOT commit on
disconnect because we (and more importantly our customers) believed it
was just too dangerous.

Off the top of my head I can't remember how this was achieved in
OCI but it was.

Martin

> Tom Schindl wrote:
>
>> Well I was already familiar with this behaviour because it's the same
>> with JDBC-Drivers.
>>
>> Tom
>>
>> Martin J. Evans wrote:
>>
>>
>>> On 07-Apr-2006 Jie Zhang wrote:
>>>
>>>
>>>
>>>> Hi,
>>>>
>>>> If I initiate a connection using autocommit=0 in DBI and I don't do
>>>> a explicit connection->commit(), should transactions automatically
>>>> commit after I do an explicit connection->disconnect()? I was
>>>> expecting an automatica rollback. But the testing result is just
>>>> the opposite.
>>>>
>>>> For example:
>>>> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
>>>> {AutoCommit => 0}, "Oracle" )
>>>> deleteRecord;
>>>> $databaseHandle->disconnect();
>>>>
>>>> Should the delete query be commited?
>>>>
>>>> thanks,
>>>>
>>>> Jie
>>>>
>>>
>>> According to the DBI docs:
>>>
>>> The transaction behaviour of the "disconnect" method
>>> is, sadly, undefined. Some database systems (such as
>>> Oracle and Ingres) will automatically commit any out-
>>> standing changes, but others (such as Informix) will
>>> rollback any outstanding changes. Applications not
>>> using "AutoCommit" should explicitly call "commit" or
>>> "rollback" before calling "disconnect".
>>>
>>> I was a bit surprised to see the comment on Oracle automatically
>>> committing. I
>>> believe there is a way to stop this happening at the oci level.
>>>
>>> Martin
>>> --
>>> Martin J. Evans
>>> Easysoft Ltd, UK
>>> http://www.easysoft.com
>>>
>>>
>>>
>>
>>
>>
>>
>
>
>

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

Yeah, I mean the disconnect should not commit if autocommit is set to 0.

In most of the case, this is not a problem because people probably do
rollback and commit. Could you change oci code? Is it owned by Oracle?

thanks,

Jie

Martin J. Evans wrote:

> Jie Zhang wrote:
>
>> Tom and Martin,
>>
>> Thank you both!
>> It is a bit counter intuitive.
>
>
> If by that you mean that you expected disconnect with a commit to
> rollback the changes then I agree. We had an Oracle ODBC driver which
> used oci which did this and ended up changing it to NOT commit on
> disconnect because we (and more importantly our customers) believed it
> was just too dangerous.
>
> Off the top of my head I can't remember how this was achieved in
> OCI but it was.
>
> Martin
>
>> Tom Schindl wrote:
>>
>>> Well I was already familiar with this behaviour because it's the same
>>> with JDBC-Drivers.
>>>
>>> Tom
>>>
>>> Martin J. Evans wrote:
>>>
>>>
>>>> On 07-Apr-2006 Jie Zhang wrote:
>>>>
>>>>
>>>>
>>>>> Hi,
>>>>>
>>>>> If I initiate a connection using autocommit=0 in DBI and I don't
>>>>> do a explicit connection->commit(), should transactions
>>>>> automatically commit after I do an explicit
>>>>> connection->disconnect()? I was expecting an automatica
>>>>> rollback. But the testing result is just the opposite.
>>>>>
>>>>> For example:
>>>>> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
>>>>> {AutoCommit => 0}, "Oracle" )
>>>>> deleteRecord;
>>>>> $databaseHandle->disconnect();
>>>>>
>>>>> Should the delete query be commited?
>>>>>
>>>>> thanks,
>>>>>
>>>>> Jie
>>>>>
>>>>
>>>>
>>>> According to the DBI docs:
>>>>
>>>> The transaction behaviour of the "disconnect" method
>>>> is, sadly, undefined. Some database systems (such as
>>>> Oracle and Ingres) will automatically commit any out-
>>>> standing changes, but others (such as Informix) will
>>>> rollback any outstanding changes. Applications not
>>>> using "AutoCommit" should explicitly call "commit" or
>>>> "rollback" before calling "disconnect".
>>>>
>>>> I was a bit surprised to see the comment on Oracle automatically
>>>> committing. I
>>>> believe there is a way to stop this happening at the oci level.
>>>>
>>>> Martin
>>>> --
>>>> Martin J. Evans
>>>> Easysoft Ltd, UK
>>>> http://www.easysoft.com
>>>>
>>>>
>>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>

Re: does disconnect() actually commits transaction even if the connectis made using autocommit=0

Jie Zhang wrote:
> Yeah, I mean the disconnect should not commit if autocommit is set to 0.
>
> In most of the case, this is not a problem because people probably do
> rollback and commit. Could you change oci code? Is it owned by Oracle?

From what I remember we did not make any change to OCI, we changed
how we used OCI. I'll have to look this up after the weekend.
This would suggest it is how DBD:oracle uses OCI.
If you don't hear anything early next week mail me personally to
remind me to look it up.

Martin

> thanks,
>
> Jie
>
> Martin J. Evans wrote:
>
>> Jie Zhang wrote:
>>
>>> Tom and Martin,
>>>
>>> Thank you both!
>>> It is a bit counter intuitive.
>>
>>
>>
>> If by that you mean that you expected disconnect with a commit to
>> rollback the changes then I agree. We had an Oracle ODBC driver which
>> used oci which did this and ended up changing it to NOT commit on
>> disconnect because we (and more importantly our customers) believed it
>> was just too dangerous.
>>
>> Off the top of my head I can't remember how this was achieved in
>> OCI but it was.
>>
>> Martin
>>
>>> Tom Schindl wrote:
>>>
>>>> Well I was already familiar with this behaviour because it's the same
>>>> with JDBC-Drivers.
>>>>
>>>> Tom
>>>>
>>>> Martin J. Evans wrote:
>>>>
>>>>
>>>>> On 07-Apr-2006 Jie Zhang wrote:
>>>>>
>>>>>
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> If I initiate a connection using autocommit=0 in DBI and I don't
>>>>>> do a explicit connection->commit(), should transactions
>>>>>> automatically commit after I do an explicit
>>>>>> connection->disconnect()? I was expecting an automatica
>>>>>> rollback. But the testing result is just the opposite.
>>>>>>
>>>>>> For example:
>>>>>> my $databaseHandle = DBI->connect( "", "$user\@$sid", "$pass",
>>>>>> {AutoCommit => 0}, "Oracle" )
>>>>>> deleteRecord;
>>>>>> $databaseHandle->disconnect();
>>>>>>
>>>>>> Should the delete query be commited?
>>>>>>
>>>>>> thanks,
>>>>>>
>>>>>> Jie
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> According to the DBI docs:
>>>>>
>>>>> The transaction behaviour of the "disconnect" method
>>>>> is, sadly, undefined. Some database systems (such as
>>>>> Oracle and Ingres) will automatically commit any out-
>>>>> standing changes, but others (such as Informix) will
>>>>> rollback any outstanding changes. Applications not
>>>>> using "AutoCommit" should explicitly call "commit" or
>>>>> "rollback" before calling "disconnect".
>>>>>
>>>>> I was a bit surprised to see the comment on Oracle automatically
>>>>> committing. I
>>>>> believe there is a way to stop this happening at the oci level.
>>>>>
>>>>> Martin
>>>>> --
>>>>> Martin J. Evans
>>>>> Easysoft Ltd, UK
>>>>> http://www.easysoft.com
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>>
>
>
>