A cron job running SELECT every 15 seconds against your main database can bring an e-commerce platform to its knees during peak traffic.

In this article, we will explore how to capture data mutations in real time using the Transactional Outbox pattern and MySQL Triggers, eliminating the friction of polling while achieving ACID consistency for event emission — without touching a single line of the legacy application.

The Black Friday Incident#

P99 database latency went through the roof.

The server was suffocating while the on-call team desperately tried to kill connections in real time. The real cause was both tragic and familiar to everyone on the team:

An inventory synchronization cron job had just executed a massive:

SELECT * FROM fktscxx001

to detect stock changes.

That query collided head-on with hundreds of customer transactions attempting to complete checkout.

The result?

A catastrophic cycle of InnoDB row locks, a failed checkout, and thousands of euros evaporating every minute.

The 20-Year-Old ERP Monolith#

The environment looked roughly like this:

  • Stack: Legacy monolith directly accessing MySQL 8.0.

  • Core: Transactional inventory table (fktscxx001) with more than 20 million records.

  • Goal: Synchronize inventory with the web platform and the logistics WMS in less than 1 second after every sale.

  • Reality: Cron jobs performing polling every 15 seconds. Massive I/O consumption, constant pressure on the InnoDB Buffer Pool, and conversion losses caused by database contention.

What We Tried — and Why It Failed#

The first thing anyone thinks about during an incident is:

“Let’s add more CPU and RAM, create a composite index on (updated_at, id), and move on.”

We did exactly that.

The result?

The polling jobs were still scanning thousands of memory pages inside the relational engine.

The bottleneck was not query speed.

It was the frequency and volume of reads.

We then evaluated synchronous double writes:

The monolith would save the inventory change to MySQL and, in the same execution path, make an HTTP/REST call to the web service.

We almost created an even bigger disaster.

As soon as the network experienced a small interruption or the receiving service became 200 ms slower, the database transaction remained blocked in memory.

The result was widespread data inconsistency and inventory divergence.

Synchronous double writes without asynchronous reconciliation were not sustainable.

We needed a pattern backed by CDC and Outbox, where event production could remain decoupled from the network while preserving transactional consistency.

Then We Went Back to ACID Transactions#

During the war room session, the core principle became obvious:

If a mutation happens in the database, the corresponding event must be part of the same local transaction.

It should not matter whether the mutation comes from:

  • the legacy application,

  • a maintenance script,

  • or an engineer executing SQL directly from a console.

To avoid entering the nightmare of rewriting the ERP — effectively turning the project into a Big Bang migration — we isolated the problem at the data layer.

We created an extremely lightweight outbox table and installed MySQL Triggers.

Transactional Capture Architecture#

The critical property here is simple:

the business-state mutation and the corresponding event are persisted atomically.

There is no separate HTTP call that can succeed or fail independently of the transaction.

Outbox Table and Capture Trigger#

1. Lightweight Outbox Table#

CREATE TABLE outbox (
    id VARCHAR(36) PRIMARY KEY,
    aggregate_type VARCHAR(64) NOT NULL,
    aggregate_id VARCHAR(64) NOT NULL,
    event_type VARCHAR(64) NOT NULL,
    payload JSON NOT NULL,
    created_at TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP(3),
    status VARCHAR(20) DEFAULT 'pending',
    INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;

2. MySQL Trigger Capturing Direct SQL and Legacy Mutations#

CREATE TRIGGER trg_fktscxx001_after_update
AFTER UPDATE ON fktscxx001
FOR EACH ROW
BEGIN
    IF OLD.stock <> NEW.stock THEN

        INSERT INTO outbox (
            id,
            aggregate_type,
            aggregate_id,
            event_type,
            payload
        )
        VALUES (
            UUID(),
            'Stock',
            CAST(NEW.art_id AS CHAR),
            'StockUpdated',
            JSON_OBJECT(
                'articulo_id', NEW.art_id,
                'stock_anterior', OLD.stock,
                'stock_nuevo', NEW.stock,
                'almacen_id', NEW.alm_id
            )
        );

    END IF;
END;

Now the trigger becomes an integration boundary at the database level.

The legacy application does not need to know that an event exists.

It does not need to know where that event is going.

It does not need to call another service.

It simply continues doing what it has been doing for twenty years:

updating the database.

The database takes care of recording the corresponding event inside the same transaction.

What Happens During a Transaction?#

The flow becomes:

Both operations belong to the same ACID transaction.

If the transaction commits, both the inventory mutation and the outbox event are persisted.

If the transaction rolls back, both disappear.

That is the fundamental advantage.

You no longer have:

inside the critical transaction path.

Instead, you have:

The network is now outside the critical path.

The Production Result#

We replaced heavy polling queries with a sequential append-only write added to the active transaction.

The result was:

  • Polling read load: 0%. Completely eliminated.

  • Table locks (row locks): Removed from the previous polling pattern.

  • InnoDB Buffer Pool stability: Reserved for actual customer transactions instead of repeated polling scans.

The important architectural change was not simply “using a trigger.”

The real change was moving event detection from a repeated read model to a transactional write model.

Instead of asking the database:

“Did anything change?”

every 15 seconds, we tell the system:

“When something changes, record the fact immediately.”

That is the fundamental shift.

The Golden Rule#

Never use synchronous HTTP calls across the network to notify downstream systems about state changes inside a database transaction.

Write the event atomically to the same database using the Transactional Outbox pattern.

The network should be handled asynchronously, outside the critical execution path.

That gives you a much cleaner separation:

Critical Path:

Asynchronous Path:

And that is where the next problem begins.

You have successfully stopped polling.

But now you have a new question:

How do you move those events out of MySQL efficiently without introducing another bottleneck?

In the next post, we will build the Go Streamer: an ultra-lightweight binary connected directly to the MySQL replication protocol, consuming the BinLog at 3,000 messages per second using only 50 mCPU.

The polling tax is gone.

Now we need to move the events.