Design and Implementation of a Database Change Tracking System for Payment Settlement Using JVM Sandbox

With the rapid growth of internet traffic, traditional monolithic architectures can no longer meet the demands of massive users, leading to the evolution from monoliths to micro‑services and a proliferation of databases and tables. In a payment settlement scenario, a single business flow may involve two databases, eleven tables, and sixteen document types, making manual SQL verification time‑consuming.

The proposed solution is to display all documents related to a business traceId on one page, using an intermediate layer to intercept database operations across different clusters and present them centrally.

Design ideas: Capture business‑level traceId, bind it to SQL statements, and collect changes via code instrumentation rather than relying on binlog, which cannot associate data with business context.

Technical implementation:

1. Code enhancement – All test‑environment clusters are equipped with jvm‑sandbox . The sandbox watches RETURN and THROW events to obtain executed SQL.

// BEFORE
try{
    // RETURN
    return;
}catch(Throwable cause){   
   // THROWS
}

Listener registration example:

moduleEventWatcher.watch(filter, listener, Event.Type.RETURN, Event.Type.THROW);

2. Enhancement points – Instrument the executeInternal method of both MySQL driver versions to capture the prepared SQL:

com.mysql.cj.jdbc.PreparedStatement#executeInternal
com.mysql.jdbc.PreparedStatement#executeInternal

Reflection is used to call PreparedStatement#asSql() and retrieve the SQL string.

3. Business association – Using transmittable‑thread‑local , a traceId generated at the traffic entry point is propagated across services and bound to each captured SQL.

4. SQL processing – Only DML statements (INSERT, UPDATE, DELETE) are relevant; they are parsed to extract changed data.

5. Transaction handling – Since RETURN events may capture uncommitted data, the system also instruments transaction boundaries ( Connection#setAutoCommit , Connection#commit , Connection#rollback ) to detect commits and rollbacks. When a transaction commits, the collected SQLs are sent; on rollback, they are discarded.

6. Front‑end display – The aggregated changes are visualized in a single page, showing a clear overview of all database modifications for the given traceId.

Future optimizations include extending support to other data stores such as Redis and Elasticsearch, providing real‑time capture similar to Charles/Fiddler, and categorizing data based on traffic entry points.