We’ve been having some Oracle deadlock issues that have been hard to reproduce locally. After a lot of investigation and solving of important problems that happened not to be THE problem we figured out that, while we’ve been pretty good creating integrity constraints in the database, we have not been good about making sure that every foreign key has a corresponding index. And that can lead to problems.

So we had a situation were our documents table had a foreign key on the accounts table that was not indexed. So updating an account row lead to a whole table lock on documents (instead of just a row lock which would have happened if there was an index) and that was very bad when we had two separate processes where one was doing a bunch of accounts stuff and the other was doing a lot of documents stuff. Deadlocks for everyone!

The sad thing is that if we had just drank the Rails cool-aid about having no integrity constraints in the db we would have been fine but we got into trouble by only implementing constraints and not the indexes they work much better with.

So we found this cool bit of SQL that helped us find all the foreign keys missing an index here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4530093713805#26568859366976

And that help us solve the deadlock problem. But what about the future? If only there was a way to run this check periodically and automatically… Time for a test!

As an additional bonus, the failing output of the test tells you how to write the index you need. Big thanks to Dave Bortz for tracking down this problem – I just swooped in and wrote the test. And took credit in this here blog.