[DB] Lock, Oracle Lock

DataBase는 데이터를 영속적으로 저장하고 있는 시스템입니다. 이런 시스템은 같은 자원(데이터)에 대해서 동시에 접근하는 경우가 생길 수 밖에 없습니다. 이럴 경우 데이터가 오염 될 수 있는데 그렇게 되지 않도록 데이터의 일관성과 무결성을 유지해야할 필요가 있습니다.

 

Lock

Lock이란 트랜잭션 처리의 순차성을 보장하기 위한 방법입니다. 트랜잭션이란 DB의 나누어지지 않는 최소한의 처리 단위입니다. 

그리고 중요한 것은 DBMS마다 Lock을 구현하는 방식과 세부적인 방법이 다릅니다. 따라서 DBMS를 효과적으로 이용하기 위해서는 해당 DB의 Lock에 대한 이해가 요구됩니다.

 

Lock의 종류

Lock의 종류로는 공유(Shared) Lock과 베타(Exclusive) Lock이 있습니다. 공유락은 다른 말로 Read Lock이라고 불리며 베타락은 Write Lock이라고도 불립니다.

 

공유(Shared) Lock (읽기 락)

공유 Lock은 데이터를 읽을 때 사용되어지는 Lock입니다. 이런 공유 Lock은 공유 Lock 끼리는 동시에 접근이 가능합니다. 즉, 하나의 데이터를 읽는 것은 여러 사용자가 동시에 할 수 있다라는 것입니다. 하지만 공유 Lock이 설정된 데이터에 베타 Lock을 사용할 수는 없습니다.

 

베타(Exclusive) Lock (쓰기락)

베타 Lock은 데이터를 변경하고자 할 때 사용되며, 트랜잭션이 완료될 때까지 유지됩니다. 베타락은 Lock이 해제될 때까지 다른 트랜잭션(읽기 포함)은 해당 리소스에 접근할 수 없습니다. 또한 해당 Lock은 다른 트랜잭션이 수행되고 있는 데이터에 대해서는 접근하여 함께 Lock을 설정할 수 없습니다.

 

Lock의 설정 범위(Level)

Lock의 설정 범위(Level)에 대해서 알아보도록 하겠습니다.

 

• [데이터베이스]
  데이터베이스 범위의 lock은 전체 데이터베이스를 기준으로 lock 하는 것입니다. 즉, 1개의 세션만이 DB의 데이터에 접근이 가능합니다. 해당 기능은 일반적으로는 사용하지 않습니다. 사용하는 때가 있다면 DB의 소프트웨어 버전을 올린다던지 주요한 DB의 업데이트에 사용합니다.
  
  
•  파일
  데이터베이스 파일을 기준으로 lock을 설정합니다. 파일 이란 테이블, row 등과 같은 실제 데이터가 쓰여지는 물리적인 저장소 입니다. 해당 범위의 Lock은 잘 사용되지는 않습니다.
  
  
• [테이블]
  테이블 수준의 Lock은 테이블을 기준으로 Lock을 설정합니다. 이는 테이블의 모든 행을 업데이트 하는 등의 전체 테이블에 영향을 주는 변경을 수행할 때 유용합니다. 즉, DDL(create, alter, drop 등) 구문과 함께 사용되며 DDL Lock이라고도 합니다.

• 페이지와 블럭
  파일의 일부인 페이지와 블록을 기준으로 Lock을 설정합니다. 잘 사용되지는 않습니다.
  
  
•  컬럼
  컬럼 기준의 Lock은 컬럼을 기준으로 Lock을 설정할 수 있습니다. 하지만 이 형식은 Lock 설정 및 해제의 리소스가 많이 들기 때문에 일반적으로 사용되지는 않습니다. 지원하는 DBMS도 많지 않습니다.
  
  
• [행(Row)]
  행 수준의 Lock은 1개의 행(Row)를 기준으로 Lock 설정을 합니다. DML에 대한 Lock으로 가장 일반적으로 사용하는 Lock입니다.

 

 

블로킹(Blocking)

블로킹은 Lock간(베타 - 베타, 베타 - 공유)의 경합이 발생하여 특정 Transaction이 작업을 진행하지 못하고 멈춰선 상태를 말합니다. 위에 설명했듯이 공유락 끼리는 블로킹이 발생하지 않지만 베타락은 블로킹을 발생시킵니다. 블로킹을 해소하기 위해서는 이전의 트랜잭션이 완료(커밋 OR 롤백)되어야 합니다. 뒤에 들어온 트랜잭션은 이전 트랜잭션이 마무리되어야 이후 진행이 가능합니다. 이런 경합은 성능에 좋지 않은 영향을 미칩니다. 따라서 경합을 최소화 할 필요가 있습니다.

blocking

DB를 사용하는 프로그래밍을 진행하면서 몇가지 주의사항을 알아보도록 하겠습니다.

1. 한 트랜잭션의 길이를 너무 길게하는 것은 경합의 확률을 올립니다.
2. 처음부터 설계할 때 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않도록 해야합니다.
3. 트랜잭션 격리성 수준을 불필요하게 상향 조정하지 않습니다.
4. 쿼리를 오랜시간 잡아두지 않도록 적절한 튜닝을 진행합니다.

이외에 DBMS에 따라서 lock 대기 시간 등을 설정할 수 있습니다.

 

오라클 blocking대처방안

예를 들어, SELECT 문에서 사용되는 조인 등에 따라 잠금 대기 시간이 발생할 수 있는데, 이 경우 인덱스를 생성하여 데이터 접근 시간을 단축할 수 있습니다. 또한, FOR UPDATE 절을 사용하여 잠금의 범위를 최소화하고, 최대한 빨리 잠금을 해제하여 Blocking을 최소화할 수 있습니다

세션 1에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금 설정

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 100
FOR UPDATE;

 

세션 2에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금 설정

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 100
FOR UPDATE;

위 예제에서는 두 개의 세션에서 모두 EMPLOYEE 테이블에서 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금을 설정하려고 하고 있습니다. 첫 번째 세션에서 해당 행에 대한 배타적 잠금을 설정했기 때문에 두 번째 세션에서 해당 행에 대한 배타적 잠금을 설정하지 못하고 대기 상태로 머물러 있게 됩니다. 이렇게 Blocking이 발생하면, 두 번째 세션에서는 해당 행에 대한 작업을 수행하지 못하고 대기하게 됩니다.

 

 

 

교착상태(DeadLock)

 교착상태는 두 트랜잭션이 각각 Lock을 설정하고 다음 서로의 Lock에 접근하여 값을 얻어오려고 할 때 이미 각각의 트랜잭션에 의해 Lock이 설정되어 있기 때문에 양쪽 트랜잭션 모두 영원히 처리가 되지않게 되는 상태를 말합니다. 예를 들어 보면, game_master, game_detail 테이블이 있습니다. 트랜잭션 A가 game_master 테이블에 5번 Row를 수정했고 이제 game_detail 테이블에 5번 Row를 이어서 수정하려고 합니다. 동시에 트랜잭션 B는 game_detail 테이블의 5번 Row를 수정하고 이어서 game_master 테이블의 5번 Row를 수정하려고 합니다. 이 경우 트랜잭션 A는 game_master 테이블의 5번 Row에 배타 락을 설정했고 트랜잭션 B는 game_detail 테이블의 5번 Row에 배타 락을 설정하였습니다. 그리고 교차로 트랜잭션 A는 game_detail의 5번 row의 Lock 설정을 하려고 하고 트랜잭션 B는 game_master의 5번 row에 Lock 설정을 하려고 합니다. 하지만 이미 각 row들은 서로다른 트랜잭션에 의해서 배타락 설정이 되어있습니다. 따라서 Lock이 해제되기를 서로 기다립니다. 하지만 이 Lock은 풀리지 않을 서로의 트랜잭션 기다리므로 영원히 풀리지 않을것입니다.

이미지로 나타내면 아래와 같습니다.

deadlock

그래서 교착상태가 발생하면 DBMS가 둘 중 한 트랜잭션에 에러를 발생시킴으로써 문제를 해결합니다. 교착상태가 발생할 가능성을 줄이기 위해서는 접근 순서를 동일하게 하는것이 중요합니다. 즉, 위의 예제라면 프로그래밍을 할 때 game_master를 업데이트 한 후 game_detail을 업데이트 한다와 같은 규칙을 정해 테이블 접근의 교차가 일어나지 않도록 하는것이 중요할 것입니다.

 

세션 1에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금 설정

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 100
FOR UPDATE;

세션 2에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 200인 행에 대한 배타적 잠금 설정

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 200
FOR UPDATE;

세션 1에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 200인 행에 대한 배타적 잠금 설정을 시도

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 200
FOR UPDATE;

세션 2에서 아래 쿼리 실행하여 EMPLOYEE 테이블에서 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금 설정을 시도

SELECT *
FROM EMPLOYEE
WHERE EMPLOYEE_ID = 100
FOR UPDATE;

 

위 예제에서는 두 개의 세션에서 각각 EMPLOYEE 테이블에서 다른 두 개의 행에 대한 배타적 잠금을 설정하려고 하고 있습니다. 하지만, 세션 1에서는 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금을 설정하고, 이후 EMPLOYEE_ID가 200인 행에 대한 배타적 잠금을 시도합니다. 반면에 세션 2에서는 EMPLOYEE_ID가 200인 행에 대한 배타적 잠금을 설정하고, 이후 EMPLOYEE_ID가 100인 행에 대한 배타적 잠금을 시도합니다.

이렇게 되면, 두 세션 모두 서로가 점유한 잠금을 해제하지 못하고 대기 상태에 머무르게 됩니다. 이러한 상황에서 더 이상 작업을 수행할 수 없으므로 Deadlock 상태가 발생하게 됩니다.

---

 

Oracle Lock

 

Oracle 데이터베이스에서는 다음과 같은 isolation lock 모드를 지원합니다.

 

• Shared lock (S) : 다른 사용자가 공유 잠금을 보유하고 있으면 자원을 읽을 수 있지만, 자원을 수정하거나 삭제할 수 없습니다.
• Exclusive lock (X) : 다른 사용자가 자원에 대한 잠금을 보유하고 있지 않은 경우 자원에 대한 전체적인 제어를 얻을 수 있으며, 자원을 수정하거나 삭제할 수 있습니다.
• Row share lock (RS) : 특정 행에 대한 잠금을 설정하며, 잠금을 보유하고 있는 동안 다른 사용자는 같은 행에 대한 공유 잠금만 보유할 수 있습니다.
• Row exclusive lock (RX) : 특정 행에 대한 전체적인 제어를 얻으며, 자원을 수정하거나 삭제할 수 있습니다. 다른 사용자는 같은 행에 대한 잠금을 보유하지 못하며, 다른 행에 대한 잠금은 보유할 수 있습니다.
  (UPDATE 기본, DELETE 기본)
• Share table lock (SS) : 테이블에 대한 잠금을 설정하며, 다른 사용자는 동일한 테이블에 대한 공유 잠금만 보유할 수 있습니다.
• Share update lock (SU) : 공유 잠금을 설정하고 다른 사용자가 업데이트 또는 삭제하려는 자원이 이미 잠금을 보유하고 있으면 자원을 업데이트하거나 삭제할 수 있습니다.

이러한 잠금 모드를 사용하여 동시성을 관리하고 데이터 무결성을 보호할 수 있습니다. 이 중 어떤 모드를 사용할지는 데이터베이스 사용자의 요구사항에 따라 결정됩니다.

 

Oracle 데이터베이스에서는 SELECT, INSERT, UPDATE, DELETE 등의 DML 작업을 수행할 때 잠금을 설정할 수 있습니다.

예를 들어, 다음과 같은 SQL 문은 employees 테이블에서 department_id가 50인 행을 선택하고 업데이트할 때 FOR UPDATE 잠금 모드를 설정합니다.

 

SELECT * FROM employees WHERE department_id = 50 FOR UPDATE;

 

위의 SQL 문에서 FOR UPDATE 절은 선택된 행에 대한 배타적 잠금 모드를 설정합니다. 즉, 이 SQL 문이 실행된 후 다른 사용자는 선택된 행을 읽거나 수정할 수 없습니다.

 

• FOR SHARE 절은 공유 잠금 모드를 설정합니다.
• FOR UPDATE절은 베타 잠금 모드를 설정합니다.
• FOR NO KEY UPDATE 절은 공유 잠금을 설정하지만 해당 행에 대한 수정은 허용합니다.
• FOR UPDATE NOWAIT 절은 배타적 잠금 모드를 설정하지만, 다른 사용자가 해당 행을 보유하고 있을 때 바로 오류를 반환합니다.

 

 

 

참조

https://sabarada.tistory.com/121