3

I have a MariaDB (5.5.41) cluster made of 2 nodes configured as master-slave. All reads and writes are sent to the same node.

I have been investigating some deadlock issues for a few weeks.

On a regular basis, my PHP application returns Message: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction.

I used to be able to run SHOW ENGINE INNODB STATUS; and would see that last deadlock, but for some reason, after a small irrelevant configuration change (changing innodb_buffer_pool_instances from 1 to 19), and a reboot of both nodes, doing a SHOW ENGINE INNODB STATUS; will not show any deadlock.

However, if I connect with my mysql client and manually create transactions resulting in a deadlock, the status command does show the deadlock.

I tried playing innodb_print_all_deadlocks ON and OFF. Nothing shows in the mysql-error.log, except for my manually triggered deadlock.

Why are the deadlocks created by my PHP application not showing anymore?

liquidity
  • 418

3 Answers3

1

I don't think I can answer your question directly, given my lack of access to your systems and the information given. However, here are some AWESOME tools I've used to get a better handle on all varieties of MySQL-derived databases I've been charged with administering.

InnoTop: https://github.com/innotop/innotop

Check out the "D" command in the innotop man page:

       D: InnoDB Deadlocks
           This mode shows the transactions involved in the last InnoDB
           deadlock.  A second table shows the locks each transaction held and
           waited for.  A deadlock is caused by a cycle in the waits-for
           graph, so there should be two locks held and one waited for unless
           the deadlock information is truncated. [...]

The "K" and "L" commands are also potentially relevant to you.

NOTE: innotop, to be fully useful, may need to change schema information and settings, and add a 'test' database to gather information. READ THE ENTIRE MAN PAGE to know what you are getting into before blindly changing your database. (Personally, I love the extra information the changes innotop unveils...)

Less directly relevant to your lock problem, but very useful, nonetheless:

The Percona Toolkit (formerly MAATKIT): https://www.percona.com/software/database-tools/percona-toolkit

Good luck!

Jesse Adelman
  • 1,008
0

It is quite puzzling that show engine innodb status is not giving you the required deadlock information. You can however check for the deadlocks by running mysqladmin debug, which logs all locks and also the LOCK TABLE locks which are not shown by show engine innodb status in this case.

These issues sometimes present in at wrong times and it wastes a lot of time. I personally use Monyog to monitor which also does this. You can try using its trial if nothing works.

Veer Abheek Singh Manhas
  • 113
-1

Things to do in [mysqld] section of my.cnf/ini 

innodb_print_all_deadlocks=ON  # for error log documentation & be proactive in correcting.
log_error=(a valid filename)  # to write to  RTM
innodb_buffer_pool_instances=8  # from 19 would be adequate RAM overhead
Wilson Hauck
  • 640