Data remains visible after deletion within a single MySQL transaction
I recently tested my PHP database wrapper class based on PHP data objects. I have successfully passed all tests with an Oracle database and started running tests with MySQL when I encountered an error that seems like an ACID nightmare.
In short, my database wrapper 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) The Wrapper class has a method execute()
that is the basis for running various SQL statements. When executing an SQL statement using a method, execute()
it checks if a transaction is active using the method PDO::inTransaction()
. If not, the transaction starts. This is what the method looks like (skipping all the 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 consider the following example, which invokes a DELETE statement followed by a SELECT statement on the same table where the conditions are:
database :: execute ('DELETE FROM dms_test WHERE id = 5');
$ data = database :: execute ('SELECT FROM dms_test WHERE id = 5');
4) Everyone would expect the SELECT statement to return an empty result set, since the previous DELETE statement simply destroyed all data in a single transaction.
5) But as crazy as it may sound, the SELECT statement returns a non-empty result, as if the DELETE statement had never been issued.
6) Interestingly, the same example works as expected in an Oracle database.
Any ideas what is wrong with MySQL? Did any of you have similar problems?
source to share
I managed to solve the problem by completely removing the PDO transaction mechanism and replacing it with my own. Apparently using the PDO transaction mechanism with MySQL RDBMS disabled and auto-commit disabled can lead to unpredictable behavior. I don't know if this is a PDO or MySQL bug.
If you want to implement out-of-the-box transactional database access using PDO, do not use built-in methods PDO::beginTransaction()
, PDO::commit()
and PDO::rollback()
for PDO.
Instead, I suggest you use the following approach:
-
When establishing a connection, use the attribute
PDO::ATTR_AUTOCOMMIT => FALSE
-
Monitor the status of the transaction by declaring your own variable for this purpose (for example
$in_transaction
) -
Register a shutdown function that calls its own database
ROLLBACK
at the end of the request (if in a transactional state)
Using this approach I was able to overcome the above error.
source to share