SQS DLQ에 메시지가 쌓이고 있었다 - 예약 발송 장애 대응기

// AWS SQS 콘솔에서 확인한 DLQ 상태
Messages Available: 147
Messages in Flight: 0

flowchart LR
    subgraph Producer
        A[예약 API] --> B[SQS 발송]
    end

    subgraph Consumer
        C[SQS Listener] --> D[메시지 발송]
        D --> E[DB 상태 업데이트]
    end

    subgraph DLQ
        F[Dead Letter Queue]
    end

    B --> C
    C -->|3회 실패| F

    style F fill:#f66,stroke:#333,color:#fff

org.springframework.dao.DeadlockLoserDataAccessException:
Deadlock found when trying to get lock;
try restarting transaction

SHOW ENGINE INNODB STATUS;

-- 결과 (일부 발췌)
LATEST DETECTED DEADLOCK
------------------------
*** (1) TRANSACTION:
TRANSACTION 12345, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 100 page no 50 n bits 72 index PRIMARY

*** (2) TRANSACTION:
TRANSACTION 12346, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1

@Service
public class MessageConsumer {

    @SqsListener("${sqs.queue.name}")
    @Transactional
    public void processMessage(ScheduledMessage message) {
        // 1. 발송 상태를 PROCESSING으로 변경
        messageRepository.updateStatus(message.getId(), Status.PROCESSING);

        // 2. 실제 메시지 발송 (외부 API 호출)
        messagingService.send(message);

        // 3. 발송 상태를 COMPLETED로 변경
        messageRepository.updateStatus(message.getId(), Status.COMPLETED);
    }
}

시간: 09:00 (예약 발송 피크 타임)

Consumer A: UPDATE messages SET status='PROCESSING' WHERE id=100
Consumer B: UPDATE messages SET status='PROCESSING' WHERE id=101
Consumer A: UPDATE messages SET status='COMPLETED' WHERE id=100  -- 대기
Consumer B: UPDATE messages SET status='COMPLETED' WHERE id=101  -- 대기

→ 데드락 발생!

@Service
public class MessageConsumer {

    @SqsListener("${sqs.queue.name}")
    @Transactional(isolation = Isolation.READ_COMMITTED)  // 변경
    public void processMessage(ScheduledMessage message) {
        messageRepository.updateStatus(message.getId(), Status.PROCESSING);
        messagingService.send(message);
        messageRepository.updateStatus(message.getId(), Status.COMPLETED);
    }
}

// CloudWatch 알람 설정 (Terraform)
resource "aws_cloudwatch_metric_alarm" "dlq_alarm" {
  alarm_name          = "scheduled-message-dlq-alarm"
  comparison_operator = "GreaterThanThreshold"
  evaluation_periods  = "1"
  metric_name         = "ApproximateNumberOfMessagesVisible"
  namespace           = "AWS/SQS"
  period              = "300"  // 5분
  statistic           = "Average"
  threshold           = "0"    // 1개라도 쌓이면 알림

  dimensions = {
    QueueName = "scheduled-message-dlq"
  }

  alarm_actions = [aws_sns_topic.alert.arn]
}

@Service
@RequiredArgsConstructor
public class DlqReprocessor {

    private final SqsClient sqsClient;
    private final MessageConsumer messageConsumer;

    /**
     * DLQ 메시지를 하나씩 꺼내서 재처리합니다.
     * 멱등성이 보장되어야 안전합니다.
     */
    @Scheduled(fixedDelay = 60000)  // 1분마다 실행
    public void reprocessDlqMessages() {
        ReceiveMessageRequest request = ReceiveMessageRequest.builder()
            .queueUrl(dlqUrl)
            .maxNumberOfMessages(10)
            .build();

        List<Message> messages = sqsClient.receiveMessage(request).messages();

        for (Message message : messages) {
            try {
                ScheduledMessage scheduledMessage = parseMessage(message);

                // 이미 처리된 메시지인지 확인 (멱등성)
                if (isAlreadyProcessed(scheduledMessage.getId())) {
                    deleteFromDlq(message);
                    continue;
                }

                // 재처리
                messageConsumer.processMessage(scheduledMessage);
                deleteFromDlq(message);

                log.info("DLQ 메시지 재처리 성공: {}", scheduledMessage.getId());

            } catch (Exception e) {
                log.error("DLQ 메시지 재처리 실패: {}", message.messageId(), e);
                // 재처리 실패 시 DLQ에 그대로 둠 → 수동 확인 필요
            }
        }
    }

    private boolean isAlreadyProcessed(Long messageId) {
        return messageRepository.findById(messageId)
            .map(m -> m.getStatus() == Status.COMPLETED)
            .orElse(false);
    }
}

flowchart LR
    subgraph Producer
        A[예약 API] --> B[SQS 발송]
    end

    subgraph Consumer
        C[SQS Listener] --> D[메시지 발송]
        D --> E[DB 상태 업데이트]
    end

    subgraph DLQ
        F[Dead Letter Queue]
        G[DLQ Reprocessor]
    end

    subgraph Monitoring
        H[CloudWatch Alarm]
        I[Slack 알림]
    end

    B --> C
    C -->|3회 실패| F
    F --> G
    G -->|재처리| D
    F --> H
    H --> I

    style F fill:#f96,stroke:#333,color:#fff
    style H fill:#4a9,stroke:#333,color:#fff
    style I fill:#4a9,stroke:#333,color:#fff