MySql(Aurora) 프로시져 Transaction 주의사항

MySql DB를 사용할때는 DB Transaction을 확인 후 개발을 진행 해야 합니다.
Transaction은 데이타를 커밋(Commit) / Select 할때 영향을 주게 되며, DB Lock 발생을 초래하여 Application에 영향을 줄 수 도 있습니다.
현재 개발추세는 DB Procedure를 미사용하여 개발을 진행하지만, 좀더 빠른속도, 개발환경상 Procedure를 사용할때가 있습니다.
이때 Transaction을 이해 안하고 개발을 진행하게 되면 운영환경 오픈시 시스템 장애를 초래하게 됩니다.
이에 Transaction Level과 프로시져 작성시 Transaction을 제어 하는 방법을 공유 드립니다.

Transaction Level

MySql은 innoDB로 세팅된 상태를 기준으로 설명 드립니다.

Transaction Level	설명
READ UNCOMMITTED (dirty read)	-. Commit전 상태를 볼 수 있음 -. Transaction이 주용하지 않은 속도가 중요할 경우 사용
READ-COMMITTED	-. Commit 된 내역에 대해서만 트랜잭션이 다른곳에서도 조회가 가능함. -. 일반적으로 가장 많이 사용됨.
REPEATABLE READ	MySQL InnoDB 스토리지 엔진의 Default Isolation Level Select 시 현재 데이터 버전의 Snapshot을 만들고, 그 Snapshot으로부터 데이터를 조회 동일 트랜잭션 내에서 데이터 일관성을 보장하고 데이터를 다시 읽기 위해서는 트랜잭션을 다시 시작해야 함
SERIALIZABLE	-. 가장 높은 Isolation Level -. SELECT 문장이 사용하는 모든 데이터에 Shared Lock이 걸림 -. 다른 트랜잭션에서는 해당 영역에 관한 데이터 변경 뿐만 아니라 입력도 불가 -. 은행권 같이 Transaction이 중요한 역활을 할때 사용함.

MySql Procedure Transaction Level 변경

MySql(Aurora) DB에서 기본으로 세팅되는 Transaction Level은 REPEATABLE READ 입니다.
Transaction Level에 대한 변경없이 프로시져를 작성하게 되면, Table Lock등이 발생하게 되어 Level을 변경하고서 프로시져를 수행 해야 합니다.

DB Transaction Level 조회
-. 조회가 안될경우 계정권한이 없는것으로 DB Admin 권한 부여 필요함.

SELECT @@GLOBAL.tx_isolation, @@tx_isolation;

프로시져 Transaction Level 변경

    ## tx_isolation변경 (READ COMMITTED 으로 변경)
    SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

{ 프로시져 개발 코드 작성}

	# 작업 완료시 명시적인 COMMIT 명령어 실행
	COMMIT; ## Transaction 최종 커밋
    
	# 작업시 COMMIT 미 처리시는 ROLLBACK 처리
	ROLLBACK;
    
	#프로시져 종료시 Transaction level 원복
	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

프로시져 작성시 주의 사항 정리
1. Transaction Level을 Read Committed으로 명시적으로 사용할것
2. Business Logic이 정상적으로 수행되면 명시적인 COMMIT을 꼭 할것
3. Business Logic에 오류가 발생하거나 프로시져 수행 중 오류 처리시 ROLLBACK 처리를 해줄것
4. 프로시져가 모두 종료되면, Transaction Level을 DB에 원래 Level로 변경처리 해줄것

실제 운영시 Trasaction Level을 고려 안하고 프로시져를 만들어 DB Lock발생 혹은 DB CPU 100%사용등에 대한 이슈가 발생 했습니다.