MYSQL
Database Lock
Lock이란?
- 데이터의 일관성을 보장하기 위한 방법이다.
- 오라클과 같이 고가의 DBMS를 사용하는 이유가 데이터의 무결성과 일관성을 유지하는 능력이 뛰어나기 때문
Lock의 종류
- Shared Lock(Read Lock)
- 보통 데이터를 읽을 때 사용한다.
- 원하는 데이터에 Lock을 걸었지만 다른세션에서 읽을 수 있다.
- 공유Lock을 설정한 경우 추가로 공유Lock을 설정할 수 있지만 배타적 Lock은 설정할 수 없다.
- 즉, 내가 보고 있는 데이터는 다른 사용자가 볼 수 있지만 변경할 수는 없다.
- Exclusive Lock(Write Lock)
- 보통 데이터를 변경할 때 사용한다.
- 이름에서 느껴지는 것 처럼 해당 Lock이 해제되기 전까지는 다른 공유Lock, 배타적Lock을 설정할 수 없다.
- 즉, 읽기와 쓰기가 불가능 하다는 의미다.
Blocking
- Blocking
- 블로킹은 Lock들의 경합이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태를 의미한다.
- 공유 Lock과 배타적 Lock 또는 배타적 Lock과 배타적 Lock 끼리 블로킹이 발생할 수 있다.
- 이를 해결하는 방법은
Transaction commit또는rollback뿐이다. - 경합이 발생할 때 먼저 Lock을 설정한 트랜잭션을 기다려야하기 때문에 이런 현상이 반복되면 빠른 서비스를 제공할 수 없다.
해결방안
- SQL문장에 가장 빠르게 실행되도록 리펙토링하는 것이 가장 기본이며 효과적인 방법이다.
- 트랜잭션을 가능한 짧게 정의하면 경합을 줄일 수 있다.
- 동일한 데이터를 동시에 변경하는 작업을 하지 않도록 설계하는 것이 좋다. 또한 트랜잭션이 활발한 주간에는 대용량 갱신 작업을 수행하면 안된다.
- 대용량작업이 불가피할 경우 작업단위를 쪼개거나
lock_timeout을 설정하여 해당Lock의 최대시간을 설정할 수 있다.
set lock_timeout 3000
Dead Lock이란?
- Dead Lock은 트랜잭션간의 교착상태를 의미한다.
-
두 개의 트랜잭션간에 각각의 트랜잭션이 가지고 있는 리소스의 Lock을 획득하려고 할 때 발생한다.
- 가장 흔히 떠올릴 수 있는 Dead Lock 상황이다.
- 1번 트랜잭션에서 2번 리소스의 잠금을 획득, 2번 트랜잭션에서는 1번 리소스의 잠금을 획득한 상태다.
- 이때, 동시에 상대방의 데이터를 엑세스하려고 할 때 기존의 Lock이 해제될 때까지 기다리게 되는 상황이다.
- 1번 트랜잭션이 공유Lock을 설정하고 Sleep에 빠졌다.
- 이때 2번 트랜잭션은 배타적 Lock을 설정하려고 할 때 무기한 기다리게 되는 교착상태에 빠지게 된다.
- Postgresql에서는 자동적으로 DeadLock을 인지하고 교착상태를 해결하기 위하여 하나의 트랜잭션을 취소시킨다.
- 그러나 어떤 트랜잭션이 취소될 지 판단하기는 어렵다.
Lock Level 과 Escalation
-
SQL 명령어에 따라서 Lock의 설정대상이 데이터 row일지 database일지 나누어진다.
- Row level
- 변경하려는 row에만 lock을 설정하는 것을 의미
- Page level
- 변경하려는 row가 담긴 데이터 page에 lock을 설정한다.
- 같은 페이지에 속한 row들은 변경작업과 무관하게 모두 lock에 의해 잠긴다.
- Table level
- 데이블과 인덱스에 모두 잠금을 설정한다.
Select table,Alter table,Vacum,Refresh,Index,Drop,Truncate등의 작업헤서 해당 레벨의 락이 설정된다.
- Database level
- 데이터베이스를 복구하거나 스키마를 변경할 때 발생한다.
Escalation
- Lock의 리소스가 임계치를 넘으면 Lock의 레벨이 확장되는 것을 의미함.
- Lock의 레벨이 낮을 수록 동시성이 좋아지지만 관리해야할 Lock이 많아지기 때문에 메모리 효율성은 떨어진다.
- 반대로 Lock레벨이 높을 수록 관리 리소스는 낮지만 동시성은 떨어진다.