DEV Community: Paulo Antunes

The rollback that only rolled back half of it

Paulo Antunes — Fri, 18 Sep 2026 19:14:29 +0000

I was testing a new screen against the production ERP database: a box comes back from the customer, and the screen has to release the material inside it so it can be scanned into a new order. Two writes, one transaction, and a ROLLBACK at the end so the rehearsal wouldn't leave a trace.

The rollback ran. No error. And half of it stayed.

The item table was back to its old state. The volume table was not — the write had persisted, in a transaction I had just explicitly undone.

The database is not one database

The schema is old. It has grown for years around a Delphi ERP that runs a real factory, and over that time it has been touched by people with different defaults. Some tables were created in the MyISAM era. The newer ones are InnoDB.

MyISAM has no transactions. An UPDATE against a MyISAM table is applied the moment it runs. Wrapping it in BEGIN doesn't do anything, COMMIT doesn't do anything, and ROLLBACK doesn't do anything either. It does not fail, and it does not warn you. The transaction simply doesn't include that table.

So in a routine that writes to both families, there is no "all or nothing". There are two independent writes, one of which is a point of no return, and the illusion that you have a transaction around them.

The field that decides is not in the document I trust

This is the part that still bothers me.

I keep an exported snapshot of the schema in the repository — JSON, 342 tables, 6,477 columns, every index. I use it constantly, and it's the file my CLAUDE.md tells the AI agent to consult before it writes any SQL, precisely so nobody invents a column name from memory.

It has tables. It has columns. It has indexes. It does not have the storage engine.

$ grep -ic engine docs/db/schema.json
0

The artifact I treat as the source of truth about the database is silent about the one property that determines whether my transaction is real. Nothing in the application layer fills that gap: SQLAlchemy commits happily, the driver reports success, the row count comes back correct. The only thing that tells you is asking the server directly.

SELECT TABLE_NAME, ENGINE
  FROM information_schema.TABLES
 WHERE TABLE_SCHEMA = DATABASE()
 ORDER BY ENGINE, TABLE_NAME;

Run it on any schema old enough to have outlived a MySQL version upgrade. It takes ten seconds, and the answer can be a surprise.

The four rules I write with now

You can't get atomicity back. MyISAM tables in a running ERP are not something you convert on a Tuesday afternoon — the Delphi application writes to them, some are big, and the conversion is a separate project with its own risk. So the routine has to be correct without a transaction.

1. Validate everything before writing anything.
The check that decides whether the operation is allowed runs first, over all affected rows, and it's all-or-nothing on its own. A box can hold volumes from more than one order; if any of them fails the rule, nothing is released — and the screen shows exactly which row blocked it. Every guard is also repeated in the WHERE of both UPDATEs, so the write can't drift from the check.

2. Order the writes: reversible first, irreversible last.
This is the whole trick. Within the pair of writes, one table can be rolled back and the other can't. So the InnoDB write goes first, commits, and only then does the MyISAM write run.

# 1) items (InnoDB): reversible. Frees the unique code that blocks re-scanning.
try:
    items = db.session.execute(text(SQL_RELEASE_ITEMS), params).rowcount
    db.session.commit()
except Exception:
    db.session.rollback()
    raise

# 2) volume (MyISAM): NOT reversible. Runs only after the first one is committed.
try:
    volumes = db.session.execute(text(SQL_RELEASE_VOLUMES), params).rowcount
    db.session.commit()
except Exception:
    db.session.rollback()   # honest no-op here, kept for symmetry
    raise

Two blocks instead of one is not a style choice. It's the shape of the guarantee.

3. Choose which half-finished state you want to be left with.
Ordering the writes means deciding, in advance, what the world looks like if the process dies between them. Here it's "item codes are free, volume still points at the box". That's the benign side: the thing that actually blocks reuse is a unique index on the item code, and it's already released. The leftover is a stale pointer, visible and fixable. The other order would have left the operator with a box that looks released but still refuses every scan — a support call nobody can diagnose.

Pick the failure state the same way you'd pick an error message: for the person who will meet it.

4. Make re-running it safe.
Since the operation can stop halfway, the fix has to be "do it again". Both statements are idempotent — they set values to NULL under conditions that are still true — so running the screen a second time completes what the crash left behind, instead of doing damage on top of it.

It isn't one weird table

I thought I'd found a quirk. I'd found a property of the schema.

A completely different routine — the finishing line, where an operator types a work order and the system pulls the next item from the production queue — has the same shape: the queue table is InnoDB and takes a real SELECT ... FOR UPDATE lock, while the stock-entry table is MyISAM, so the rollback doesn't undo the stock entry. Same reasoning, same ordering decision: the stock stays right and the traceability lags, which is the safe direction of the error.

And a third routine, a quality checkpoint, writes to three tables that all happen to be InnoDB — so a single commit genuinely covers the set, and I wrote it as one block. That's the point: the answer is per-table, every time. "Is this atomic?" has no schema-wide answer here.

The engine changes the deployment story too. Adding an index to a MyISAM table rebuilds and locks the whole table, so that migration is scheduled outside scanning hours — the same ALTER on InnoDB would have been routine.

If you don't have a legacy MySQL schema

You probably still have this problem.

Write to Postgres and S3 in the same request, and there's no transaction around the pair. Insert a row and publish to a queue — same thing. Call a payment API and update your own record — same thing. Every one of those is "two stores, no shared transaction", and the four rules transfer without modification: validate first, order reversible before irreversible, choose the benign half-state on purpose, make retry safe.

The mixed storage engines just make it impossible to pretend otherwise. ROLLBACK looks like it's protecting you right up until you check.

Which is why my rehearsals now start with a query against information_schema, and why the next thing I want to add to that schema export is one more column.

Python beside Delphi, not instead of it

Paulo Antunes — Wed, 16 Sep 2026 19:59:05 +0000

I maintain an ERP that runs a footwear factory. It is a Delphi VCL desktop application: 488 units, roughly 160,000 lines of Object Pascal, sitting on a MySQL database of 342 tables. It handles production planning, warehouse management, shipping, inventory and sales. It has been in production for years, and the factory does not stop so that I can refactor.

I am the only engineer on it. There are other developers in the company, but nobody else touches this system.

Every conversation about a codebase like this eventually arrives at the same place: when are you rewriting it? The honest answer is never, or at least not as a project with a start date. A rewrite means running two systems in parallel for years while the business keeps changing under both. That is not a technical problem I can solve alone.

So Python did not arrive to replace the Delphi. It arrived beside it. Nothing in the Delphi build changed. Not one unit, not one dependency. The Python lives in a tools/ directory and never ships to a single user's machine.

Here is what it actually does.

1. It makes the schema a file

The first thing I wrote was a schema dumper. It connects to MySQL and writes the entire structure — tables, columns, types, indexes — to a single JSON file that lives in the repository.

That sounds trivial. It was the highest-leverage hundred lines in the project.

Before it existed, the answer to "what columns does this table have?" was a round trip to a database client, and the answer to "what did this table look like in March?" was nothing at all. Now the schema is versioned next to the code that queries it. A diff on that file shows exactly what the database did between two releases.

It also turned out to be the thing that made AI coding assistants useful on this project rather than dangerous. An assistant guessing at column names in a 342-table schema invents plausible SQL that fails at runtime. Pointed at a real schema file, it stops guessing. The rule in my repo instructions is blunt: check the schema file before touching any SQL, do not recall table names from memory.

2. It reads the Delphi source

In this codebase, SQL lives inside Object Pascal, assembled at runtime:

Query.SQL.Add('SELECT ... FROM ... WHERE ...');

Spread across dozens of DAO units. There is no way to ask the Delphi compiler "show me every query that touches this table."

So I wrote a Python script that parses the DAO units with regular expressions, pulls out every SQL.Add and SQL.Text assignment, reassembles the fragments, and produces an inventory of every query in the application. Regex parsing of a real language is normally a bad idea. For this — finding string literals in a consistent, machine-generated-ish pattern — it is completely adequate, and it took an afternoon instead of a semester.

The same trick works on Delphi's form files. Keyboard shortcuts in a VCL TActionManager are stored as bitmask integers. Forty-four lines of Python decode the masks back into Ctrl+Shift+F4 and print the whole shortcut map. That document did not exist before; nobody was going to build a Delphi tool to produce it.

The useful reframe: the legacy source code is itself a dataset. You do not need the legacy language to query it.

3. It pre-flights changes before they ship

This is the pattern I would keep if I had to throw the rest away.

I needed to add a validation to an export routine — the one that pushes orders to the vendor ERP the company also runs. The new check would abort the export when the data was inconsistent. The obvious risk: how many orders sitting in production right now would this new check reject?

In the old workflow, I would find out after deploying, from the people whose day I had just ruined.

Instead I wrote a read-only Python script that reimplements the exact queries the Delphi routine runs, executes them against production data, and prints, per order, whether the new validation would abort and why. No Delphi rebuild, no deploy, no write path at all.

It is a deliberately redundant implementation, and the redundancy is the point. Two implementations of the same logic in two languages, where one of them is cheap to run against real data and physically cannot write anything.

4. It does the jobs not worth a screen

A spreadsheet arrives from the warehouse and needs to become a transfer order: hundreds of rows, each one a product code, a size and a quantity, fanned out across a header table and a detail table with the fiscal header cloned from a template order.

Building a screen for that in Delphi is a week. It gets used four times a year.

It is 260 lines of Python — openpyxl in, pymysql out — and it defaults to dry run. It prints everything it would write and exits. Committing requires an explicit --executar flag, and the writes happen inside a transaction.

Dry-run-by-default is not a nicety on a script that writes to a production ERP. It is the only reason I am willing to run it.

The trap nobody warns you about

Here is the part that cost me real damage before I understood it.

Delphi compiles these .pas files as Windows-1252, no BOM. Portuguese source is full of accented characters — column names like SITUAÇÃO, UI strings like Preço. Every modern tool in existence assumes UTF-8.

The failure mode is quiet and total. A script reads a .pas as UTF-8, rewrites it as UTF-8, and every byte in the 128–255 range becomes a malformed sequence. You change one line and git diff --shortstat reports four hundred. Sometimes the accent is not mangled but deleted — CLASSIFICAÇÃO silently becomes CLASSIFICAO, and now a query fails at runtime against a column that no longer matches.

Two things fixed it. First, an explicit rule: any tool that writes a .pas reads and writes it as CP1252, never the platform default. Second, a detection step, because the rule is not universal — a handful of newer files in this project are UTF-8 with BOM, and applying the CP1252 procedure to those corrupts them just as thoroughly. So you check the file before you touch it, every time:

raw = path.read_bytes()
if raw.startswith(b"\xef\xbb\xbf"):
    encoding = "utf-8-sig"
else:
    encoding = "cp1252"

The general lesson is broader than encodings. When you bring a modern toolchain alongside an old codebase, the modern toolchain carries assumptions the old code never agreed to. Those assumptions fail silently, in bulk, and the diff is where you notice.

Then, and only then, the web

Once the tooling layer had been running for a while, a second thing became possible: moving individual screens to the browser.

That is now a Flask application on the same MySQL database. Daily production, a machine dashboard, the bill-of-materials views, some logistics screens. Per-user and per-group permissions with page access controlled in the database. It also reaches the vendor ERP's SQL Server over ODBC, so it talks to two databases at once. It ships in Docker behind nginx.

It is not a replacement. The desktop application is still open on the factory floor, hitting the same tables, and it will be for a long time. The web app is a second front end, not a migration.

But I could only build it with confidence because of what came before it: a schema I could diff, an inventory of every query in the legacy system, and the habit of testing a change against production data before shipping it.

What I would tell myself three years ago

Do not frame it as a rewrite. Frame it as what can I learn about this system with a language that has good libraries.

The Delphi build stayed frozen the entire time. That constraint is a feature — it means the tooling layer can never break production, so you can write it fast and carelessly and keep the care for the things that do ship.

Start with the schema. Make it a file. Everything else gets easier once the shape of the data is something you can read, diff and hand to a tool.