7장

[somefood]

트랜잭션과 동시성 제어

트랜잭션이란

• 우리가 테이블을 갱신을 수행하기 위해 INSERT/DELETE/UPDATE를 사용하는데, 단일 쿼리만으로 구성하지 않고 보통 복수 쿼리를 연속적으로 수행하는 경우가 많음
• 또한, 갱신 전의 데이터로 SELECT를 사용할 때 이를 포함해 복수 쿼리를 일관된 형태의 한덩어리로 관리해야 함.
• 트랜잭션은 이런 복수 쿼리를 한 단위로 묶은 것이라고 보면 된다.
• 트랜잭션 하면 보통 ACID 특성과 격리레벨, 3가지 부정합에 대해서 알아야 함

MySQL에서 많이 알려진 엔진은 MyISAM과 InnoDB가 있는데, InnoDB만 트랜잭션을 사용할 수 있다.

ACID

ACID는 다음의 단어의 앞 글자들을 따온 것이다.

• Atomicity(원자성)
• Consistency(일관성)
• Isolation(고립성 또는 격리성)
• Durability(지속성)

Atomicity, 원자성

• 데이터의 변경(INSERT/DELTE/UPDATE)을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조를 의미
• All or Nothing으로 기억하면 좀 더 수월
• 예를 들어 내가 부산으로 여행을 간다하면 다음 절차를 수행할 것이다.
  • 서울 -> 부산행 좌석 예매
  • 부산 숙소 예약
  • 부산 -> 서울행 좌석 예매
  • 1~3의 대금을 지급
• 4까지 전부 잘 진행되면 트랜잭션은 COMMIT을 실행해 처리를 확정
• 이렇게 하여 각 데이터의 조작은 영구적으로 저장되어 결과가 손실되지 않는다.
• 만약 3번 과정을 진행 중 에러가 터진다면? ROLLBACK을 실행시켜, 1번 이전의 상황으로 돌아가야 한다. 이렇게 하여 데이터가 무결해질 수 있도록 해야함.

Consistency, 일관성

• 데이터베이스는 오브젝트(테이블이나 데이터베이스 내 정의할 수 있는 것들)에 대해 각종 정합성 제약을 추가할 수 있음
• 이는 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는 일관성을 유지하기 위함
• EX) 사용자 식별 번호를 만들 때 유니크 제약을 걸어두어 중복된 번호를 사용할 수 없도록 할 수 있다.

Isolation, 고립성(격리성)

• 일련의 데이터 조작을 복수 사용자가 동시에 실행해도 각각의 처리가 모순없이 실행되는 것을 보증한다.

• 격리성 없이 진행되면 이런 사태가 발생할 수 있음

• 만약 부산 호텔에 남은 방이 10개 있을 때의 예약 흐름

  • 현재 방의 수를 확인한다. 10개 조회 (select)
  • 빈 방에서 1을 빼고 결과를 갱신해준다. 9개로 갱신 (update)
  • 근데 여기서 사용자 A와 B가 극적으로 동시에 수행하게 되면, 둘 다 10개를 조회하고 9개로 업데이트하여 방 수는 1개만 줄어들게 된다.

• 이런 사태를 방지하기 위해 DB에서는 오브젝트인 테이블에 대해 잠금(Lock)을 걸어 후속 처리를 블록(Block)할 수 있음

• 잠금 단위는 테이블 전체, 블록, 행 등이 있는데 MySQL에선 트랜잭션 처리를 할 때 주로 행 단위의 잠금이 이용됨

• 사용자 A가 1번의 과정을 처리할 때 SELECT ~ FOR UPDATE구문을 통해 방 수에 대해 잠금을 걸어두면 사용자 B는 A의 처리가 끝나 잠금을 획득할 때까지 대기하게 되며, 이렇게 하여 정상적인 방 수를 갱신할 수 있게 된다.

• 또한, InnoDB의 경우에는 MVCC 구조로 동작하기 때문에 단순 값 참조의 경우엔 SELECT FOR UPDATE는 불필요하며 읽기는 블록되지 않는다. 테이블을 갱신하는 사용자가 소수고 참조하는 사용자가 많은 경우엔 사용자의 동시성, 병렬성이 높아진다.

• 격리성에는 여러 레벨이 존재하는데 ANSI에서 제시한 4개의 단계는 아래와 같다.

  • 커밋되지 않은 읽기 (Read Uncommitted)
  • 커밋된 읽기 (Read Committed)
  • 반복 읽기 (Repeatable Read)
  • 직렬화 가능 (Serializable)

• 직렬화 가능이 가장 엄격하며 숫자가 작아질수록 완화된다.

• 격리 수준에 따라 3 가지의 데이터 부정합이 발생할 수 있고, 다음과 같다.

현상	개요
더티 읽기(Dirty Read)	어떤 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽는 현상.EX)사용자 A가 값을 변경하고 아직 커밋하지 않았는데, 사용자 B가 이 변경된 값을 읽는 것 사용자 A가 10에서 9로 바꿨는데, B가 바로 이 9를 읽어버린 것이다. 확정 전의 더럽혀진(Dirty) 데이터를 읽어서 붙여졌다.
애매한 읽기(Fuzzy/NonRepeatable Read)	어떤 트랜잭션이 이전에 읽어 들인 데이터를 다시 읽었는데, 2회 이후의 결과가 1회 때와 다른 현상. EX) 사용자 A가 10을 읽고, 즉후 B가 값을 9로 변경해 커밋하였다. 다시 A가 조회하니 10이 아닌 9를 읽어 온 것이다. 10이란 값이 보증되지 않고 읽을 때마다 값이 애매하게 (Fuzzy) 조회되는 것에 붙여졌다.
팬텀 읽기(Phantom Read)	어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상 EX) 사용자 A가 범위 검색(빈 방이 10개 이상인 호텔) 하여 3행을 읽었다고 가정. 여기서 사용자 B가 그 범위에 들어가는 데이터 1행을 INSERT하고 커밋했다. 다시 A가 조회해보니 최조 SELECT해서 얻었던 3행이 아닌 4행이 선택된다. 이렇게 나타나거나 사라지는(DELTE/UPDATE) 데이터가 유령(Phantom) 같아 붙여졌다.

• 위와 같이 3가지 부정합은 격리레벨에 따라 발생할 수 있는데 아래의 표와 같다.

격리 수준	더티 읽기	애매한 읽기	팬텀 읽기
커밋되지 않은 읽기	O	O	O
커밋된 읽기	X	O	O
반복 읽기	X	X	O
직렬화 가능	X	X	X

Durability, 지속성

• 트랜잭션이 COMMIT 된 후, 조작된 데이터에 대해 영구적으로 반영되어 그 결과를 잃지 않는 것을 말한다.
• 이는 시스템이 정상일 때나 DB, OS의 이상 종료 같은 시스템 장애에도 견딜 수 있는 것이다. 트랜잭션을 지원하는 DB들은 하드 디스크에 로그를 기록하고 시스템에 이상이 발생하면 그 로그를 활용해 이상 발생 전의 상태까지 복원할 수 있다.

물론 하드 같은 저장소 자체가 문제되면... RIP...

어떤것들은 다른 커넥션에서 왜 바로 보일까

• DDL에 따른 암묵적인 커밋

MySQL이나 Oracle에서는 CREATE TABLE과 같은 DDL 실행 시 암묵적인 커밋이 발행한다. 이 때문에 한 개의 커넥션에서 실행된 CREATE TABLE이 성공하면 그 이후에 다른 커넥션에서도 참조할 수 있게 된다.

• 오토커밋 설정

트랜잭션의 개시 (BEGIN TRANSACTION, START TRANSACTION, SET TRANSACTION 등)가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법은 2가지가 있다.

하나의 SQL 문이 하나의 트랜잭션으로 구분됨 -> 이것이 오토 커밋 모드
사용자가 COMMIT 또는 ROLLBACK을 실행하기까지가 하나의 트랜잭션이 된다.
MySQL, PostgresSQL, SQL Server는 기본적으로 오토 커밋 모드로 되어있고 끌 수 있다.

Oracle은 커밋을 해주어야 하는데, 나도 이걸 몰랐다가 분명 변경했는데 반영이 안돼서 뭔가 했더니 커밋이 안 된거였다. 이렇게 실수하면 락걸려서 서비스 장애가 생길 수 있으니 DBMS마다 차이를 잘 인지해야겠다.

MVCC(Multi Versioning Concurrency Control)

• MySQL에서는 동시선 제어를 위해 MVCC를 사용하며 다음의 특성을 지님

1. 읽기를 수행할 경우 갱신 중이라도 블록되지 않는다
2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있음
3. 갱신 시 배타적 잠금을 얻음. 기본적으로 행 단위이며, 트랙잭션이 종료할 때까지 유지됨
4. 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 블록된다. 일정 시간을 기다리며 그 사이에 잠금을 획득할 수 없는 경우 잠금 타임 아웃 됨
5. 갱신하는 경우 갱신 전의 데이터를 UNDO 로그에 유지한다.

잠금 타임아웃&교착 상태

잠금 타임아웃

• 갱신과 참조는 서로 블록하지 않지만, 갱신과 갱신의 경우에는 나중에 온 갱신이 잠금 대기 상태가 된다.
• 잠금 해제를 기다리는 쪽에서 값을 통해 얼마나 기다릴지 여부를 설정할 수 있음

set innodb_lock_wait_timeout=1;

교착 상태

• 트랜잭션이 각각 특정 자원에 잠금을 걸고, 서로 잠금을 기다리며 발생하는 경우를 교착상태라고 함
• 일반적인 DBMS에서 교착 상태를 독자적으로 보고하며 이를 인식하여 시스템에 영향이 작은 쪽의 트랜잭션을 트랜잭션 개시 시점까지 롤백함
• 교착 상태의 빈도를 낮추려면 아래의 사항 고려
  • 트랜잭션 단위를 작게 하여 자주 커밋하기
  • 정해진 테이블 순서로 액세스하게 하기
  • 필요 없는 경우 읽기 잠금 획득을 지양하기
  • 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 지정. EX) InnoDB의 격리 레벨을 REPEATABLE -> READ COMMITTED로 변경
  • 동시에 많은 연결에서 갱신 때문에 발생하면 테이블 단위로 잠금을 획득해 갱신을 직렬화. 대신 동시성은 떨어짐
  • MySQL은 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용. 인덱스가 사용되지 않을 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸림

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

• 오토커밋
  • 쿼리 단위로 커밋하는 설정.
  • MySQL의 새로운 연결은 모두 기본으로 오토 커밋임.
  • 대화형 도구 같은 툴 사용 시 간단하여 편하지만, 애플리케이션의 잠금을 실행하는 데는 커밋의 부하가 너무 높음
• 긴 트랙잭션
  • 동시성이나 자원의 유효성을 저하시킴
  • 대량의 갱신 처리를 한 개의 트랙잭션으로 처리하면 UNDO로그의 크기가 커짐. 또한, OS 파일 시스템에서 크기도 줄어들지 않기에 좋지 않음. 적당한 크기로 나누어서 커밋 하도록 처리 권장
  • 단순 SELECT 같이 트랜잭션이 필요없는 작업에서 (REPETABLE일 때) 불필요한 UNDO가 유지되기에 유의하기
  • 사용자와의 대화 처리를 집어넣으면 기다리는 시간으로 인해 시스템 전체 효율이 떨어짐. 필요한 경우 상한을 걸어 두기
  • 시스템의 성능이나 요건(갱신이 많은지, 조회가 많은지)를 잘 판단하여 적절한 커넥션 상한 수를 지정해야 함

[JoungMinJu]

1. 트랜잭션이란?

한 덩어리의 쿼리 처리 단위를 트랜잭션이라고 한다.

트랜잭션은 다음 네 가지의 특성으로 정의된다.

1. Atomicity(원자성)
2. Consistency(일관성)
3. Isolation(고립성 또는 격리성)
4. Durability(지속성)

1-1. 원자성

변경을 수반하는 일련의 데이터 조작이 전부 성공할지 전부 실패할지를 보증하는 구조.

1-2. 일관성

일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는 구조.

1-3. 격리성

일련의 데이터 조작을 복수 사용자가 동시에 실행해도 '각각의 처리가 모순없이 실행되는 것을 보증한다'는 것.

이를 보증하기 위해 DB에서는 DB 오브젝트인 테이블에 대해 잠금을 걸어서 후속처리를 블록하는 방법이 있다.
잠금 단위에는 "테이블 전체, 블록, 행" 등이 있다. (MySQL은 주로 행 단위 잠금을 활용)

그렇다면 어떤 상태가 모순이 없다고 할 수 있을까?

바로 복수의 트랜잭션이 순서대로 실행되는 경우와 같은 결과를 얻을 수 있는 상태를 의미한다.

이것을 DBMS에서 격리 수준으로 구현하고 제공하는 것이 Serializable이라는 사양이다. Serializable이 보장되면 모순이 없는 상태가 만들어지기 훨씬 쉽겠지만, 성능면에선 결코 실용적이지 않다.

따라서 격리 수준을 네 단계로 나누는 것을 ANSI라는 규격 단체에서 정의했다.

1. Read Uncommitted
2. Read Committed
3. Repeatable Read
4. Serializable

격리 수준에 따라 발생할 수 있는 이상 현상은 아래와 같다.

1. Dirty Read
2. Non-Repeatable Read
3. Phantom Read

1-4. 지속성

시스템이 정상일 때만이 아니라 DB나 OS의 이상 종료, 즉 시스템 장애도 견딜 수 있다는 것을 의미한다.

많은 DB 구현에서는 트랜잭션 조작을 HDD에 "로그"로 기록하고 시스템에 이상이 발생하면 그 로그를 사용하여 이상 발생 전의 상태까지 복원하는 것으로 지속성을 실현하고 있다.

2. 다른 커넥션에서 테이블을 본다?

기본적으로 DDL과 DML에 의한 데이터 저장은 트랜잭션이 커밋되기 전까지는 다른 커넥션에서 보이지 않는다.

하지만

1. DDL에 따른 암묵적인 커밋
   MySQL이나 Oracle에서는 CREATE TABLE과 같은 DDL 실행 시 암묵적인 커밋이 발행한다. 이 때문에 한 개의 커넥션에서 실행된 CREATE TABLE이 성공하면 그 이후에는 다른 커넥션에서도 참조할 수 있게 된다.

2. 오토커밋 설정
   BEGIN TRANSACTION START TRANSACTION SET TRANSACTION과 같은 트랜잭션의 개시가 명시적으로 지정되지 않았을 때 트랜잭션을 구별하는 방법으로는 (1) 하나의 SQL문이 하나의 트랜잭션으로 구분, (2) 사용자가 커밋 또는 롤백을 실행하기 전까지가 하나의 트랜잭션이 된다 이 두 가지가 존재한다.
   이때 MySQL은 기본 설정이 오토커밋이다.

3. 트랜잭션 격리 수준에 따른 외관상의 차이

MySQL은 MVCC라는 기술을 사용하기 때문에 아래의 특징을 갖는다.

1. 읽기를 수행할 경우 갱신중이라도 block 되지 않는다. (읽기와 읽기도 서로 block 되지 않는다.)
2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.
3. 갱신 시 배타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제로 잠금하는 행의 범위가 다른 경우가 있다.
4. 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 block 된다. 일정 시간을 기다리며 그 사이에 잠금을 획득할 수 없는 경우에는 lock timeout 된다.
5. 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 "롤백 세그먼트"라는 영역에 유지한다. 이 UNDO 로그는 용도가 두 가지인데 첫 번째는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리는 것이고 두 번째는 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다.(같은 행을 갱신할 때마다 UNDO 로그가 작성되어 같은 행에 대한 ㅗㄱ수 버전이 존재하며 이에 의해 1과 2를 실현하고 있다.)

4. 잠금 타임아웃과 교착 상태가 발생하는 이유

잠금 타임아웃이란?

갱신과 참조는 서로를 block 하지 않지만, 갱신과 갱신이 부딪치는 경우에는 나중에 온 갱신이 잠금 대기 상태가 된다.
이때 어느정도 기다릴지를 설저할 수 있다. MySQL과 같은 경우엔 innodb_lock_wait_timeout 이란 시스템 변수로 설정한다.

잠금 대기로 타임아웃이 발생하는 경우 DBMS로부터 롤백되는 단위가 다를 때가 있는데, 해당 트랜잭션 전체를 롤백하는 경우와 쿼리만 롤백하는 것이다.
MySQL에서는 잠금 대기로 타임아웃이 발생하면 롤백되는 것은 기본으로 오류가 발생한 쿼리이다. 트랜잭션 전체를 롤백하고 싶다면 다음 방법으로 할수 있다.

• 타임아웃 오류 후 명시적으로 rollback을 실행한다.
• innodb_rollback_on_tiemout 시스템 변수를 설정한다.

교착상태란?

잠금을 유지한 채 서로 잠금을 건 자원에 잠금이 필요한 처리를 실행하면 아무리 기다려도 상황이 바뀌지 않는 상태가 된다. 이것이 바로 교착상태이다.

MySQL은 교착 상태가 일어나면 이를 즉시 인식해 시스템에 영향이 작은 쪽의 트랜잭션을 트랜잭션 개시 시점까지 롤백한다.

교착상태 빈도 낮추는 대책

교착 상태는 완벽하게 없앨 수는 없다. 따라서 어플리케이션 쪽에서는 항상 트랜잭션이 교착 상태를 일으켜 롤백되는 경우에 트랜잭션을 재실행할 수 있는 구조로 만들어야 한다.

DBMS 전반적인 대책

1. 트랜잭션을 자주 커밋한다.
2. 정해진 순서로 테이블에 액세스하게 한다.
3. 필요 없는 경우에는 읽기 잠금 획득의 사용을 ㅣ한다.
4. 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 한다.
5. 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착 상태가 발생하기 쉽다. 동시에 많은 연결에서 갱신 때문에 교착 상태가 자주 발생한다면 테이블 단위의 잠금을 획득해 갱신을 직렬화하면 동시성은 떨어지지만 교착 상태는 회피할 수 있어서 전체 처리로 보면 좋은 예도 있다.

MySQL의 대책

1. 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대한 잠금이 걸리게 된다.

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

1. 오토커밋

오토커밋이란 쿼리 단위로 커밋하는 설정이다.
이 설정은 커밋의 부하가 너무 높아서 적절한 단위와 트랜잭션 격리 수준에서 트랜잭션을 이용하 오토커밋을 사용하지 않도록 해야한다.

2. 긴 트랜잭션

DB 트랜잭션의 동시성이나 자원의 유효성을 저하한다.
-> 또한 대량의 갱신 처리를 한 개의 트랜잭션으로 실행하면 대량 갱신 처리를 롤백하기 위한 대량의 UNDO 로그를 트랜잭션 종료까지 유지해야 한다. 따라서 대량 처리는 적당한 크기의 트랜잭션으로 나눠서 실행하는 것을 추천다한다
-> 한 번 SELECT를 하고 아무것도 하지 않고 트랜잭션을 열린 채로 두면 같은 테이블에 갱신을 실행할 때 UNDO 로그가 계속 유지된 상태가 되기 때문에 이러한 상태를 피해야 한다.
-> 사용자와의 대화 처리를 트랜잭션에 넣게 되면 처리 대기 시간이 길어지고 이는 시스템 전체의 효율을 떨어뜨리게 된다.
-> 부하실험을 통해 적절한 트랜잭션의 수를 측정하여 이에 맞게 실행해야 한다.

[rawfishthelgh]

7장

트랜잭션과 동시성 제어

• 테이블 갱신은 보통 단일 쿼리가 아닌 복수의 쿼리로 이루어짐. 갱신이 이루어질 때 생기는 복수의 쿼리를 한 단위로 묶은 것을 트랜잭션이라 한다.

트랜잭션의 특성 : ACID

• 원자성(Atomicity) : 데이터의 변경을 수반하는 일련의 데이터 조작이 전부 성공하거나 전부 실패해야 한다. 각 데이터를 조작하는 동작에서 하나라도 오류가 발생하면 롤백을 수행하여 처음 단계로 되돌려야 한다.
• 일관성(Consistency) : 데이터 조작이 끝나면 그 상태가 유지되어야 한다
• 고립성(Isolation) : 일련의 데이터 조작을 복수 사용자가 동시에 실행해도, 각각의 처리가 모순 없이 실행됨을 보증해야 한다. 테이블에 대해 잠금을 걸어서 후속 처리를 블록할 수 있다. 후속 처리는 해당 잠금이 해제될 때(커밋 또는 롤백)까지 대기한 후 처리한다. 여기서 “모순 없다”의 의미는 “복수의 트랜잭션이 순서되로 실행되는 경우와 같은 결과를 얻을 수 있는 상태”를 의미한다.
  • 트랜잭션이 직렬로 수행된다면 모순 없음을 보장할 수 있다. 이를 dbms에서 격리 수준으로 구현하고 제공하는 것이 “직렬화 가능”이라는 사양이다
  • 그러나 직렬화 가능은 동시에 동작하는 트랜잭션이 1개의 이미지가 되어 성능면에서 실용적이지 않다.
  • 따라서 직렬화 기능으로부터 직렬화 가능 이외에도 격리 수준을 완화해 자신이 아닌 다른 트랜잭션의 영향을 받는 것을 허용하는 4개의 단계를 “ANSI”에서 정의 했다
    1. 커밋되지 않은 읽기(read uncommited)
    2. 커밋된 읽기(read commited)
    3. 반복 읽기(repeatable read)
    4. 직렬화 가능(serializable)
  • 위 중 직렬화 가능이 가장 엄격하며 1로 갈수록 격리 수준이 완화된다.
  • 격리 수준이 완화되며, 직렬화 가능에서는 발생하지 않았던 여러 현상이 생길 수 있다. 아래와 같은 3개의 현상이 일어난다
  • 더티 읽기(dirty read) : 어떤 트랜잭션이 커밋되기 전에 다른 트랜잭션에서 데이터를 읽는 현상. 사용자 a가 값을 9에서 10으로 변경하고 커밋하지 않았는데, 사용자 b가 10으로 읽는다. 확정전의 더럽혀진 데이터를 읽는다는 의미에서 붙여진 이름
  • 애매한 읽기(fuzzy / NonRepeatable Read) : 어떤 트랜잭션이 이전에 읽는 데이터를 다시 읽을 때, 2회 이후의 결과가 1회 때와 다른 현상. 사용자 a가 10을 읽고 사용자 b가 9로 변경해 커밋한다면, 사용자 a가 다시 select를 할 때 결과는 9다. 최초의 값인 10이 2회 이후의 select에서 보증되지 않고 애매하게 되는 것에서 붙여진 이름
  • 팬텀 읽기(Phantom Read) : 어떤 트랜잭션을 읽을 때 선택할 수 있는 데이터가 나타나거나 사라지는 현상이다. 사용자 a가 특정 select 쿼리를 실행하고 사용자 b가 커밋한 후 a가 같은 쿼리를 실행할 때 다른 데이터가 선택 된다. 나타나거나 사라지는 데이터가 유령과 닮아서 붙여진 이름이다.
• 지속성(durability) : 일련의 데이터 조작을 완료(커밋)하고 완료 통재를 사용자가 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것. 트랜잭션 조작을 로그로 남기고 시스템 이상 발생 시 로그를 통해 이상 전 상태까지 복원하며 지속성 실현한다

다른 커넥션에서 테이블을 보는 경우

• ddl이나 dml에 의한 데이터 저장은 트랜잭션이 커밋되기 전까지는 다른 커넥션에서 보이지 않으나, 이에 사오간없이 다른 커넥션에서 보이는 경우 있음

1. ddl에 따른 암묵적 커밋 : mysql이나 oracle에서는 ddl 실행 시 암묵적인 커밋이 발행
2. 오토커밋 설정 : 트랜잭션의 개시가 명시적으로 지정되지 않았을 때.
   1. 하나의 sql 문이 하나의 트랜잭션으로 구성
   2. 사용자가 커밋 또는 롤백을 실행하기까지가 하나의 트랜잭션으로 구정

하는 방법이 있는데, 이 때 a를 오토 커밋이라 한다. mysql 등은 기본적으로 오토 커밋이 기본 설정이다.

트랜잭션 격리 수준에 따른 외관상 차이

MVCC에 따른 MySql의 특성

• mysql은 현재 dbms의 주류가 된 mvcc라는 기술을 사용한다. mvcc를 사용하기 때문에 가지는 특성은 아래와 같다

1. 읽기를 수행할 경우 갱신 중이라도 블록되지 않음
2. 읽기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있음
3. 갱신 시 배타적 잠금을 얻는다. 잠금은 기본적으로 행 단위로 얻으며 트랜잭션이 종료할 떄까지 유지한다.
4. 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 획득하려고 할 때 블록된다. 일정 시간을 기다리며 그사이에 잠금을 획득할 수 없는 경우에는 “잠금 타임아웃”이 된다.
5. 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 “롤백 세그먼트”라는 영역에 유지한다. UNDO 로그는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리거나, 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다.

트랜잭션 격리 수준별 외관

• mysql 트랜잭션 격리 수준의 기본값은 “반복 읽기(Repeatable read)”다.

반복 읽기

• 최초 쿼리를 실행한 시점의 커밋된 데이터를 읽어 들인다. 따라서 같은 쿼리를 복수 실행하면 최초 읽은 내용의 결과 세트가 반환된다. 즉 복수 회의 쿼리 실행 사이에 다른 트랜잭션이 커밋해도 결과가 바뀌지 않는다.

커밋된 읽기

• 쿼리를 실행한 시점에서 커밋된 데이터를 읽어들인다. 같은 쿼리를 복수 회 실행 중 다른 트랜잭션에서 커밋되면, 최신 쿼리 실행 시점의 커밋 데이터를 읽는다.

커밋되지 않는 읽기가 사용되지 않는 이유

• 커밋되지 않았더라도 그 순간의 스냅샷이 필요할 때, 커밋되지 않은 읽기를 이용하면 읽기가 블록되지 않아 그순간의 대략적인 값을 알 수 있어 편리하다
• 그러나 위 내용처럼 MVCC를 이용하면 읽기가 블록되지 않으므로 커밋되지 않는 읽기를 사용할 필요가 없다

잠금 타임아웃과 교착 상태

잠금 타임아웃

• 갱신과 갱신이 부딪히는 경우에는 나중에 온 갱신이 잠금 대기 상태가 된다. 잠금 대기를 하는 쪽에는 잠금을 기다릴지, 말지, 혹은 어느 정도까지 기다릴지를 설정한다
• mysql은 잠금 대기로 타임아웃이 발생하면 기본적으로 오류가 발생한 쿼리를 롤백한다
• 트랜잭션 전체를 롤백할 수도 있다

교착 상태

• 트랜잭션 a가 테이블 a의 잠금을, 트랜잭션 b가 테이블 b의 잠금을 얻었다고 가정하자
• 잠금을 유지한 채 트랜잭션 a는 테이블 b의 자원을, 트랜잭션 b는 테이블 a의 자원을 필요로 하는 처리를 한다면, 아무리 기다려도 상황이 바뀌지 않는다. 이를 교착상태라 한다

교착상태 피하기

• 일반적인 dbms는 교착 상태를 독자적으로 검출해 상태를 보고한다. mysql도 교착상태가 일어나면 즉시 인식해 시스템에 영향이 작은 쪽의 트랜잭션을 트랜잭션 개시 시점까지 롤백한다
• 애플리케이션 쪽에서는 항상 트랜잭션이 교착 상태를 일으켜 롤백되는 경우에 트랜잭션을 재실행할 수 있는 구조로 만들어야 한다
• 다음과 같은 상황을 고려한 대책을 세운다
  • 트랜잭션을 자주 커밋해 작은 단위로 만듦
  • 항상 정해진 순서로 테이블에 액세스하도록 함
  • 필요 없는 경우에는 읽기 잠금 획득을 피함
  • 잠금의 범위나 잠금 정도를 더 작은 것으로 한다.(MySql은 트랜잭션 격리 수준을 되도록 “커밋된 읽기”로 함)
  • 테이블 단위의 잠금을 획득해 갱신을 직렬화하면 동시성은 떨어지지만 교착 상태는 회피할 수 있어서 전체 처리로 보면 좋은 예도 있다.
  • (MySql의 경우) 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스캔한 행 전체에 대해 잠금이 걸리게 한다.
• 교착 상태는 클라이언트에서 롤백된 트랜잭션의 오류, 서버에서 오류 로그나 명령으로 확인할 수 있다.

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

1. 오토 커밋 : 커밋의 부하가 너무 높다. 적절한 단위와 트랜잭션 격리 수준에서 트랜잭션을 이용해 오토커밋을 사용하지 말자
2. 긴 트랜잭션 : 트랜잭션의 동시성이나 자원의 유효성을 저하한다. 트랜잭션이 갱신을 포함할 경우 다른 갱신 트랜잭션을 블록하므로, 트랜잭션이 길면 타임아웃이 일어날 가능성이 높다. 또 잠금과 블록 트랜잭션 사이에 교차가 발생하면 교착 상태가 되고, 긴 트랜잭션 중 하나가 롤백될 수 있다.
3. 대량 처리를 한 개의 트랜잭션이 실행 : 롤백하기 위한 대량의 UNDO 로그를 트랜잭션 종료 시점까지 유지해야 함. UNDO 로그가 불필요해진 시점이 될 때, OS의 파일 시스템에서 불필요한 메모리를 가져간다.
4. 아무것도 하지 않는 트랜잭션 유의 : 역시 불필요 undo 로그 유지
5. 트랜잭션 중에 대화 처리 : 사용자의 대화 처리는 언제 끝날지 모르는 불명확한 처리. 트랜잭션은 되도록 작게 하고 위와 같은 종료 시점이 불명확한 처리를 포함해서는 안된다. 이런 처리는 트랜잭션에 넣지 말자
6. 처리 능력 이상의 트랜잭션 수 : 트랜잭션의 실행이 다른 트랜잭션의 잠금 때문에 막힌다면 잠금 타임아웃이나 교착 상태의 확률이 증가하고 성능 저하로 이어짐. 따라서 부하 실험을 통해 최대의 커넥션 수를 파악하고 조절하자

[seokhwan-an]

7장 트랜잭션과 동시성 제어

💡목표

복수의 쿼리를 통합하는 과정에서 데이터의 정합성을 가지기 위해 트랜잭션과 동시성 제어에 대해 학습한다.

트랜잭션

• 데이터를 INSERT/DELETE/UPDATE에서 주로 UPDATE는 단일 쿼리만으로 구성된 것이 아니라 복수 쿼리를 연속해서 수행하는 경우가 많다.
• 이와 같은 복수의 쿼리를 하나의 단위로 묶은 것을 트랜잭션이라고 한다.
• 트랜잭션은 Atomicity(원자성), Consistency(일관성), Isolation(고립성 또는 격리성), Durability(지속성) 4가지의 특성으로 정의된다.

Atomicity(원자성)

• 데이터의 변경을 수반하는 일련의 데이처 조작이 전부 성공할지 전부 실패할지 보증하는 구조
• 서울에서 부산 여행을 다녀오는 예시
  • 서울 → 부산행 좌석을 예매한다.
  • 부산에서의 숙박을 예약한다.
  • 부산 → 서울행 좌석을 예매한다.
  • 위의 과정에서 발생한 요금을 지불한다.
• 예시 절차에서 오류없이 잘 진행되면 트랜잭션에서는 COMMIT을 실행해 처리를 확정한다.
• COMMIT이된 데이터의 경우 데이터베이스에 영구적으로 저장된다.
• 예시 절차에서 오류가 발생하면 ROLLACK을 실행해 맨 처음 단계의 직전 상태로 돌아간다.
• 비즈니스 로직에서 오류뿐만이 아니라 서버 통신이 끊기거나 서버가 다운 되는 경우에도 ROLLACK이 이루어진다.

Consistency(일관성)

• 데이터베이스 오브젝트에 대해 각종 정합성 제약을 추가가능한데 이는 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보장하는 일관성을 위한 구조이다.
• 트랜잭션이 성공적으로 수행된 후에도 데이터베이스가 일관성 있는 상태를 유지하는 것
• 트랜잭션이 커밋되면 데이터베이스에 적용한 제약조건(PRIMARY KEY, UNIQUE, NOT NULL 등)을 위반하지 않는다는 것을 보장
  • 사용자가 주민등록번호를 등록하려고 하는데, 주민등록번호에는 UNIQUE 제약이 설정되어 있어 중복된 주민등록번호를 저장할 수 없어야 한다.
• 트랜잭션의 작업이 애플리케이션에서 의도하고자 한 작동이 정상적으로 일어난다는 보장을 의미한다.
  • 상품 재고가 떨어졌을 때, 더 이상 판매를 할 수 없도록 제한해야 한다.

Isolation(고립성)

• 일련의 데이터 조작을 복수 사용자가 동시에 실행해도 각각의 처리가 모순없이 실행되는 것을 보증한다다는 것이다.

  • 모순없이 실행된다는 것은 복수의 트랜잭션이 순서대로 실행되는 경우와 같은 결과를 얻을 수 있는 상태를 의미한다. ⇒ 직렬로 실행되는 상태를 의미
  • 복수의 트랜잭션을 순서대로 처리하기 위해서 DBMS에서 격리 수준으로 구현하고 요청을 직렬화하여 처리한다. ⇒ 성능적으로 좋은 방법이 않고 이를 완화해나가면서 현재 격리수준을 4가지로 나누어서 규격으로 정하고 있다.

  커밋되지 않은 읽기(Read Uncommitted)

  커밋된 읽기(Read Committed)
  반복 읽기(Repeatable Read)
  직렬화 가능(Serializable)

  💡더티 읽기, 애매한 읽기, 팬텀 읽기

• 위와 같은 상황에서 사용자A,B가 같은 싱글룸을 동시에 확보하는 상황이 발생하면 2명이 싱글룸을 확보지만 1개만 줄어드는 문제가 발생한다.
• 이와 같은 문제를 막기 위해서 LOCK 걸어서 후속 처리를 BLOCK하는 방법이 있다.

• 위와 같은 방식으로 후속처리의 경우 해당 행의 잠금이 풀릴 때까지 대기하게 되며 올바른 처리를 계속할 수 있게 된다.

Durability(지속성)

• 일련의 데이터 조작을 완료된 시점부터 그 조작이 영구적이 되어 그 결과를 잃지 않는 것
• 이것은 데이터베이스나 OS의 이상 종료, 즉 시스템 장애도 견딜 수 있다는 것을 의미한다.
• 대부분의 데이터베이스에서는 트랜잭션 조작을 하드 디스크에 ‘로그’로 기록하고 시스템에 이상이 발생하면 로그를 사용해 이상 발생 전의 상태까지 복원하는 것으로 지속성을 실현

다른 커넥션에서 테이블을 본다.

• 기본적으로 트렌잭션이 완전히 커밋이 된 이후에 다른 커넥션에서 그 데이터에 접근을 할 수 있다.

• DDL의 경우 암묵적인 커밋이 발생하여 다른 커넥션에서도 참조가 가능하다.

• 일반적으로 DBMS에서는 오토커밋을 처리할 수 있다.

  • 오토커밋이란 사용자가 Commit 명령을 따로 하지 않아도 자동으로 모든 명령이 Commit되어 즉시 반영되는 명령어

  # auto commit이 활성화 되어 있는지 확인하는 방법
  
  SELECT @AUTOCOMMIT; # 1이나오면 auto commit이 활성화 된 것이다.
  또는
  SHOW variables LIKE 'autocommit%'; # ON이 나오면 auto commit이 활성화 된 것이다.
  
  # auto commit 활성화/비활성화 하는 두 가지 방법
  
  SET autocommit = 1;
  SET autocommit = 0;
  또는
  SET autocommit = true;
  SET autocommit = false;

트랜잭션 격리 수준에 따른 외관상 차이

• MySQL은 MVCC라는 기술을 사용하고 있기 때문에 다음과 같은 특성을 가진다.
  • 일기를 수행할 경우 갱신 중이라도 블록되지 않는다( 읽기와 읽기도 서로 블록되지 않는다.)
  • 일기 내용은 격리 수준에 따라 내용이 바뀌는 경우가 있다.
  • 갱신 시 배타적 장금을 얻는다. 잠금은 기본적으로 행 단위로 얻으면 트랜잭션이 종료할 때까지 유지한다. 격리 수준이나 InnoDB의 설정에 따라 실제로 잠금 하는 행의 범위가 다른 경우가 있다.
  • 갱신과 갱신은 나중에 온 트랜잭션이 잠금을 흭득하려고 할 때 블록된다. 일정 시간을 기다리며 그 사이에 잠금을 흭득할 수 없는 경우에는 ‘잠금 타임아웃’이 된다.
  • 갱신하는 경우 갱신 전의 데이터를 UNDO 로그로 ‘롤백 세그먼트’라는 영역에 유지한다. 이 ‘UNDO 로그’는 용도가 2가진데, 첫 번 째는 갱신하는 트랜잭션의 롤백 시 갱신 전으로 되돌리는 것이고, 두 번째는 복수의 트랜잭션으로부터 격리 수준에 따라 대응하는 갱신 데이터를 참조하는 데 이용한다.
• MySQL 트랜잭션 격리 수준의 기본값은 반복 읽기(Repeatable Read) 입니다.
  • 반복 읽기의 경우 같은 쿼리를 복수번 실행하면 최초 쿼리를 실행한 시점에서 커밋된 데이터를 읽어온다. (중간에 다른 트랜잭션이 반영되어도 최초 쿼리 결과값을 읽어온다.)
• 커밋된 읽기 격리 수준에서는 같은 쿼리를 복수 회 실행하면 그 사이에 다른 트랜잭션이 반영되면 그 이후로는 다른 트랜잭션이 반영된 데이터를 불러온다.

잠금 타임아웃과 교착 상태

• 잠금 타임아웃은 갱신과 갱신 상황에서 잠금을 건 쪽에서 언제 잠금을 풀지 알 수 없어서 잠금해제를 기다리고 있는 쪽이 얼만큼 기다릴지를 설정하는 것
• 교착 상태는 잠금이 채원 자원에 대해서 잠금이 필요한 작업을 처리하려고 할 때 아무리 기다려도 상황이 바뀌지 않는 상태이다.
• 잠금 타임아웃의 경우 시간설정을 통해 해결이 될 수 있지만 교착상태는 개선의 여지가 없다.

교착상태의 빈도를 낮추기 위한 사항

• DBMS 전반적인 대책
  • 트랜잭션을 자주 커밋한다. 이에 따라 트랜재션은 더 작은 단위가 되어 교착 상태의 가능성을 낮춘다.
  • 정해진 순서로 테이블에 액세스하게 한다. 트랜잭션A가 테이블a → 테이블b 순으로 접근하고 트랜잭션B가 테이블b → 테이블a 순으로 접근하면 둘 중하나의 접근 순서로 통일시킨다.
  • 필요 없는 경우에는 일기 잠금 흭득의 사용을 피한다.
  • 쿼리에 의한 잠금 범위를 더 좁히거나 잠금 정도를 더 작은 것으로 한다.
  • 한 테이블의 복수 행을 복수의 연결에서 순서 변경 없이 갱신하면 교착 상태가 발생하기 쉽다.
• MySQL의 대책
  • 테이블에 적절한 인덱스를 추가해 쿼리가 이를 이용하게 한다. 인덱스가 사용되지 않는 경우에는 필요한 행의 잠금이 아닌 스킨한 행 전체에 대해 잠금이 걸리게 된다.

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

• 오토커밋 이용하지 않기
• 긴 트랜잭션 피하기
  • 대량 처리를 적당한 트랜잭션으로 나누어서 처리하기
  • 아무것도 하지 않는 트랜잭션을 유의한다.
  • 데이터베이스 서버의 커넥션 수 상한을 설정한다.

[rawfishthelgh]

1. For update에 대해서 간단하게 설명해줄 수 있나요? 처음 봤습니다 오늘ㅎㅎ
2. 일관성이랑 정합성 조건이랑 무슨 관련이 있을까요? 잘 이해가 안 가네요ㅠㅠ
3. 오토커밋은 상황에 관계없이 무조건 끄는 건가요?
4. 영향이 작은 트랜잭션은 어떤 기준으로 보통 선택하나요?
5. 인덱스는 빠른 조회의 이점 때문에 쓰지 않나요? 교착 상태랑 어떤 관련인지 잘 이해가 안갔어요
6. 인덱스를 왜 거나요?
7. 사용자 대화 처리가 무엇이죠? 이거랑 트랜잭션 처리를 해서 안 되는 거랑 어떤 관련이 있는지 모르겠어요