📔설명

Lock, 트랜잭션, 동시성 제어에 대해 알아보자

---

🎂Lock

1. Lock 기본

Lock이란?

같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면 트랜잭션 순차적 진행을 보장할 수 있는 직렬화(serialization) 장치 필요
=> Lock을 통해 가능케 함

공유 Lock과 배타적 Lock

트랜잭션 오퍼레이션별로 적당한 수준의 Lock을 자동으로 설정

• 공유 Lock : 데이터를 읽을 때 사용
  => 다른 공유 Lock과 호환
  => 배타적 Lock과 호환X

• 배타적 Lock : 데이터를 변경할 때 사용
  => 트랜잭션 완료까지 유지
  => 해제될 때 까지 다른 트랜잭션은 해당 리소스에 접근 불가
  => 공유 Lock, 배타적 Lock과 호환X

블로킹과 교착상태

블로킹 : Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태
=> 해소하는 방법은 커밋(또는 롤백)뿐
=> Lock 경합이 발생하면 먼저 Lock을 설정한 트랜잭션이 완료될 때까지 후행 트랜잭션은 기다려야 하며, 이런 현상이 자주 나타나면 사용자가 느끼는 성능 안좋음
=> Lock에 의한 성능 저하 최소화 방안

• 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능하면 짧게 정의
• 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계, 트랜잭션이 활발한 주간에 대용량 갱신 작업 수행X
• 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 대해 사용자가 무한정 기다리지 않도록 적절한 프로그래밍 기법 도입 (ex. SQL Server의 LOCK_TIMEOUT)

--sql server
set lock_timeout 2000 --대기 시간이 최대 2초를 넘지 않도록

--oracle
select * from t where no = 1 for update nowait -- 대기없이 Exception을 던짐 
select * from t where no = 1 for update wait 3 -- 3초 대기 후 Exception을 던짐

• 트랜잭션 격리성 수준을 불필요하게 상향 조정X
• 트랜잭션을 잘 설계하고 대기 현상을 피하는 프로그래밍 기법을 적용하기 앞서, 결과가 가장 빨리 나오는 SQL을 작성하는 것이 Lock 튜닝 기본

교착상태 : 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주 보며 진행하는 상황
=> 둘 중 하나가 뒤로 물러나지 않으면 영영 안풀림
=> DBMS가 둘 중 한 세션에 에러 발생을 시켜 문제 해결
=> 교착상태 발생 가능성 줄이는 방안

• 테이블 접근 순서를 같게 처리
  ex. 마스터 테이블과 상세 테이블을 둘 다 갱신하려고 할 때, 마스터 테이블 다음에 상세 테이블 갱신하기로 규칙을 정하고, 모든 애플리케이션 개발자가 이 규칙을 지키면 교착상태 발생X
• SQL Server는 갱신(update Lock) 사용해 발생 가능성 줄임

2. SQL Server Lock

Lock 종류

공유 Lock
=> 트랜잭션이나 쿼리 수행이 완료될 때까지 유지되는 것이 아니라, 다음 레코드를 읽으면 곧바로 해제 (기본 트랜잭션 격리성 수준(Read committed)에서만)
=> 트랜잭션 내에서 공유 Lock을 유지하려면, 테이블 힌트로 holdlock 지정

-- 변경할 목적으로 레코드 읽을 땐 반드시 아래처럼 처리해야 함
begin tran 

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

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

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

commit

배타적 Lock
=> 앞에서 설명한 내용과 동일

갱신 Lock
=> ex. 위 예제의 트랜잭션이 같은 고객에 대해 동시에 수행됐다고 가정, 두 트랜잭션 모두 처음에는 공유 Lock을 설정했다가 적립포인트 변경 직전에 배타적 Lock을 설정하려고 할 것. 그러면 상대편 트랜잭션에 의한 공유 Lock이 해제되기만을 기다리는 교착상태에 빠짐
=> 위 경우를 방지하려고 갱신(Update) Lock을 두었으며, uplock 힌트를 지정
=> 한 자원에 대한 갱신 Lock은 한 트랜잭션만 설정
=> 갱신 Lock은 공유 Lock과는 호환

begin tran 

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

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

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

commit

의도 Lock
=> 특정 로우에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지, 익스텐트, 테이블)에 내부적으로 의도(Intent) Lock이 설정
=> Lock을 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행중인지를 알리는 용도
=> 일종의 푯말
=> ex. 구조 변경을 위해 테이블을 잠그려 할 때, 그 하위의 모든 페이지나 익스텐트, 심지어 로우에 어떤 Lock이 설정돼 있는지 일일이 검사하려면 오래걸리는데, 이런 현상 방지. 즉 해당 테이블에 어떤 모드의 의도 Lock이 설정돼 있는지만 보고도 작업을 진행할지 아니면 기다릴지 결정 가능

스키마 Lock
=> 테이블 스키마에 의존적인 작업을 수행할 때 사용

• Sch-S(Schema Stability) : SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생, 읽는 스키마 정보를 수정하거나 삭제하지 못하도록 함
• Sch-M(Schema Modification) : 테이블 구조를 변경하는 DDL문을 수행할 때 발생, 수정중인 스키마 정보를 다른 세션이 참조하지 못하도록 함

Bulk Update Lock
=> 테이블 Lock의 일종으로, 테이블에 데이터를 Bulk Copy할 때 발생
=> 병렬 데이터 로딩(Bulk Insert나 bcp)은 허용, 일반적인 트랜잭션 작업 허용X

Lock 레벨과 Escalation

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

Lock 레벨이 낮을수록 동시성 증가, 관리해야 할 Lock 개수 증가로 리소스 많이 소비
Lock 레벨이 높을수록 적은 양의 리소스사용, 하나의 Lock으로 수많은 레코드를 한꺼번에 잠그기 때문에 동시성 나빠짐

Lock 호환성

호환된다 : 한 리소스에 두 개 이상의 Lock 동시 설정 가능

스키마 Lock 호환성

• Sch-S는 Sch-M 제외 모든 Lock과 호환
• Sch-M은 어떤 Lock도 호환X

3. Oracle Lock

DML Lock, DDL Lock, 래치, 버퍼, 라이브러리 캐시 Lock/Pin 등 다양한 종류의 Lock을 사용

DML Lock : 다중 사용자에 의해 동시에 액세스되는 사용자 데이터의 무결성을 보호해줌
=> 로우 Lock과 테이블 Lock

로우 Lock

오라클 로우 Lock은 항상 배타적
=> insert, update, delete, select ~ for update 문을 수행한 트랜잭션에 의해 설정
=> 해당 트랜잭션이 커밋 또는 롤백할 때까지 다른 트랜잭션은 해당 로우 변경 불가

일반 select문에 의해 읽힌 레코드에는 어떤 Lock도 설정X
=> 오라클의 읽기와 갱신은 서로 방해X

• 읽으려는 데이터를 다른 트랜잭션이 갱신중이더라도 기다리지X
• 갱신하려는 데이터를 다른 트랜잭션이 읽는중이더라도 기다리지X(select ~ for update 제외)
• 갱신하려는 데이터를 다른 트랜잭션이 갱신중이면 기다림

오라클은 아무리 많은 레코드를 갱신하더라도 Lock Escalation 발생X

테이블 Lock

한 트랜잭션이 로우 Lock을 얻을 때 해당 테이블의 테이블 Lock도 동시에 얻음
=> 현재 트랜잭션이 갱신중인 테이블의 구조를 변경(DDL)하지 못하게 막기 위해서

테이블 Lock 종류

• Row Share(RS)
• Row Exclusive(RX)
• Share(S)
• Share Row Exclusive(SRX)
• Exclusive(X)

select .. for update 문 수행 시 RS 모드 테이블 Lock
insert, update, delete 문 수행 시 RX 모드 테이블 Lock

-- 명시적으로 Table Lock 얻기
lock table emp in row share mode; 
lock table emp in row exclusive mode; 
lock table emp in share mode; 
lock table emp in share row exclusive mode; 
lock table emp in exclusive mode;

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

---

🍰트랜잭션

트랜잭션(Transaction) : 업무 처리를 위한 논리적인 작업 단위
=> 여러 개의 갱신 연산이 하나의 작업처럼 전부 처리되거나 아예 하나도 처리되지 않도록(All or Nothing) 동시 실행 구현

1. 트랜잭션의 특징

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

2. 트랜잭션 격리성

트랜잭션 격리성 : 일관성과 마찬가지로, Lock을 강하게 오래 유지할수록 강화, Lock을 최소화할 수록 약화

낮은 단계의 격리성 수준에서 발생할 수 있는 현상들

• Dirty Read : 다른 트랜잭션이 수정한 후 커밋하지 않은 데이터를 읽는 것
  => 변경 후 아직 커밋되지 않은 값을 읽었는데, 변경을 가한 트랜잭션이 최종적으로 롤백된다면 그 값을 읽은 트랜잭션은 비일관된 상태

• Non-Repeatable Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 그 사이 다른 트랜잭션이 값을 수정 또는 삭제하는 바람에 두 쿼리 결과가 다르게 나타나는 현상

• Phantom Read : 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에서 없던 유령 레코드가 두 번째 쿼리에서 나타나는 현상

트랜잭션 격리성 수준

• Read Uncommitted : 트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽는 것 허용
• Read Committed : 트랜잭션이 커밋돼 확정된 데이터만 다른 트랜잭션이 읽도록 허용함으로써 Dirty Read 방지
  => Non-Repeatable Read와 Phantom Read 현상은 못막음
• Repeatable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌는 현상을 방지
  => Phantom Read 현상 못막음
• Serializable Read : 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 레코드가 사라지거나 값이 바뀌지 않고 새로운 레코드가 나타나지 않음

다중 트랜잭션 환경에서 DBMS가 제공하는 기능을 이용해 동시성을 제어하려면 트랜잭션 시작 전에 명시적으로 Set Transaction 명령어를 수행하기만 하면 됨

-- serializable Read로 상향 조정
set transaction isolation level read serializable;

Repeatable Read나 Serializable Read로 올리면, 이를 구현하기 위해 Locking 메커니즘에 의존
=> 공유 Lock을 트랜잭션이 끝날때 까지 유지

다중버전 동시성 제어(Multiversion Concurrency Control) : 진행 중인 트랜잭션에 의해 변경된 데이터를 읽고자 할 때는 변경 이전 상태로 되돌린 버전을 읽는 것
=> 변경이 아직 확정되지 않은 값을 읽으려는 것이 아니므로 공유 Lock 설정 X
=> 읽는 세션과 변경하는 세션이 서로 간섭현상 일으키지 X

---

🧁동시성 제어

동시성 제어 : 동시에 작동하는 다중 트랜잭션의 상호 간섭 작용에서 데이터베이스를 보호하는 것

동시성과 일관성은 트레이드 오프 관계
=> 동시성을 높이려고 Lock 사용 최소화하면 일관성 유지 어려움
=> 일관성을 높이려고 Lock 적극적으로 사용하면 동시성 저하

1. 비관적 동시성 제어 vs. 낙관적 동시성 제어

비관적 동시성 제어

비관적 동시성 제어(Pessimistic Concurrency Control) : 사용자들이 같은 데이터를 동시에 수정할 것이라고 가정
=> 데이터를 읽는 시점에 Lock을 걸고 트랜잭션이 완료될 때 까지 유지

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

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

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

select 시점에 Lock을 거는 것은 동시성을 떨어뜨릴 수 있으므로 wait 또는 nowait 옵션을 함께 사용

for update nowait -- 대기없이 Exception을 던짐 
for update wait 3 -- 3초 대기 후 Exception을 던짐

SQL Server에서는 명시적으로 cursor를 선언할 때만 for update 절이 가능하므로 holdlock이나 updlock힌트를 사용하는 것이 편리

낙관적 동시성 제어

낙관적 동시성 제어(Optimistic Concurrency Control) : 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정
=> 데이터 읽을 때 Lock 설정X
=> 수정 시점에 다른 사용자에 의해 값이 변경됐는지 반드시 검사

select 적립포인트, 방문횟수, 최근방문일시, 구매실적 into :a, :b, :c, :d 
from 고객 
where 고객번호 = :cust_num; 

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

update 고객 set 적립포인트 = :적립포인트 
where 고객번호 = :cust_num 
and 적립포인트 = :a 
and 방문횟수 = :b 
and 최근방문일시 = :c 
and 구매실적 = :d ; 

if sql%rowcount = 0 then 
	alert('다른 사용자에 의해 변경되었습니다.'); 
end if;

2. 다중버전 동시성 제어

일반적인 Locking 메커니즘의 문제점

읽기 작업에 공유 Lock을 사용하는 Locking 메커니즘에서는 읽기 작업과 쓰기 작업이 서로 방해를 일으켜 동시성에 문제

잔고 총합을 구하는 아래 쿼리가 TX1 트랜잭션에서 수행

TX1> select sum(잔고) from 계좌 ;

계좌이체를 처리하는 TX2 트랜잭션도 작업 시작

TX2> update 계좌 set 잔고 = 잔고 + 100 where 계좌번호 = 7; -- 1 
TX2> update 계좌 set 잔고 = 잔고 - 100 where 계좌번호 = 3; -- 2 
TX2> commit;

• TX1 : 2번 계좌까지 읽는다. 현재 잔고 총합은 2000원
• TX2 : 1번 Update를 실행한다. 7번 계좌 잔고는 1100원이 됐고, 아직 커밋 X
• TX1 : 6번 계좌까지 읽어 내려간다. 현재까지 잔고 총합은 5000원이다.
• TX2 : 2번 Update를 실행함으로써 3번 계좌는 900원, 7번 계좌는 1100원인 상태에서 커밋
• TX1 : 10번 계좌까지 읽어 내려간다. 7번 계좌 잔고를 1100원으로 바꾼 TX2 트랜잭션이 커밋됐으므로 이 값을 읽어서 구한 잔고 총합은 10100원이 된다.

트랜잭션 격리성 수준을 상향 조정하면 일관성이 높아지지만 동시성을 저하시키고 교착상태가 발생할 가능성이 높다.
=> 트랜잭션 격리성 수준을 Repeatable Read로 설정하면, TX2가 1번 Update를 통해 7번 레코드에 배타적 Lock을 설정하고, TX1은 3번 레코드에 공유 Lock을 설정하면, TX2는 2번 update를 실행하는 단계에서 3번 레코드에 걸린 공유 Lock을 대기하고, TX1은 7번 레코드를 읽으려는 순간 영원히 Lock이 풀릴 수 없는 교착상태에 빠짐

다중버전 동시성 제어

다중버전 동시성 제어(Multiversion Concurrency Control, MVCC) : 동시성과 일관성을 동시에 높이는 모델

MVCC 메커니즘

• 데이터를 변경할 때마다 그 변경사항을 Undo 영역에 저장
• 데이터를 읽다가 쿼리(또는 트랜잭션) 시작 시점 이후에 변경된(변경 진행중 또는 커밋된)값을 발견하면 Undo 영역에 저장된 정보를 이용해 쿼리 시작 시점의 일관성 있는 버전(CR copy)을 생성하고 그것을 읽는다

쿼리 도중에 배타적 Lock이 걸린 레코드를 만나더라도 대기X -> 동시성 측면 유리
사용자에게 제공되는 데이터의 기준 시점이 쿼리 시작 시점으로 고정 -> 일관성 측면 유리

MVCC를 이용한 읽기 일관성에는 문장 수준과 트랜잭션 수준 2가지가 있음

문장수준 읽기 일관성

문장수준 읽기 일관성(Statement-Level Read Consistency) : 다른 트랜잭션에 의해 데이터의 추가, 변경, 삭제가 발생하더라도 단일 SQL문 내에서 일관성 있게 값을 읽는 것
=> 일관성 기준 시점 : 쿼리 시작 시점

쿼리가 10023 시점 이후 변경된 데이터 블록을 만났을 때 Undo 세그먼트에 저장된 정보를 이용해 10023 이전 시점으로 되돌리고서 값을 읽음

--sql server에서 문장수준 읽기 일관성 모드로 DB 운영
alter database <데이터베이스 이름> set read_committed_snapshot on;

트랜잭션 수준 읽기 일관성

트랜잭션 수준 읽기 일관성(Transaction-Level Read Consistency) : 다른 트랜잭션에 의해 데이터가 추가, 변경, 삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것
=> 격리성 수준을 Serializable Read로 올려야 함

--sql server에서 트랜잭션 읽기 일관성 모드로 DB 운영
alter database <데이터베이스 이름> set allow_snapshot_isolation on;

--트랜잭션 시작 전 트랜잭션 격리성 수준을 sanpshot으로 변경
set transaction isolation level snapshot 

begin tran 
  select ... ; 
  update ... ; 
commit;

Snapshot too old

Snapshot too old : Undo 영역에 저장된 Undo 정보가 다른 트랜잭션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생

Snapshot too old 에러 발생 가능성 줄이는 방법

• Undo 영역의 크기 증가
• 불필요하게 자주 커밋 수행X
• fetch across commit 형태의 프로그램 작성을 피해 다른 방식으로 구현
  => 커밋 이전에 열려 있던 커서는 더는 Fetch하면 안됨. 또는 커밋 횟수 줄임
• 트랜잭션이 몰리는 시간대에 오래 걸리는 쿼리가 같이 수행되지 않도록 시간 조정
• 큰 테이블을 일정 범위로 나누어 읽고 단계적으로 실행할 수 있도록 코딩
  => 일관성에 문제 없을 때만
• 오랜 시간에 걸쳐 같은 블록을 여러 번 방문하는 Nested Loop 조인문 또는 인덱스를 경유한 테이블 액세스를 수반하는 프로그램이 있는지 확인하고, 회피할 수 있는 방법 찾는다
• 소트 부하를 감수하더라도 order by 등을 강제로 삽입해 소트연산이 발생하도록 한다
• 대량 업데이트 후에 곧바로 해당 테이블 또는 인덱스를 Full Scan하도록 쿼리를 수행

select /*+ full(t) */ count(*) from table_name t

select count(*) from table_name where index_column > 0