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

✍️ 1번 : Lock

Lock에 대한 설명으로 가장 부적절한 것

1. DB 모니터링 과정에 Lock이 자주 나타나는 것은 자연스러운 현상이다. 👉 ⭕️
2. DB 모니터링 과정에 블로킹(Blocking)이 자주 나타나는 것은 좋지 않은 현상이다. 👉 ⭕️
3. Lock에 의해 발생하는 문제를 해결하는 가장 효과적인 방법은 SQL 튜닝해서 I/O를 줄이는
   것이다. 👉 ⭕️
4. 모니터링 과정에 교착상태(Deadlock)이 발견되면 두 세션 중 하나를 DBA가 강제 종료해야한다. 👉 ❌

🍋 기출 포인트

1. 교착상태가 발생하면, DBMS가 둘 중 한 세션에 에러를 발생시킴으로써 교착 상태를 해소한다.

🍒 문제 해설

1. Lock 경합(=블로킹)은 최소화해야 하지만, Lock 자체는 DML을 수행하는 과정에 당연히 나타나는 자연스러운 현상이다.

✍️ 2번 : Look과 트랜잭션

Look과 트랜잭션에 대한 설명으로 가장 부적절한 것

1. 원자성(Atomicity)을 훼손하지 않는 범위에서 트랜잭션은 가능한 짧게 정의하는 것이 좋다. 👉 ⭕️
2. 공유 Lock과 배타적 Lock은 서로 호환되지 않으므로 블로킹이 발생한다. 👉 ⭕️
3. 온라인 트랜잭션에서 자주 변경하는 데이터를 배치(Batch) 프로그램이 동시에 변경하지 않도록 프로그램을 설계하거나 시간대를 조절하는 것이 좋다. 👉 ⭕️
4. 온라인 트랜잭션을 처리하는 프로그램에 SELECT FOR UPDATE 문을 사용해선 안 된다. 👉 ❌

🍋 기출 포인트

1. 한 트랜잭션 내에서 나중에 변경할 목적으로 데이터를 읽을 때는 SELECT FOR UPDATE 문을 사용해야 한다.

✍️ 3번 : Lock Escalation

✅ Lock Escalation

• 로우 레벨 Lock이 페이지, 익스텐트, 테이블 레벨 Lock으로 점점 확장되는 것이다.

✍️ 4번 : Lock 호환성

아래 SQL을 실행하는 동안 다른 트랜잭션에서 동시 수행이 가능한 SOL

1. ❌

   delete from 주문 where 주문번호 = 100 ;

2. ❌

   update 주문 set 배송지주소 = dlvr_addr where 주문번호 = 100 ; 

3. ❌

   update 주문 set 주문수량 = 주문수량 + 1 where 주문번호 = 100 ;

🍋 기출 포인트

1. 모든 DBMS가 DML 수행 시 배타적 Lock을 사용한다. 배타적 Lock끼리는 호환되지 않으므로 서로 블로킹 한다. 3번처럼 서로 다른 컬럼을 Update 하더라도 Lock 경합을 피할 수 없다. Row 단위 Lock을 사용하기 때문이다.

✍️ 5번 : Lock 호환성

SQL Server에서 다른 트랜잭션에서 동시 수행이 가능한 SQL

▶ 주문_PK : 주문번호

begin transaction
update 주문 set 배송지주소 = :dlvr_addr where 주문번호 = 100;

1. ❌

   select * from 주문 where 주문번호 = 100

2. ❌

   insert into 주문(주문번호, …) values (100, … )

3. ❌

   delete from 주문 where 주문번호 = 100

🍋 기출 포인트

1. SQL Server는 SELECT 문으로 데이터를 읽을 때 공유 Lock을 사용한다. 공유 Lock끼리는 호환되므로 서로 블로킹 하지 않지만, 공유 Lock은 배타적 Lock과 호환되지 않으므로 서로 불로킹 한다.

🍒 문제 해설

1. 모든 DBMS가 DML 수행 시 배타적 Lock을 사용한다. 배타적 Lock끼리는 호환되지 않으므로 서로 블로킹 한다.
2. MVCC 모델을 사용하는 오라클은 SELECT 문 수행 시 어떤 Lock도 사용하지 않는다.따라서 DML이 수행 중인 데이터를 어떤 간섭도 받지 않고 읽을 수 있다.
3. 오라클은 다른 트랜잭션이 읽고 있는 데이터를 변경할 때도 블로킹은 발생하지 않는다.

✍️ 6번 : Lock 호환성

오라클에서 다른 트랜잭션에서 동시 수행이 가능한 SQL을 고르시오

▶ 주문_PK : 주문번호
아래
update 주문 set 배송지주소 = :dlvr_addr where 주문번호 = 100;

1. ⭕️

   select * from 주문 where 주문번호 = 100

2. ❌

   update 주문 set 주문수량 = 주문수량 + 1 where 주문번호 = 100

3. ❌

   delete from 주문 where 주문번호 = 100

🍋 기출 포인트

1. MVCC 모델을 사용하는 오라클은 SELECT문 수행 시 어떤 Lock도 사용하지 않는다.
2. 따라서 DML이 수행 중인 데이터를 어떤 간섭도 받지 않고 읽을 수 있다.
3. 물론, 다른 트랜잭션이 읽고 있는 데이터를 변경할 때도 블로킹은 발생하지 않는다.

✍️ 7번 : 오라클 Lock

오라클 Lock에 대한 설명으로 가장 부적절한 것

1. (FOR UPDATE를 사용하지 않는 한) SELECT와 DML은 서로 진행을 방해하지 않는다. 👉 ⭕️
2. 다른 트랜잭션이 변경 중인 레코드를 삭제할 수 없다. 👉 ⭕️
3. 오라클에서는 Lock Escalation이 발생하지 않는다. 👉 ⭕️
4. 다른 트랜잭션이 변경 중인 레코드를 읽어야 할 때 기다리지 않고 변경된(변경하고 아직 커밋하지 않은) 현재 값을 그대로 읽는다. 👉 ❌

🍋 기출 포인트

1. 오라클은 다른 트랜잭션이 변경 중인 레코드를 읽고자 할 때 기다리지 않는 것은 맞지만, 변경 중인 값을 읽지는 않는다.
2. 오라클은 다중버전 동시성 메커니즘을 사용하므로 변경되기 이전 값을 Undo에서 찾아서 읽는다.

✍️ 8번 : SQL Server의 Lock

SQL Server의 Lock에 대한 설명으로 가장 부적절한 것

1. 다른 트랜잭션이 변경 중인 레코드를 읽으려면 커밋할 때까지 기다려야 한다. 👉 ⭕️
2. 다른 트랜잭션이 조회 중인 레코드를 읽을 때는 기다리지 않아도 된다. 👉 ⭕️
3. 변경하는 데이터가 많아지면 Lock Escalation이 발생한다. 👉 ⭕️
4. 다른 트랜잭션이 조회 중인 레코드를 변경하려면 마지막 레코드를 읽고 조회를 마칠 때까지 기다려야 한다. 👉 ❌

🍋 기출 포인트

1. 다른 트랜잭션이 조회 중인 레코드를 변경하려면 다음 레코드로 이동할 때까지만 기다리면
   된다.

✍️ 9번 : Lock 호환성

SQL Server에서 아래 SQL을 실행하는 동안 다른 트랜잭션에서 동시 수행이 가능한 SQL

(단, 주문_임시 테이블에는 어떤 인덱스나 제약 설정도 없는 상태)

begin transaction
insert into 주문_임시 (주문번호, …) values (100, …);
go

1. ❌

   select * from 주문_임시 where 주문번호 = 100;

2. ❌

   update 주문_임시 set 배송지주소 = dlvr_addr where 주문번호 = 100;

3. ⭕️

   insert into 주문_임시 (주문번호, …) values (100, … );

🍋 기출 포인트

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

✍️ 10번 : Lock 호환성

SQL Server에서 아래 SQL을 실행하는 동안 다른 트랜잭션에서 동시 수행이 가능한 SQL

(단, 주문_임시 테이블에는 어떤 인덱스나 제약 설정도 없는 상태임)

begin transaction

insert into 주문_임시 (주문번호, 고객ID, 주문일자, 주문금액 )
values ( 100, 'ORAKING', '20210331', 20000);

go

INSERT 중인 데이터를 다른 트랜잭션이 읽거나 변경하거나 삭제하는 작업은 인덱스나 제약
유무에 상관없이 불가능하다. 
 

1. ❌

   select * from 주문_임시 where 주문번호 = 100;

2. ❌

   select * from 주문_임시 where 주문금액 > 10000;

3. ❌

   select * from 주문_임시 where 주문금액 < 10000;

🍋 기출 포인트

1. 3번 SQL 조건절은 INSERT 중인 데이터를 포함하지 않지만, 인덱스가 없으므로 ⭐️Full Scan⭐️으로 처리된다.
2. 따라서 INSERT 중인(= Lock이 걸린) 데이터를 읽고 지나가야 하므로 그 과정
   에 블로킹 된다.

🍒 문제 해설

1. 1번과 2번 SQL은 INSERT 중인 데이터를 읽어야 하므로 블로킹된다.

✍️ 11번 : Lock 호환성

SQL Server에서 아래 SQL을 실행하는 동안 다른 트랜잭션에서 동시 수행이 가능한 SQL

[인덱스 구성]
주문_PK : 주문번호
주문_X1 : 주문금액

begin transaction
insert into 주문 ( 주문번호, 고객ID, 주문일자, 주문금액 )
values ( 100, 'ORAKING', '20210331', 20000 )
go 

1. ❌

   select * from 주문 where 주문번호 = 100;

2. ❌

   select * from 주문 where 주문금액 > 10000;

3. ⭕️

   select * from 주문 where 주문금액 < 10000;

🍋 기출 포인트

1. 3번 조건절은 주문금액이 선두 컬럼인 인덱스를 사용한다면, INSERT 중인 데이터를 전혀 액세스하지 않으므로 블로킹 되지 않는다.
2. 1번과 2번 SQL은 INSERT 중인 데이터를 읽어야 하므로 블로킹
   된다.

✍️ 12번 : 오라클 DBMS Lock 경합

오라클 DBMS를 모니터링하던 중 아래 SELECT 문에서 Lock 경합이 관찰되었다. 아래 SELECT 문에 Lock 경합을 유발할 가능성이 없는 SQL을 고르시오.

select 주문번호, 주문일시, 주문금액 from 주문 where 주문번호 = ord_no
for update;

1. ❌

   insert into 주문
    select * from 주문_임시 where 주문번호 = ord_no;

2. ⭕️

   update 주문 set 배송지주소 = dlv_addr where 주문번호 = ord_no;

3. ⭕️

   delete from 주문 where 주문번호 = ord_no;

4. ⭕️

   select 배송지주소 from 주문 where 주문번호 = ord_no for update;

🍋 기출 포인트

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

🍒 문제 해설

1. 양쪽 모두 :ord_no 변수에 같은 값을 입력함으로써 같은 데이터를 액세스하면 SELECT
   FOR UPDATE와 UPDATE는 서로 블로킹 한다.
2. 양쪽 모두 :ord_no 변수에 같은 값을 입력함으로써 같은 데이터를 액세스하면 SELECT
   FOR UPDATE와 DELETE는 서로 블로킹 한다.
3. 양쪽 ord_no 변수에 같은 값을 입력함으로써 같은 데이터를 액세스하면 SELECT FOR
   UPDATE 끼리도 서로 블로킹 한다.

✍️ 13번 : 오라클 테이블 Lock

오라클 테이블 Lock에 대한 설명으로 가장 부적절한 것

1. 테이블 Lock이 설정되더라도 데이터를 조회하는 데는 문제가 없다. 👉 ⭕️
2. 테이블 Lock을 사용하는 가장 중요한 이유는 DML과 DDL 간 충돌을 막기 위해서다. 👉 ⭕️
3. RX(Row Exclusive) 모드끼리 서로 호환된다. 👉 ⭕️
4. (Lock 모드와 상관없이) 한 사용자가 테이블 Lock을 설정하면, 다른 사용자는 해당 테이블의 데이터를 변경하지 못한다. 👉 ❌

🍋 기출 포인트

1. 테이블 Lock이 설정됐다고 해서 무조건 데이터를 변경하지 못하도록 테이블 전체에
   Lock을 설정하는 것은 아니다.

🍒 문제 해설

1. 오라클에서 데이터를 변경하려면 로우 Lock 뿐만 아니라 테이블 Lock도 반드시 획득해야 하는데 이는 사용자가 데이터를 갱신(DML)하는 도중에 DBA가 테이블 구조를 변경(DDL)하지 못하게 막기 위해서다.
2. 또한 오라클의 테이블 Lock은 DBA가 테이블 구조를 변경(DDL)하는 도중에 사용자가 데이터를 갱신
   (DML)하지 못하게 막는 역할도 한다.
3. 오라클에서 테이블 Lock(=TM Lock)은 Lock을 획득한 선행 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행 중인지를 알리는 일종의 푯말(Flag)이다.

✍️ 14번 : TM Lock과 TX Lock

한 트랜잭션에서 아래와 같이 2개의 DML을 실행한 상황에서 Lock을 모니터링시 TM Lock과 TX Lock이 각각 몇 개씩 관찰되는지?

UPDATE EMP SET DEPTNO = 30 WHERE DEPTNO = 20;
DELETE FROM DEPT WHERE DEPTNO = 30;

🍋 기출 포인트

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

🍒 정답

1. TX Lock 1개, TM Lock 2개

✍️ 15번 : 교착 상태가 검출되지 않는 SQL

트랜잭션 2가 t4 시점에 실행했을 때 교착 상태가 검출되지 않는 SQL

1. ❌(교착상태 미검출)

INSERT INTO EMP (EMPNO, ... ) VALUES ( 7900, ... );

2. ⭕️(교착상태 검출)

   DELETE FROM EMP WHERE EMPNO > 7788;

3. ⭕️(교착상태 검출)

   UPDATE EMP SET ENAME='ORAKING' WHERE EMPNO = 7900;

4. ⭕️(교착상태 검출)

   SELECT * FROM EMP WHERE EMPNO = 7900 FOR UPDATE;

🍋 기출 포인트

1. 트랜잭션 2가 t4 시점에 2번 INSERT문을 실행하면, EMPNO에 PK 제약을 설정하지 않은 경우 정상적으로 처리된다.
2. PK 제약을 설정한 경우에는 'ORA-00001(무결성 제약 위배)' 에러를 만나므로 교착 상태가 발생하지 않는다.