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
Welcome back! If you're the original bug submitter, here's where you can edit the bug or add additional notes.
If you forgot your password, you can retrieve your password here.
Password:
Status:
Package:
Bug Type:
Summary:
From: pau dot ferran dot grau at gmail dot com
New email:
PHP Version: OS:

 

 [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: Tue Dec 03 08:01:28 2024 UTC