Lock wait timeout causing subsequent saves to fail

Question

But of an odd one this, wondering if anyone else has come across it. I am running into strange behaviour when saving a number of new models in a row. If the first model fails to save due to a lock wait timeout on the database (and the exception is caught), all subsequent saves on the other models then fail even with the lock removed. There is no indication that data has not been saved and the transaction appears to finish completely normally and successfully, but no data is inserted into the database.

Now the auto increment id column is incremented in line with the number of failed saves on the models suggesting rolled back transactons, but there doesn't appear to be any transaction roll back occurring but no data is inserted into the database. Additionally when doing exactly the same with identical data for all models but with no lock, everything is saved fine and appears in the database as you would expect.

I'm sure I'll get to the bottom of this but thought I would ask in case anyone else can put there finger on the problem.

Answer 1

Figured it out. Long story short - when the exception was being thrown a core class had no error handling, but this same class was, before the exception, opening a transaction but the exception stopped the transaction completing. This left an open transaction for all remaining saves and because after these saves the transaction wasn't completed with a commit or rollback MySQL auto rolled back removing all changes.