The Commit That Wasn't: When autocommit=False Rolls Back Silently

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.

We had shipped a path that could report a statement ran, and could not prove the effect outlived the process. Reads still looked normal. The SQL was valid. The process finished cleanly. Then the next read arrived and the mutation had vanished.

The failure sat at the transaction boundary

The statement was fine. The problem was the gap between execute() and a durable result.

The write path used pyodbc, whose connection default is autocommit=False. With that setting a successful statement is not a finished unit of work. It belongs to an open transaction until the caller explicitly commits. Our command ran the mutation, reached the end of the connection’s lifetime, and closed without cn.commit(). The driver rolled back every apparently successful production mutation and said nothing.

The control flow, with configuration removed, should have been:

cn = pyodbc.connect(...)
cursor.execute(sql)
cn.commit()

The missing line was the whole bug. Before the fix, the command’s visible contract was “the command sent a statement.” The contract we needed was “the intended state now exists.” With autocommit off, those are very different promises.

Diagnosing from an absence

There was no stack trace to start from. A write returned normally and its side effect was missing. That leaves a lot of suspects: the wrong target, a malformed predicate, an application layer overwriting the value later, or a command that never issued its query.

The 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. No error was raised, the process ended, and the database matched its pre-write state.

From there the work was mechanical. We traced the path from the command through pyodbc, checked how the connection behaved with autocommit=False, and followed what happened at close. The missing commit was not an intermittent condition. It was a permanent hole in the tool’s lifecycle.

A silent rollback looks healthy from inside the process. If the only signal you check is whether execute() returned, you have tested statement dispatch and learned nothing about persistence. The failure only showed up after the connection was gone, when a later read could not find the change.

Why the fix was an explicit commit

Two transaction policies were available. One is to connect with autocommit=True, so each statement is durable immediately. The other is to keep autocommit=False and name the point where a transaction becomes durable. The tool was already running under the second policy, whether or not we had acknowledged it.

Turning on autocommit would have changed the policy for every statement. Adding cn.commit() finished the policy the connection was already using. It put the write boundary in the command that owned the mutation, where a reviewer can see it.

A driver default is easy to treat as plumbing, especially in a small command whose SQL returns normally. In practice it defines the command’s production semantics. Leaving durability implicit meant every caller and reviewer had to remember that a clean close rolls work back.

That is why the fix was a commit call, with no retry, extra log line or broader exception handler. A retry can repeat the same rollback. A log line can confirm a statement ran. A catch block can hide a failure. Only the owner of the transaction can establish the durability boundary.

The correction shipped in the hotfix commit recorded for that incident. We also redefined what a successful write means for this tool. A clean exit does not count, and neither does a cursor that accepted a statement. A write succeeded when the state change is still true after the connection closes.

A write tool needs a proof point

The test for this class of bug is simple: run the mutation, end the connection, then verify the result 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, so it does not depend on whoever 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.

The fix took one line, and it was still an architecture problem. Our command reported success at the wrong layer. The database was right to roll back work nobody committed. Our interface was wrong to make that outcome look like a completed write.

A write that returns without an error may not have changed anything. For each internal data tool you own, find out how it proves, from a fresh read after the connection closes, that a change persisted, and make that check part of the tool.