문제의 시작
Database trigger는 강력하지만 위험하다. application layer 밖에서 동작하기 때문에 흐름을 놓치기 쉽고, 외부 API 호출까지 묶이면 장애 전파와 transaction boundary가 복잡해진다. 이 글은 trigger에서 API를 호출하는 방식을 확인하며, 왜 신중하게 다뤄야 하는지 정리한 기록이다.
데이터베이스에서 트리거는 흔히 데이터 완결성 유지, 검증 등의 비즈니스 필요에 의해 사용된다. 어떤 특정 테이블, 특정 행, 특정 컬럼에 대해 INSERT, DELETE 등 operation 이 일어날 때 같은 트랜잭션에 묶여 기존의 작업에 더해 다른 작업을 수행하는 것이다.
그런데 여기에 통상 server application 단에서 하는 API 를 호출하는 작업을 묶을 수도 있다.
우선 서두에 강조할 부분은, 이는 절대 권장하지 않는 행위이다. 앞서 말했듯이 어플리케이션쪽에서 실행되는 데이터베이스 동작과 함께, 컨트롤러와 서비스 레이어에서 API를 호출하는 것이 당연한 절차인데, 굳이 민감하고 ACID성을 유지해야 할 데이터베이스 작업에서 API를 호출함으로 인해 예측하기 힘든 데이터베이스 부하를 일으킬 필요가 전혀 없다.
그 경고를 전제로, 데이터베이스 trigger에 API 호출을 붙이면 어떤 식으로 동작하고 어떤 부하와 결합도가 생기는지 확인해보자. 이는 일반적인 설계가 아니라, 비즈니스 제약 때문에 더 나은 선택지가 없을 때 검토할 수 있는 last-resort에 가깝다.
데이터베이스는 무난한 SQL SEVER 2019 와 분석에는 Datagrip 을 사용하였다.
먼저 간단한 name column만 가진 table을 만든다.
구현하면서 확인한 흐름
CREATE TABLE BasicTable (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL
);
GOHTTP call에는 name parameter를 넘기면 나이를 예측해주는 public API를 사용했다. 내 이름을 넣었을 때는 66세로 예측되었다.
결과를 어떻게 검증했는가
Trigger 내부에서 직접 API call을 하려면 SQL Server에서 다음 option을 켜야 한다.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;이는 Microsoft COM(Component Object Model)의 OLE Automation Object를 사용하기 위한 MSSQL option이다. 현재 HTTP request에는 COM component 중 MSXML2.ServerXMLHTTP를 사용할 것이기 때문에 이 option을 켜야 한다. 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.Trigger 동작을 기록하기 위한 별도의 log table도 만든다.
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
);
GOoperation_type (UPDATE, SELECT, INSERT, DELETE) 와 함께 API 로 전달될 name 과 상응하는 response 를 넣어주겠다.
성능 테스트를 위해 API 호출 시간, DB insert 시간도 함께 기록하도록 확장했다.
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;
GOSnippet이 길지만, 중요한 점은 HTTP request가 trigger transaction path 안에서 실행된다는 것이다.
Trigger를 생성한 뒤 BasicTable을 수정해본다.
변경을 실행한다.
결과를 보면 API call이 실행되었고 response도 저장되었다.
여기서 핵심은 api_call_duration_ms다. 222ms(0.2s)로 찍혀 있는데, server-to-server API call만 놓고 보면 무난한 수치다. 문제는 이 시간이 database transaction 안에 포함된다는 것이다. 뒤의 insert_duration_ms를 보면 insert 자체는 ms 이하 단위로 기록된다.
무결해야 할 하나의 operation이 트리거에서 API call 을 함으로 인해 유의미한 시간 소모가 생겼다.
이제 database load를 확인해본다.
-- 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;먼저 trigger를 제거한 상태에서 테스트한다.
[2024-06-02 07:23:53] 50 rows affected in 26 ms
20개의 insert, 15개의 update, 15개의 delete가 약 0.02초 안에 끝났다. 조금 자세히 보면,
insert의 경우 clustered index insert 작업의 cost와 실제 수행 시간을 확인할 수 있다.
이제 trigger를 다시 활성화하고 benchmark를 확인한다.
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 msINSERT 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이 생길 수 있고, 오류가 조용히 삼켜진 채 트랜잭션이 넘어가더라도 원하는 API response를 받지 못했으므로 로직은 완성되지 않은 상태가 된다.
결론적으로, latency를 낮추기 위해 관리해 온 데이터베이스에 trigger 기반 API call을 붙여 가장 가벼워야 할 동작을 무겁게 만들면 안 된다. API call이 충분히 가볍다 하더라도, API call을 처리하는 서버와 데이터베이스가 실행되는 서버는 다르기 때문에 불필요한 의존 관계가 생긴다.
트리거의 API call은 트랜잭션의 atomicity, 즉 원자성을 해치기도 한다. 외부 호출은 데이터베이스 트랜잭션 관리의 일부가 되지 못하기 때문이다.
또한 오류가 발생했을 때 디버깅 난도가 크게 올라간다. 경우에 따라서는 원인 추적 자체가 어려워질 수도 있다.
명확한 비즈니스 요구와 대안 검토 없이 trigger에서 외부 API를 호출하는 설계는 피하는 편이 맞다.
설계 결론
Trigger 기반 API 호출은 문제를 빠르게 해결하는 우회로처럼 보일 수 있다. 하지만 ownership이 DB로 내려가면서 observability, retry, rollback, testability가 모두 어려워진다. 사용할 수는 있지만, application event나 outbox pattern으로 대체할 수 없는 이유가 분명할 때만 선택해야 한다.