AWS Aurora for MariaDB를 사용하다 보면 트랜잭션이 락을 점유한 상태로 오래 유지될 수 있습니다. 이렇게 되면 다른 트랜잭션이 대기해야 하며, 성능 저하나 데드락 문제가 발생할 수 있습니다. 이를 방지하기 위해 트랜잭션이 일정 시간 이상 락을 유지하면 자동으로 롤백되도록 설정하는 방법을 정리했습니다.
1. innodb_lock_wait_timeout 설정 (트랜잭션 대기 시간 제한)
InnoDB에서 트랜잭션이 락을 기다릴 수 있는 최대 시간을 설정하는 파라미터입니다. 기본값은 50초이며, 이를 줄이면 일정 시간이 지나면 자동으로 롤백됩니다.
SET GLOBAL innodb_lock_wait_timeout = 10;
- 기본값: 50초
- 설정값 예시: 10초 (10초 이상 락을 기다리면 자동 롤백)
- 적용 방식: Aurora Parameter Group에서 설정 가능
트랜잭션이 innodb_lock_wait_timeout을 초과하면 Lock wait timeout exceeded 오류가 발생하고 자동 롤백됩니다.
2. innodb_rollback_on_timeout 설정 (타임아웃 시 강제 롤백 활성화)
기본적으로 MariaDB는 트랜잭션이 타임아웃될 경우 일부 변경 사항을 유지할 수 있습니다.
이를 방지하고 타임아웃 발생 시 전체 트랜잭션을 롤백하려면 아래 옵션을 활성화해야 합니다.
SET GLOBAL innodb_rollback_on_timeout = 1;
- 기본값: 0 (타임아웃 시 일부 변경 유지)
- 설정값 예시: 1 (타임아웃 발생 시 전체 트랜잭션 자동 롤백)
이 설정을 활성화하면 innodb_lock_wait_timeout에 의해 트랜잭션이 롤백될 때 일부 변경이 유지되지 않고 전체가 롤백됩니다.
3. max_statement_time 설정 (쿼리 실행 시간 제한)
특정 SQL 쿼리가 너무 오래 실행될 경우 자동으로 중단할 수도 있습니다.
SET GLOBAL max_statement_time = 5;
- 기본값: 0 (제한 없음)
- 설정값 예시: 5 (쿼리가 5초 이상 실행되면 강제 종료)
주의: 이 설정은 트랜잭션의 락과 직접적인 관련은 없으며, 개별 쿼리의 실행 시간을 제한하는 역할을 합니다.
4. wait_timeout 설정 (유휴 세션 자동 종료)
사용자의 세션이 너무 오래 유지되면 락이 걸릴 수 있습니다. 일정 시간이 지나면 자동으로 세션을 종료하려면 아래 설정을 추가하세요.
SET GLOBAL wait_timeout = 30;
- 기본값: 28800초 (8시간)
- 설정값 예시: 30 (30초 동안 유휴 상태면 자동 종료)
주의: 이 설정은 실행 중인 트랜잭션이 아닌 유휴 세션에만 적용됩니다.
결론: 트랜잭션이 일정 시간 락을 점유하면 자동으로 롤백하려면?
innodb_lock_wait_timeout = X 설정 → 트랜잭션이 대기하는 최대 시간을 제한
innodb_rollback_on_timeout = 1 설정 → 타임아웃 발생 시 강제 롤백
추가적으로, max_statement_time 및 wait_timeout을 설정하여 쿼리 실행 시간 및 세션 유지 시간을 관리
이렇게 설정하면 AWS Aurora for MariaDB에서 트랜잭션이 오랜 시간 락을 점유하는 것을 방지하고, 자동으로 롤백되도록 설정할 수 있습니다.