[MySQL Stored Procedure]트랜잭션 관리

Isolation Level

 - READ UNCOMMITED
   Dirty read허용
   속도는 빠르지만 commit 되지 않은 ROW를 다른 세션에서 볼 수 있음

 - READ COMMITED
   Commit된 row만 읽을 수 있음

 - REPEATABLE READ
   트랜잭션이 시작된 시점 기준의 값을 볼 수 있음

   Default 설정

 - SERIALIZABLE
   각 트랜잭션이 독립적으로 동작
   SELECT시에도 락을 사용
 

트랜잭션 관련 Command
 - START TRANSACTION : 시작, AUTO_COMMIT를 0으로 설정
 - COMMIT : 트랜잭션의 변경 내용을 저장, LOCK해제
 - ROLLBACK : 트랜잭션의 변경 취소
 - SAVEPOINT savepoint_name : 저장단계 지정
 - ROLLBACK TO SAVEPOINT savepoint_name : 단계로 롤백
 - SET TRANSACTION : Isolation레벨 지정
 - [LOCK|UNLOCK] TABLE : 테이블에 락을 지정/해제
 
유의사항
 - 아래 구문들은 트랜잭션 처리가 되지 않음

   ALTER [FUNCTION|PROCEDURE|TABLE]
   CREATE [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   DROP [DATABASE]FUNCTION|INDEX|PROCEDUER|TABLE]
   LOCK TABLES/UNLOCK TABLES
   RENAME TABLE/TRUNCATE TABLE
   BEGIN, SET AUTOCOMMIT=1, START TRANSACTION
   LOAD MASTER DATA

 

 

LOCK의 종류별 발생 Case
 - UPDATE : 변경되는 모든 ROW에 락설정
 - INSERT : PK, Unique Key레코드에 락설정
 - LOCK TABLES : 전체 테이블에 락설정
 - SELECT ... FOR UPDATE : Select 결과 row에 대해 Exclusive 락 설정, Read/Write 불가능
 - SELECT ... LOCK IN SHARE MODE : Select 결과 row에 대해 Shared Lock 설정, Read는 가능

 

Deatlock
 - InnoDB는 DeadLock상황을 탐지해서 트랜잭션들을 강제로 Rollback함

 

 

LockTimeout
 - 지정한 시간동안 Lock를 획득하지 못하면 Rollback처리

 

 

Locking 전략
 - Pessimistic Locking Strategy
   트랜잭션에서 읽는 row에 미리 락을 설정
   concurrent update가 빈번하다 가정
   단순하고 견고한 코드
   트랜잭션 처리 시간이 길어지는 경우 성능 저하 유발

 - Optimistic Locking Strategy
   마지막으로 업데이트 하는 순간 Lock를 확인하고 처리
   update 빈도가 낮다고 가정

 

 

트랜잭션 디자인 가이드라인
 - 최대한 작게 유지
 - Rollback는 최대한 자제 - Retry할 수 있도록 예외 처리를 하는 것이 좋음
 - Savepoint는 사용 자제
 - Pessimistic Locking Strategy를 기본으로 하고 처리량이 중요한 경우 Optimistic 고려