[SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (2) 트랜잭션

Yu River·2022년 8월 14일
0

SQLP필기연습

목록 보기
25/35

✍️ 16번 : 트랜잭션의 4가지 특징

트랜잭션의 4가지 특징 중 아래 내용과 가장 관련이 깊은 것

모든 DBMS는 기본적으로 Dirty Read를 허용하지 않는다.

  1. 격리성(Isolation) 👉 ⭕️
  2. 원자성(Atomicity) 👉 ❌
  3. 영속성(Durability) 👉 ❌
  4. 일관성(Consistency) 👉 ❌

🍒 문제 해설

  1. 다른 트랜잭션이 변경 후 아직 커밋하지 않은 데이터를 읽는 것을 'Dirty Read'라고 한다.

✅ 트랜잭션의 4가지 특징(ACID)

  • 원자성(Atomicity) : 트랜잭션은 분해가 불가능한 업무의 최소단위이므로, 전부 처리되거나 아예 하나도 처리되지 않아야 한다.
  • 일관성(Consistency) : 일관된 상태의 데이터베이스에서 하나의 트랜잭션을 성공적으로 완료하고 나면 그 데이터베이스는 여전히 일관된 상태여야 한다. 즉, 트랜잭션 실행의 결
    과로 데이터베이스 상태가 모순되지 않아야 한다.
  • 격리성(Isolation) : 실행 중인 트랜잭션의 중간결과를 다른 트랜잭션이 접근할 수 없다.
  • 영속성(Durability) : 트랜잭션이 일단 그 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장된다.

✍️ 17번 : 동시 트랜잭션에 의해 나타날 수 있는 이상 현상

은행 ATM기에서 통장잔고 50,000원을 확인한 후에 30,000원을 출금 신청했는데, 잔고가 부족하다는 메시지를 받았다. 이는 어떤 현상과 관련이 있는지?

  1. Non-Repeatable Read 👉 ⭕️
  2. Lost Update 👉 ❌
  3. Dirty Read 👉 ❌
  4. Phantom Read 👉 ❌

🍋 기출 포인트

  1. 통장잔고 50,000원을 확인한 후에 30,000원을 출금 신청했는데도 잔고가 부족하다는 메시지를 받았다면, 중간에 다른 트랜잭션이 통장잔고를 30,000원 미만으로 변경한 것이다.

🍒 문제 해설

✅ 동시 트랜잭션에 의해 나타날 수 있는 이상 현상

  • Dirty Read : 다른 트랜잭션이 변경 중인 데이터를 읽었는데 그 트랜잭션이 최종 롤백됨으로써 비일관성 상태에 놓이는 현상
  • Non-Repeatable Read : 한 트랜잭션 내에서 같은 데이터를 두 번 이상 읽을 때, 다른 트랜잭션이 값을 수정 또는 삭제함으로 인해 읽은 값이 서로 달라지는 현상
  • Phantom Read : 한 트랜잭션 내에서 일정 범위의 데이터를 두 번 이상 읽을 때, 다른 트랜잭션이 새로운 데이터를 추가함으로 인해 첫 번째 읽을 때 없던 데이터가 나타나는 현

✍️ 18번 : 동시 트랜잭션에 의해 나타날 수 있는 이상 현상

두 개의 트랜잭션이 동시에 진행한 후에 지역별고객과 연령대별고객에서 총 고객수를 조회하면 서로 다른 결과 값을 반환하게 된다. 이는 어떤 현상과 관련이 있는지 ?


1. Non-Repeatable Read 👉 ❌
1. Lost Update 👉 ❌
1. Dirty Read 👉 ❌
1. Phantom Read 👉 ⭕️

🍋 기출 포인트

  1. 트랜잭션 1이 14 시점에 INSERT를 실행하기 직전에 트랜잭션 2에서 신규 고객을 INSERT 했기 때문이다.

✍️ 19번 : 격리성 수준별 비일관성 현상

Serializable 격리성 수준에서 나타날 수 있는 현상을 모두 나열한 것

  1. Phantom Read 👉 ❌
  2. Non-Repeatable Read, Phantom Read 👉 ❌
  3. Dirty Read, Non-Repeatable Read, Phantom Read 👉 ❌
  4. 없음 👉 ⭕️

🍒 문제 해설

✅ 격리성 수준별 비일관성 현상

  • Read Uncommitted : Dirty Read, Non-Repeatable Read, Phantom Read
  • Read Committed : Non-Repeatable Read, Phantom Read
  • Repeatable Read : Phantom Read
  • Serializable : 없음

✍️ 20번 : 격리성 수준별 비일관성 현상

Read Commited 격리성 수준에서 나타날 수 있는 현상

  1. Non-Repeatable Read, Phantom Read 👉 ⭕️
  2. Phantom Read 👉 ❌
  3. Dirty Read, Non-Repeatable Read, Phantom Read 👉 ❌
  4. 없음 👉 ❌

✍️ 21번 : 공유 Lock의 지속시간

SQL Server에서 기본 Read Committed 격리성 수준에서 공유 Lock의 지속시간

  1. 레코드를 읽기 직전에 Lock을 획득하고, 다음 레코드로 이동하는 순간에 Lock을 해제한다. 👉 ⭕️
  2. 트랜잭션을 시작하는 시점에 Lock을 획득하고, 레코드를 읽자마자 Lock을 해제한다. 👉 ❌
  3. 레코드를 읽기 직전에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ❌
  4. 트랜잭션을 시작하는 시점에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ❌

🍋 기출 포인트

  1. Read Committed 격리성 수준에서는 레코드를 읽기 직전에 공유 Lock을 획득하고, 다음 레코드로 이동하는 순간에 Lock을 해제한다.

🍒 문제 해설

  1. SQL Server는 SELECT 문으로 데이터를 읽을 때 공유 Lock을 설정한다.

✍️ 22번 : 공유 Lock의 지속시간

트랜잭션 격리성 수준을 Repeatable Read로 상향 조정했을 때 공유 Lock의 지속시간을 올바르게 설명한 것

  1. 레코드를 읽기 직전에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ⭕️
  2. 트랜잭션을 시작하는 시점에 Lock을 획득하고, 레코드를 읽자마자 Lock을 해제한다. 👉 ❌
  3. 레코드를 읽기 직전에 Lock을 획득하고, 다음 레코드로 이동하는 순간에 Lock을 해제한다. 👉 ❌
  4. 트랜잭션을 시작하는 시점에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ❌

🍋 기출 포인트

  1. Repeatable Read 격리성 수준에서는 레코드를 읽기 직전에 공유 Lock을 획득하고, 최종 커밋 또는 롤백 하는 순간에 Lock을 해제한다.

🍒 문제 해설

  1. SQL Server는 SELECT 문으로 데이터를 읽을 때 공유 Lock을 설정한다.

✍️ 23번 : 오라클에서의 Serializable 격리성

오라클에서 트랜잭션 격리성 수준을 Serializable로 상향 조정하고 SELECT 문으로 데이터를 읽을 때 Lock의 지속시간을 올바르게 설명한 것

  1. 어떤 Lock도 사용하지 않는다. 👉 ⭕️
  2. 레코드를 읽기 직전에 Lock을 획득하고, 다음 레코드로 이동하는 순간에 Lock을 해제한다. 👉 ❌
  3. 레코드를 읽기 직전에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ❌
  4. 트랜잭션을 시작하는 시점에 Lock을 획득하고, 최종 커밋 또는 롤백하는 순간에 Lock을 해제한다. 👉 ❌

🍋 기출 포인트

  1. ⭐️오라클에서 SELECT 문으로 데이터를 읽을 때는 Serializable 수준에서도 Lock을 전혀 사용하지 않는다.⭐️
  2. ⭐️따라서 트랜잭션 격리성 수준을 상향 조정했다고 해서 Lock 경합이 증가하거나 조회 성능이 느려지지는 않는다.⭐️

✍️ 24번 : 트랜잭션 격리성 수준

트랜잭션 격리성 수준에 대한 설명으로 가장 적절한 것

  1. 기본 격리성 수준은 Read Committed이므로 Non-Repeatable Read, Phantom Read 현상이 발생하지 않도록 구현하는 것은 개발자의 몫이다. 👉 ⭕️
  2. 공유 Lock을 사용하는 SQL Server에서는 쿼리 성능 향상을 위해 SELECT 문에 'with (nolock)' 힌트를 사용하는 것이 좋다. 👉 ❌
  3. 일관성을 높이기 위해 DBMS의 기본 격리성 수준을 Serializable로 설정해야 한다. 👉 ❌
  4. 상용 DBMS를 사용한다면 ACID(원자성, 일관성, 격리성, 영속성)는 기본으로 보장해 준다. 👉 ❌

🍋 기출 포인트

  1. DBMS의 기본 격리성 수준인 Read Committed에서는 Dirty Read만 방지해 준다. 따라서 Non-Repeatable Read, Phantom Read 현상이 발생하지 않도록 구현하는 것은 개발자의 몫이다.
  2. ACID를 DBMS가 보장해 주지는 않는다. 트랜잭션이 갖추어야 할 특성을 이론적으로 정의한 것에 불과하므로 DBMS가 제공하는 Lock 기능을 이용해 ACID를 구현하는 것은 개발자
    의 몫이다.
  3. 데이터를 읽을 때 공유 Lock을 사용하는 SQL Server에서는 DML과의 Lock 경합으로 성능이 저하되는 일이 종종 발생한다. 이를 피하기 위해 SELECT 문에 'with (nolock)' 힌트를 습관적으로 사용하는데, 이는 Dirty Read를 허용하게 하는 힌트로 일관성이 보장되지 않아도 업무에 지장을 주지 않는 상황에서 제한적으로 사용해야 한다.
  4. 데이터베이스의 기본 격리성 수준을 Serializable로 설정하면 트랜잭션에서 처리하
    는 데이터의 일관성은 좋아지지만, 여러가지 부작용이 나타날 수 있다.

🍒 문제 해설

✅ Serializable의 여러가지 부작용

  • Sql Server는 심한 Lock 경합으로 인해 DBMS가 제대로 작동하지 않을 가능성이 높다.
  • 오라클은 Serializable로 설정해도 Lock 경합이 증가하거나 조회 성능이 느려지진 않지만, 수행 시 Update 충돌(ORA-08177 : can't serialize access for this transaction)이 자주 발생해 작업을 실패하는 빈도가 높아진다.

✍️ 25번 : 트랜잭션 격리성 수준을 변경 명령어

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

🍋 기출 포인트

  1. 트랜잭션 격리성 수준을 변경할 때 SET TRANSACTION 명령을 사용한다.

✍️ 26번 : 두 개의 트랜잭션이 동시에 수행되었을 때

오라클에서 아래 TX1과 TX2 두 개의 트랜잭션이 동시에 수행되었을 때 C1 컬럼의 최종 값

[ T1 테이블 ]
CREATE TABLE T1 (C1 NUMBER);
INSERT INTO T1 VALUES (1);
COMMIT;

🍒 정답

4

🍋 기출 포인트

  1. 오라클에서는 ㉡UPDATE를 시작하는 시점에 C1 = 2인 레코드는 없으므로 어떤 변경도 일어나지 않는다.
    • 참고로 SQL Server였다면, TX2 트랜잭션은 ㉠ UPDATE가 끝나기를 기다렸다가 C1 값이 2로 바뀐 사실을 확인하고 ㉡ UPDATE를 정상적으로 처리한다.
  2. UPDATE를 시작하는 시점에는 TX1에서 C1 = 1인 레코드의 C1 값을 2로 변경하고 COMMIT까지 마친 상태이므로 TX2는 해당 레코드를 읽어서 C1을 4로 갱신한다.
    • 아직 COMMIT 하지 않았으므로 C1 = 2인 레코드에 대한 Lock은 TX2가 획득한 상태다.
  3. C1 = 2인 레코드를 UPDATE 하려는 TX1은 블록킹 됐다가 TX2가 COMMIT을 수행하고 나면 Lock을 획득하고 갱신을 시작한다.
  4. UPDATE를 시작한 시점에는 조건절 컬럼 C1의 값이 2여서 기다렸다가 갱신하려고 보니 4로 변경된 사실을 발견한 TX1은 UPDATE 문을 다시 실행한다.
  5. 다시 실행한 시점에는 C1 = 2인 레코드가 없으므로 어떤 처리도 일어나지 않는다. 따라서 C1 컬럼의 최종 값은 4가 된다.

🍒 문제 해설

✅ 갱신 대상 식별

  • 두 트랜잭션이 동일한 행을 갱신하고자 할 때 후행 트랜잭션은 선행 트랜잭션이 설정한
    Lock이 해제되기를 기다리며 블로킹 된다.
  • 갱신을 마친 선행 트랜잭션이 COMMIT을 완료하고나면 비로소 후행 트랜잭션은 Lock을 획득 하고 갱신을 시작한다.이때 UPDATE를 처리하는 방식이 DBMS마다 다르다.
  • 오라클처럼 MVCC(Multi-Version Concurrency Control) 모델을 사용하는 DBMS는 UPDATE 문이 시작된 시점을 기준으로 갱신 대상을 식별한다. 만약 대상으로 식별된 레코드 중 UPDATE문 시작 이후에 조건절 값이 변경된 레코드가 발견되면, 일관성 확보를 위해 UPDATE 문을 재시작한다.조건절 값이 변경된 레코드가 발견되지 않으면 그대로 UPDATE를 진행한다.
  • SQL Server처럼 MVCC 모델을 사용하지 않는 DBMS는 (UPDATE 문 시작 시점이 아니라) 레코드에 도달한 시점을 기준으로 갱신 대상을 식별한다.

✍️ 27번 : 두 개의 트랜잭션이 동시에 수행되었을 때

EMP 테이블 7788번 사원의 SAL 값이 현재 3000인 상황에서 아래 TX1과 TX2 두 개의 트랜잭션이 동시에 수행되었다. 양쪽 트랜잭션이 모두 완료된 시점에 7788번 사원의 SAL 값으로 올바른 것

🍒 정답

3300

🍋 기출 포인트

  1. 만약 최종 결과가 3100이라면 TX2의 Update 결과가 상실된(losted) 것이고, 3280이라면 TX1의 Update 결과가 상실된(losted) 것이다. 이런 현상을 'Lost Update'라고 한다.
  2. ⭐️DBMS에서 절대 'Lost Update' 현상이 생겨서는 안 된다.⭐️
  3. 위 트랜잭션을 어떤 DBMS에서 수행하든 결과는 3300이다.

✍️ 28번 : 두 개의 트랜잭션이 동시에 수행되었을 때

EMP 테이블 7788번 사원의 SAL 값이 현재 3000인 상황에서 아래 TX1과 TX2 두 개의 트랜잭션이 동시에 수행되었다. 양쪽 트랜잭션이 모두 완료된 시점에 7788번 사원의 SAL 값으로 올바른 것

🍒 정답

3100

🍋 기출 포인트

  1. SQL Server에서 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 기다린다. TX1이 끝났을 때 7788 사원의 SAL 값은 3100이다. SQL Server는 Update 하는 순간에 읽은 값으로 갱신 대상 여부를 결정하므로 TX2 트랜잭션의 Update는 실패한다. 따라서 TX1 트랜잭션에 의해 3100으로 변경된 값이 7788 사원의 최종 SAL 값이 된다.
  2. 오라클은 Update 문이 시작된 시점을 기준으로 갱신 대상 여부를 결정한다. TX2의 Update가 시작할 시점에 7788 사원의 SAL 값은 3000이므로 TX1 Update의 갱신 대상이다. TX1이 Update를 끝내고 커밋할 때까지 기다렸다가 Update를 진행하려고 보니 조건절 중 하나인 SAL 값이 변경된 사실을 발견하는 순간 TX2는 Update 문을 재실행한다. 재실행한 시점에는 7788 사원의 SAL 값이 3000이 아니므로 TX2 트랜잭션의 Update는 실패하고 만다. 결국 TX1 트랜잭션에 의해 3100으로 변경된 값이 7788 사원의 최종 SAL 값이 된다.

✍️ 29번 : 두 개의 트랜잭션이 동시에 수행되었을 때

SQL Server에서 EMP 테이블 7788번 사원의 SAL 값이 현재 3000인 상황에서 아래 TX1과 TX2 두개의 트랜잭션이 동시에 수행되었다. 양쪽 트랜잭션이 모두 완료된 시점에 7788번 사원의 SAL 값

🍒 정답

3000

🍋 기출 포인트

  1. SQL Server에서 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 기다린다. TX1이 끝났을 때 7788 사원의 SAL 값은 4000이므로 TX2 트랜잭션이 정상적으로 진행해 값을 3000으로 바꾼다.
  2. 오라클은 Update 문이 시작되는 시점을 기준으로 갱신 대상 레코드를 식별하므로 TX2 트랜잭션의 Update는 기다리지도 않고 바로 실행을 종료한다. 따라서 TX1 트랜잭션에 의해 4000으로 변경된 값이 7788 사원의 최종 SAL 값이 된다.
profile
도광양회(韜光養晦) ‘빛을 감추고 어둠속에서 힘을 기른다’

0개의 댓글