Bug #80924 [Nab]: PDO: Begin transaction plus lock table fail
Edit report at https://bugs.php.net/bug.php?id=80924&edit=1
ID: 80924
User updated by: pau dot ferran dot grau at gmail dot com
Reported by: pau dot ferran dot grau at gmail dot com
Summary: PDO: Begin transaction plus lock table fail
Status: Not a bug
Type: Bug
Package: PDO MySQL
Operating System: All
PHP Version: 8.0.3
Block user comment: N
Private report: N
New Comment:
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.
Previous Comments:
------------------------------------------------------------------------
[2021-03-31 20:54:04] 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 20:48:05] 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:39:13] 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:31:27] 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:20:55] 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] =>
)
------------------------------------------------------------------------
--
Edit this bug report at https://bugs.php.net/bug.php?id=80924&edit=1
Thread (6 messages)