php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #80924 PDO: Begin transaction plus lock table fail
Submitted: 2021-03-31 20:20 UTC Modified: 2021-03-31 21:21 UTC
From: pau dot ferran dot grau at gmail dot com Assigned:
Status: Not a bug Package: PDO MySQL
PHP Version: 8.0.3 OS: All
Private report: No CVE-ID: None
 [2021-03-31 20:20 UTC] pau dot ferran dot grau at gmail dot com
Description:
------------
when I begin a transaction and I try lock a table then pdo throw the exception with the message: There is no active transaction.


I use a MySQL 5.7.20

Test script:
---------------
try {
    $dbh =  new \PDO('mysql:host=127.0.0.1;port=3309;dbname=event_sourcing_test', 'root', null);
    $dbh->beginTransaction();

    $dbh->exec('LOCK TABLE test WRITE');
    $dbh->exec("INSERT INTO test (id) VALUES ('a')");

    $dbh->commit();

    $dbh->exec('UNLOCK TABLES');

} catch (\PDOException $e) {
    
    echo $e->getMessage() . PHP_EOL; //There is no active transaction
}


Expected result:
----------------
Pdo doesn't throw an exception

Actual result:
--------------
Pdo throws an exception

PDOException Object
(
    [message:protected] => There is no active transaction
    [string:Exception:private] =>
    [code:protected] => 0
    [file:protected] => /Users/pau/Projects/apps/event-sourcing/test.php
    [line:protected] => 10
    [trace:Exception:private] => Array
        (
            [0] => Array
                (
                    [file] => /Users/pau/Projects/apps/event-sourcing/test.php
                    [line] => 10
                    [function] => commit
                    [class] => PDO
                    [type] => ->
                    [args] => Array
                        (
                        )

                )

        )

    [previous:Exception:private] =>
    [errorInfo] =>
)

Patches

Pull Requests

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2021-03-31 20:31 UTC] levim@php.net
I am not an expert in databases. Based on my reading of the docs, transactions and locking are not particularly compatible:

https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions
 [2021-03-31 20:39 UTC] danack@php.net
Reading the manual, I think your code may be wrong: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html#lock-tables-and-transactions

"LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables."

So you'd need to beginTransaction after the lock tables, would be my guess.
 [2021-03-31 20:48 UTC] pau dot ferran dot grau at gmail dot com
Try to imagine this scenario:

You have an app and a usecase. Your usecase in executed in transactional mode and some vendor lock a table. 

This scenario with php 8 always will fail. (with php <8 it will run well)
 [2021-03-31 20:54 UTC] nikic@php.net
-Status: Open +Status: Not a bug
 [2021-03-31 20:54 UTC] nikic@php.net
Use if ($dbh->inTransaction()) $dbh->commit(); if you intentionally want to ignore commits without an active transaction.

The changed transaction handling for PDO MySQL is noted in https://www.php.net/manual/en/migration80.incompatible.php#migration80.incompatible.pdo-mysql.
 [2021-03-31 21:21 UTC] pau dot ferran dot grau at gmail dot com
First of all thank you for your comments guys!

About the comment of Nikita

I always think I have an active transaction because my use case is in transactional mode.

I used `dbh->inTransaction()` and it worked as expected.

I'm working with doctrine dbal and not work `isTransactionActive` but this is another battle.

I think we can close this issue if everyone think the app need manage every commit.
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Sat Dec 14 02:01:27 2024 UTC