1

We are running basic web app on PHP 7.0 and MariaDB 10.0. Every query goes into the database via PDO class.

The problem is that PDO sometimes throws deadlock exception:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction

But when I look into MariaDB for deadlock specifications (using SHOW ENGINE INNODB STATUS), the "last deadlock" is not the real last deadlock. For example there is shown deadlock from 2019-10-01, but PDO alerted to me last deadlock on 2019-10-05. It's like the PDO is making up deadlocks. But again - it's not every time. And MariaDB property "Innodb deadlocks" (int) is not increasing, when the last deadlock is not shown in "SHOW ENGINE INNODB STATUS".

It is happenning just for one transaction at this time. All tables in the transaction run on InnoDB engine. It's about 40 queries (select, update, insert). MariaDB property "innodb print all deadlocks" is on. There is only one database server available to connect.

Is PDO just reporting all errors from database or can it "make up deadlocks"? Or could it be problem of old versions of PHP and MariaDB? We are planning to upgrade, but not right now.

And just for sure - I'm not looking to solve this current deadlock, but this whole abnormality.

EDIT: I found out, that this current deadlock was not "made up" by PDO. It was real deadlock, but the problem was query with "TRUNCATE" in other transaction (cron job).

Long description:

Let's assume table x1 and table x2 referencing to x1 primary key. The story was:

  1. first transaction: insert row into table x1;
  2. second transaction: TRUNCATE table x2; (waiting for lock)
  3. first transaction: update the inserted row at table x1;

And the third step caused deadlock. Even though the primary key from inserted row of table x1 wasn't really in table x2. Table x2 was just referencing to x1.

Martin K
  • 21
  • 4
  • See [this post](https://stackoverflow.com/questions/2332768/how-to-avoid-mysql-deadlock-found-when-trying-to-get-lock-try-restarting-trans) for overall solutions – Jamie_D Oct 11 '19 at 10:04
  • Thank you for answer, but my problem is about MariaDB "not remembering" some deadlocks, that PDO showed. I know, how a deadlock is created. Well.. mostly :-D – Martin K Oct 11 '19 at 10:25
  • Try setting the system variable [innodb_print_all_deadlocks](https://mariadb.com/kb/en/library/innodb-system-variables/#innodb_print_all_deadlocks) . This will print to the MariaDB configured error log. – Jamie_D Oct 11 '19 at 10:37
  • I already said that this variable is enabled :-) And I have checked the MariaDB log and it's the same like "SHOW ENGINE INNODB STATUS" - the deadlock is not there. – Martin K Oct 11 '19 at 18:00
  • That transaction isolation mode are you using? – Rick James Oct 17 '19 at 05:09
  • We are using REPEATABLE-READ mode. – Martin K Oct 18 '19 at 06:22

1 Answers1

0

If you are TRUNCATEing as a step in replacing the contents, then change to:

CREATE TABLE x LIKE real;
load data into x
RENAME TABLE real TO old,
             x TO real;
DROP TABLE old;
Rick James
  • 135,179
  • 13
  • 127
  • 222