[SQL Server] Lock

1. Lock 기본

🔥 Lock이란?

같은 자원을 액세스하려는 다중 트랜잭션 환경에서 데이터베이스의 일관성과 무결성을 유지하려면 트랜잭션의 순차적 진행을 보장할 수 있는 직렬화(Serialization) 장치가 필요하다.

이런 직렬화를 가능하게 하려고 모든 DBMS가 공통적으로 사용하는 매커니즘이 바로 Lock이다.

 

🔥 공유 Lock과 배타적 Lock

공유 Lock

- 읽기 모드로 데이터 읽을 때 사용

- 다른 공유 Lock과는 호환되지만 배타적 Lock과는 호환 X

 

배타적 Lock

- 쓰기 모드로 데이터를 변경할 때 사용

- 트랜잭션이 완료될 때까지 유지

- Lock이 해제될 때까지 다른 트랜잭션은 해당 리소스에 접근 불가

 

🔥블로킹과 교착 상태

① 블로킹(Blocking)

Lock 경합이 발생해 특정 세션이 작업을 진행하지 못하고 멈춰 선 상태

블로킹 상태를 해소하는 방법은 커밋(롤백) 뿐

 

- 공유 Lock과  배타적 Lock

- 배타적 Lock끼리

 

🔮Lock에 의한 성능 저하를 최소화하는 방안

- 우선 트랜잭션의 원자성을 훼손하지 않는 선에서 트랜잭션은 가능한 짧게 정의

- 같은 데이터를 갱신하는 트랜잭션이 수행되지 않도록 설계하는 것도 중요

→  특히 트랜잭션이 활발한 주간에 대용량 갱신 작업을 수행해선 안된다

- 주간에 대용량 갱신 작업이 불가피하다면, 블로킹 현상에 의해 사용자가 무한정 기다리지 않도록 적절한 프로그래밍 기법 도입

- 트랜잭션 격리성 수준을 불필요하게 상향 조정 X

- 트랜잭션을 잘 설계하고 대기현상을 피하는 프로그래밍 기법을 적용하기에 앞서, 결과가 가장 빨리 나오는 SQL을 작성하는 게 Lock 튜닝의 기본이고, 효과가 가장 확실

 

②  교착상태(Deadlock)

두 세션이 각각 Lock을 설정한 리소스를 서로 액세스하려고 마주 보며 진행하는 상황

블로킹이랑 비슷한건가?

No.
블로킹은 다른 트랜잭션이 쓰지 못하도록 잠그는 것.
교착상태는 두 트랜잭션이 하염없이 기다리는 것.

 

교착 상태가 발생하면 DBMS가 둘 중 한 세션에 에러를 발생시킴으로써 문제를 해결한다.

이를 방지하기 위해 앞서 말한 Lock 튜닝 방안을 사용할 수 있고, SQL Server의 경우 Update Lock을 사용하여 교착상태 발생 가능성을 줄일 수 있다.

 

 

🔮 어떻게 Deadlock이 생기는 것일까?

트랜잭션을 아래와 같이 2개 만들면 간단히 확인할 수 있다.

-- run this in query window 1
BEGIN TRANSACTION
 
--1
UPDATE ##TableA
SET Val = 'E'
WHERE ID = 1
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB
SET Val= N'G'
WHERE ID = 1
-------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

-- run this in query window 2
BEGIN TRANSACTION
 
--2
UPDATE ##TableB
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

첫 번째 쿼리가 실행되자마자 두 번째 쿼리도 실행한다.

결과적으로 첫 번째 쿼리의 결과는 저장되나, 두 번째 쿼리의 결과는 롤백된다.

 

첫 번째 트랜잭션이 실행되면 테이블 A를 변경하고, 테이블 B를 변경하고자 한다.

그런데 테이블B를 변경하려고 할 때, 이미 두 번째 트랜잭션이 테이블 B를 변경하려고 시도하나 아직 커밋되지 않은 상태이므로 첫 번째 트랜잭션는 두 번째 트랜잭션이 마칠 때까지 기다린다.

 

반면 두 번째 트랜잭션은 테이블 A를 변경했으나 아직 커밋하지 않은 첫 번째 트랜잭션을 기다리고 있다.

이렇게 되면 두 트랜잭션은 서로를 블로킹하고 교착상태가 발생한다.

2. SQL Server Lock

가. Lock 종류

SQL Server은 하나의 트랜잭션만 커밋되도록 하고, 다른 트랜잭션은 롤백되도록 한다.

이러한 트랜잭션은 SQL Server Engine에 의해 선택된다.

 

잦은 Deadlock은 롤백되면서 시간과 CPU 리소스가 사용되어 성능에 영향을 준다.

따라서 Deadlock가 생기지 않도록 디자인하는 것이 중요하다.

이를 위해 사용하는 Lock이 있다.

 

🔮Shared Lock(공유 Lock)

- 주로 데이터를 읽을 때 사용

- 여러 트랜잭션이 동시에(concurrently) 읽는 것은 허용하나 쓰는 것은 허용 X

 

🔮Exclusive Lock(배타적 Lock)

- 주로 데이터를 쓰거나 변경할 때 사용

- 만약 Exclusive Lock이 리소스에 놓이면 다른 트랜잭션들은 해당 리소스의 데이터를 읽는 것도 불가능

(예외로 READUNCOMMITTED isolation level이나 NOLOCK 힌트를 쓰는 트랜잭션은 dirty read를 허용)

- 만약 두 트랜잭션이 대기 중인데 공유Lock에서 배타적 Lock으로 바꾸면 교착상태가 발생

 

🔮Update Lock(갱신 Lock)

 

- Update Lock을 사용해 교착상태를 방지하여 Performance를 향상 가능

- Exclusive Lock과 다르게 이미 Shared Lock에 의해 쓰이는 리소스는 Shared Lock을 둔다.

그리고 Update Lock을 둘 수도 있고, 변경할 때는 Exclusive Lock으로 바꿀 수도 있다.

- UPDLOCK 힌트 사용

- 한 리소스에 대한 Update Lock은 한 트랜잭션만 설정 가능(Update Lock끼리 호환 불가능)

 

예시로 두 트랜잭션을 실행한다.

이 경우에는 교착상태가 발생하지 않는다. 왜냐하면 첫 번째 트랜잭션에 UPDLOCK이 걸려있기 때문이다.

그래서 두 번째 트랜잭션이 적용될 수 있었다.

-- run this in query window 1
BEGIN TRANSACTION
 
SELECT @@SPID AS FirstTransactionProcessID
 
SELECT ID 
FROM ##TableB WITH (UPDLOCK)
WHERE ID=1
 
 --1
UPDATE ##TableA 
SET Val = 'E'
WHERE ID = 1 
------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--3
UPDATE ##TableB 
SET Val= N'G'
WHERE ID = 1
------------------------------------------------------------------
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

-- run this in query window 2
BEGIN TRANSACTION
 
--2
SELECT @@SPID AS SecondTransactionProcessID
EXEC sp_lock
 
UPDATE ##TableB 
SET Val = N'F'
WHERE ID = 1
--------------------------------------
WAITFOR DELAY '00:00:07'
 
 
--4
UPDATE ##TableA
SET Val = N'H'
WHERE ID = 1
 
 
COMMIT
 
SELECT Val, GETDATE() AS CompletionTime FROM ##TableA WHERE ID=1
SELECT Val, GETDATE() AS CompletionTime FROM ##TableB WHERE ID=1

참고로 sp_lock은 여기서 LOCK을 확인하는 프로시저이다.

두 번째 프로시저에서 테이블 A의 SELECT 구문에서 UPDLOCK은 필요없다.

 

🔮Intent Lock(의도 Lock)

- 특정 Row에 Lock을 설정하면 그와 동시에 상위 레벨 개체(페이지, 익스텐트, 테이블)에 내부적으로 의도 Lock이 설정

- Lock을 설정하려는 개체의 하위 레벨에서 선행 트랜잭션이 어떤 작업을 수행중인지를 알리는 용도(Flag)

👉 예를 들어 구조를 변경하기 위해 테이블을 잠그려할 때, 그 하위의 모든 페이지나 익스텐트, 심지어 로우에 어떤 Lock이 설정돼 있는지를 일일이 검사해야 한다면 좀처럼 작업이 끝나지 않을 수 있다.

👉 의도 Lock은 이런 현상을 방지

👉 해당 테이블에 어떤 모드의 의도 Lock이 설정돼 있는지만 보고도 작업을 진행할지 아니면 기다릴지 결정

 

🔮Schema Lock(스키마 Lock)

- 테이블 스키마에 의존적인 작업을 수행할 때 사용

- Sch-S(Schema Stability): SQL을 컴파일하면서 오브젝트 스키마를 참조할 때 발생하며, 읽는 스키마 정보를 수정하거나 삭제하지 못하도록 함

- Sch-M(Schema Modification): 테이블 구조를 변경하는 DDL문을 수행할 때 발생하며, 수정중인 스키마 정보를 다른 세션이 참조하지 못하도록 함

 

🔮Bulk Update Lock

- 테이블 Lock의 일종으로 테이블에 데이터를 Bulk Copy할 때 발생한다.

- 병령 데이터 로딩은 허용하지만, 일반적인 트랜잭션 작업은 허용하지 않는다.

 

나. Lock 레벨과 Escalation

Lock 레벨	설명
로우 레벨	변경하려는 로우(실제로는 RID)에만 Lock을 설정
페이지 레벨	변경하려는 로우가 담긴 데이터 페이지(또는 인덱스 페이지)에 Lock을 설정 같은 페이지에 속한 로우는 진행중인 변경 작업과 무관하더라도 모두 잠긴 것과 같은 효과
익스텐트	익스텐트 전체가 잠김 SQL Server는 하나의 익스텐트가 8개 페이지로 구성되므로, 8개의 페이지에 속한 모든 로우가 잠긴 것과 같은 효과
테이블 레벨	테이블 전체와 관련 인덱스까지 모두 잠김
데이터베이스 레벨	데이터베이스 전체가 잠김 보통 데이터베이스를 복구하거나 스키마를 변경할 때 발생

위 5가지 레벨 외에 인덱스 키(Key)에 로우 레벨 Lock을 거는 경우가 있다.

 

 

 

🔮Lock Escalation

관리할 리소스가 정해진 임계치를 넘으면서 로우 레벨 락이 페이지, 익스텐트, 테이블 레벨 락으로 점점 확장되는 것 의미한다.

Locking 레벨이 낮을수록 동시성은 좋지만 관리해야할 Lock 개수가 증가하므로 더 많은 리소스를 소비한다.

반대로 Locking레벨이 높을수록 적은 양의 Lock 리소스를 사용하지만, 하나의 Lock으로 수많은 레코드를 한꺼번에 잠그기 때문에 동시성은 나빠진다.

 

 

 

🔮참고

- https://www.mssqltips.com/sqlservertip/6290/sql-server-update-lock-and-updlock-table-hints/