재귀 저장 프로시저(Recursive Stored Procedure)란?
저장 프로시저(stored procedure)가 스스로를 다시 호출하는 경우, 이를 재귀 저장 프로시저라고 부릅니다. 이처럼 자기 자신을 반복적으로 호출하여 문제를 해결하는 프로그래밍 기법을 일반적으로 재귀(recursion)라고 합니다.
재귀는 계층 구조 탐색, 팩토리얼 계산, 조직도 순회 등 반복적인 하위 작업을 처리할 때 유용하지만, 종료 조건이 잘못되면 무한히 호출되면서 시스템 자원을 고갈시킬 수 있습니다. 이러한 위험 때문에 MySQL은 저장 프로시저의 재귀 호출 깊이를 제한하고 있습니다.
MySQL이 재귀를 제한하는 이유
MySQL은 재귀 호출 횟수에 제한을 두어 잘못 작성된 프로시저로 인해 발생할 수 있는 심각한 오류나 서버 과부하를 사전에 방지합니다. 이 제한 값은 시스템 변수인 max_sp_recursion_depth로 관리되며, 아래 쿼리로 현재 설정 값을 확인할 수 있습니다.
mysql> SHOW VARIABLES LIKE '%recur%'; +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_sp_recursion_depth | 0 | +------------------------+-------+ 1 row in set (0.01 sec)
위 결과에서 볼 수 있듯이, 기본값은 0입니다. 즉, 기본 설정에서는 저장 프로시저의 재귀 호출이 허용되지 않습니다.
재귀 깊이 제한 변경하기
필요하다면 이 값을 최대 255까지 늘릴 수 있습니다. 다음과 같이 SET 구문을 사용하면 됩니다.
mysql> SET @@GLOBAL.max_sp_recursion_depth = 255// Query OK, 0 rows affected (0.00 sec) mysql> SHOW VARIABLES LIKE '%recur%'// +------------------------+-------+ | Variable_name | Value | +------------------------+-------+ | max_sp_recursion_depth | 255 | +------------------------+-------+ 1 row in set (0.01 sec)
참고로 이 값은 세션 단위(@@SESSION.max_sp_recursion_depth)와 전역 단위(@@GLOBAL.max_sp_recursion_depth) 모두 설정 가능하며, 세션 값은 전역 값을 초과할 수 없습니다.
프로시저 작성 시 제한 확장하기
전역 설정을 변경하는 것 외에도, 저장 프로시저를 작성하는 시점에 직접 재귀 깊이 제한을 지정할 수도 있습니다. 프로시저 본문 내부에서 해당 세션의 재귀 깊이 값을 설정하면, 그 프로시저가 실행되는 동안에만 유효한 재귀 제한을 적용할 수 있습니다.
단, 재귀 깊이를 크게 설정할수록 스레드당 메모리 사용량이 증가하므로, 실제 필요한 범위 내에서 신중하게 값을 조정하는 것이 좋습니다.