Your Database Change Needs To Be Committed!

It was a harrowing situation. Most of my development team was gone for the day and I was trying to swing back into action.

The customer was unable to work with their equipment, and I was pretty sure I knew the culprit. With a tip from the UI lead, I found the chunk of code that had the bug, replicated the SQL being fired back to the database, and was very confident in the update statement that would solve the problem– and it only took an hour to get there!

Nothing.

Oh, a SELECT query showed my changes, but the website still wasn’t showing the correct option! I brought in other developers to take a look at my logic, showing the function call in the UI all the way back to the database and what should be working, only it wasn’t.

My problem? I hadn’t used a COMMIT statement to commit my changes.

I worked in Oracle in the past– back some nine years ago now– and my most recent experience is in MySQL, which auto-commits every transaction. I didn’t even think to run a COMMIT, and that was my fatal flaw. Fortunately (or unfortunately?), this table is rarely changed, so there were no blocking transactions to give us a clue of my mistake.

Manual commits are great for Transactions when you want to make sure that everything works before committing the entire process. They’re even good for helping to prevent a rogue update statement that has too few where clause items. But when you come from the auto-commit world they feel redundant.

Fortunately, I ran the COMMIT statement, checked the browser, and the bug was fixed. I’m writing this, in part, to remember that I need to COMMIT next time I INSERT or UPDATE in Oracle.