제품을 운영하다보면 Deadlock found when trying to get lock, Lock wait timout exceeded와 같은 DB 동시성 이슈를 마주할 수 있게 된다. 보통 여러 트랜잭션이 동시에 동일 데이터에 대한 select, update 작업들을 수행할때 발생하게 되는데 이와 관련해서 DB Lock에 대해 정리해보고자 한다.
락(Lock)이란?
데이터베이스는 여러 사용자들이 같은 데이터를 동시에 접근하는 상황에서, 데이터 무결성과 일관성을 지키기 위해 락을 사용한다.
즉, 여러 트랜잭션 간의 동시성을 제어하기 위한 기능이다.
락의 설정 범위
데이터베이스
데이터베이스 범위의 Lock은 전체 데이터베이스를 기준으로 Lock 을 거는 것이다. 즉, 1개의 세션만이 DB의 데이터에 접근이 가능하다.
해당 기능은 일반적으로 사용하지 않는다. DB의 버전을 올리는 등 주요한 DB 업데이트에 사용한다.
파일
데이터베이스 파일을 기준으로 Lock을 거는 것이다. ‘파일’ 이란 테이블, row 등과 같은 실제 데이터가 쓰여지는 물리적인 저장소입니다. 해당 범위의 Lock은 잘 사용되지 않는다.
테이블
테이블 수준의 Lock은 테이블을 기준으로 Lock을 거는 것이다. 테이블의 모든 행을 업데이트 하는 등의 전체 테이블에 영향을 주는 변경을 수행할 때 유용하다. 즉 DDL(CREATE, ALTER, DROP) 구문과 함께 사용되며 DDL Lock이라고도 한다.
페이지와 블록
파일의 일부인 페이지와 블록을 기준으로 Lock을 거는 것이다. 잘 사용되지는 않습니다.
컬럼
컬럼 기준의 Lock은 컬럼을 기준으로 Lock을 거는 것이다. 하지만 이 형식은 Lock 설정 및 해제의 리소스가 많이 들기 때문에 일반적으로 사용되지는 않는다. 지원하는 DBMS도 많지 않다.
행
테이블내 1개의 행(Row)를 기준으로 Lock을 거는 것이다. DML에 대한 Lock으로 가장 일반적으로 사용하는 Lock 이다.
Lock의 종류
공유 락(Shared Lock)
데이터를 변경하지 않는 읽기(ex. select 쿼리)에 대해 주어지는 락으로 Read Lock이라고도 불리며, Shared 의 앞 글자를 따서 주로 S로 표기한다.
여러 사용자가 동시에 데이터를 읽어도 데이터 일관성에는 아무런 영향을 주지 않기 때문에, 공유 락끼리는 동시 접근이 가능하다.
베타 락(Exclusive Lock)
데이터 변경을 가하는 쓰기 명령(ex. update쿼리 등)들에 대해 주어지는 락으로 Writer Lock 으로도 불리며, X로 표기한다.
베타 락은 이름처럼 다른 세션이 해당 자원에 접근(ex. select 쿼리, insert 쿼리 등)하는 것을 막는다.
이러한 점에서 베타 락은 멀티 쓰레딩 환경에서, 임계 영역을 안전하게 관리하기 위해 활용되는 뮤텍스와 유사하다고 볼 수 있다. 베타 락은 트랜잭션 동안만 유지된다.
업데이트 락(Update Lock)
데이터를 수정하기 위해 베타 락(X)을 걸기 전, 데드 락(교착 상태)을 방지하기 위해 사용되는 락이다. 일반적으로 업데이트 락은 UPDATE 쿼리의 필터(WHERE)가 실행되는 과정에서 적용된다.
서로 다른 트랜잭션에서 동일한 자원에 대해 읽기 쿼리 이후, 업데이트 쿼리를 적용하는 경우 컨버젼 데드락이 발생하는데, 이를 막기 위해 일부 SELECT 퀴리에서도 업데이트 락을 적용(WITH(UPDLOCK))하기도 한다.
예를 들어, 두 트랜잭션이 동시에 수행되면 처음에는 공유 락을 걸었다가 update 하는 시점에 베타락으로 전환할 것이다. 그러면 두 트랜잭션은 상대편 트랜잭션의 공유락이 해제되기만을 바라는 DeadLock(교착상태)에 빠지게 된다. 이런 상태를 방지하기 위해 Update(갱신) Lock을 사용할 수 있으며 다음과 같이 사용할 수 있다.
1
2
3
4
5
6
7
8
9
10
11
begin tran
select *
from dbo.PLAYER with(uplock)
where player_id = 3
update dbo.PLAYER
set weight = 80
where player_id = 3
commit
위와 같은 쿼리를 실행하면 첫번째 트랜잭션이 업데이트 락을 설정 후 베타 락으로 전환했다가 해제 될때까지, 두번째 트랜잭션이 기다려야하므로 DeadLock(교착상태)를 방지할 수 있다.
내재 락(Intent Lock)
내재 락은 앞서 소개한 락들과 사뭇 다른 기능을 한다. 내재 락은 사용자가 요청한 범위에 대한 락(ex, 테이블 락)을 걸 수 있는지 여부를 빠르게 파악하기 위해 사용되는 락이다. 내재 락은 공유 락과 베타 락 앞에 I 기호를 붙인 IS, IX, SIX 등이 있다.
사용자 A가 테이블의 하나의 로우(row)에 대해 베타 락(X)을 건 경우, 사용자 B가 테이블 전체에 대한 락을 걸기 위해서는(ex. 스키마 변경) 사용자 A의 트랜잭션이 끝날 때까지 기다려야 한다. 그러나, 사용자 B가 테이블에 락(DDL Lock)을 걸 수 있는지 여부를 파악하기 위해 테이블에 존재하는 모든 로우와 관련된 락을 찾아보는 것은 매우 비효율적인 작업이다.
따라서, 데이터베이스는 사용자 A가 로우에 베타 락(X)을 거는 시점에, 해당 로우의 상위 객체들(ex, 페이지, 테이블)에 대한 내재 락(IX)을 걸어, 다른 사용자가 더 큰 범위의 자원들에 대해 락을 걸 수 있는지 여부를 빠르게 파악할 수 있도록 하는 것이다.
출처: Why do we need Intent Locks in SQL Server?
Lock Escalation
하나의 로우에 대해 락을 생성하면, 상위 객체들에 대한 내재 락들이 함께 생성된다.
그러나, 락은 많은 메모리 자원을 필요로 한다. 따라서, 많은 데이터베이스들은 테이블 내 일정 비율 이상의 row에 대한 락을 생성할 경우, 모든 로우에 대해 락을 거는 것이 아닌, 더 상위 객체인 테이블에만 락을 걸도록하여 메모리 사용을 최적화하는 기능인 Lock Escalation을 지원한다.
Lock의 호환성과 Conversion Deadlock
내제 공유 락(IS)은 베타 락을 제외한 모든 락과 함께 실행이 가능하다. 베타 락은 이름 그대로 다른 락과 호환이 불가능하며, 다른 트랜잭션의 모든 락이 해제될 때까지 실행될 수 없다. 동일하게, 베타 락을 얻은 트랜잭션이 커밋/롤백 할 때까지 동일 범위에 대한 모든 락은 실행 권한을 얻을 수 없다.
제품 운영 과정에서 종종 발생하는 컨버젼 데드락(Conversion Deadlock)은 다른 공유(S)락과 업데이트 락(U)이 동일한 자원에 접근하는 것을 허용하는 공유 락(S)의 성질에서 기인한다. 데이터베이스는 업데이트(UPDATE … SET … WHERE…)를 실행할 때에, 먼저 해당 자원에 대한 공유 락(S)을 얻어온 후 이를 베타 락(X)으로 전환하게 된다. 이러한 업데이트 쿼리가 동시에 실행되어 두 트랜잭션이 모두 공유 락(S)을 얻은 후 이를 베타 락(X)으로 전환하려 할때, 상대 트랜잭션이 소유하고 있는 공유 락(S)으로 인해 모든 트랜잭션이 베타 락(X)을 얻지 못 해 업데이트에 실패하게 된다.
출처: https://velog.io/@koo8624/Database-데이터베이스-락Lock의-종류와-역할