프로시저(Procedure) 동작 방식과 동시성 문제

현 직장에서 가장 많이 만나는 문제 중 하나는 동시성 문제입니다. 동시성이 발생하는 이유도 가지 각색인데

• 웹서버의 병목현상으로 인해 요청이 한 번에 처리되는 경우
• 동시에 여러 사용자가 동일한 작업을 수행하는 경우 
• 사용자의 작업과 스케줄러의 작업이 겹치는 경우 

등등 여러 가지 이유로 인해 동시성 문제가 발생하게 되면 그때부터는 왜 그런 상황이 발생했는지 찾는 탐정놀이가 시작됩니다.

최근에 발생한 동시성 문제는 좀 특이해서 다음에 비슷한 문제가 발생했을 경우를 대비해 기록하려고 합니다. 

 

---

문제 발생

이번에 발생한 동시성 문제의 원인은 정확히 결론 내릴 수 없었지만, 다른 가능성을 소거해 가면서 원인을 파악한 결과, 웹 서버의 병목현상으로 인해 처리 대기 중이던 요청이 한 번에 처리되어 발생한 것으로 결론 내렸습니다.

 

문제 상황에 대해 설명하기 전에 처리 로직과 관련한 사전 도메인 지식을 알아야 합니다.

• 송장 출력이 끝난 주문은 발송처리 된다. (발송 처리란 배송 중 상태로 변경함을 의미한다.)
• 만약 발송 처리의 주문을 취소한다면 해당 주문의 출고를 취소해야한다. 즉, 입고처리 해야 한다. 

문제가 발생했다고 보고가 들어온 건, 발송 취소에 대해 부분 적으로 중복 입고 처리가 되었다는 것이다. 예를 들어 7개의 품목이 합포 된 주문이 발송 취소 되었다면 7개의 상품이 각각 한 번씩 입고 처리 되었어야 했는데 상품별로 어떤 상품은 2번 입고 처리 되었다는 것이 문제였습니다.

---

원인 분석

사용자의 작업 내역을 살펴보니, 동시간대에 두 명의 사용자가 발송 취소 요청을 한 내역이 확인되었습니다. 여기서 이상한 점은 다음과 같습니다.

1. 두 사용자의 발송 취소 요청 시각 차이는 불과 몇 나노초에 불과했습니다.
2. 두 사용자의 마지막 접속 시각 차이는 30분 이상이었습니다.

즉, 두 사용자의 최근 로그인 기록의 차이에 비해 발송 취소 요청 간의 시각 차이가 매우 짧았습니다. 이로 인해 문제의 원인을 웹서버의 병목 현상으로 결론지었습니다. 첫 번째 사용자가 요청한 발송 취소 작업이 요청 시점에서 즉시 처리되지 못하고 대기 상태에 있다가, 두 번째 사용자의 요청과 거의 동시에 처리되기 시작하면서 문제가 발생한 것으로 판단했습니다.

 

보통 발송취소와 같이 특정 주문의 변경이 발생하는 경우는 작업이 처리 되는 중에 다른 작업의 요청을 받지 못하도록 홀딩을 걸어 이중 발송 취소를 방지하고 있었습니다. 다만, 이 경우에서는 발송취소 간의 나노초 차이로 인해 두 요청이 모두 백엔드 서버로 진입이 성공했고 현재와 같은 문제상황을 야기하게 되었습니다. 

 

여기까지는 그럴 수 있다고 판단했습니다. 동시성 문제로 인해 중복 입고처리가 되었다면 빠르게 중복 입고처리가 된 재고를 찾아서 삭제해 주는 작업으로 현 상황을 마무리 짓고 트랜잭션 등으로 동일 현상이 발생하지 않도록 방지를 하면 될 문제였습니다. 

 

다만 이해가되지 않는 부분은 부분 중복입고 처리였습니다. 예를 들어 동시성 문제로 인해 7개의 상품이 발송취소가 두 번 됐다면, 총 7번씩 두 번 즉, 14개의 입고 기록이 남아있어야 했지만, 7개 중 3개는 중복 입고, 4개는 1번만 입고처리가 되는 알 수 없는 상황이 발생했습니다. 

이와 같은 문제의 원인은 결론적으로 동일 프로시저를 동시에 호출하면서 발생한 문제였습니다.

---

프로시저의 동작 방식

프로시저는 데이터베이스에 반복적으로 수행하는 작업이나 복잡한 데이터베이스 연산을 처리하도록 미리 설계 뙨 SQL을 의미합니다. 레거시 프로젝트에서 발송 취소 후 입고처리하는 부분을 미리 정의된 프로시저를 호출하도록 설계되어 있었습니다.

 

프로시저의 동작 방식을 이해가 위해서는 먼저 커서(Cursor)라는 개념을 알아야 합니다. 커서는 쿼리의 결과 집합을 하나씩 처리할 수 있도록 하는 데이터베이스 객체를 의미합니다. 이를 통해 반복적인 행 단위 처리가 가능하게 지원합니다.

 

아래의 쿼리처럼 커서를 사용할 수 있습니다. 

CREATE PROCEDURE PrintEmployeeNames
AS
BEGIN
    DECLARE @EmployeeName NVARCHAR(50); // 1. 커서 선언하기 
    DECLARE employee_cursor CURSOR FOR
    SELECT name FROM employees;

    OPEN employee_cursor; // 2. 커서 열기 

    FETCH NEXT FROM employee_cursor INTO @EmployeeName; // 3. 커서에서 행 가져오기 

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT @EmployeeName;
        FETCH NEXT FROM employee_cursor INTO @EmployeeName;
    END;

    CLOSE employee_cursor; // 4. 커서 닫기
    DEALLOCATE employee_cursor; // 5. 커서 해제
END;

 

부분적으로 중복 입고 처리되는 상황의 시나리오를 정리하자면 아래 이미지와 같습니다. 

 

부분 중복 입고 시나리오

• 동일한 시각 t1에 동일 주문 Ord1에 대한 발송 취소 처리 요청이 2번 들어옴. (Ord1은 합포 주문 건으로 2개의 상품을 포함하고 있음)
• 발송 취소 후 입고 처리를 위한 프로시저가 동시간대에 호출됨. 
• 각 프로시저마다, 입고 처리를 위한 재고를 조회하고 재고 상품 별로 입고처리 여부를 판단하고 입고가 필요하다고 판단되면 입고 처리함. 
• 문제는 각 프로시저가 입고가 필요한 전체재고를 조회하고 조회의 결과집합을 행을 돌면서 각 재고 상품이 입고가 필요한지 판단하는 로직을 수행함. 
• 두 개의 프로시저가 함께 돌면서 각 프로시저의 작업 처리 결과가 다른 프로시저의 작업에 영향을 주는 상황이 발생함. 
• 예를 들어 각 프로시저에서 입고가 필요하다고 판단할 재고를 읽을 때 어떤 경우는 아직 입고되지 않은 시점에서는 둘 다 입고가 필요하다고 판단해서 중복 입고 처리가 되고, 또 어떤 시점에서는 특정 프로시저에서 특정 재고를 입고처리 해서 그 프로시저보다 늦게 수행되고 있는 다른 프로시저에서는 해당 재고가 이미 입고되어 중복입고 되지 않는 결과를 초래함. 

즉, 프로시저의 커서가 입고를 위해 조회한 재고 목록의 결과집합을 행 단위로 하나씩 처리하고, 동일한 작업을 수행하는 두 개의 프로시저가 서로 간의 작업에 영향을 주면서 부분 적으로 중복 입고 처리 하는 결과를 초래했습니다.

---

해결방법

원래는 동시에 프로시저를 호출하지 못하도록 프런트에서 홀딩을 걸어 접근 자체를 하지 못하도록 하려고 했지만, 제대로 방어가 되지 못해 이런 문제가 발생했다고 보고 있습니다. 때문에 동시에 동일한 프로시저를 호출하는 상황에서 동시성 문제를 예방하기 위해서 별도의 조치가 필요하다고 생각했습니다. 

 

가장 간단 한 방법은 프로시저 내에서 동기식으로 작업되어야 하는 부분에 트랜잭션을 선언하는 것이었습니다. 

BEGIN TRANSACTION;

... 입고처리 

COMMIT;

 

 

또 다른 방법은 트랜잭션 격리 수준을 SERIALIZABLE 하게 설정하는 방법이 있습니다. (SERIALIZABLE는 가장 엄격한 격리 수준으로 트랜잭션이 시작된 이후 트랜잭션에서 데이터를 삽입하거나 수정할 수 없도록 설정합니다.)

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

BEGIN TRANSACTION;

... 입고처리

COMMIT;

 

더 나아가 특정 데이터베이스에서는 (MySQL, PostgreSQL 등) 행 레벨에서 락을 사용하는 것이 가능하다는 걸 알았습니다. 즉, 특정 행을 수정할 때 그 행에 대한 락을 설정하여 다른 트랜잭션이 동시에 접근하지 못하게 막을 수 있습니다. 이렇게 하면 좁은 범위에서 락을 걸 수 있기 때문에 이전보다 서버의 처리 효율성이 개선될 수 있습니다. 

 

예를 들어 MySQL에서 행 단위로 락을 거는 방법은 다음과 같습니다. 

START TRANSACTION;

SELECT * FROM inventory WHERE product_id = 1 FOR UPDATE;

UPDATE inventory SET quantity = quantity - 5 WHERE product_id = 1; // 락 적용 시점

COMMIT;

---

마무리 

웹 서버의 병목현상으로 인해 동시성 문제가 발생할 수 있다는 것도, 동일한 프로시저가 동시에 호출될 경우 발생할 수 있는 동시성 문제도 처음 경험해 봤기에 당황스러웠지만, 이번 일을 계기로 프로시저에서 동시성 문제를 예방할 수 있는 방법을 학습할 수 있었습니다.. 추가로 웹서버에서 병목현상이 자주 발생하지 않기 위해서는 어떤 처리를 할 수 있는지도 궁금해졌습니다. 다음에는 그 부분에 대해서 추가로 학습해봐야 할 것 같습니다.