Lock (feat. Isolation level, 공유락, 베타락)

1. Lock

---

# Overview

공유락과 베타락에 대해 공부하였지만 락이 언제 발생하는지 쉽게 이해 되지는 않았습니다. 블로그 글을 읽어보기 보단 직접 실험해 보면서 이해해 보고자 합니다.

 

# Tip

Lock을 이해하기 위해서 Isolation level과 함께 알아보려고 합니다. 각 레벨에서 SELECT(공유락)과 UPDATE(베타락)이 어떻게 작용하는지 이해해 보면 좋을것 같습니다.

 

# 오해 

저는 베타락에 대한 오해가 몇가지 있었습니다.

1. 공유락과 베타락은 단순히 SELECT와 UPDATE를 사용하면 적용되는줄 알았습니다.

2. Isolation level에 상관없이 베타락과 공유락의 작동 규칙이 동일한 줄 알았습니다.

 

# KeyPoint

핵심만 보고 싶으시면

REPEATABLE READ의 UPDATE - UPDATE와

SERIALIZABLE의 UPDATE - SELECT만 중점적으로 봐주세요!

 

# 마지막으로...

제가 알고있는 내용을 공유하기 보단 제가 모르는 내용을 정리해 보려는 취지가 강합니다. 때문에 게시글 곧곧에 잘못된 개념을 설명할 수 있습니다! 또한 Isolation Level에 따라 발생하는 문제점에 대해 알아보는 것이 아닌 Isolation Level에 따라 공유락과 베타락이 어떻게 작동하는지 알아보기 위한 것입니다.

 

Isolation Level에 따른 문제점과 해결에 대한 글은 아래 링크를참조해 주세요!

https://idea-sketch.tistory.com/45 

https://mangkyu.tistory.com/30?category=761304 

 

사진이 많아 복잡해 마지막에 정리글을 남겨두겠습니다!

 

 

 

2. 이론 및 실습

---

MySQL에서는 Isolation Level의 Default 값으로 REPEATABLE READ를 사용한다. 즉, REPEATABLE READ이하의 트랜잭션 격리 수준 이하에서 InnoDB 테이블에 대한 SELECT 쿼리는 기본적으로 아무런 잠금을 사용하지 않는다. SERIALIZABLE 격리 수준에서는 모든 SELECT 쿼리에 자동으로 LOCK IN SHARE MODE가 붙여져서 실행되는 효과를 내기 때문에 모든 SELECT 쿼리가 읽기 잠금을 걸고 레코드를 읽는다.

 

SELECT 쿼리로 읽은 레코드를 잠그는 방법은 읽기 모드와 쓰기 모드 두 가지가 있다.

# 읽기 모드
SELECT * FROM TABLE WHERE ID = 1 LOCK IN SHARE MODE;

# 쓰기 모드
SELECT * FROM TABLE WHERE ID = 1 FOR UPDATE;

 

• 읽기모드 : 테이블에 쓰기 잠금이 걸려 있지 않으면 읽기 잠금을 획득하고 읽기 작업을 시작할 수 있다.
• 쓰기모드 : 테이블에 아무런 잠금이 걸려 있지 않아야 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 기다려야 한다.

두 가지 방법 모두 SELECT 쿼리로 읽은 레코드를 다른 커넥션의 트랜잭션에서 변경하지 못하게 막는 역할을 한다. 하지만 LOCK IN SHARE MODE는 읽기 잠금만 걸기 때문에 잠금을 획득한 트랜잭션에서도 변경하려면 쓰기 잠금을 다시 획득해야 한다. 읽기 잠금을 가진 상태에서 다시 쓰기 잠금을 획득하는 과정은 데드락을 유발하는 가장 일반적인 형태이다.

 

UPDATE / INSERT / DELETE 쿼리는 기본적으로 쓰기 잠금을 사용하며 필요시 읽기 잠금을 사용할 수도 있다. UPDATE 와 DELETE SQL 문장이 조건에 일치하는 레코드를 찾기 위해 스캔하는 인덱스의 모든 레코드에 잠금을 건다. 즉, WHERE  조건문보다 큰 범위의 레코드도 잠금될 가능성이 있다. 

 

락에 대해 조금더 알고 싶으면 링크 클릭!

https://idea-sketch.tistory.com/47?category=547413 

[MySQL]MySQL 벼락치기(6) - 트랜잭션과잠금(2)

 

 

[ Isolation Level 0 / READ UNCOMMITTED]

# 초기작업
> SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
> SHOW VARIABLES LIKE 'transaction_isolation';
> USE FOODEATS
> START TRANSACTION;

# SELECT - SELECT

 

# SELECT - UPDATE

# UPDATE - SELECT

 

# UPDATE - UPDATE

 

[ Isolation Level 1 / READ COMMITTED ]

# 초기작업
> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
> SHOW VARIABLES LIKE 'transaction_isolation';
> USE FOODEATS
> START TRANSACTION;

 

# SELECT - SELECT

 

# SELECT - UPDATE

 

# UPDATE - SELECT

 

# UPDATE - UPDATE

 

[ Isolation Level 2 / REPEATABLE READ ]

# 초기작업
> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
> SHOW VARIABLES LIKE 'transaction_isolation';
> USE FOODEATS
> START TRANSACTION;

 

# SELECT - SELECT

 

# SELECT - UPDATE

 

# UPDATE - SELECT

 

# UPDATE - UPDATE

 

[ Isolation Level 3 / SIRIALIZABLE ]

# 초기작업
> SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
> SHOW VARIABLES LIKE 'transaction_isolation'
> USE FOODEATS
> START TRANSACTION;

 

# SELECT - SELECT

 

# SELECT - UPDATE

 

# UPDATE - SELECT

 

# UPDATE - UPDATE

 

 

3. 정리

---

# 결과 분석

# READ-UNCOMMITTEDD
select - select 가능
select - update 가능
update - select 가능
update - update Lock발생

 

고립 수준이 Level 0으로 가장 낮은 명령어로, 자신의 데이터에 아무런 공유락을 걸지 않늗다.

---

# READ-COMMITTED
select - select 가능
select - update 가능
update - select 가능
update - update Lock발생

 

고립 수준이 Level 1인 명령어로, 오손 페이지의 참조를 피하기 위해 자신의 데이터를 읽는 동안 공유락을 걸지만 트랜잭션이 끝나기 전에라도 해지 가능하다.

---

# REPEATABLE READ
select - select 가능
select - update 가능
update - select 가능
update - update Lock발생

 

고립 수준이 Level 2인 명령어로, MySQL에서  Default로 사용하는 Isolation Level이다. 자신의 데이터에 설정된 공유락과 배타락을 트랜잭션이 종료될 때까지 유지하여 다른 트랜잭션이 자신의 데이터를 갱신(Update)할 수 없도록 한다.

 

REAPEATABLE READ 이하의 Isolation Level에서는 4가지 경우에 모두 동일한 결과가 나오는 것을 보았다. 그 이유에 대해 알아보던 중 명확한 답은 발견하지 못했다. 하지만 Real MySQL 책에 따르면 REPEATABLE READ 이하의 고립 레벨에서는 SELECT 쿼리를 실행 할 때 아무런 잠금을 사용하지 않는다고 한다. SERIALIZABLE에서만 SELECT 쿼리에 LOCK IN SHARE MODE를 사용하여 읽기 모드 잠금을 한다고 한다. 그래서 UPDATE - UPDATE 동작을 제외하고는 Lock이 발생하지 않았다고 생각한다.

 

반면 UPDATE 쿼리에 대해서는 기본적으로 쓰기 잠금(베타락(?))을 사용하며 WHERE 조건에 베타락을 건다. 데이터에 베타락이 걸려있는 경우 공유락과 베타락 모두 허용하지 않기 때문에 UPDATE - UPDATE 동작에서 Lock이 발생한다고 생각한다.

 

---

# SERIALIZABLE
select - select 가능
select - update Lock발생
update - select Lock발생
update - update Lock발생

 

고립 수준이 Level 3으로 가장 높은 명령어로, 실행 중인 트랜잭션은 다른 트랜잭션으로부터 완벽하게 분리된다.

 

SERIALIZABLE은 데이터 집합에 범위를 지어 잠금을 설정할 수 있기 때문에 다른 사용자가 데이터를 변경(UPDATE) 또는 삽입(Insert)하려고 할 때 트랜잭션을 완벽하게 분리할 수 있다. 이 명령어는 가장 제한이 심하고 동시성도 낮다. 즉, SELECT 문이 사용하는 모든 데이터에 Shared Lock이 걸리므로 다른 사용자는 그 영역에 해당되는 데이터에 대한UPDATE 및 INSERT 가 불가능하다.

 

---

 

# 공유락과 베타락을 사용하는 규칙

• 데이터에 락이 걸려있지 않으면 트랜잭션은 데이터에 락을 걸 수 있다.
• 트랜잭션이 데이터 X를 읽기만 할 경우 LS(X)를 요청하고, 읽거나 쓸 경우 LX(X)를 요청한다.
• 다른 트랜잭션이 데이터에 LS(X)를 걸어둔 경우, LS(X)의요청은 허용하고 LX(X)는 허용하지 않는다.
• 다른 트랜잭션이 데이터에 LX(X)를 걸어둔 경우, LS(X)와 LX(X) 모두 허용하지 않는다.
• 트랜잭션이 락을 허용받지 못하면 대기 상태가 된다.