7장. 트랜잭션과 동시성 제어 - 복수의 쿼리 통합

트랜잭션이란?

• 쿼리를 처리하는 단위. 한 번에 실행되어야 하는 복수의 쿼리를 한 단위로 묶은 것

ACID 특성

1. Atomicity(원자성)
   • 트랜잭션이 전부 성공하거나 전부 실패하여야 함
2. Consistency(일관성)
   • 트랜잭션이 성공적으로 실행 완료되면 언제나 일관성 있는 데이터베이스 상태를 유지해야 함
   • 일관성 유지를 위해 데이터베이스 오브젝트에 각종 정합성 제약(유니크 등) 사용 가능
3. Isolation(고립성/격리성)
   • 트랜잭션 수행 시 다른 트랜잭션의 연산 작업이 끼어들 수 없어야 함
   • 트랜잭션 밖의 연산이 중간 단계의 데이터를 볼 수 없음
   • 고립성을 위해 Lock과 격리수준을 사용할 수 있음
4. Durability(지속성)
   • 성공적으로 수행된 트랜잭션은 영원히 반영되어야 함
   • 모든 트랜잭션은 로그로 남고 시스템 장애 시 발생 전 상태로 되돌릴 수 있어야 함

MySQL 테이블

MyISAM형

• 트랜잭션을 사용할 수 없는 단순한 구조

InnoDB형

MySQL InnoDB란? 글 참고

• 일반적인 DBMS처럼 트랜잭션 구조 사용 가능. MVCC(Multi Versioning Concurrency Control) 구조로 동작
• read 수행 시 write 중이어도 Block되지 않음 (read와 read도 서로 Block되지 않음)
  • read 내용은 격리 수준에 따라 내용이 바뀔 수 있음
• write 시 배타적 Lock을 얻음
  • 기본적으로 행 단위로 Lock되며 트랜잭션 종료까지 유지됨
  • 격리 수준이나 InnoDB 설정에 따라 실제 Lock되는 행의 범위가 다를 수 있음
  • Lock 단위는 테이블 전체, 블록, 행 등이 될 수 있음
• write와 write의 경우 나중에 온 트랜잭션이 Lock을 획득하려고 할 때 Block 됨
  • 일정 시간 사이에 Lock을 획득하지 못하면 Lock Timeout
• write 시 이전의 데이터를 UNDO 로그로 Rollback Segment 영역에 유지
• UNDO 로그의 용도
  • write 트랜잭션 rollback 시 write 전으로 되돌리기 위해 이용
  • 복수의 트랜잭션으로부터 격리 수준에 따른 write 데이터 참조에 이용
• 같은 행을 write 할 때마다 UNDO 로그가 작성되어 같은 행에 대한 복수 버전 존재

ANSI 격리 수준 (Transaction Isolation Level)

MySQL 격리 수준(Transaction Isolation Level) 글 참고

• Read Uncommitted (커밋되지 않은 읽기)
  • 가장 완화된 격리 수준
  • DIRTY READ 발생
  • 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽음
  • MVCC 사용 시 Read가 Block되는 경우가 없으므로 해당 격리 수준을 사용할 필요가 없음
• Read Committed (커밋된 읽기)
  • NON-REPEATABLE READ 발생
  • 트랜잭션 안에서 이전에 읽은 데이터를 다시 읽을 때 이전의 결과와 다를 수 있음
• Repeatable Read (반복 읽기)
  • PHANTOM READ 발생
  • 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상 발생 가능
  • MySQL의 기본 설정
• Serializable (직렬화 가능)
  • 가장 엄격한 격리 수준

Lock Timeout

• read와 write는 서로를 block하지 않지만, write와 write에서는 나중에 실행된 write가 lock 대기 상태가 됨
• 언제 lock이 풀릴지 모르므로 다른 한쪽은 lock을 기다릴지, 기다리지 않을지, 어느 정도만 기다릴지 설정 가능
• MySQL에서 innodb_lock_wait_timeout 시스템 변수로 조정 가능

  -- MySQL에서 기다리지 않는 설정은 없으므로 유효값은 1(초) 이상
  > set innodb_lock_wait_timeout=1;

• Lock Timeout이 발생하는 경우 rollback되는 단위는 2가지
  • 트랜잭션 전체 rollback
  • 해당 쿼리만 rollback (default)
• 트랜잭션 전체 rollback 설정 방법
  • Timeout 이후 명시적으로 ROLLBACK 수행
  • innodb_rollback_on_timeout 시스템 변수 설정

DeadLock (교착 상태)

• 두 개 이상의 트랜잭션이 서로의 lock이 끝나기만을 기다리며 결과적으로 아무것도 완료되지 못하는 상태
• 일반적인 DBMS에서는 DeadLock을 검출해 교착 상태를 보고
• MySQL에서는 즉시 인식하고 시스템에 영향이 작은 쪽의 트랜잭션을 전체 rollback
• DeadLock을 완전 방지하는 것은 불가능하므로 DeadLock 발생 시 애플리케이션에서 트랜잭션을 재실행할 수 있어야 함
• 클라이언트에서 rollback된 트랜잭션의 오류, 서버 오류 로그 또는 모니터링 커맨드로 DeadLock 확인 가능
• DBMS 전반적 대책
  1. 트랜잭션을 자주 커밋
     • 트랜잭션의 단위가 더 작아지므로 교착 상태의 가능성을 낮춤
  2. 정해진 순서로 테이블(및 행)에 엑세스
     • 모든 트랜잭션이 테이블 a를 액세스한 후에 테이블 b를 액세스하도록 함
  3. 필요 없는 경우에는 Read Lock 획득(SELECT ~ FOR UPDATE 등)의 사용 피하기
  4. 쿼리에 의한 Lock 범위를 더 좁히거나 Lock 정도를 더 작게 하기
     • 가능한 행 잠금 사용
     • MySQL의 경우 트랜잭션 격리수준을 Read Committed으로 설정
  5. 한 테이블의 여러 행을 복수 커넥션에서 순서 변경 없이 write하면 DeadLock 발생 가능성이 높아짐
     • 여러 커넥션에서 write로 인한 DeadLock이 자주 발생할 경우 테이블 단위의 Lock을 통해 write를 Serialize하면 동시성은 떨어지지만 DeadLock은 피할 수 있음
• MySQL(InnoDB)의 대책
  6. 테이블에 적절한 인덱스 추가
     • 인덱스를 사용하지 않는 경우 필요한 행의 Lock이 아닌 스캔한 행 전체에 Lock이 걸리게 됨

복수 커넥션에서의 트랜잭션

• DDL에 따른 테이블 작성과 DML에 의한 데이터 저장은 트랜잭션이 커밋되기 전까지는 다른 커넥션에서 보이지 않음
• 하지만 이와 상관없이 다른 커넥션에서 보이는 경우가 있음

DDL에 따른 암묵적 Commit

• MySQL이나 Oracle에서는 CREATE TABLE과 같은 DDL 실행 시 암묵적 커밋이 발행됨
• 암묵적 커밋으로 인해 한 개의 커넥션에서 실행된 CREATE TABLE이 성공하면 이후에 다른 커넥션에서 참조 가능

Auto Commit 설정

• 트랜잭션의 개시(BEGIN TRANSACTION, START TRANSACTION, SET TRANSACTION 등)가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법은 2가지 모드가 있음
  • Auto Commit: 하나의 SQL문이 하나의 트랜잭션
  • 사용자가 COMMIT 또는 ROLLBACK을 실행하기까지가 하나의 트랜잭션
• MySQL, PostgreSQL, SQL Server 등에서는 기본 설정이 Auto Commit

해서는 안되는 트랜잭션 처리

Auto Commit

• MySQL에서 새로운 연결은 모두 default로 Auto Commit(하나의 SQL문이 하나의 트랜잭션)이 설정됨
• command line client 같은 대화형 도구를 사용해 간단한 쿼리 실행과 테스트를 하는 경우에는 편리하지만 애플리케이션의 Lock 실행 시에는 Commit 부하가 너무 높음
• 일정 수 이상의 갱신 처리나 트랜잭션 기능 등은 Auto Commit 대신 적절한 단위와 트랜잭션 격리 수준을 사용하도록 함

긴 트랜잭션

• 긴 트랜잭션은 데이터베이스 트랜잭션의 동시성이나 자원의 유효성을 저하시킴
• 갱신을 포함하는 경우 Lock이 장시간 이어지고 Block된 트랜잭션을 Timeout 시킴 ➡️ DeadLock 발생 가능
• 대량 처리는 적당한 크기의 트랜잭션으로 나눠 실행하기
  • ex) 신규 테이블에 데이터 로드 시 1만건당 커밋 수행
• 아무것도 하지 않는 트랜잭션 주의
  • 읽기 후 트랜잭션이 열린 상태를 유지한다면, 반복 읽기를 위해 UNDO 로그가 계속 유지됨
• 트랜잭션 중에 대화 처리 넣지 않기
  • 사용자와의 대화 처리는 타임아웃을 설정하지 않는 한 끝없이 사용자를 기다리게 됨 ➡️ 전체 효율 저하
  • 대화 처리를 최대한 피하고, 사용하는 경우에는 상한을 정하여 무한정 기다리지 않기
• 시스템의 처리 능력을 파악하고 트랜잭션 수를 제한하기
  • 시스템의 요건이나 하드웨어 성능에 따라 최적의 트랜잭션 수가 달라짐
  • 부하 실험을 수행하여 측정하는 수밖에 없음
  • 커넥션 수 상한을 설정하는 max_connections 시스템 변수로 조절
• 트랜잭션 관련 설정 확인
  • 시스템 요건이나 애플리케이션 로직에 맞춰 Auto Commit, 격리 수준 등에 대한 설정 변경하기