Why are my deadlocks not shown by SHOW ENGINE INNODB STATUS;?

Question

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?

Solution

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!

Comments

[rich remer]

I'm experiencing the same on Maria 10.1. Super frustrating.

[liquidity]

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.

[Jesse Adelman]

@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?

[liquidity]

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.

[Jesse Adelman]

@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. :)

[liquidity]

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.

[Jesse Adelman]

Yes, it does rely on the output of that command. However, that command output is dependent on the configuration of your database and it's environment. That is what the man page shows innotop handles...

[Jesse Adelman]

I am trying to help you solve your own problem, since you have not given enough information here to allow us to. You cannot easily solve your problems if you refuse to use the tools made available for you to do so.

[liquidity]

My question indicates that I am able to see some deadlocks with "SHOW ENGINE INNODB STATUS", which would imply that my configuration is valid. But even then, "SHOW ENGINE INNODB STATUS" isn't normally bound to any specific configuration and should work out of the box. Whatever innotop does, it will not change the fact that the status command won't output the deadlocks generated by my php application. What information do you need that I am not providing? I'm happy to provide.

[liquidity]

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

[Wilson Hauck]

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]

Your error.log will have each deadlock listed.

[liquidity]

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.

[Wilson Hauck]

Would you consider posting complete text results of SHOW GLOBAL VARIABLES LIKE 'version%'; and SHOW GLOBAL VARIABLES LIKE 'innodb_print_all%'; ? Thanks