How to Build a MyBatis Plugin to Throttle Burst SQL Traffic and Protect Your Database

Background

In production environments short‑lived traffic bursts can cause sharp load spikes that affect both application and database layers. Typical sources are remote service calls (e.g., JSF, REST), MQ messages, scheduled jobs, or asynchronous tasks. The symptoms include high CPU usage, increased response time, higher TP99, QPS spikes, more slow SQL statements and a rise in active connections.

Design Goal

Create a MyBatis interceptor plugin that protects the database by limiting the rate of identical SQL statements during a configurable time window.

Functional Requirements

Intercept every SQL execution via a MyBatis Interceptor and generate a deterministic SQL fingerprint (e.g., normalized SQL string hashed with MD5/SHA‑1).

Define an interface SqlThresholdProvider that, given a fingerprint, returns a ThresholdConfig containing:

Maximum allowed occurrences per time window.

Rate (e.g., 2/3) that determines the proportion of matched statements to which the handling strategy is applied.

The default implementation loads these mappings from a Spring/YAML configuration file; alternative implementations can be supplied via ServiceLoader SPI.

Maintain an in‑memory counter per fingerprint using a sliding‑window algorithm. The counter records the number of occurrences in the last windowSize milliseconds (default 60 000 ms) with configurable bucket granularity.

When the counter exceeds the configured threshold, invoke one of three handling strategies:

AbortPolicy : throw a RuntimeException to abort the execution.

IgnorePolicy : silently skip the execution and return null (or an empty result) without raising an exception.

DelayPolicy : pause the current thread for a configurable delay (e.g., 100 ms) before proceeding.

The selected strategy respects the rate setting. For example, with a rate of 2/3 and AbortPolicy, two‑thirds of the matching statements are aborted while the remaining one‑third are processed normally.

Implementation Details

The plugin class SQLBasedDatabaseProtectorPlugin implements Interceptor. Its intercept method performs the following steps:

Extract the raw SQL from the BoundSql object.

Normalize the SQL (remove whitespace, literals, comments) and compute the fingerprint.

Query SqlThresholdProvider for the corresponding ThresholdConfig.

Update the SlidingWindowCounter for that fingerprint.

If the count exceeds the threshold, decide based on the configured rate whether to apply the handling strategy or let the statement pass.

Key classes: SQLBasedDatabaseProtectorPlugin – the MyBatis interceptor entry point. SqlThresholdProvider – SPI interface; default implementation SpringSqlThresholdProvider reads application.yml entries such as mybatis.protector.thresholds.{fingerprint}=100. ThresholdConfig – POJO holding maxCount, rate and strategy. strategy package – contains AbortPolicy, IgnorePolicy, DelayPolicy implementing a common ProtectionStrategy interface. counter.SlidingWindowCounter – implements a circular bucket array; each bucket stores an AtomicLong. The record() method updates the current bucket without allocating new objects, thereby reducing memory churn.

Performance Optimisation

The original implementation created a new Window object on every record() call, leading to unnecessary allocations. The revised SlidingWindowCounter reuses a fixed-size bucket array and only updates timestamps, eliminating per‑record object creation.

An OptimizedSlidingWindowCounter class was introduced but later found unused; it has been removed to keep the codebase clean.

Package Refactoring

To improve readability, classes have been reorganised into sub‑packages under com.jd.sword.mybatis.plugin.protector: strategy – all protection strategy implementations. exception – custom runtime exceptions such as ProtectionAbortException. counter – sliding‑window counter utilities.

The main plugin class remains in the root protector package to preserve existing import contracts.

Testing

Unit tests for SQLBasedDatabaseProtectorPlugin cover:

Correct fingerprint generation for varied SQL statements.

Threshold enforcement and rate‑based decision making.

Behaviour of each handling strategy (exception thrown, execution skipped, thread sleep).

Integration with a mock SqlSession to verify that the interceptor is correctly wired.

Tests use JUnit 5 and Mockito for mocking MyBatis components.

Documentation & Build

Design, usage and simplification guides have been updated ( DESIGN_DOCUMENT.md, README.md, SIMPLIFICATION_GUIDE.md) to reflect the final implementation. Maven coordinates have been corrected to:

<groupId>com.jd.sword</groupId>
<artifactId>sword-mybatis-plugins</artifactId>
<version>${project.version}</version>

All references to the previous incorrect mybatis-sql-limit-plugin artifact have been removed.