[스크랩] SQL lock / blocking / dead lock 간단정리

https://chrisjune-13837.medium.com/db-lock-%EB%9D%BD%EC%9D%B4%EB%9E%80-%EB%AC%B4%EC%97%87%EC%9D%B8%EA%B0%80-d908296d0279

[DB] Lock이란?

DBMS에서 데이터의 일관성을 보장하기 위한 기본적인 방법인 Lock에 대해서 알아봅니다.

Lock이란?

데이터의 일관성을 보장하기 위한 방법입니다. 오라클과 같이 고가의 DBMS를 사용하는 이유가 데이터의 무결성과 일관성을 유지하는 능력이 뛰어나기 때문입니다.

Lock의 종류는?

Lock은 상황에 따라서 크게 두가지로 나누어 집니다.

1. Shared Lock(공유 Lock 또는 Read Lock)

보통 데이터를 읽을 때 사용합니다. 원하는 데이터에 lock을 걸었지만 다른 세션에서 읽을 수 있습니다. 공유Lock을 설정한 경우 추가로 공유Lock을 설정할 수 있지만, 배타적 Lock은 설정할 수 없습니다. 즉, 내가 보고 있는 데이터는 다른 사용자가 볼 수 있지만, 변경할 수는 없습니다.

2. Exclusive Lock(배타적 Lock 또는 Write lock)

보통 데이터를 변경할 때 사용합니다. 이름에서 느껴지는 것 처럼 해당 Lock이 해제되기 전까지는, 다른 공유Lock, 배타적Lock을 설정할 수 없습니다. 즉, 읽기 기와 쓰기가 불가능하다는 의미입니다.

Blocking ?

경합으로 인한 상태인 블로킹에 대하여 알아봅니다.

Blocking

블로킹은 Lock들의 경합(Race condition이라고도 합니다)이 발생하여 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태를 의미합니다. 공유Lock과 배타적Lock 또는 배타적Lock과 배타적Lock끼리 블로킹이 발생할 수 있습니다. 이를 해결하는 방법은 Transaction commit 또는 rollback 뿐입니다.

경합이 발생할 때, 먼저 Lock을 설정한 트랜젝션을 기다려야하기 때문에, 이런 현상이 반복되면 빠른 서비스를 제공할 수 없습니다.

해결방안

1. SQL 문장에 가장 빠르게 실행되도록 리펙토링하는 것이 가장 기본이며 효과적인 방법입니다.
2. 트랜젝션을 가능한 짧게 정의하면 경합을 줄일 수 있습니다.
3. 동일한 데이터를 동시에 변경하는 작업을 하지 않도록 설계하는 것이 좋습니다. 또한 트랜젝션이 활발한 주간에는 대용량 갱신 작업을 수행하면 안됩니다.
4. 대용량작업이 불가피할 경우, 작업단위를 쪼개거나 lock_timeout을 설정하여 해당 Lock의 최대시간을 설정할 수 있습니다.

set lock_timeout 3000

Dead Lock이란?

deadlock은 트랜젝션간의 교착상태를 의미합니다. 두개의 트랜젝션간에 각각의 트랜젝션이 가지고 있는 리소스의 Lock을 획득하려고 할 때 발생합니다.

Dead Lock Example1

가장 흔히 떠올릴 수 있는 deadlock 상황입니다. 1번 트랜젝션에서 2번 리소스의 잠금을 획득, 2번 트랜젝션에서는 1번 리소스의 잠금을 획득한 상태입니다. 이때, 동시에 상대방의 데이터를 엑세스하려고 할때 기존의 Lock이 해제될 때까지 기다리게 되는 상황입니다.

Dead Lock Example2

두번째 예는, 1번 트랜젝션이 공유Lock을 설정하고 Sleep에 빠졌습니다. 이때 2번 트랜젝션은 배타적Lock을 설정하려고 할때, 무기한 기다리게되는 교착상태에 빠지게 됩니다.

Postgresql에서는 자동적으로 deadlock을 인지하고 교착상태를 해결하기 위하여 하나의 트랜젝션을 취소시킵니다. 그러나, 어떤 트랜젝션이 취소될 지 판단하기는 어렵습니다.

Lock Level & Escalation이란?

SQL 명령어에 따라서 Lock의 설정대상이 데이터 row일지 database일지 나누어집니다.

1. Row level, 변경하려는 row에만 lock을 설정하는 것을 의미합니다.
2. Page level, 변경하려는 row가 담긴 데이터 page (또는 인덱스 페이지)에 lock을 설정합니다. 같은 페이지에 속한 row들은 변경작업과 무관하게 모두 lock에 의해 잠깁니다.
3. Tabel level, 테이블과 인덱스에 모두 잠금을 설정합니다. Select table, Alter table, Vacuum, Refresh, Index, Drop, Truncate 등의 작업에서 해당레벨의 락이 설정됩니다.
4. Database level, 데이터베이스를 복구하거나 스키마를 변경할 때 발생합니다.

Escalation

Lock리소스가 임계치를 넘으면 Lock 레벨이 확장되는 것을 의미합니다. Lock 레벨이 낮을 수록 동시성이 좋아지지만, 관리해야할 Lock이 많아지기 때문에 메모리 효율성은 떨어집니다. 반대로 Lock레벨이 높을 수록 관리리소스는 낮지만, 동시성은 떨어집니다.

참고링크

• http://www.sqler.com/320577
• https://www.postgresql.org/docs/9.4/explicit-locking.html