DB lock

session이나 connection 과 DB lock 간의 관계

A session can acquire or release locks only for itself. One session cannot acquire locks for another session or release locks held by another session.

세선은 그 자체만으로 lock을 잡거나 풀 수 있다. 단 다른 세션을 위해 락을 잡거나 해제하는 것은 못한다.

명시적으로 해제하지 않더라도, 모든 락은 세션이 끝날 때 자동적으로 해제된다.

Concurrency Control

DB에서의 lock은 Concurrency Control 을 하기 위한 메커니즘이다.

• 배타 모드와 공유 모드가 있다. 배타 모드는 write 할 때, 공유 모드는 read 할 때 잡는다
• Lock-based protocol
  • conflict serializability를 보장하지 않는다.
  • Lock-based protocol의 단점
    • Deadlock: 두 개의 트랜잭션이 서로 lock release 하기를 기다린다
    • Starvation: 계속 하나의 트랜잭션만 lock을 잡는 현상
• Two-Phase locking protocol (2PL)

• • conflict serializability를 보장한다
  • Growing phase와 Shrinking phase 두 가지 phase를 갖는다
    • Growing phase (phase1)
      • 트랜잭션은 lock을 가질 수만 있다
    • Shrinking phase (phase2)
      • 트랜잭션은 lock을 해제할 수만 있다
  • 단점
    • deadlock
    • cascading rollback
  • 보완한 버전
    • strict 2PL: commit이나 abort 전까지 모든 배타 락을 잡고 있어야 한다
    • rigorous 2PL: strict 보다 더 엄격. 배타 락 뿐만 아니라 공유 락까지 hold하고 있어야 한다
• Lock conversion(2PL with lock conversion)
  • 기존의 2PL에 lock conversion을 추가한 것. 다음 두 가지 phase 로 구성된다
  • First Phase
    • lock-S, lock-X 를 가질 수 있다
    • lock-S를 lock-X로 convert 할 수 있다 (upgrade)
  • Second Phase
    • lock-S, lock-X 를 해제할 수 있다
    • lock-X를 lock-S로 convert 할 수 있다 (downgrade)lock conversion 으로 인해 2PL보다 더 concurrency를 보장한다.기존의 2PL으로 보면, T8에 write(a1)이 있기 때문에 T8에서 a1에 대한 락이 무조건 lock-X가 되어야 한다. 따라서 T8과 T9가 concurrent하기 어렵다. 하지만 lock conversion의 경우 upgrade(a1)가 있어서 concurrent 할 수 있게 해준다.
• 다음의 예를 보면, 맨 왼쪽을 2PL 과 lock conversion 으로 구현한 것이다.
  
• 💡 [https://otugi.tistory.com/222] 이어서 더 정리하기

Optimistic Concurrency

https://stackoverflow.com/questions/19786003/locking-database-record-for-editing

→ Pessimistic Concurrency 는 절대 안돼!! 확장성도 없고 그냥.. 안 좋아. 쓰지마!

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency?redirectedfrom=MSDN

multiuser 환경에서 DB 데이터를 갱신하는데 두 가지 모델이 있다: Optimistic Concurrency와 Pessimistic Concurrency 이다.

그 중 Optimistic Concurrency 은 long-running activities에 적합하다.

Pessimistic 모델은 현재 유저가 락을 잡는 동안 다른 유저가 락을 잡지 못하도록 막는 것이다. 이 모델은 주로 데이터를 여러 유저가 서로 잡으려하는 환경에서 사용된다. 따라서 동시성 충돌이 발생하여 롤백을 하는 것보다 락으로 데이터를 보호하는 비용이 더 적은 경우이다.

그러므로, Pessimistic 모델은 수정중인 유저가 락을 풀 때까지 아무도 변경하지 못한다. 이러한 이유로, lock time이 짧은 경우에 사용하는 것이 좋다. 반대로 긴 시간동안 record를 변경하거나 데이터로 상호작용하는 상황에서는 좋지 못하다. 또한 한 번에 여러 개의 row를 update해야 한다면, pessimistic locking을 사용하는 것보다 트랜잭션을 만드는 것이 더 확장성 있는 방법일 수 있다.

아무런 lock도 잡지 않는 모델?

반면에, optimistic 모델을 사용하면 읽는 동안 row를 lock할 수 없다. 애플리케이션은 유저가 row를 읽은 후 해당 row를 갱신할 때 다른 유저가 같은 row를 변경하는 경우 어떻게 할 지 결정해야 한다. optimistic 는 같은 데이터를 동시에 접근하는 경우가 적은 경우에 적합하다. optimistic 는 record에 대한 lock을 잡지 않아도 되므로 성능을 향상시킬 수 있다.

예를 들어,

1. A 유저가 "Bob"이라는 데이터를 읽었다.
2. B 유저가 "Bob"이라는 데이터를 읽었다.
3. B 유저가 "Robert"로 갱신하였다.
4. A 유저가 "James"로 갱신하려고 하는데 데이터가 "Bob"이 아니라 "Robert"로 변경되어 있다.

→ 여기서 애플리케이션은 이를 overwrite 할 지 B 유저의 변경 사항을 적용할 지 결정해야 한다.

그렇다면 Optimistic Concurrency violation이 일어나는 경우는?

1. row가 update되는 timestamp 를 계속 기록한다고 해보자. update 될 때 수정되는 시각과 original timestamp를 비교하여 일치하지 확인
2. update 하기 전에 처음 읽을 때의 row 값들과 현재 값이 일치하는지 확인

UPDATE Table1 Set Col1 = @NewCol1Value,  
              Set Col2 = @NewCol2Value,  
              Set Col3 = @NewCol3Value  
WHERE Col1 = @OldCol1Value AND  
      Col2 = @OldCol2Value AND  
      Col3 = @OldCol3Value

공유 Lock과 배타적 Lock

1. 공유 Lock동시에 읽을 수는 있지만 배타적 lock(CUD)이 걸려있는 중에는 잡을 수 없다.
2. select 시 사용.
3. 배타적 Lock다른 트랜잭션이면 공유든 배타적 Lock이든 잡을 수 없다.
4. 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때까지 유지된다.

Blocking과 DeadLock

1. Blocking둘 중 하나라도 배타 Lock이면 발생할 수 있다.
   1. 트랜잭션을 가능한 짧게 정의. Oracle은 select 시 공유 Lock을 사용하지 않는다.
   2. 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 설계
      1. url pull 서비스 (트랜잭션이 활발한 때에 대용량 갱신 작업 수행하면 안 된다)
      2. ical update
   3. LOCK_TIMEOUT 같은 설정값 지정
   4. 쿼리도 빠르게, 트랜잭션도 빠르게
2. 최대한 방지하는 방법
3. Lock 경합으로 인해 멈춘 상태.
4. DeadLock방지하는 방법
   1. 테이블 접근 순서를 정하고 같게 처리
5. 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주보며 진행하는 상황.

Lock의 설정 범위

• 데이터베이스
  • 전체 데이터베이스를 기준으로 lock하는 것
  • DB의 버전을 올린다던지 주요한 DB의 업데이트에 사용
• 파일
  • 데이터베이스 파일을 기준으로 lock 설정. 즉 실제 물리적인 저장소
  • 잘 사용 안함
• 테이블
  • 전체 테이블에 영향을 주는 변경 시
  • DDL 구문과 함께 사용되며 DDL Lock이라고도 한다
• 페이지와 블럭
  • 파일의 일부인 페이지와 블록을 기준으로 Lock (파일 시스템)
  • 잘 사용 안 함
• 컬럼
  • 컬럼
  • Lock 설정 및 해제의 리소스가 많이 들어서 잘 사용하지 않음
• 행 (row)
  • 1개의 행
  • DML에 대한 Lock. 가장 일반적

티베로에서의 Lock 확인

SELECT * FROM dict where table_name like '%LOCK%'; --lock tabel 정의

SELECT * FROM V$BLOCKER_SESSION; -- BLOCKED SESSION 조회

SELECT * FROM V$LOCK WHERE TYPE = 'WLOCK_TX'
--AND LMODE = 0 --V$BLOCKER_SESSION 에서 조회되는 LOCK
--AND REQUESTED == 0; --현재 요청 대기중인 세션

현재 진행중인 트랜잭션 확인

select distinct vs.sid, --SESSION ID
vs.serial#,
vs.username, --트랜잭션 수행중인 세션의 사용자
vs.username || '.' || va.object "Object", --현재 동작중인 트랜잭션에서 사용중인 Table의 목록
vs.status,
vt.used_blk, --현재 사용중인 Block의 개수
vt.usn ,
vt.start_time,
floor(mod((sysdate - vt.start_time)*24, 24)) || ':'||
lpad(floor(mod((sysdate - vt.start_time)*1440, 60)), 2, 0) ||':'||
lpad(floor(mod((sysdate - vt.start_time)*86400, 60)), 2, 0) AS "Transaction Time", 
	-- 트랜잭션이 진행된 시간
vst.sql_text
FROM v$session vs,
vt_transaction vt,
v$sqltext vst,
v$access va
WHERE vt.sess_id = vs.sid
and vt.sess_id = va.sid
and nvl(vs.sql_id, vs.prev_sql_id) = vst.sql_id;

WLOCK 정보(for 세션)

SELECT S.USER_NAME "USERNAME",
 S.SESS_ID || ',' || S.SERIAL_NO "SESSION",
 O.OWNER|| '.' ||O.OBJECT_NAME "OBJECT", -- DML Lock이 걸려있는(또는 요청하고 있는) Schema 오브젝트
 S.STATUS "STATUS",
 --S.TYPE, S.LOGON_TIME, S.VTR_TID, L.TYPE,
 FLOOR(MOD((SYSDATE - VT.START_TIME)*24, 24)) || ':'||
 LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*1440, 60)), 2, 0) ||':'||
 LPAD(FLOOR(MOD((SYSDATE - VT.START_TIME)*86400, 60)), 2, 0)
 AS "TRANSACTION TIME",
 DECODE(L.LMODE, 0,'[0]NONE ', 1,'[1]NULL', 2, '[2]ROW-S (SS)',3,'[3]ROW-X (SX)'
 , 4, '[4]SHARE', 5, '[5]S/ROW-X (SSX)'
 , 6, '[6]EXCLUSIVE', TO_CHAR(L.LMODE) ) "LOCK MODE",
	-- wlock owner의 모드. (RS=1, RX=2, S=3, SRX=4, X=5)
 L.STATUS "STATE",
 --L.ID1, L.ID2,
 NVL(S.SQL_ID, S.PREV_SQL_ID) SQL_ID
FROM VT_WLOCK L,
 VT_SESSION S,
 DBA_OBJECTS O ,
 VT_TRANSACTION VT
WHERE L.LMODE =2 -- Grater than 1
 AND L.THR_ID = S.VTR_TID
 AND L.ID1 = O.OBJECT_ID (+)
 AND L.THR_ID = VT.SESS_ID ;

Blocking하고 있는 Lock & Waiting하고 있는 Lock

SELECT bs.user_name "Blocking User" ,
ws.user_name "Waiting User" ,
bs.sess_id "Blocking Sid" ,
ws.sess_id "Waiting Sid" ,
wk.type "Lock Type" , -- 기다리고 있는 wlock의 타입
hk.lmode "Holding mode" ,
wk.lmode "Request mode" ,
nvl(bs.sql_id, bs.prev_sql_id) "Sql_id"
FROM vt_wlock hk,
vt_session bs,
vt_wlock wk,
vt_session ws
WHERE wk.status = 'WAITER'
and hk.status = 'OWNER'
제4장 Lock & Current 트랜잭션 43
and hk.lmode > 1
and wk.type = hk.type
and wk.id1 = hk.id1
and wk.id2 = hk.id2
and wk.thr_id = ws.vtr_tid
and hk.thr_id = bs.vtr_tid
ORDER BY 1;

update 쿼리에 subquery로 select 가 있다면?

이 쿼리문은 subquery로부터 반환될 때 update table에서 row lock을 잡을 것이다.

처음부터 전체 테이블에 공유 lock만 잡힐 것이다. (당신이 사용하는 동안 그 테이블을 수정하지 못하도록).

만약 subquery가 group by, order by 혹은 실행되려면 모든 row가 반환되어야 하는 조항을 포함한다면 row lock이 연속적으로 발생할 것이다. 그렇지 않으면 더 오랜 기간 발생할 것이다.

TX Lock이란?

• 트랜잭션 Lock
• row level lock에 의한 경합 발생 상황
• 트랜잭션이 첫 번째 변경을 시작할 때 획득하며, 커밋 또는 롤백할 때 해제한다.
• TX-row lock 경합이 발생하는 Case
  • 여러 세션이 동일 Unique Key 충돌을 일으키는 경우
  • 여러 세션이 비트맵 인덱스 충돌을 일으키는 경우
  • 여러 세션이 동일 로우를 변경하는 경우
• Lock 모드간 호환성(Compatibility)
  • 로우 Lock은 항상 Exclusive 모드이지만, 테이블 Lock에는 여러 가지 Lock 모드가 가용됨
  
  • 표시는 두 모드 간에 호환성이 있음을 의미
  • RS : Row Share(또는 SS : sub share)
    • select for update 문을 위해 로우 Lock을 설정하려면 먼저 획득해야 함
  • RX : Row Exclusive(또는 SX : sub exclusive)
    • insert, update, delete, merge 문을 위해 로우 Lock을 설정하려면 먼저 획득해야 함
  • S : Share
  • SRX : share row exclusive(또는 SSX : share/sub exclusive)
  • X : exclusive
  • 선행 트랜젝션과 호환되지 않는 모드로 테이블 Lock을 설정하려는 후행 트랜젝션은 대기하거나 작업을 포기해야 한다
  • RS, RX 간에는 어떤 조합으로도 호환이 되므로 select for update나 DML문 수행시 이들간에 테이블 Lock에 의한 경합은 절대 발생하지 않는다
    • 다만, 같은 로우를 갱신하려 할 때 로우 Lock에 의한 경합은 발생