Data is still visible after deletion within the same MySQL transaction

Question

Lately I've been testing my PHP framework's database wrapper class which is based on PHP Data Objects. I've successfully passed all the tests with Oracle database and started to do the tests with MySQL when I came across to a bug which seems like an ACID nightmare.

In short my database driver wrapper class does the following:

1) It establishes a persistent database connection with the following attributes


    self::$connection = new PDO(
        $dsn
        ,DATABASE_USERNAME
        ,DATABASE_PASSWORD
        ,[
            PDO::ATTR_AUTOCOMMIT => FALSE                                   // Do not autocommit every single statement
            ,PDO::ATTR_CASE => PDO::CASE_LOWER                              // Force column names to lower case
            ,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC               // Return result set as an array indexed by column name
            ,PDO::ATTR_EMULATE_PREPARES => (DATABASE_DRIVER == 'mysql')     // Allow emulation of prepared statements only for MySQL
            ,PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION                    // Throw an exception and rollback transaction on error
            ,PDO::ATTR_ORACLE_NULLS => PDO::NULL_EMPTY_STRING               // Convert emtpy strings to NULL
            ,PDO::ATTR_PERSISTENT => TRUE                                   // Use persistent connection
        ]
    );

2) Wrapper class has a execute() method which is a backbone for running various SQL statements. When executing SQL statement with execute() method it checks if transaction is active using PDO::inTransaction() method. If not, it begins the transaction. Here is how this method looks like (skipping all boring parts):


   public static function execute($sql, $bind_values = [], $limit = -1, $offset = 0) {
   ...
      if (!self::$connection->inTransaction()) {
          self::$connection->beginTransaction();
      }
   ...
   }

3) So far so good. But let's look at the following example which calls DELETE statement followed by a SELECT statement against the same table with the very same where conditions:


    database::execute('DELETE FROM dms_test WHERE id = 5');
    $data = database::execute('SELECT FROM dms_test WHERE id = 5');

4) Everyone would expect that SELECT statement returns an empty result-set since the previous DELETE statement just wiped out all the data within the same transaction.

5) But as crazy as it may sound, the SELECT statement returns non-empty result-set as though as DELETE statement would never have been issued.

6) It's interesting that the very same example works as intended within Oracle database.

Any ideas what is wrong with MySQL? Have any of you had similar problems?

PDO::beginTransaction Turns off autocommit mode. While autocommit mode is turned off, changes made to the database via the PDO object instance are not committed until you end the transaction by calling PDO::commit(). Calling PDO::rollBack() will roll back all changes to the database and return the connection to autocommit mode. Read the manual before posting here. — Daan
Which MySQL storage engine does your dms_test table use? The MyISAM storage engine lacks support for transactions. That could explain your issue. — O. Jones
"Everyone sane would expect" is getting awfully personal. This is just free software (free as in kittens, free as in beer) we're talking about here. With respect, lighten up! — O. Jones
Daan, with all the respect, before asking me to read the manual, please read my post in the first place. Auto-commit mode was never enabled, it is turned off with connection attributes (see point #1). Secondly you also haven't read that I said, that the same code on Oracle works fine (see point #6). Thirdly the problem is that MySQL RDBMS returns result set (where it shouldn't in the same transaction, -- note the DELETE is before the SELECT). Also nor COMMIT, nor ROLLBACK was issued during this example. Ollie Jones, no of course I'm not using MyISAM as it lacks basic ACID compliance. — Globetrotter
I can't see anything particularly wrong in your code, nor can I imagine a scenario where such thing happens. May I suggest you log queries just to discard obvious problems? — Álvaro González

Globetrotter Globetrotter · Accepted Answer · 2014-08-04T18:34:00

I was able to solve the problem by completely removing PDO's transaction mechanism and replacing it with my own. Apparently using PDO transaction mechanism with MySQL RDBMS and auto-commit mode disabled may cause unpredictable behavior. I don't know if this is a PDO or MySQL bug.

If you want to implement out-of-the-box transactional access to database using PDO do not use PDO's built-in PDO::beginTransaction(), PDO::commit() and PDO::rollback() methods.

Instead I suggest you to use the following approach:

When establishing connection, use attribute PDO::ATTR_AUTOCOMMIT => FALSE
Keep track of in-transaction state by declaring your own variable for this purpose (e.g. $in_transaction)
Register shutdown function that calls native database ROLLBACK at the end of the request (if in-transaction state)

Using this approach I was able to overcome the above mentioned bug.

Data is still visible after deletion within the same MySQL transaction

2 Answers