TL;DR

여러 사용자가 규칙없이 데이터를 입력하면 데이터 베이스는 엉망이 될 수 있다. 이를 방지하기 위한 대표적인 기법이 데이터 무결성과 제약 조건, 트랜잭션과 동시성 제어다.

• 무결성 제약 조건 : 데이터가 정확하고 유효하게 유지되도록 지키는 방법
  • 개체 무결성 제약조건 : 기본키를 구성하는 모든 속성은 널(NULL) 값이거나 중복 값을 가질 수 없다.
  • 참조 무결성 제약조건 : 외래키는 참조할 수 없는 값을 가질 수 없다.
• 트랜잭션 : 데이터 베이스의 상태를 변화시키기 위해 수행하는 논리적 수행 단위
• Locking : 동시성을 제어하기 위한 기능
• 교착상태 : 두 개 이상의 트랜잭션이 특정 자원의 lock을 획득한 채 다른 트랜잭션이 소유하고 있는 lock을 요구하여 실행하지 못하고 서로 무한정 기다리는 상태

무결성 제약 조건

• 데이터의 무결성 : 데이터에 결함이 없는 상태, 즉 데이터가 정확하고 유효하게 유지되는 상태

• 제약 조건 : 정해놓은 규칙에 맞는 데이터만 입력받고 규칙에 어긋나는 데이터는 거부해 데이터 무결성을 지키는 방법

• RDBMS에서 데이터의 무결성을 지키기 위한 제약 조건은 아래의 2가지가 있으며 2가지 모두 지켜져야 한다.

  • 개체 무결성 제약조건 : 기본키를 구성하는 모든 속성은 널(NULL) 값이거나 중복 값을 가질 수 없다.
  • 참조 무결성 제약조건 : 외래키는 참조할 수 없는 값을 가질 수 없다.

1) 개체 무결성 제약조건 (Entity Integrity Constraint)

• 릴레이션의 기본키에는 NULL 값이나 중복된 값이 올 수 없다.

  → 릴레이션에서 레코드를 유일하게 구별해주고 각 레코드에 쉽게 접근하게 해주는 기본키의 역할을 할 수 있도록 기본키를 정의

• 기본키에 NULL 값이나 중복된 값이 들어가게 하는 SQL문은 거부함으로써 개체 무결성 제약조건을 준수한다.

2) 참조 무결성 제약조건 (Reference Integrity Constraint)

• 외래키의 값은 참조 릴레이션의 기본키 값과 동일해야 한다.
• 참조 무결성 제약조건은 두 릴레이션의 연관된 투플들 사이의 일관성을 유지하는데 사용된다. 따라서 외래키는 참조할 수 없는 값을 가질 수 없다.

3) 도메인 제약조건 (Domain Constraint)

• 각 어트리뷰트 값이 반드시 원자값이어야 함
• 어트리뷰트 값의 디폴트 값과 가능한 범위를 지정할 수 있음

트랜잭션

• 트랜잭션은 여러 개의 작업을 하나로 묶어 모든 작업들을 완료해야 정상적으로 종료되는 실행 유닛
• 논리적인 작업의 단위
• 데이터 베이스의 회복과 동시성 제어를 가능하게 해 데이터 베이스가 일관된 상태를 유지하게 한다.
  • 동시성 제어 : 여러 사용자가 데이터 베이스를 동시에 공유할 수 있도록 여러 트랜잭션이 동시에 수행될 수 있게 함
  • 회복 : 데이터 베이스에 장애 발생 시 장애 발생 전 상태로 복구시키는 것.
• 트랜잭션은 모두 실행되거나, 모두 실행되지 않거나 (All or Nothing).

트랜잭션 특징 (ACID)

ACID는 데이터 베이스에서 트랜잭션의 안전성을 보장하기 위한 성질입니다.

예를 들면, 계좌 이체에서 나의 계좌에서 10만원을 빼고, A에게 10만원을 추가하는 종합적인 과정이 트랜잭션입니다.

1. 원자성 (Atomicity)
   • 트랜잭션의 연산들이 모두 정상적으로 실행되거나 하나도 실행되지 않아야 한다. all-or-nothing
   • 원자성 보장을 위해 장애 발생시 회복 기능이 필요하다.
2. 일관성 (Consistency)
   • 트랜잭션이 성공적으로 수행된 후에도 데이터베이스가 일관성 있는 상태를 유지해야 한다.
   • 트랜잭션이 일어난 이후에도 데이터베이스의 제약이나 규칙을 만족해야 한다.
3. 격리성 (Isolation)
   • 수행 중인 트랜잭션이 완료될 때까지 다른 트랜잭션들이 중간 연산 결과에 접근할 수 없다.
   • Locking 사용
4. 지속성 (Durability)
   • 트랜잭션이 성공적으로 완료된 후 데이터베이스에 반영된 수행 결과는 영구적이어야 한다.

트랜잭션의 주요 연산

1. commit

• 트랜잭션의 수행이 성공적으로 완료되었음을 선언

• 트랜잭션이 변경하려는 내용이 데이터 베이스에 완전하게 반영되는 연산

  → 일반적으로는 개발자가 추가적인 작업을 하지 않아도 트랜잭션 내부의 SQL문들의 수행이 모두 끝나면 auto-commit이 된다.

1. rollback

• 트랜잭션의 수행이 실패했음을 선언

• 트랜잭션이 갱신한 사항을 트랜잭션이 수행되기 전의 상태로 되돌리는 연산

  → 원자성을 보장하기 위함

동시성 제어 (Concurency Control)

• 동시성 제어를 통해 여러 사용자가 동시에 데이터 베이스를 공유할 수 있도록, 여러 트랜잭션이 동시에 수행될 수 있도록 한다. (병렬처리)
• 병렬처리 : CPU가 여러 프로세스를 처리하는 것처럼 트랜잭션에 정해진 시간을 할당해 작업을 하다가 부여된 시간이 끝나면 다른 트랜잭션을 실행하는 방식으로 트랜잭션들을 조금씩 처리하는 것

로킹 (Locking)

• Lock : 동시성을 제어하기 위한 기능
• 트랜잭션을 일렬로 대기시키면 데이터 베이스의 성능이 현저하게 떨어지므로 응답성을 높이기 위해 효율적인 Locking 방법이 필요하다.

• 동시에 수행되는 트랜잭션이 동일 데이터에 접근하지 못하도록 lock/unlock 연산으로 제어

• Lock 연산
  • 공용 lock
    • 트랜젝션에서 읽기를 목적으로 데이터 항목 접근시 요청
    • 데이터에 대한 사용권을 여러 트랜잭션이 함께 가질 수 있음
    • 트랜잭션이 데이터에 대해 공용 lock을 수행하면, 해당 데이터에 read 연산은 할 수 있지만 write 연산은 수행할 수 없음
  • 전용 lock
    • 트랜잭션에서 갱신(삽입, 수정, 삭제)을 목적으로 데이터 항목을 접근할 때 요청
    • 트랜잭션이 데이터에 대해 전용 lock을 수행하면, 해당 데이터에 대해 read, write 연산 모두 할 수 있고 다른 트랜잭션은 어떤 lock 연산도 할 수 없음

2단계 로킹 프로토콜 (2PL)

• Lock 을 요청하는 것과 해제하는 것이 2단계로 이루어진다는 규약

• 직렬 가능한 스케줄이 항상 보장되지 않는다는 문제 해결

• Lock 과 Unlock이 번갈아 수행되지 않고 Lock이 쭉 수행된 후에 Unlock이 쭉 수행됨

  • 확장 단계 : 트랜잭션이 lock만 수행할 수 있고, unlock은 수행할 수 없음
  • 축소 단계 : 트랜잭션이 unlock만 수행할 수 있음

• 교착상태(Deadlock) 발생 가능

  트랜잭션이 상대가 독점한 데이터에 unlock 연산이 실행되기 서로 기다리면서 트랜잭션 수행을 중단하고 있는 상태

Lock의 설정 범위

• 데이터 베이스 : 1개의 세션만 DB 데이터에 접근 가능
• 테이블 : 테이블의 모든 행을 업데이트하는 등 전체 테이블에 영향주는 변경 수행할 때 유용
  • DDL(create, alter, drop 등) 구문과 함께 사용되며 DDL Lock 이라고도 함
• 행 : 1개의 행 기준으로 Lock 설정
  • DML에 대한 Lock으로 가장 일반적으로 사용하는 Lock

트랜잭션의 Isolation Level

• Isolation Level 에 대한 조정은, 동시성과 데이터 무결성에 연관되어 있다.

  • 동시성을 증가시키면 데이터 무결성에 문제가 발생하고,

  • 데이터 무결성을 유지하면 동시성이 떨어지게 됨

  • 레벨을 높게 조정할 수록 발생하는 비용이 증가함

👆 여기서 잠깐! <용어 정리하고="" 들어가기="">

DIRTY READ : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았음에도 다른 트랜잭션에서 볼 수 있게 되는 현상

NON-REAPEATABLE READ : 한 트랜잭션에서 같은 쿼리를 두 번 수행할 때 그 사이에 다른 트랜잭션 값을 수정 또는 삭제하면서 두 쿼리의 결과가 상이하게 나타나는 일관성이 깨진 현상

PHANTOM READ : 한 트랜잭션 내에서 동일한 쿼리를 두 번 수행했는데 첫번째 쿼리에서 존재하지 않았던 레코드가 두번째 쿼리에서 나타나는 현상

UNDO RECORD : 데이터 갱신으로 테이블을 변경할 때 변경하기 전의 데이터를 보관하는 곳

Level 0) Read Uncommitted

Select 문장을 수행하는 경우 해당 데이터에 Shared Lock이 걸리지 않는 Level

• 랜잭션에 처리중이거나, 아직 Commit되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용함
• 데이터베이스의 일관성을 유지하는 것이 불가능함

Level1) Read Committed

Select 문장이 수행되는 동안 해당 데이터에 Shared Lock 걸린다.

• 특정 트랜잭션의 변경 내용이 Commit 되어야만 다른 트랜잭션에서 접근할 수 있다.

• SQL이 Default로 사용하는 Isolation Level

• 변경중인 table을 조회하면 테이블이 아니라 UNDO 영역의 백업된 레코드에서 데이터를 가져온다.

• 트랜잭션의 중 같은 쿼리인데 결과 값이 달라질 수 있다. (중간에 다른 사용자가 변경한 경우)

  → Non-Repeatable Read, Phantom Read 발생

Level2) Reapeatable Read

트랜잭션이 완료될 때까지 Select 문장이 사용하는 모든 데이터에 Shared Lock이 걸린다.

• 트랜잭션이 범위 내에서 조회한 데이터 내용이 항상 동일함을 보장
• transaction 도중 다른 transaction이 commit 되더라도 새로이 commit 된 데이터는 보이지 않는다.
• 다른 사용자는 트랜잭션 영역에 해당되는 데이터에 대한 수정 불가능
• MySQL에서 Default 로 사용하는 Isolation Level

→ Phantom Read 발생

Level3) Serializable Read

• 한 트랜잭션에서 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.

• 모든 동작이 직렬화

  → 동시 처리 성능은 많이 떨어지고 완벽한 읽기 일관성을 제공한다.

교착상태

• 두 개 이상의 트랜잭션이 특정 자원의 lock을 획득한 채 다른 트랜잭션이 소유하고 있는 lock을 요구하여 실행하지 못하고 서로 무한정 기다리는 상태

👆 여기서 잠깐!

읽기 잠금 모드 : LOCK IN SHARE OPTION

→ 데이터를 읽을 때 사용하는 Read Lock의 개념으로 공유 잠금끼리는 충돌이 없다.

쓰기 잠금 모드 : FOR UPDATE

→ 데이터를 쓸 때 사용하는 Write Lock의 개념으로 공유 잠금과 배타 잠금 모두에게 충돌이 발생한다.

교착 상태의 빈도를 낮추는 방법

• 트랜잭션을 자주 commit 하기
• 트랜잭션이 테이블에 접근하는 순서 동일하게 하기
• 읽기 잠금 획득 (SELECT ~ FOR UPDATE)의 사용 피하기
• 트랜잭션이 테이블 단위의 잠금을 획득해 갱신을 직렬화 하기 (동시성은 떨어지지만 교착상태는 회피할 수 있다.)

교착 상태 회피기법

• 자원을 할당할 때 Time Stamp를 사용

• 방법1) Wait - Die 방식

  트랜잭션의 TS 우선순위가 높은 경우(선행 트랜잭션, 숫자가 작은 경우)에만 기다리는 것이 허용되고 낮은 경우(후행 트랜잭션)에는 롤백한다.

• 방법2) Wound-Wait 방식

  선행 트랜잭션의 경우 자원을 빼앗고 후행 트랜잭션의 경우 대기한다(=기다린다).

질문

은행 계좌 데이터 베이스이 관계형 데이터 베이스이고, 이름/잔액 두 가지 column을 가진다고 하자. A가 B에게 만원을 송금할 때, 실제로 데이터 베이스에서는 어떤 과정으로 이루어질까요?

트랜잭션을 보장하기위해 DBMS에는 무엇이 필요할까요?

예를 들면 트랜잭션에서 rollback 하는 경우 이전 데이터로 복구를 해야하는데, 이를 DBMS에서는 어떻게 지원해줄 수 있을까요?

• 페이지 버퍼 (UNDO 복구를 위해)
• Locking 과 같은 함수 기능

트랜잭션에서 Isolation 을 보장하기위해 모든 트랜잭션을 직렬로 수행한다면 처리 시간이 너무 오래걸릴텐데 이것을 어떻게 해결할 수 있나요?

• 트랜잭션의 Isolation Level을 조정할 수 있습니다.

트랜잭션과 Locking은 각각 무엇을 위한 기능인가요?

잠금은 동시성을 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능

트랜잭션을 사용할 때 주의할 점은?

트랜잭션은 최소한의 SQL문에만 적용하는 것이 좋다.

→ 데이터 베이스의 커넥션의 개수는 제한적인데, 하나의 프로그램이 커넥션을 소유하는 시간이 길어지면 여유 커넥션의 개수가 줄어들이 때문이다. 트랜잭션을 과하게 적용할 경우 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.

표준 SQL에서 제약 조건을 표시하기 위해 제공하는 것에는 무엇이 있나요?

1) 개체 무결성 제약조건

• NOT NULL : NULL값 입력 금지
• UNIQUE : 중복값 입력 금지 (NULL값은 중복입력 가능)
• PRIMARY KEY : NOT NULL + UNIQUE
• FOREIGN KEY : 다른 테이블의 컬럼을 조회해서 무결성 검사
• CHECK : 조건으로 설정된 값만 입력 허용

2) 참조 무결성 제약조건 (Reference Integrity Constraint)

• RESTRICT : 위배를 야기한 연산은 거절 (다른 테이블에서 참조하고 있을 때 해당 데이터 삭제 안함)
• CASCADE : 참조되는 릴레이션에서 투플을 삭제하고, 참조하는 릴레이션에서 이 투플을 참조하는 투플도 함께 삭제
• NULLIFY : 참조되는 릴레이션에서 투플을 삭제하고, 참조하는 릴레이션에서 이 투플ㅇ르 참조하는 투플의 외래키에 널값 삽입

References

• 트랜잭션 Isolation Level
• gyoogle/tech-interview-for-developer/Transaction Isolation Level

• gyoogle/tech-interview-for-developer/Transaction

• 무결성 제약 조건