Everything

php

[PDO] I want to unify the behavior when changing the autocommit mode setting while a transaction is open.

php.internals

Saki Takamachi

2 years ago

Hi internals,

The following code:

```
$db = new PDO(/* omission */, [PDO::ATTR_ATUOCOMMIT => 0]);
$db->beginTransaction();
$db->setAttribute(PDO::ATTR_ATUOCOMMIT, 1);

```

The behavior in this case varies depending on the DB driver and is not unified. MySQL, OCI, ODBC, and Firebird currently support PDO autocommit mode.

- MySQL always sets the new mode regardless of the state of the transaction, resulting in an implicit commit.
- OCI does an explicit commit and sets the new autocommit mode.
- Firebird raises an error.
- ODBC does not implement this behavior in the first place (it is a bug)

I believe that code that changes the autocommit mode with a transaction open is usually just forgetting to close the transaction. Therefore, in such cases, I would like to unify the behavior by generating an error like Firebird.

I would appreciate any feedback on this proposal and whether this change requires an RFC.

Regards.

Saki

Saki Takamachi

2 years ago

Please ignore the spelling mistakes....

Saki

Matteo Beccati

2 years ago

Hi Saki,

Il 26/11/2023 03:30, Saki Takamachi ha scritto:

> The behavior in this case varies depending on the DB driver and is not unified. MySQL, OCI, ODBC, and Firebird currently support PDO autocommit mode.
>
> - MySQL always sets the new mode regardless of the state of the transaction, resulting in an implicit commit.
> - OCI does an explicit commit and sets the new autocommit mode.
> - Firebird raises an error.
> - ODBC does not implement this behavior in the first place (it is a bug)
>
> I believe that code that changes the autocommit mode with a transaction open is usually just forgetting to close the transaction. Therefore, in such cases, I would like to unify the behavior by generating an error like Firebird.
>
> I would appreciate any feedback on this proposal and whether this change requires an RFC.

PDO's main goal was to make building db drivers easier, and it's not
meant to be a full DB abstraction layer.

As such, it is not expected that all databases behave the same,
especially for exotic functionality, such as autocommit.

My 2c


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Saki Takamachi

2 years ago

Hi Matteo,

> PDO's main goal was to make building db drivers easier, and it's not meant to be a full DB abstraction layer.
>
> As such, it is not expected that all databases behave the same, especially for exotic functionality, such as autocommit.

As you say, such behavior varies from DB to DB. However, with the current implementation of PDO, we don't know how it will work until you actually try it, and each driver has different behavior, so it is important to describe how it works in the documentation is also in a difficult situation.

Personally, I think it would be better if specifications could be standardized to a certain extent, within reason, but what do you think?

Regards.

Saki

Matteo Beccati

2 years ago

Hi,

Il 27/11/2023 10:23, Saki Takamachi ha scritto:

> As you say, such behavior varies from DB to DB. However, with the current implementation of PDO, we don't know how it will work until you actually try it, and each driver has different behavior, so it is important to describe how it works in the documentation is also in a difficult situation.
>
> Personally, I think it would be better if specifications could be standardized to a certain extent, within reason, but what do you think?

Realistically, I would think it is extremely rare that anyone is both
usin autocommit and is planning to switch database type from mysql to
firebird or others.

Even turning on autocommit after having already made operations on the
database seems either an application bug or something that is done on
purpose. In the latter case, I suppose they would expect the behaviour
to be compliant with the database standards.


Cheers

--
Matteo Beccati

Development & Consulting - http://www.beccati.com/

Saki Takamachi

2 years ago

Hi,

> In the latter case, I suppose they would expect the behaviour to be compliant with the database standards.

I may not have had this perspective. And this is a strong basis for explaining that different drivers behave differently.

I will continue with the current implementation and consider the optimal method for each driver.

Thank you!

Saki

Ayesh - PHP.Watch

2 years ago

>
> Hi,
>
> > In the latter case, I suppose they would expect the behaviour to be compliant with the database standards.
>
> I may not have had this perspective. And this is a strong basis for explaining that different drivers behave differently.
>
> I will continue with the current implementation and consider the optimal method for each driver.
>
>

I wanted to mention a recent RFC that passed its vote
(https://wiki.php.net/rfc/pdo_driver_specific_subclasses). It proposes
to add a PDO subclass for each driver, so we can make individual
driver-specific functions and changes more visible at the API level.

Unfortunately it did not make it to PHP 8.3, but the RFC vote
indicates the trajectory everyone hopes to have in PDO. It was voted
yes unanimously, so there is definitely a preference in majority of us
to iron out the differences, but perhaps in a non-BC way, preferably
making these inconsistencies obvious from the API level and not at the
test/doc levels.

Saki Takamachi

2 years ago

Hi Ayesh,

> Unfortunately it did not make it to PHP 8.3, but the RFC vote
> indicates the trajectory everyone hopes to have in PDO. It was voted
> yes unanimously, so there is definitely a preference in majority of us
> to iron out the differences, but perhaps in a non-BC way, preferably
> making these inconsistencies obvious from the API level and not at the
> test/doc levels.

Thank you, as you said, it seems that subclasses will allow implementations that take advantage of the features of the DB.

Considering that, there may be no need to forcefully unify the PDO specifications.

Regards.

Saki