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?

1
  • 1
    I'm experiencing the same on Maria 10.1. Super frustrating. – rich remer Jan 4 '19 at 7:52
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!

8
  • Thanks for the tools, Jesse. I can't however accept the answer as it doesn't solve my exact issue. The "D" command uses "SHOW STATUS..." and as such won't work, the "L" command might be useful though. – liquidity Jun 5 '17 at 19:02
  • @liquidity The changes to the MySQL environment that InnoTop makes should do what is needed to get SHOW STATUS working for you. Are the deadlock tables created? – Jesse Adelman Jun 6 '17 at 2:50
  • if you read my question carefully, you'll notice that I do see some deadlocks: the ones I create manually. What innotop does is read the result of the "SHOW STATUS..." command every x seconds and print out the deadlock section. This assumes that "SHOW STATUS" works, which, in my case, doesn't. – liquidity Jun 6 '17 at 17:41
  • @liquidity I encourage you to read the man page for innotop. You are making assertions about something which you need more information before making conclusions. Better yet, just install it and try it. :) – Jesse Adelman Jun 6 '17 at 22:01
  • I also encourage you to read the manpage as it specifically indicates that innotop bases its output on "SHOW INNODB STATUS ...". Re-reading my original question carefully or ask for clarification if something's unclear might help too. – liquidity Jun 6 '17 at 22:27
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.

-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
6
  • Like my question says: I tried playing innodb_print_all_deadlocks ON and OFF. Nothing shows in the mysql-error.log, except for my manually triggered deadlock. As for innodb_buffer_pool_instances, this parameter is tuned according to my needs and has no bearings as far as deadlocks are concerned – liquidity Feb 13 '18 at 21:58
  • OK, to have predictability, in your my.cfg/ini [mysqld] section, have a single line addressing innodb_print_all_deadlocks=ON and SHOW STATUS needs to be SHOW GLOBAL STATUS; for any reasonably current version. SHOW ENGINE INNODB STATUS; will list the LAST deadlock. – Wilson Hauck Feb 13 '18 at 22:11
  • Your error.log will have each deadlock listed. – Wilson Hauck Feb 14 '18 at 0:57
  • Have you considered that using 19 innodb_buffer_pool_Instances may be the reason you can not SHOW ENGINE INNODB STATUS: at this time? – Wilson Hauck Feb 14 '18 at 1:16
  • I do not believe innodb_buffer_pool_instances set to 19 to be the issue as I am not aware of this setting having any impact on the "SHOW ENGINE..." command. As for innodb_print_all_deadlocks, like stated in my question, it had no effect as far as showing deadlocks is concerned. As for "SHOW ENGINE INNODB STATUS; will list the LAST deadlock", like stated in my question: it does not. Why? That's what I'd like to figure out. – liquidity Feb 14 '18 at 19:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.