[1] Lock 기본

(1) Lock이란 ?

같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하기 위해
트랜잭션의 순차적 진행을 보장할 수 있는 직렬화(Serialization) 장치

(2) 공유 Lock과 배타적 Lock

1. 공유 Lock (= Shared Lock)

• 데이터를 읽고자 할 때 사용한다.
• ⭐️ 다른 공유 Lock과는 호환되지만 배타적 lock과는 호환되지 않는다.

2. 배타적 Lock (= Exclusive Lock)

• 데이터를 변경하고자 할 때 사용되는데 이 때 트랜잭션이 완료될 때까지 락을 유지한다.
• 해당 Lock이 해제될 때까지 다른 트랜잭션은 해당 Resource에 접근할 수 없다.

(3) 블로킹과 교착상태

1. 블로킹

• Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태이다.
• ⭐️ 공유 Lock과 배타적 Lock은 함께 설정될 수 없으므로 Blocking 이 발생한다.
• Blocking을 해소 할 수 있는 방법은 Commit 또는 Rollback 뿐이다.
• Lock 경합이 발생하면 먼저 Lock이 완료될 때까지 후행 트랜잭션을 기다려야 한다.

Lock에 의한 성능 저하를 최소화 하는 방법

• 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능한 짧게 정의한다.
  • ⭐️ Oracle은 데이터를 읽을 때 Shared Lock을 사용하지 않는다. 따라서 상대적으로 Lock 경합이 적다.
• 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계한다.
  • 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록
    적절한 프로그램 기법을 도입한다.
• 트랜잭션 격리성 수준(Isolation Level)를 불필요하게 상향 조정하지 않는다.
• SQL문장이 가장 빠른 시간 내에 처리를 완료하도록 한다.

  ✅ Blocking을 무한정 기다리지 않는 방법

  • SQL Server
    set lock_timeout 2000
  • Oracle
    SELECT FROM T WHERE NO=1 FOR UPDATE NOWAIT -- 대기없이 Exception을 던진다.
    SELECT FROM T WHERE NO=1 FOR UPDATE WAIT 3 -- 3초 대기 후 Exception을 던진다.

2. 교착상태

• 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주보며 진행하는 상황이다.
• 둘 중 하나가 뒤로 물러나지 않으면 영영 풀릴 수 없다.
• Oracle의 경우 하나의 Transaction을 Rollback하고 Alert파일에 기록된다.
• 여러 테이블을 액세스하면서 발생하는 교착상태는 테이블 접근 순서를 같게 처리하여 회피한다.
• SQL Server라면 갱신(Update) Lock을 사용함으로써 교착상태 발생 가능성을 줄일 수 있다.

[2] SQL Server Lock

(1) Lock 종류

1. 공유 Lock

• SQL Server의 공유 Lock은 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라 다음 레코드가 읽히면 곧바로 해제된다.
  • 단 , Isolation Level이 Read Committed일 경우만!
  • Isolation Level을 변경하지 않고 트랜잭션 내에서 공유 Lock이 유지되도록 하려면 테이블 힌트로 holdlock을 지정하면 된다.

[예시] 나중에 변경할 목적으로 레코드를 읽을 경우

• 고객 데이터를 읽고 적립포인트를 변경하기 전에 다른 트랜잭션이 해당 고객 데이터를 변경했다면
  적립포인트가 비일관된 상태에 놓일 수 있다.
  • 따라서 아래와 같은 패턴으로 트랜잭션을 처리 해야한다.

begin tran

select 적립포인트, 방문횟수, 최근방문일시, 구매실적
 from 고객 with(holdlock)
where 고객번호 = :cust_num

-- 새로운 적립포인트 계사
update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num

commit;

2. 배타적 Lock : 데이터 변경시 사용한다.

3. 갱신 Lock

[예시]

만약 두 트랜잭션이 동시에 같은 고객에 대해서 Update를 수행시 두 트랜잭션 모두 처음에는 공유 Lock을 설정했다가
적립포인트를 변경하기 직전에 배타적 Lock을 설정하려고 한다면 두 트랜잭션은 상태편 트랜잭션에 의한
공유 Lock이 해제되기만을 기다리는 교착상태에 빠지게 된다.
이런 잠재적인 교착상태를 방지하려고 SQL Server는 갱신(Update) Lock을 사용 할 수 있다.
이 때, 한 자원에 대한 갱신 Lock은 한 트랜잭션만 설정할 수 있다.

begin tran

select 적립포인트, 방문횟수, 최근방문일시, 구매실적
 from 고객 with(updlock)
where 고객번호 = :cust_num;


-- 새로운 적립포인트 계산

update 고객 set 적립포인트 = :적립포인트 where 고객번호 = :cust_num

commit;

4. 의도 Lock

• 특정 로우에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지, 익스텐트, 테이블)에 내부적으로 의도(Intent) Lock이 설정된다.
  • 즉, 로우와 그의 테이블 , 익스텐트 , 페이지에 로우 Lock이 걸리는 것이다.
• Lock을 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행 중인지를 알리는 용도로 사용되며, 일종의 푯말(Flag)라고 할 수 있다.

[사용 예시]

• 구조를 변경하기 위해 테이블을 잠그려 할 때 그 하위의 모든 페이지나 익스텐트, 로우에 어떤 Lock이 설정돼 있는지 검사할 경우
  • 모든 요소를 검사하려면 오래 소요되므로 해당 테이블에 어떤 모드의 의도 Lock이 설정돼 있는지만 보고도 작업을 진행할지 아니면 기다릴지를 결정한다.

5. 스키마 Lock

Sch-S(Schema Stability)

• SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생한다.
• 읽는 스키마 정보가 수정되거나 삭제되지 못하도록 한다.

Sch-M(Schema Modification)

• 테이블 구조를 변경하는 DDL문을 수행할 때 발생한다.
• 수정 중인 스키마 정보를 다른 세션이 참조하지 못하도록 한다.

6. Bulk Update Lock

• 테이블 Lock의 일종이다.
• 테이블에 데이터를 대용량 copy(Bulk Copy)할 때 발생한다.
• 병렬 데이터 로딩(Bulk Insert나 bcp 작업을 동시 수행)을 허용하지만 일반적인 트랜잭션 작업은 허용되지 않는다.

(2) Lock 레벨과 Escalation

1. Lock 레벨

1. 로우 레벨 : 변경하려는 로우(실제로는 RID)에만 Lock을 설정하는 것
2. 페이지 레벨 : 변경하려는 로우가 담긴 데이터 페이지(또는 인덱스 페이지)에 Lock을 설정하는 것
   • 같은 페이지에 속한 로우는 진행 중인 변경 작업과 무관하더라도 모두 잠긴것과 같은 효과가 나타난다.
3. 익스텐트 레벨 : 익스텐트 전체에 Lock이 설정된다.
   • SQL Server의 경우, 하나의 익스텐트가 여덟 개 페이지로 구성되므로 8개 페이지에 속한 모든 로우가 잠긴 것과 같은 효과가 나타난다.
4. 테이블 레벨 : 테이블 전체 그리고 관련 인덱스까지 모두 Lock이 설정된다.
5. 데이터베이스 레벨 데이터베이스 전체가 Lock이 설정된다.
   • 보통 데이터베이스를 복구하거나 스키마를 변경할 때 일어난다.

2. Escalation

Escalation이란 관리할 Lock 리소스가 정해진 임계치를 넘으면 로우 레벨 락이 -> 페이지 -> 익스텐트 -> 테이블 레벨 락으로 점점 확장되는 것이다.

• SQL Server, DB2 UDB 처럼 한정된 메모리 상에서 Lock 매니저를 통해 lock 정보를 관리하는 DBMS에서 공통적으로 발생할 수 있는 현상이다.
  • Locking 레벨이 낮을 수록 동시성은 좋지만 관리해야 할 Lock 개수가 증가하기 대문에 더 많은 리소스를 소비한다.
  • Locking 레벨이 높을수록 적은 양의 Lock 리소스를 사용하지만 하나의 Lock으로 수많은 레코드를 한꺼번에 Locking하기 때문에 동시성은 나빠진다.

(3) Lock호환성

✅ '호환된다'의 의미

한 리소스에 두 개 이상의 Lock을 동시에 설정할 수 있음을 뜻한다.

• 스키마 Lock 호환성
  • Sch-S는 Sch-M을 제외한 모든 Lock과 호환된다.
  • Sch-M은 어떤 Lock과도 호화되지 않는다.(스키마 변경시)

[3] Oracle Lock

Oraclde Lock은 공유 리소스와 사용자 데이터를 보호한다.

DML Lock, DDL Lock, 래치(Latch), 버퍼 Lock, 라이브러리 캐시 Lock/Pin등 다양한 종류의 Lock이 있다.

(1) 로우 Lock

• 로우 Lock은 항상 배타적이다.
• INSERT, UPDATE, DELETE 문이나 SELECT....FOR UPDATE 문을 수행한 트랜잭션에 의해 설정되면, 트랜잭션이 커밋 또는 롤백할 때까지 다른 트랜잭션은 해당 로우는 변경할 수 없다.
• ⭐️ Oracle에서 읽는 과정에서는 어떤 Lock도 설정하지 않는다.
  • ⭐️ 즉, 읽기와 갱신 작업은 서로 방해 하지 않는다.

    ⭐️ 읽는 과정에서의 로우 Lock

    읽으려는 데이터를 다른 트랜잭션이 갱신 중이더라도 기다리지 않는다.
    갱신하려는 데이터를 다른 트랜잭션이 읽는 중이더라도 기다리지 않는다.
    (SELECT...FOR UPDATE 구문은 제외)
    갱신하려는 데이터를 다른 트랜잭션이 갱신중이면 기다린다.

• oracle이 공유 Lock을 사용하지 않고도 일관성을 유지할 수 있는 이유
  • 👉 UNDO 데이터를 이용한 다중 버전 동시성 제어 매커니즘을 사용하기 때문이다. ⭐️⭐️
• Oracle은 별도의 Lock 매니저 없이 레코드의 속성으로서 로우 Lock을 구현했기 때문에 아무리 많은 레코드를 갱신하더라도 절대 Lock Escalation은 발생하지 않는다.

(2) 테이블 Lock

• 한 트랜잭션이 로우 Lock을 얻는 순간, 해당 테이블에 대한 테이블 Lock도 동시에 얻어 현재 트랜잭션이 갱신 중인 테이블에 대한 호환되지 않는 ⭐️ DDL 오퍼레이션을 방지 한다.⭐️
• RS 모드 테이블 Lock을 얻는다.
• RX 모드 테이블 Lock을 얻는다.

테이블 Lock 종류

1. Row Share(RS)
   • SELECT...FOR UPDATE 문을 수행할 때
2. Row Exclusive(RX)
   • insert, update, delete 문을 수행할 때
3. Share(S)
4. Share row Exclusive(SRX)
5. Exclusive(X)

• 일반적으로 DML 로우 Lock을 처음 얻는 순간 묵시적으로 테이블 Lock을 얻지만, 아래처럼 명령어를 이용해서도 가능하다.

lock table emp in row share mode;

lock table emp in row esclusive mode;

lock table emp in share mode;

lock table emp in share row exclusive mode;

lock table emp in exclusive mode;

• ⭐️'테이블 Lock'이라고해서 테이블 전체에 Lock이 걸리는 것이 아니다!!⭐️

  ✅ Oracle의 테이블 Lock의 의미

  • Lock을 획득한 선행 트랜잭션이 해당 테이블에서 현재 어떤 작업을 수행중인지를 알리는 일종의 푯말(Flag)
  • 후행 트랜잭션은 어떤 테이블 Lock이 설정돼 있는지만 보고도 그 테이블로의 진입 여부를 결정할수 있다.