Kafka Consumer Processed the Message but Database Insertion Failed — How Will You Prevent Data Loss?

This is one of the most common and critical production problems in Kafka-based microservices architecture.

Scenario:

Kafka Consumer Receives Message
        ↓
Business Logic Executes
        ↓
Database Insert Fails

If not handled properly:

Message may be lost
Data inconsistency may occur
Financial transactions may fail
Orders may disappear
Duplicate processing may happen

Real-Time Banking Example

Scenario

Suppose a banking system processes money transfer events from Kafka.

Producer
    ↓
Kafka Topic
    ↓
Transaction Consumer
    ↓
Insert Transaction Into Database

Problem Scenario

Consumer successfully reads message:

Transfer ₹5000
From Account A
To Account B

But database insertion fails because:

Database down
Connection timeout
Deadlock
Disk full
Constraint violation

Dangerous Situation

If Kafka offset is already committed:

Kafka thinks message processed successfully

But database does not contain transaction.

This creates:

Data Loss

Root Cause

Kafka and database are two separate systems.

Their transactions are independent.

Wrong Flow

Step 1:
Consumer Reads Message
       ↓

Step 2:
Kafka Offset Committed
       ↓

Step 3:
Database Insert Failed

Final Result

Message Lost Forever

Production-Level Solutions

Manual Offset Commit
Retry Mechanism
Dead Letter Queue (DLQ)
Idempotency
Transactional Consumer
Outbox Pattern
Exactly Once Processing
Error Handling and Recovery

Most Important Rule

Never Commit Kafka Offset Before Database Success

Correct Flow

Step 1:
Read Kafka Message
       ↓

Step 2:
Insert Into Database
       ↓

Step 3:
If DB Success → Commit Offset

Spring Kafka Manual Acknowledgment

Disable Auto Commit

spring:
  kafka:
    consumer:
      enable-auto-commit: false

Kafka Listener Example

@KafkaListener(topics = "payment-topic")
public void consume(
        String message,
        Acknowledgment acknowledgment) {

    try {

        saveToDatabase(message);

        acknowledgment.acknowledge();

    } catch(Exception ex) {

        log.error("DB Insert Failed", ex);
    }
}

How This Prevents Data Loss

If database insertion fails:

Offset is NOT committed
Kafka re-delivers message
Consumer retries processing

Retry Mechanism

Temporary failures should be retried automatically.

Production Failures

Temporary DB outage
Network issue
Container restart
Short deadlock

Spring Kafka Retry Example

@RetryableTopic(
attempts = "3",
backoff = @Backoff(delay = 2000)
)
@KafkaListener(topics = "payment-topic")
public void consume(String message) {

    saveToDatabase(message);
}

Retry Flow

Attempt 1 → Failed
Attempt 2 → Failed
Attempt 3 → Failed

If still failing:

Move Message to DLQ

Dead Letter Queue (DLQ)

DLQ stores permanently failed messages.

DLQ Example

payment-topic.DLT

Benefits of DLQ

No message loss
Manual investigation possible
Replay failed messages later

DLQ Consumer Example

@KafkaListener(topics = "payment-topic.DLT")
public void processDLQ(String message) {

    log.error("Failed Message: {}", message);
}

Idempotency Handling

Kafka may re-deliver messages during retries.

Without idempotency:

Duplicate database inserts may occur

Real Banking Example

Money Transfer Message Reprocessed
       ↓
Amount Deducted Twice

Very dangerous.

Production Solution

Use unique transaction ID.

Example

if(transactionAlreadyProcessed(transactionId)) {

    return;
}

Database Unique Constraint

ALTER TABLE transactions
ADD CONSTRAINT unique_txn
UNIQUE(transaction_id);

Transactional Consumer

Spring Kafka supports Kafka transactions.

Configuration

spring:
  kafka:
    producer:
      transaction-id-prefix: txn-

Transactional Listener Example

@Transactional
@KafkaListener(topics = "payment-topic")
public void consume(String message) {

    saveToDatabase(message);
}

How It Works

If database transaction fails:

Offset not committed
Message reprocessed

Exactly Once Processing

Kafka supports Exactly Once Semantics (EOS).

Production Configuration

spring:
  kafka:
    producer:
      properties:
        enable.idempotence: true

Benefits

No duplicate messages
No message loss
Reliable event processing

Outbox Pattern

Outbox Pattern ensures reliable event publishing and processing.

Problem Without Outbox

Database Saved
      ↓
Kafka Publish Failed

Or:

Kafka Processed
      ↓
Database Failed

Outbox Table Example

CREATE TABLE outbox_events (

    id BIGINT PRIMARY KEY,

    event_type VARCHAR(100),

    payload TEXT,

    status VARCHAR(20)
);

Transactional Save Example

@Transactional
public void processEvent() {

    transactionRepository.save(transaction);

    outboxRepository.save(event);
}

Outbox Publisher Job

@Scheduled(fixedDelay = 5000)
public void publishEvents() {

    List<OutboxEvent> events =
        repository.findPending();

    for(OutboxEvent event : events) {

        kafkaTemplate.send(
            event.getTopic(),
            event.getPayload()
        );

        event.setStatus("COMPLETED");
    }
}

Error Handling Example

@KafkaListener(topics = "payment-topic")
public void consume(String message) {

    try {

        saveToDatabase(message);

    } catch(Exception ex) {

        log.error("Error Processing Message", ex);

        throw ex;

Kafka consumer processed the message but database insertion failed. How will you prevent data loss?