Ruby on Rails and inconsistent database results

I started stressing a RoR project that has grown pretty big. Some serious hands on testing was showing that functionality was working well, performance was fine, but sometimes I would just get weird results from the database or ActiveRecord. I would create a new AR object, save it, use it a few times, update it, then it would suddenly just disappear. I would start getting ActiveRecord::RecordNotFound exceptions doing a Thingy.find(1234), when thingy#1234 definitely existed in the database. It would take a restart of Phusion Passenger or for one of the workers to timeout before I would start seeing the object again, and if I refreshed a page with Thingy.all(:conditions=>c) the results would change, then change back. I'm using MySQL so its not exactly what I was expecting to see.

I had issues in the past with some forking of processes that could just run through to conclusion in the background - they were removed. I made sure that there were good Thingy.transaction do end blocks covering my updates. Still, things were getting worse, not better.

Eventually I ended up hunting around the code from the dim and distant past. That stuff I don't touch because it "just works". Well, I roll up to an interesting section in a class :


 sql = ActiveRecord::Base.connection();
      sql.execute "SET autocommit=0";
      sql.begin_db_transaction
      sql.delete 'delete from a_table where some_conditions' 
      sql.update sqlstring
      sql.commit_db_transaction


This was valid, as the SQL going on in the sqlstring was complex to say the least. But since I've removed this from the main flow of the application things seem to have settled down considerably.

I'm guessing that my standard transactions were getting caught up in my attempt to borrow a connection from the pool explicitly and who knows what was happening. Or maybe Passenger was losing its connection and recreating a connection. I don't know, but I'm not doing it again!