🗓 22-08-30

SQLP 이론 복습

(1) 고도화

SQLP 실기 풀이

(1) [SQLP실기문제/풀이]6장 고급SQL튜닝(6) 고급 SQL 활용 61번
(2) [SQLP실기문제/풀이]6장 고급SQL튜닝(6) 고급 SQL 활용 62번
(3) [SQLP실기풀이]4장 조인튜닝(3)-스칼라서브쿼리 43번
(4) [SQLP실기풀이]4장 조인튜닝(3)-스칼라서브쿼리 44번
(5) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 47번
(6) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 48번
(7) [SQLP실기풀이]4장 조인튜닝(4)-고급조인기법 49번

SQLP 필기 풀이

(1) [SQLD필기풀이]2장 SQL활용 65~104번
p.78 ~ p.108
(2) [SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (1) LOCK
(3) [SQLP필기풀이]7장 LOCK과 트랜잭션 동시성 제어 (2) 트랜잭션

👀 8/30 복기

[1] LOCK

2번

2번

• 온라인 트랜잭션을 처리하는 프로그램에 SELECT FOR UPDATE 문을 사용해선 안 된다. 👉 ❌
  • 한 트랜잭션 내에서 나중에 변경할 목적으로 데이터를 읽을 때는 SELECT FOR UPDATE 문을 사용해야 한다.

[2] Lock 호환성 (SQL SERVER)

문제

5번

• update 락을 select 할 수 없다.
  • SQL Server에서 공유 Lock끼리는 호환되므로 서로 블로킹 하지 않지만, 공유 Lock은 배타적 Lock과 호환되지 않으므로 서로 불로킹 한다.

9번

• 테이블에 Unique 인덱스나 제약이 없으면, INSERT 끼리는 서로 블로킹 하지 않는다.
• 테이블에 Unique 인덱스나 제약이 설정돼 있으면, 같은 값을 동시에 INSERT 하지 못한다.
• 후행 트랜잭션은 기다렸다가 선행 트랜잭션이 커밋하면 Unique 제약 위반 에러가 발생하고,롤백하면 INSERT를 진행한다.
• INSERT 중인 데이터를 다른 트랜잭션이 읽거나 변경하거나 삭제하는 작업은 인덱스나 제약 유무에 상관없이 불가능하다.

⭐️ 10번 ⭐️

• 인덱스 여부를 잘 보아야한다!!!(풀스캔하는지 원하는 범위만 스캔하는지)
• INSERT 중인 데이터를 포함하지 않지만, 인덱스가 없으므로 ⭐️Full Scan⭐️으로 처리된다.따라서 INSERT 중인(= Lock이 걸린) 데이터를 읽고 지나가야 하므로 그 과정
  에 블로킹 된다.
  

[3] Lock 호환성 (ORACLE)

5번

• MVCC 모델을 사용하는 오라클은 SELECT 문 수행 시 어떤 Lock도 사용하지 않는다.따라서 DML이 수행 중인 데이터를 어떤 간섭도 받지 않고 읽을 수 있다.

12번

• SQL Server에서는 SELECT와 INSERT가 서로 방해할 수 있지만, MWCC 모델을 사용하는 오라클에서는 서로 방해하는 일이 없다.

[4] TM Lock과 TX Lock

14번

• 오라클에서 ⭐️TX Lock은 트랜잭션별로 단 하나씩 설정⭐️하고, TM Lock은 DML을 수행하는 테이블 별로 하나씩 설정한다.

[5] 공유 Lock의 지속시간 (SQL SERVER)

문제

21번

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

[6] Lock의 지속시간 (ORACLE)

문제

21번

• 오라클에서 SELECT 문으로 데이터를 읽을 때는 Serializable 수준에서도 Lock을 전혀 사용하지 않는다.

[7] ⭐️ 트랜잭션 컬럼 최종값 문제

문제

중요 !!

• 오라클처럼 MVCC(Multi-Version Concurrency Control) 모델을 사용하는 DBMS는 UPDATE 문이 시작된 시점을 기준으로 갱신 대상을 식별한다. 만약 대상으로 식별된 레코드 중 UPDATE문 시작 이후에 조건절 값이 변경된 레코드가 발견되면, 일관성 확보를 위해 UPDATE 문을 재시작한다.조건절 값이 변경된 레코드가 발견되지 않으면 그대로 UPDATE를 진행한다.
• SQL Server처럼 MVCC 모델을 사용하지 않는 DBMS는 (UPDATE 문 시작 시점이 아니라) 레코드에 도달한 시점을 기준으로 갱신 대상을 식별한다.

26번

• 오라클에서는 ㉡UPDATE를 시작하는 시점에서의 값을 본다.즉 , 커밋되지 않은 값으 볼 수 없음. C1 = 2인 레코드는 없으므로 어떤 변경도 일어나지 않는다.
• C1 = 2인 레코드를 UPDATE 하려는 TX1은 블록킹 됐다가 TX2가 COMMIT을 수행하고 나면 Lock을 획득하고 갱신을 시작한다.
• UPDATE를 시작한 시점에는 조건절 컬럼 C1의 값이 2여서 기다렸다가 갱신하려고 보니 4로 변경된 사실을 발견한 TX1은 UPDATE 문을 ⭐️다시 실행한다.⭐️
• 다시 실행한 시점에는 C1 = 2인 레코드가 없으므로 어떤 처리도 일어나지 않는다.

29번

• SQL Server에서 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 기다린다. TX1이 끝났을 때 7788 사원의 SAL 값은 4000이므로(⭐️커밋전이라도!!⭐️) TX2 트랜잭션이 정상적으로 진행해 값을 3000으로 바꾼다.
• 오라클은 Update 문이 시작되는 시점을 기준으로 갱신 대상 레코드를 식별(⭐️커밋전 데이터는 무시 !!⭐️)하므로 TX2 트랜잭션의 Update는 기다리지도 않고 바로 실행을 종료한다. 따라서 TX1 트랜잭션에 의해 4000으로 변경된 값이 7788 사원의 최종 SAL 값이 된다.

[SQLD]

p.82

조인

70번

• 조인할 때 USING절엔 ALIAS 못들어감 , 접두사 못붙임

USING A.ID = B.ID -- 틀림
USING(ID) -- 맞음

• select 할 때도 A.ID , B.ID가 아니라 SELECT ID 라고 해야함

카타시안곱

• CROSS JOIN이라고 쓰면 된다.

FROM T1 CROSS JOIN T2

⭐️ 아우터조인 조건절 조심 (72번)

FROM A LEFT OUTER JOIN B
WHERE A.ID in (1,2)
ON A.PID=B.PID

와

FROM A LEFT OUTER JOIN B
ON A.ID in (1,2) AND A.PID=B.PID

는 결과가 전혀 다르다.

p.86

76번

• between 1 and 3의 범위는 1,2,3이다.

p.95

계층형 질의

• 89번 : START WITH절은 WHERE절을 적용하지 않는다.

p.100

95번

• 복수행 비교 연산자 : IN
• 다중 컬럼 서브쿼리는 SQL Server에서 지원하지 않는다.