No PDOException from PDO MySQL transaction commit during DB rollback because of deadlock

Description

We had an issue where mysql detected a deadlock and made a rollback but rollback information never reached on the caller client.

During transaction we print the id of inserted rows but when we search those IDs on DB they are missing.

Seems related to these:

Our code on yii for saving data on DB through transaction

<?php

 // Start transaction
 $dbConnection = Yii::$app->db;

 // Transaction start
 $dbTransaction = $dbConnection->beginTransaction();

 try {
   echo 'Start saving';

   // Some yii model saves...

   echo 'Save complete, start committing changes';

   // Commit
   $dbTransaction->commit();

   echo Changes committed successfully 

 } catch (\Throwable $e) {

   echo 'Transaction failed, rolling back';

   $dbTransaction->rollback();
 }

Also, we are using \PDO::ATTR_EMULATE_PREPARES = true. Another bug report mentions that the issue is only appeared when \PDO::ATTR_EMULATE_PREPARES = false but we have the same issue with true.

Resulted in this behaviour:

The application code report that the transaction is successful through 1) $pdo->errorCode() [which was '00000'] 2) and from $pdo->errorInfo() [which was {"00000", null, null} ]

So the logs told us that the actual commit to DB was successful and all of our data was inserted. But in reality those data missing from DB and the mysql log reported a deadlock and a rollback

=====================================
2022-05-26 16:07:05 140231679018752 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 678 srv_active, 0 srv_shutdown, 2876 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1244
OS WAIT ARRAY INFO: signal count 5429
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2022-05-26 16:03:31 140231695804160
*** (1) TRANSACTION:
TRANSACTION 30778934, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 17 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 19
MySQL thread id 1188, OS thread handle 140231625492224, query id 344509 172.19.0.9 routemeuser updating
UPDATE `project_problem` SET `modified`=1, `modification_datetime`=UTC_TIMESTAMP() WHERE `id`=4715

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3749 page no 21 n bits 120 index PRIMARY of table `db`.`project_problem` trx id 30778934 lock mode S locks rec but not gap
Record lock, heap no 47 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 4; hex 0000126b; asc    k;;
 1: len 6; hex 000001d5a639; asc      9;;
 2: len 7; hex 01000001a70615; asc        ;;
 3: len 4; hex 0000010f; asc     ;;
 4: len 5; hex 99acf62780; asc    ' ;;
 5: len 2; hex 7861; asc xa;;
 6: len 1; hex 00; asc  ;;
 7: len 1; hex 07; asc  ;;
 8: len 1; hex 01; asc  ;;
 9: len 1; hex 00; asc  ;;
 10: len 1; hex 80; asc  ;;
 11: len 1; hex 80; asc  ;;
 12: len 5; hex 99acf5004d; asc     M;;
 13: len 5; hex 99acf500df; asc      ;;

But i expected this output instead: The expected behaviour it would be that the $dbTransaction->commit(); must throw an exception in for us to catch it and report that the request failed.

P.S The try-catch code for commiting changes to DB is working perfectly fine when other errors occur during commit, eg when trying to delete a row where its PK is referenced somewhere else.

PHP Version

PHP-8.1.6

Operating System

No response

php / php-src