Q. 공통 기술팀에서 개발표준 업무를 담당하는 고성능 씨가 Lock 경합에 의한 성능 저하를 최소화하기 위해 개발팀에 제시한 가이드 라인으로 가장 부적절한 것은?
① 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션올 가능한 짧게 정의할 것 ② 같은 데이터를 갱신하는 프로그램이 가급적 동시에 수행되지 않도록 트랜잭션을 설계할 것 ③ select 문장에 for update 문장을 사용하지 말 것 ④ 온라인 트랜잭션을 처리하는 DML 문장을 1순위로 튜닝함으로써 조건절에 맞는 최적의 인덱스를 제공할 것
해설 : for update 구문을 반드시 사용해야 할 경우가 있는데, 성능을 이유로 사용을 못하게 하면 데이터 정합성을 해칠 수 있다. 성능보다 중요한 것이 데이터 정합성이다. nowait 이나 wait 옵션을 잘 활용하면 select for update 문장을 통해 오히려 동시성을 높일 수도 있다.
※ Lock 에 의한 성능 저하를 최소화하는 방안
① 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션올 가능한 짧게 정의할 것 ② 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계할 것 ③ 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록 적절한 프로그래밍 기법을 도입할 것 ④ 트랜잭션 격라성 수준을 불필요하게 상향 조정하지 않을 것 ⑤ 트랜잭션을 잘 설계하고 대기 현상을 피하는 프로그래밍 기법을 적용하기에 앞서, SQL 문장이 가장 빠른 시간 내에 처리를 완료할 것
문제 2
Q. MS-SQL Server에서 아래 UPDATE문과 블로킹 없이 동시 수행이 가능한 SQL문을 보기에서 고르시오. (단, Transaction Isolation Level을 조정하지 않았고, Snapshot 관련 데이터베이스 설정도 초기값 그대로인 상황이다.)
UPDATE EMP SET SAL = SAL * 1.1 WHERE DEPTNO = 20
①
select * from emp where empno = 7900
②
update emp set sal = sal * 1.1 where empno = 7900
③
delete from emp where empno = 7903
④
insert into emp(empno, ename, deptno, sal) values(8014, '이정훈'，40, 4000)
해설 : SQL Server에서 Share Lock 과 Exclusive Lock 호환이 되지 않으므로 ①번 SELECT문에는 블로킹이 발생할 수 있다. ④번 INSERT 문은 문제의 UPDATE문과 DEPTNO가 서로 다르고 PK 중복도 없으므로 블로킹 없이 진행된다.
※ 불로킹(Blocking)
- Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태 - 공유 Lock 끼리는 호환되기 때문에 불로킹이 발생하지 않는다.
- 공유(Share) Lock과 배타적(Exclusive) Lock은 호환되지 않아 블로킹이 발생할 수 있다.
문제 3
Q. 다음 중 오라클 PL/SQL로 작성한 아래 프로그램을 MS-SQL Server T-SQL 구문으로 변환하고자 한다. 아래 FOR UPDATE 구문을 대신하기 위해 사용할 SQL Server 힌트로 가장 적절한 것을 2개 고르시오.
SELECT 적립포인트, 구매실적, 방문횟수, 최근방문일자
INTO v_적립포인트, v_구매실적, v_방문횟수, v_최근방문일자
FROM 고객
WHERE 고객번호 = 100
FOR UPDATE;
-- do anything ...
UPDATE 고객 SET 적립포인트 = v_적립포인트 WHERE 고객번호 = 100;
COMMIT;
① FROM 고객 WITH (HOLDLOCK) ② FROM 고객 WITH (UPLOCK) ③ FROM 고객 WITH (READPAST) ④ FROM 고객 WITH (TABLOCK)
※ SQL Server 의 공유 Lock은 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라 다음 레코드가 읽히면 곧바로 해제된다. 단, 기본 트랜잭션 격리성 수준(Read Comitted)에서만 그렇다. 격리성 수준을 변경하지 않고도 트랜잭션 내에서 공유 Lock이 유지되도록 하려면 테이블 힌트로 holdlock을 지정하면 된다. 또한, 두 트랜잭션은 상대편 트랜잭션에 의한 공유 Lock이 해제되기만을 기다리는 교착상태를 방지하려고 SQL Server는 갱신(Update) Lock을 두게 되었고, 이 기능을 사용하려면 uplock 힌트를 지정하면 된다.
문제 4
Q. 다음 중 아래와 같은 락(Lock) 모니터링 결과가 발생할 수 있는 SQL로 가장 적절한 것은?
SELECT a.SESSION_ID, a.LOCK_TYPE, a.MODE_HELD, a.MODE_REQUESTED, a.LOCK.ID1, a.LOCK_ID2
FROM DBA_LOCK a, V$SESSION b
WHERE a.SESSION_ID b.SID
AND a.LOCK_TYPE IN ('Transaction', 'DML')
AND b.USERNAME = USER;
①
INSERT INTO EMP (EMPNO, ENAME) VALUES (1000, 'SQLP');
②
UPDATE EMP SET SAL = 1000 WHERE EMPNO = 7369;
③
INSERT /*+ APPEND */ INTO EMP SELECT * FROM SCOTT.EMP;
해설 : Update 문에서 TM Lock은 호환성이 있는 Row-X (SX) 모드로, TX Lock은 호환성이 없는 Exclusive 모드로 Lock을 획득한다.
300x250
문제 6
Q. 다음 중 아래에서 트랜잭션의 특징을 연결한 것으로 가장 적절한 것은?
가. 더 이상 분해가 불가능한 업무의 최소단위를 말한다. 나. 트랜잭션이 그 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 변환한다. 즉, 트랜잭션 실행의 결과로 데이터베이스 상태가 모순되지 않는다. 다. 트랜잭션이 실행 중에 생성하는 연산의 중간 결과는 다른 트랜잭션이 접근할 수 없다. 라. 트랜잭션이 일단 그 실행을 성공적으로 완료하면 그 결과는 데이터베이스에 영속적으로 저장된다.
해설 :트랜잭션의 주요 특징은 원자성(Atomicity), 일관성(Consistency), 격리성(Isolation), 영속성(Durability)이며, 영문 첫 글자를 따서 'ACID'라고 한다. '가'는 원자성, '나'는 일관성, '다'는 격리성, '라'는 영속성에 대한 설명이다.
문제 7
Q.아래와 같이 100번 세션(SID=100)과 200번 세션(SID=200)에서 쿼리를 순차적으로 실행했을 때 (가), (나)의 결과로 가장 적절한 것은?
[테이블]
CREATE TABLE t1
AS
SELECT LEVEL AS c1
FROM DUAL
CONNECT BY LEVEL <= 10;
해설 : 대부분 DBMS가 Read Committed 를 기본 트랜잭션 격리성 수준으로 채택하고 있으므로 Dirty Read가 발생할까 걱정하지 않아도 되지만, Non-Repeatable Read, Phantom Read 현상에 대해선 세심한 주의가 필요하다.
※ 낮은 단계의 격리성 수준에서 발생할 수 있는 현상들
- Dirty Read : 다른 트랜잭션에 의해 수정됐지만 아직 커밋되지 않은 데이터를 읽는 것 - Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이에 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상 - Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령(Phantom) 레코드가 두 번째 쿼리에서 나타나는 현상
문제 9
Q. 다음 중 트랜잭션 동시성 제어에 대한 설명으로 가장 부적절한 것은?
① 비관적 동시성 제어(Pessimistic Concurrency Control)는 두 트랜잭 션이 같은 데이터를 동시에 수정할 것이라고 가정하고 데이터를 읽는 시점에 Lock을 설정하는 방식을 말한다. ② 낙관적 동시성 제어(Optimistic Concurrency Control)는 두 트랜잭션이 같은 데이터를 동시에 수정하지 않을 것이라고 가정하고 데이터를 읽는 시점에 Lock을 설정하지 않는 방삭을 말한다. 이 방식에선 데이터를 수정하는 시점에, 앞서 읽은 데이터가 다른 트랜잭션에 의해 변경되었는지 반드시 확인해야 데이터 정합성을 유지할 수 있다. ③ 트랜잭션 격리성 수준(Transaction Isolation Level)을 Serializable로 상향 조정하면 일반적으로 동시성과 일관성이 같이 높아진다. ④ 트랜잭션 격리성 수준(Transaction Isolation Level)을 Serializable로 상향 조정하면 프로그램에서 별도의 동시성 제어를 하지 않아도 DBMS가 트랜잭션 단위 일관성올 보장해 준다.
■ Read Uncommitted : 트랜잭션에서 처리 중인, 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것을 허용한다. ■ Read Committed : 트랜잭션이 커밋되어 확정된 데이터만 다른 트랜잭션이 읽도록 허용함으로써 Dirty Read를 방지해준다. ■ Repeatable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지해준다. ■ Serializable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않음은 물론 새로운 레코드가 나타나지도 않는다.
728x90
문제 10
Q. EMP 테이블 7788번 사원의 SAL 값이 현재 1,000인 상황에서 아래 TX1, TX2 두 개의 트랜잭션이 동시에 수행되었다. 양쪽 트랜잭션이 모두 완료된 시점에 7788번 사원의 SAL 값은 Oracle과 SQL Server에서 각각 얼마인지 작성하시오. (Oracle과 SQL Server 순으로 적으시오. 단, Transaction Isolation Level을 조정하지 않았고, Snapshot 관련 데이터베이스 설정도 초기값 그대로인 상황이다.)
해설 : Oracle은 Update 문장이 시작되는 시작을 기준으로 갱신 대상 레코드를 식별하므로 TX2 트랜잭션의 Update는 실패한다. 따라서 TX1 트랜잭션의 결과가 7788 사원의 최종 결과가 된다. SQL Server에서 TX2 트랜잭션은 TX1 트랜잭션이 완료될 때까지 기다린다. TX1이 끝났을 때 7788 사원의 sal 값은 2000이므로 TX2 트랜잭션이 정상적으로 진행해 값을 3000으로 바꾼다.
문제 11
Q. 다중버전 동시성 제어(Multiversion Concurrency Control, 이하 MVCC) 모델을 채택하는 DBMS가 늘고 있다. 다음 중 DBMS의 읽기 일관성 매커니즘을 설명한 것으로 부적절한 것은?
① 읽기 일관성을 위해 Undo 세그먼트(또는 버전 저장소)에 저장된 Undo(또는 Snapshot) 데이터를 활용한다. ② MVCC 모델은 기본적으로 완벽한 문장 수준 읽기 일관성을 보장한다. ③ MVCC 모델은 기본적으로 완벽한 트랜잭션 수준 읽기 일관성을 보장한다. ④ 트랜잭션 수준 읽기 일관성이란, 트랜책션이 시작된 시점을 기준으로 일관성 있게 데이터를 읽어들이는 것을 말한다.
해설 : MVCC 모델은 문장 수준의 읽기 일관성을 완벽히 보장하지만, 트랜잭션 수준의 읽기 일관성을 보장하지는 않는다.
※ 다중버전 동시성 제어 (Multiversion Concurrency Control, 이하 MVCC) - 데이터룰 변경할 때마다 그 변경사항을 Undo 영역에 저장해둔다. - 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경이 진행 중이거나 이미 커밋된) 값을 발견하면, Undo 영역에 저장된 정보롤 이용해 쿼리(또는 트랜잭션) 시작 시점의 일관성 있는 버전(CR Copy)울 생성하고 그것을 읽는다.
