SQLP/SQL 전문가 가이드

Lock과 트랜잭션 동시성 제어

개발자 키우기 2024. 1. 1. 12:26

1. Lock

  • 같은 자원을 액세스 하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하면서 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화 장치가 필요한데 그중 자원에 대한 접근을 동기화하고 제어하기 위한 메커니즘을 Lock이라 한다.
  • 공유 Lock은 여러 트랜잭션이 동시에 데이터를 읽을 수 있지만, 변경은 불가능한 락이다. 배타적 Lock은 하나의 트랜잭션이 데이터를 수정하는 동안 다른 트랜잭션들이 해당 데이터에 접근하지 못하게 하는 락이다.
  • 블로킹은 Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈취 선 상태를 말한다. 블로킹 상태를 해소하는 방법은 커밋이나 롤백뿐이기 때문에 블로킹으로 인한 성능저하를 최소화하기 위해 다음과 같은 방안을 고려할 수 있다. 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션을 가능하면 짧게 정의한다. 같은 데이터를 갱신하는 트랜잭션이 동시에 수행되지 않게 설계하고 블로킹 현상에 대처하기 위해 SQL Server는 세션 레밸에 LOCK_TIMEOUT을 설정할 수 있고 오라클에서는 update/delete 문장을 수행하기 전에 nowait나 wait 옵션을 사용하여 예외사항에 따라 프로그래밍을 설정할 수 있다. 또한 트랜잭션을 잘 설계하고 SQL을 잘 작성해 Lock 시간을 줄이고 트랜잭션 격리성 수준을 낮추는 것도 방안이 될 수 있다.
  • 교착상태는 두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 서로 대기하는 상황으로 둘 중 하나가 풀려야 문제가 해결된다. 교착상태를 풀기 위해서는 블로킹을 해결하기 위한 방법과 유사하며 추가적으로 테이블 접근 순서를 같게 처리하면 대부분 문제가 해결된다.

2. SQL Server Lock

  • 공유 Lock은 격리성 수준이 Read Committed일때 레코드 단위로 락이 걸린다. 격리성 수준이 더 높아지면 트랜잭션이나 쿼리 수행을 기준으로 락이 걸린다. 격리성 수준을 변경하지 않고 트랜잭션 내에서 공유 Lock을 유지하려면 wit (holdlock) 힌트를 사용하면 된다. 
  • 배타적 Lock은 동일하다.
  • 갱신 Lock은 배타적 Lock 끼리의 교착상태를 방지하기 위해 update문을 사용할 때 사용하는 Lock이다.
  • 의도 Lock은 트랜잭션이 자원에 대한 락을 설정하기 전에 해당 자원에 대한 의도를 미리 알림으로써 다른 트랜잭션들에게 어떤 종류의 락이 예정되어 있음을 알려주는 락으로 충돌의 방지하고 동시성을 관리하는데 사용된다.
  • 스키마 Lock은 테이블 스키마에 의존적인 작업을 수행할 때 사용되며 Sch-S는 SQL을 컴파일하면서 오프젝트 스키마를 참조할 때 발생하며, 읽는 스키마 정보를 수정하거나 삭제하지 못하게 한다. Sch-M은 테이블 구조를 변경하는 DDL 문을 수행할 때 발생하며, 수정 중인 스키마 정보를 다른 세션이 참조하지 못하게 한다.
  • Bulk Update Lock은 테이블에 데이터를 Bulk Copy할 때 발생하며 병렬 데이터 로딩은 허용하지만 일반적인 트랜잭션 작업은 허용하지 않는다.
  • Lock 레벨은 로우, 페이지, 익스텐트, 인덱스 키, 테이블, 데이터베이스 레벨이 있다. 만약 Lock 리소스가 정해진 임계치를 넘으면 Lock 매니저를 통해 락의 레벨을 점점 확장해 나간다.

3. 오라클 Lock

  • DML Lock, DDL Lock, 래치, 버퍼 Lock, 라이브러리 캐시 Lock/Pin 등 다양한 Lock을 사용한다. 이 중에서 DML Lock은 다중 사용자에 의해 동시에 액세스 되는 사용자 데이터 무결성을 보호해 주는 락으로 애플리케이션 개발 측면에서 가장 중요하게 다루어야 하는 락이다. 또한 오라클은 Undo 데이터를 이용한 다중버전 동시성 제어 메커니즘을 사용하기 때문에 select 문의 읽힌 레코드에 어떤 Lock도 설정되지 않는다.
  • DML Lock에서 로우 Lock은 베타적 Lock와 유사하다. 테이블 Lock은 한 트랜잭션이 로우 Lock을 얻을 때 해당 테이블의 테이블 Lock도 동시에 얻는데 트랜잭션이 갱신 중인 테이블 구조를 변경하지 못하게 하기 위해서이다.

4. 트랜잭션

  • 트랜잭션은 업무 처리를 위한 논리적인 작업 단위며 원자성, 일관성, 격리성, 영속성의 특징(ACID)을 가진다. 트랜잭션의 격리성은 일관성과 마찬가지로 Lock을 강하게 할수록 강해지고 최소화할수록 약해진다.
  • 격리성 수준이 낮다면 다른 트랜잭션이 수정한 후 커밋하지 않는 데이터를 읽는 Dirty Read, 한 트래잭션 내에서 같은 쿼리를 두 번 수행했는데 다른 트랜잭션이 값을 수정 및 삭제하여 두 결과가 다르게 나타나는 Non-Repeatable Read, 한 트랜잭션 내에서 같은 쿼리를 두 번 수행했는데, 첫 번째 쿼리에 없던 유령 레코드가 두 번째 쿼리에 나타나는 Phantom Read 현상이 발생한다.
  • 격리성 수준은 트랜잭션에서 처리 중인 아직 커밋되지 않은 데이터를 다른 트랜잭션이 읽을 수 있는 Read Uncommitted, 트랜잭션이 커밋돼 확정만 데이터만 다른 트랜잭션이 읽도록 허용하는 Read Committed(Dirty Read 방지), 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때 첫 번째 쿼리와 두 번째 쿼리의 결과가 같게 해주는 Repeatable Read(Non-Repeatable Read 방지), 트랜잭션 내에서 쿼리를 두 번 이상 수행할 때, 첫 번째 쿼리에 있던 두 번째 쿼리에서 레코드가 사라지거나 값이 바뀌지 않으며 새로 나타나지도 않는 Serializable Read(Phantom Read 방지)가 있다. SQL Server는 모든 수준을 지원하지만 오라클은 Read Committed와 Serializable Read만 지원한다. 대부분의 DBMS는 Read Committed을 기본 격리성 수준으로 정하고 있다.
  • 다중버전 동시성 제어는 스냅샷 격리성 수준이라고도 하며 현재 진행 중인 트랜잭션에 의해 변경된 데이터를 읽고자 할 때는 변경 이전 상태로 되돌린 버전을 읽는 것으로 공유 Lock을 설정하지 않아도 되며 읽는 세션과 변경하는 세션이 서로 간섭현상도 없다.
# 격리성 수준 조정
set transaction isolation level read serializable;

 

5. 동시성 제어

  • 동시성 제어란 동시에 작동하는 다중 트랜젝션의 상호 간섭 작용에서 데이터베이스를 보호하는 것을 말하며 Lock 기능을 활용하여 여러 사용자가 엑스스하는 것처럼 보이지만 내부적으로는 하나씩 실행되도록 트랜잭션을 직력화한다.  
  • 동시성과 일관성은 트레이드 오프 관계이기 때문에 동시성 제어가 어렵다.
  • 동시성 제어 기법에는 사용자들이 데이터를 동시에 수정할 것이라고 가정해서 데이터를 읽는 시점에 Lock을 걸고 트랜잭션이 완료될 때까지 유지하는 비관적 동시성 제어가 있다. select 시점에 Lock을 거는 행위는 동시성을 심각하게 떨어뜨릴 우려가 있기에 오라클에서는 wait나 nowait를 SQL Server에서는 holdlock, updlock 힌트를 사용하자. 낙관적 동시성 제어는 사용자들이 같은 데이터를 동시에 수정하지 않을 것이라고 가정하는 것으로 데이터를 읽을 때는 Lock을 설정하지 않지만 수정 시점에 다른 사용자로부터 값이 변경됐는지를 반드시 검사해야 한다.

6. 다중버전 동시성 제어

  • 다중버전 동시성 제어(MVCC)는 데이터를 변경할 때마다 그 변경사항을 Undo 영역에 저장한다. 이때 다른 트랜젝션이 데이터를 읽기 위해 Undo 영역에 저장된 데이터를 사용하기에 동시성과 일관성을 동시에 높일 수 있다. 하지만 Undo 블록 I/O, CR Copy 생성 및 블록 캐싱 같은 추가 작업이 필요하기에 주의해야 한다. 오라클은 Undo 세그먼트에 저장하고 SQL Server는 tempdb에 저장한다.
  • 문장 수준 읽기 일관성은 다른 트랜젝션에 의해 데이터의 추가 변경/삭제가 발생하더라도 단인 SQL 문 내에서는 일고나성 있게 값을 읽는것을 말한다. 트랜잭션 수준 읽기 일관성을 다른 트랜잭션에 의헤 데이터의 추가/변경/삭제가 발생하더라도 트랜잭션 내에서 일관성 있게 값을 읽는 것을 말한다.
  • Snapshot too old 에러는 Undo 영역에 저장된 Undo 정보가 다른 트랜젝션에 의해 재사용돼 필요한 CR Copy을 생성할 수 없을 때 발생한다. 이때는 Undo 영역 크기를 증가시키고 불필요한 커밋을 줄이며 fetch across commit 형태의 프로그램 작성을 피해야 한다. 또한 작업시간이 오래 걸리는 쿼리(Nested Loop 조인)를 트랜잭션이 몰리는 시간대에 수행하지 않고 큰 테이블을 일정 범위로 나누어 읽으며 소트 부하를 감수하더라도 소트연산이 발생하도록 하거나 대량 업데이트 이후 바로 테이블을 Full Scan 하는 것이다.

 

참고 서적 - SQL 전문가 가이드