Trigger를 사용하여 Database 에서 API 호출하기

데이터베이스에서 트리거는 흔히 데이터 완결성 유지, 검증 등의 비즈니스 필요에 의해 사용된다. 어떤 특정 테이블, 특정 행, 특정 컬럼에 대해 INSERT, DELETE 등 operation 이 일어날 때 같은 트랜잭션에 묶여 기존의 작업에 더해 다른 작업을 수행하는 것이다.

그런데 여기에 통상 server application 단에서 하는 API 를 호출하는 작업을 묶을 수도 있다.

 

우선 서두에 강조할 부분은, 이는 절대 권장하지 않는 행위이다. 앞서 말했듯이 어플리케이션쪽에서 실행되는 데이터베이스 동작과 함께, 컨트롤러와 서비스 레이어에서 API를 호출하는 것이 당연한 절차인데, 굳이 민감하고 ACID성을 유지해야 할 데이터베이스 작업에서 API를 호출함으로 인해 예측하기 힘든 데이터베이스 부하를 일으킬 필요가 전혀 없다.

 

조직의 비즈니스 필요사항으로 인해 차악을 택할 수 밖에 없는 매우 특수한 상황에서만 눈물을 머금고 사용할 흑마법과도 같은 데이터베이스 트리거에 API 호출 달기와 그로 인한 데이터베이스 부하를 알아보도록 하자.

 

데이터베이스는 무난한 SQL SEVER 2019 와 분석에는 Datagrip 을 사용하였다.

 

우선 간단한 이름 컬럼만 있는 테이블을 생성해보자.

CREATE TABLE BasicTable (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100) NOT NULL
);
GO

 

API 콜은 그냥 어딘가에 있는 이름 파라미터를 넣으면 나이를 예측해주는 API 를 사용해보자. 내이름을 넣으니 66세로 예측된단다고 한다.

그리고 trigger 내에서 직접 API call 을 하려면 켜야 하는 옵션이 있는데, 다음이다.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

 

MSSQL 에서만 사용되는, microsoft COM(Component Object Model) 내의 OLE Automation Object 를 사용하기 위한 옵션이다. 특정적으로 현재 HTTP request 를 위해 COM compent 중 MSXML2.ServerXMLHTTP 를 사용할 것이기 때문에 이 옵션 활성화가 필수적이다. OLE 란 무엇인가 하면 https://en.wikipedia.org/wiki/Object_Linking_and_Embedding 를 읽어보자.

 

HTTP call 을 하고 response 를 받아오기까지 사용될 대략적인 procedure 들은 다음과 같다.

sp_OACreate: Creates an instance of an OLE Automation object.
sp_OADestroy: Destroys an instance of an OLE Automation object.
sp_OAGetProperty: Gets a property value of an OLE Automation object.
sp_OASetProperty: Sets a property value of an OLE Automation object.
sp_OAMethod: Calls a method of an OLE Automation object.

 

위에 대충 트랜잭션에 사용될 테이블을 생성해두었으니 이를 로깅하기 위한 별도의 테이블을 생성해보자.

CREATE TABLE ApiResponseLog (
    id INT IDENTITY(1,1) PRIMARY KEY,
    operation_type NVARCHAR(10),
    name NVARCHAR(100),
    response NVARCHAR(1000),
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
);
GO

 

operation_type (UPDATE, SELECT, INSERT, DELETE) 와 함께 API 로 전달될 name 과 상응하는 response 를 넣어주겠다.

 

근데 성능 테스트를 위해 API 호출시간, DB 삽입 시간 등도 기록해보고 싶어졌다.

ALTER TABLE ApiResponseLog
ADD api_call_start_time DATETIME2,
    api_call_end_time DATETIME2,
    api_call_duration_ms INT,
    insert_start_time DATETIME2,
    insert_end_time DATETIME2,
    insert_duration_ms INT;
GO

 

이제 트리거를 작성해보자.

CREATE TRIGGER trgAfterAllOperations ON BasicTable
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    DECLARE @name NVARCHAR(100);
    DECLARE @url NVARCHAR(200);
    DECLARE @response NVARCHAR(1000);
    DECLARE @object INT;
    DECLARE @status INT;
    DECLARE @operation_type NVARCHAR(10);
    DECLARE @api_call_start_time DATETIME2;
    DECLARE @api_call_end_time DATETIME2;
    DECLARE @insert_start_time DATETIME2;
    DECLARE @insert_end_time DATETIME2;

    -- operation type 결정
    -- update 의 경우 delete/insert pseudotable 모두에 행이 생성됨
    IF EXISTS (SELECT * FROM inserted) AND EXISTS (SELECT * FROM deleted)
        SET @operation_type = 'UPDATE';
    ELSE IF EXISTS (SELECT * FROM inserted)
        SET @operation_type = 'INSERT';
    ELSE IF EXISTS (SELECT * FROM deleted)
        SET @operation_type = 'DELETE';

    IF @operation_type = 'INSERT' OR @operation_type = 'UPDATE'
    BEGIN
        SELECT TOP 1 @name = name FROM inserted;
    END
    ELSE IF @operation_type = 'DELETE'
    BEGIN
        SELECT TOP 1 @name = name FROM deleted;
    END

    -- API URL 지정
    SET @url = 'https://api.agify.io?name=' + @name;

    -- API call 시작시간 기록
    SET @api_call_start_time = SYSDATETIME();

    -- API call!!
    EXEC @status = sp_OACreate 'MSXML2.ServerXMLHTTP', @object OUT;
    IF @status = 0 EXEC @status = sp_OAMethod @object, 'open', NULL, 'GET', @url, 'false';
    IF @status = 0 EXEC @status = sp_OAMethod @object, 'send';
    IF @status = 0 EXEC @status = sp_OAGetProperty @object, 'responseText', @response OUT;
    IF @status = 0 EXEC sp_OADestroy @object;

    -- API call 끝난시간 기록
    SET @api_call_end_time = SYSDATETIME();

    -- insert 시작시간 기록
    SET @insert_start_time = SYSDATETIME();

    -- 트리거 로깅
    INSERT INTO ApiResponseLog (
        operation_type,
        name,
        response,
        api_call_start_time,
        api_call_end_time,
        api_call_duration_ms,
        insert_start_time,
        insert_end_time,
        insert_duration_ms
    )
    VALUES (
        @operation_type,
        @name,
        @response,
        @api_call_start_time,
        @api_call_end_time,
        DATEDIFF(MILLISECOND, @api_call_start_time, @api_call_end_time),
        @insert_start_time,
        SYSDATETIME(), -- insert 끝난시간 기록
        DATEDIFF(MILLISECOND, @insert_start_time, SYSDATETIME())
    );
END;
GO

 

SQL 마크다운이 화나게 된다.

 

아무튼 이렇게 하고, BasicTable을 수정해보자. 

딸깎

API 콜이 잘 이루어지고, 데이터도 잘 저장됨을 확인할 수 있다.

여기서 주목해야할 것은 저 api_call_duration_ms 이다. 222ms(0.2s) 로 찍혀있는데, 통상적인 서버로의 API 콜을 생각한다면 무난한 수치이다. 그런데 이것이 데이터베이스 트랜잭션 내에 포함된다는 것이다. 뒤의 insert_duration_ms 를 보면, ms 이하의 단위로 기록됨을 확인할 수 있다.

무결해야 할 하나의 operation이 트리거에서 API call 을 함으로 인해 유의미한 시간 소모가 생겼다.

 

데이터베이스 부하를 실험해보자.

 

-- Insert 20 records
INSERT INTO BasicTable (name) VALUES ('Name1');
INSERT INTO BasicTable (name) VALUES ('Name2');
INSERT INTO BasicTable (name) VALUES ('Name3');
INSERT INTO BasicTable (name) VALUES ('Name4');
INSERT INTO BasicTable (name) VALUES ('Name5');
INSERT INTO BasicTable (name) VALUES ('Name6');
INSERT INTO BasicTable (name) VALUES ('Name7');
INSERT INTO BasicTable (name) VALUES ('Name8');
INSERT INTO BasicTable (name) VALUES ('Name9');
INSERT INTO BasicTable (name) VALUES ('Name10');
INSERT INTO BasicTable (name) VALUES ('Name11');
INSERT INTO BasicTable (name) VALUES ('Name12');
INSERT INTO BasicTable (name) VALUES ('Name13');
INSERT INTO BasicTable (name) VALUES ('Name14');
INSERT INTO BasicTable (name) VALUES ('Name15');
INSERT INTO BasicTable (name) VALUES ('Name16');
INSERT INTO BasicTable (name) VALUES ('Name17');
INSERT INTO BasicTable (name) VALUES ('Name18');
INSERT INTO BasicTable (name) VALUES ('Name19');
INSERT INTO BasicTable (name) VALUES ('Name20');

-- Update 15 records
UPDATE BasicTable SET name = 'UpdatedName1' WHERE id = 1;
UPDATE BasicTable SET name = 'UpdatedName2' WHERE id = 2;
UPDATE BasicTable SET name = 'UpdatedName3' WHERE id = 3;
UPDATE BasicTable SET name = 'UpdatedName4' WHERE id = 4;
UPDATE BasicTable SET name = 'UpdatedName5' WHERE id = 5;
UPDATE BasicTable SET name = 'UpdatedName6' WHERE id = 6;
UPDATE BasicTable SET name = 'UpdatedName7' WHERE id = 7;
UPDATE BasicTable SET name = 'UpdatedName8' WHERE id = 8;
UPDATE BasicTable SET name = 'UpdatedName9' WHERE id = 9;
UPDATE BasicTable SET name = 'UpdatedName10' WHERE id = 10;
UPDATE BasicTable SET name = 'UpdatedName11' WHERE id = 11;
UPDATE BasicTable SET name = 'UpdatedName12' WHERE id = 12;
UPDATE BasicTable SET name = 'UpdatedName13' WHERE id = 13;
UPDATE BasicTable SET name = 'UpdatedName14' WHERE id = 14;
UPDATE BasicTable SET name = 'UpdatedName15' WHERE id = 15;

-- Delete 15 records
DELETE FROM BasicTable WHERE id = 1;
DELETE FROM BasicTable WHERE id = 2;
DELETE FROM BasicTable WHERE id = 3;
DELETE FROM BasicTable WHERE id = 4;
DELETE FROM BasicTable WHERE id = 5;
DELETE FROM BasicTable WHERE id = 6;
DELETE FROM BasicTable WHERE id = 7;
DELETE FROM BasicTable WHERE id = 8;
DELETE FROM BasicTable WHERE id = 9;
DELETE FROM BasicTable WHERE id = 10;
DELETE FROM BasicTable WHERE id = 11;
DELETE FROM BasicTable WHERE id = 12;
DELETE FROM BasicTable WHERE id = 13;
DELETE FROM BasicTable WHERE id = 14;
DELETE FROM BasicTable WHERE id = 15;

 

우선 트리거를 제거하고 이를 시험해보자. 

 

[2024-06-02 07:23:53] 50 rows affected in 26 ms

 

20개의 삽입, 15개의 update, 15개의 delete 가 0.02초만에 수행되었음을 확인할 수 있다. 조금 자세히 보자면,

insert 의 경우, clustered index insert 를 수행하는 작업의 cost 와 실제 수행 시간을 확인할 수 있다. 

 

이제 트리거를 삽입하고 벤치마크를 확인해보자.

        INSERT INTO BasicTable (name) VALUES ('Name28');
        INSERT INTO BasicTable (name) VALUES ('Name29');
        INSERT INTO BasicTable (name) VALUES ('Name30');
        INSERT INTO BasicTable (name) VALUES ('Name31');
        INSERT INTO BasicTable (name) VALUES ('Name32');
        INSERT INTO BasicTable (name) VALUES ('Name33');
        INSERT INTO BasicTable (name) VALUES ('Name34');
        INSERT INTO BasicTable (name) VALUES ('Name35');
        INSERT INTO BasicTable (name) VALUES ('Name36');
        INSERT INTO BasicTable (name) VALUES ('Name37');
        INSERT INTO BasicTable (name) VALUES ('Name38');
        INSERT INTO BasicTable (name) VALUES ('Name39');
        INSERT INTO BasicTable (name) VALUES ('Name40');
        INSERT INTO BasicTable (name) VALUES ('Name41');
        INSERT INTO BasicTable (name) VALUES ('Name42');
        INSERT INTO BasicTable (name) VALUES ('Name43');
        INSERT INTO BasicTable (name) VALUES ('Name44');
        INSERT INTO BasicTable (name) VALUES ('Name45');
        INSERT INTO BasicTable (name) VALUES ('Name46');
        INSERT INTO BasicTable (name) VALUES ('Name47');
[2024-06-02 07:33:20] 20 rows affected in 4 s 894 ms
master> -- Update 15 records
        UPDATE BasicTable SET name = 'UpdatedName28' WHERE id = 28;
        UPDATE BasicTable SET name = 'UpdatedName29' WHERE id = 29;
        UPDATE BasicTable SET name = 'UpdatedName30' WHERE id = 30;
        UPDATE BasicTable SET name = 'UpdatedName31' WHERE id = 31;
        UPDATE BasicTable SET name = 'UpdatedName32' WHERE id = 32;
        UPDATE BasicTable SET name = 'UpdatedName33' WHERE id = 33;
        UPDATE BasicTable SET name = 'UpdatedName34' WHERE id = 34;
        UPDATE BasicTable SET name = 'UpdatedName35' WHERE id = 35;
        UPDATE BasicTable SET name = 'UpdatedName36' WHERE id = 36;
        UPDATE BasicTable SET name = 'UpdatedName37' WHERE id = 37;
        UPDATE BasicTable SET name = 'UpdatedName38' WHERE id = 38;
        UPDATE BasicTable SET name = 'UpdatedName39' WHERE id = 39;
        UPDATE BasicTable SET name = 'UpdatedName40' WHERE id = 40;
        UPDATE BasicTable SET name = 'UpdatedName41' WHERE id = 41;
        UPDATE BasicTable SET name = 'UpdatedName42' WHERE id = 42;
[2024-06-02 07:33:23] 15 rows affected in 3 s 642 ms
master> -- Delete 15 records
        DELETE FROM BasicTable WHERE id = 28;
        DELETE FROM BasicTable WHERE id = 29;
        DELETE FROM BasicTable WHERE id = 30;
        DELETE FROM BasicTable WHERE id = 31;
        DELETE FROM BasicTable WHERE id = 32;
        DELETE FROM BasicTable WHERE id = 33;
        DELETE FROM BasicTable WHERE id = 34;
        DELETE FROM BasicTable WHERE id = 35;
        DELETE FROM BasicTable WHERE id = 36;
        DELETE FROM BasicTable WHERE id = 37;
        DELETE FROM BasicTable WHERE id = 38;
        DELETE FROM BasicTable WHERE id = 39;
        DELETE FROM BasicTable WHERE id = 40;
        DELETE FROM BasicTable WHERE id = 41;
        DELETE FROM BasicTable WHERE id = 42;
[2024-06-02 07:33:26] 15 rows affected in 3 s 91 ms

 

INSERT 20 개에 4.892s, UPDATE 15개에 3.642s, DELETE 15개에 3.091s 로 총 11초정도가 소요되었음을 확인할 수 있다. 어찌보면 트리거에 API 호출을 묶어버렸으니 당연한 결과이다. 

 

그렇다면 API 호출과 ApiResponseLog 테이블에 삽입은 제대로 이루어졌을까?

 

안타깝게도 그렇지 못하다.

 

SSMS에 들어가서 보니, SQL trigger 가 CPU time 을 잡아먹고 있는 것이 관찰된다. 이것이 쌓이다 보니 concurrency가 급증하여 제대로 전달되지 못하는 객체들이 생기는 것 같다.

 

비단 이뿐이 아니라 서버에서 API response 가 정상적인 시간에 전달되지 않는 경우에도 Trigger execution limit 에 도달하여 트랜잭션이 폭발할 수도 있다. 이로 인해 병목이나 lock 이 생겨버릴 수도 있고, 그냥 silent throw 로 트랜잭션이 넘어가더라도 원하는 API response 는 받아오지 못해 로직이 달성되지 못한 것이다.

 

결론으로, latency 를 줄이려고 애쓰고 애쓴 데이터베이스에 트리거로 API call 을 다는 선택을 해서 제일 가벼워야 할 동작을 망쳐버리지 말자. API call 이 무시할 만큼 가볍다 하더라도 API call 을 하는 서버와 데이터베이스가 실행되는 서버는 다르기 때문에 의존 관계가 생겨버릴 수밖에 없다. 

 

트리거의 API call 은 트랜잭션의 atomicity - 원자성을 해쳐버리기도 한다. 외부 호출은 데이터베이스 트랜잭션 관리의 일부가 되지 못하기 때문이다. 

 

또한 오류가 발생했을때 디버깅 지옥도가 펼쳐질 것이다. 아예 못할수도 있을듯 싶다.

 

진짜진짜진짜 비즈니스 로직상 필요한 경우가 아닌 이상 쓰지말자. 그냥 쓰지말자.