Showing posts from December, 2010

Oracle Foreign Key without Index Test

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 very 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 …