On July 22, every production mutation sent through our internal SQL tool’s --write path rolled back when the connection closed. The command executed its SQL and returned without an exception. What did not happen was the only part anyone needed from a write: the requested change was not there.
I had shipped a path that could report that a statement ran, but could not prove that its effect survived the process. That distinction is easy to miss in a command line tool. Reads still look normal. The SQL can be valid. The process can finish cleanly. Then the next read arrives and the mutation has vanished.
The failure was at the transaction boundary
The bug was not in the statement. It was in the gap between execute() and a durable result.
The write path used pyodbc. Its connection default was autocommit=False. That setting means a successful statement is not its own completed unit of work. It belongs to an open transaction until the caller explicitly commits it. Our command ran the mutation, reached the end of its connection lifetime, and closed without cn.commit(). The driver silently rolled back every apparently successful production mutation.
The relevant control flow, with configuration removed, was this:
cn = pyodbc.connect(...)cursor.execute(sql)cn.commit()The missing line was the architecture. Before the fix, the command’s visible contract was, “I sent a statement.” Its required contract was, “The intended state now exists.” Those are not remotely equivalent when autocommit is off.
What the missing side effect told me
The diagnostic path started with absence, not a stack trace. A write returned normally, but its side effect was missing. That initially leaves plenty of places to look: the wrong target, a malformed predicate, an application layer that overwrote the value later, or a command that never actually issued its query.
The consistent pattern narrowed it fast. This was not one bad record or one rejected value. Every production mutation through the --write path had the same shape. There was no raised error to explain away. The process simply ended, and the database state matched the pre-write state.
From there, the sequence was mechanical. I traced the path from the command through pyodbc, checked the connection’s autocommit=False behavior, and followed what happened at close. The missing commit was not an intermittent operational condition. It was a permanent hole in the tool’s lifecycle.
That distinction matters because a silent rollback looks deceptively healthy from inside the process. If the only signal you inspect is whether execute() returned, you have tested statement dispatch, not persistence. The actual failure was visible only outside that boundary, after the connection was gone and a later read could not find the intended change.
Why I added an explicit commit
There were two coherent transaction policies available. One is to connect with autocommit=True, making each statement durable immediately. The other is to keep autocommit=False and name the point where a transaction becomes durable. The tool was already operating under the second policy, whether I had acknowledged it or not.
Flipping on autocommit would have changed the transactional policy for every statement. Adding cn.commit() completed the policy the connection was already using. It made the write boundary explicit, local to the command that owned the mutation, and reviewable in code.
That placement matters. A driver default is easy to treat as plumbing, especially when a command is small and its SQL returns normally. In practice it defines the command’s production semantics. Leaving durability implicit required every caller and reviewer to remember that a clean close rolls work back. Putting the commit next to the mutation makes the lifecycle legible where the decision is made.
That is why the fix was a commit call, not a retry, an extra log line, or a broader exception handler. None of those changes would turn an uncommitted transaction into persisted state. A retry can repeat the same rollback. Logging can confirm that a statement ran. A catch block can make a failure quieter. Only the transaction owner can establish the durability boundary.
The correction shipped in the hotfix commit recorded for that incident. More importantly, we redefined what a successful write means for this tool. It is not a clean exit. It is not a cursor that accepted a statement. It is a state change that remains true after the connection closes.
A write tool needs a proof point
The operational test for this class of bug is simple, although it is not optional: run the mutation, end the connection, then verify the resulting state through a fresh read. A test that stops at the return value can certify a command that has already undone its own work.
That check belongs with the tool, not only with the operator who happens to notice a missing side effect. A write path should have one explicit owner for commit(), one definition of success, and a verification step that crosses the same process boundary production will cross.
This bug took one line to fix. It was still an architecture problem. We had allowed our command to report success at the wrong layer of the system. The database was correct to roll back work nobody committed. Our interface was wrong to make that outcome look like a completed write.