Local Message Tables: Production-Ready Microservice Async Consistency Solution

Microservice architectures that rely on asynchronous messaging via message queues frequently suffer from data inconsistency issues: orders created but points not credited, payments successful but order status unchanged, inventory deducted but orders cancelled, and messages lost or duplicated. The root cause is the lack of distributed transactions — asynchronous calls cannot guarantee 100% success, and relying solely on MQ reliability or retries still leads to data corruption.

Three Fundamental Contradictions in Async Messaging

The standard flow — execute business SQL, send MQ message, downstream service consumes — contains three unsolvable contradictions:

Contradiction 1: Send message first, then persist to DB. If the message is sent but the database transaction rolls back, the downstream service executes against non-existent data, creating dirty data.

Contradiction 2: Persist to DB first, then send message. If the database succeeds but the service crashes before sending the message, the downstream never executes, creating a data gap.

Contradiction 3: MQ does not guarantee 100% delivery. Network jitter and broker failures make message loss a common occurrence.

As long as you use microservices with MQ async decoupling, consistency problems are an architectural birth defect.

Distributed Transaction Solution Comparison

A production selection table compares four mainstream approaches:

Seata AT: Weak consistency, medium intrusion, high operational cost — suitable for core financial/strong transaction scenarios.

TCC: Strong consistency, extremely high intrusion, extremely high operational cost — suitable for core fund transfer chains.

MQ Transaction Messages: Eventual consistency, low intrusion, medium operational cost — RocketMQ-specific with version constraints.

Local Message Table: Eventual consistency, extremely low intrusion, extremely low operational cost — fits 90% of ordinary business, most universal and stable.

Conclusion: For the vast majority of internet businesses, the local message table is the highest cost-performance, least accident-prone solution.

Core Principle of Local Message Table

Core idea: Business data + message data persisted in the same transaction, guaranteeing atomicity. No longer dependent on MQ reliability or machine luck.

Complete core flow:

Start local transaction

Insert/update business data

Insert a PENDING message record into the local message table

Commit transaction (business + message both persist, all succeed or all roll back)

Independent scheduled task polls message table for PENDING messages

On send success, update status to SENT On consumption success, callback updates to COMPLETED On send failure, retry indefinitely until success

This completely solves message loss, transaction inconsistency, and crash data loss.

Production-Grade Local Message Table Schema

CREATE TABLE `local_message` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID',
  `message_no` varchar(64) NOT NULL COMMENT 'Unique message number',
  `topic` varchar(64) NOT NULL COMMENT 'MQ topic',
  `message_body` text NOT NULL COMMENT 'Message body content',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '0 pending 1 sent 2 completed 3 failed',
  `retry_count` int NOT NULL DEFAULT '0' COMMENT 'Retry count',
  `next_retry_time` datetime DEFAULT NULL COMMENT 'Next retry time',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_msg_no` (`message_no`),
  KEY `idx_status_retry` (`status`, `next_retry_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Local message transaction table';

Index design highlight: Composite index on status + retry time ensures scheduled polling does not full-table scan, delivering high performance.

Core Production Implementation Code

1. Business Transaction Persistence (Core Atomic Logic)

@Transactional(rollbackFor = Exception.class)
public void createOrder(OrderDTO dto) {
    // 1. Create order business data
    Order order = buildOrder(dto);
    orderMapper.insert(order);
    // 2. Persist to local message table in same transaction (atomic guarantee)
    LocalMessage message = new LocalMessage();
    message.setMessageNo(UUID.randomUUID().toString());
    message.setTopic("order_create_topic");
    message.setMessageBody(JSON.toJSONString(order));
    message.setStatus(0);
    localMessageMapper.insert(message);
}

Key point: Business table and message table in same transaction — absolutely impossible to have half success, half failure.

2. Scheduled Task Polling and Resending Messages

@Scheduled(fixedDelay = 3000)
public void scanAndSendMessage() {
    // Query pending messages that have reached retry time
    List<LocalMessage> messageList = localMessageMapper.selectWaitSendMessage();
    for (LocalMessage msg : messageList) {
        try {
            // Send MQ
            mqProducer.send(msg.getTopic(), msg.getMessageBody());
            // On send success update status
            msg.setStatus(1);
            localMessageMapper.updateById(msg);
        } catch (Exception e) {
            // On send failure, record retry count, schedule delayed retry
            msg.setRetryCount(msg.getRetryCount() + 1);
            msg.setNextRetryTime(getNextRetryTime(msg.getRetryCount()));
            localMessageMapper.updateById(msg);
        }
    }
}

Three Core Production Problem Solutions

1. Duplicate Message Delivery

Retry mechanisms inevitably cause duplicate delivery.

Solution: Consumer-side idempotency. Use the unique messageNo for idempotency — if already consumed, return immediately without re-executing business logic.

2. Message Backlog and Infinite Retry Deadlock

Solution: Maximum retry count safeguard. Set a maximum retry threshold; exceeding it marks the message as failed for manual intervention, avoiding infinite loops.

3. Message Ordering Chaos

Solution: Version number control. Use business timestamps and version numbers to control final overwrite order, guaranteeing eventual data consistency.

Complete State Transition Loop (Production Standard)

0 PENDING

→ Scheduled task scans → MQ send success → 1 SENT

Consumer consumes successfully → Callback updates → 2 COMPLETED

Send failure → Record retry count + delayed retry → Multiple failures enter manual investigation

Full closed loop, zero data loss, zero data corruption.

Real Production Incident Postmortem

🔴 Symptom: Order payment succeeded, user points occasionally not credited, probabilistic and intermittent.

🔍 Root Cause Analysis: Original architecture sent MQ first, then updated business state. Occasional service crashes caused:

Message delivered successfully, business rolled back

Or business succeeded, MQ send failed

✅ Fix: Adopted local message table — business and message persisted in same transaction, scheduled task guarantees redelivery.

🎯 After Refactor: Production async data inconsistency issues completely eliminated.

Architect Summary

Microservice async architecture must never run MQ naked — data inconsistency is inevitable.

Local message table is the lightweight, low-intrusion, high-stability ultimate eventual consistency solution.

Core essence: Business and message same-transaction persistence, scheduled task guarantees redelivery.

Combined with consumer idempotency, completely cures message loss, duplicate consumption, data corruption.

Small-to-medium teams should not blindly adopt heavy transaction frameworks — local message table outperforms all alternatives on cost-performance.