DB Lock

트랜잭션이란 ?

모든 명령어의 성공 또는 실패 를 한꺼번에 모아서 처리 하는 것

명령어 중에 하나라도 실패하면 한 트랜잭션 내의 모든 명령어들이 무효화 (롤백) 됨

EX) 은행에서 돈을 송금하려고 할 때

내 계좌에서 돈을 출금
다른 계좌로 돈을 입금

하는 과정이 함께 일어 나야 하는데 2번 과정에서 에러가 발생해서 입금이 되지 않았다면 돈이 붕 떠버리는 사태가 일어나게 됨

=> 이 경우엔 1, 2 를 한 트랜잭션으로 묶어서 하나의 과정이라도 실패하면 롤백 되도록 해야함

트랜잭션의 성질 (ACID)

Atomicity(원자성)
트랜잭션은 부분적으로 완료 될 수 없으며, 전체가 다 되거나 아무것도 되지 않거나 둘 중 하나여야 한다
➡ 출금하는 것만 성공하고 입금 되는 건 성공하면 안됨
Consistency(일관성)
트랜잭션이 실행을 성공적으로 완료하면 언제나 일관성 있는 데이터베이스 상태로 유지
➡ 계좌 이체 하기 전 진행 전의 돈의 합과 진행 후의 돈의 합이 같아야 함
Isolation(독립성,격리성)
트랜잭션을 수행 시 다른 트랜잭션의 연산 작업이 끼어들지 못하도록 보장
➡ 출금 후 다른 트랜잭션이 끼어 들어 내 계좌에 남아 있는 금액을 보지 못함
Durablility(영속성,지속성)
성공적으로 수행된 트랜잭션은 영원히 반영
➡ 완료 후 시스템 문제가 생기더라도 커밋이 되었다면 DB 에 영구적으로 반영

=> 하지만 ACID 원칙을 완벽하게 지키게 되면 동시성이 매우 떨어지기 때문에 완벽하게 지켜지지 않는 경우도 있음

그렇기 때문에 ACID 원칙을 희생하여 동시성을 얻을 수 있는 방법인 isolation Level (격리 수준) 을 제공함

InnoDB 의 Lock

Lock 이란 ?

트랜잭션 처리의 순차성을 보장하기 위한 방법

DB 가 처리하는 가장 작은 단위
트랜잭션이 완벽하게 끝날 때까지 다른 요청을 막아줌

Lock 의 종류

락 적용 요소에 따른 분류

Shared Lock(S)
Exclusive Lock(E)
Intention Lock

락이 적용되는 상황에 따른 분류

Row-level Lock (default)
Record Lock
Gap Lock
Next-key Lock
Insert Intention Lock
Auto-INC Lock

innoDB storage engine 은 기본적으로 Row-level Lock 을 사용

락 적용 요소에 따른 분류

Shared Lock (`S`)

: Row-level Lock: read 에 대한 Lock; 데이터를 읽을 때 사용되어지는 Lock; S Lock 을 사용하는 쿼리 끼리는 같은 row 에 접근 가능하다; SELECT ... FOR SHARE 등 일부 SELECT 쿼리는 read 작업을 수행할 때 InnoDB가 각 row에 S lock

Exclusive Lock(`E`)

: Row-level Lock: write 에 대한 Lock; 데이터를 변경하고자 할 때 사용되어지는 Lock; 트랜잭션이 완료 될 때까지 유지되며, Lock이 해제될 때까지 다른 트랜잭션(읽기 포함)은 해당 리소스에 접근 X; SELECT ... FOR UPDATE나 UPDATE, DELETE 등의 수정 쿼리를 날릴 때 각 row에 걸리는 lock

S Lock, X Lock 거는 규칙

여러 트랜잭션이 한 row 에 S Lock 을 걸 수 있다 => 여러 트랜잭션이 한번에 여러 row 를 읽을 수 있다
S Lock이 걸려 있는 row 에 다른 트랜잭션이 X Lock을 걸 수 없다 => 다른 트랜잭션이 읽고 있는 row 를 수정/삭제 불가능
X Lock 이 걸려 있는 row 에 다른 트랜잭션이 S Lock, X Lock 을 걸 수 없다 => 다른 트랜잭션이 수정/삭제 하고 있는 row 는 읽기, 수정 , 삭제 모두 불가능

Lock 에 따른 경쟁 여부 관계

	S-Lock	X-Lock
S-Lock	X	O
X-Lock	O	O

####

Intention Lock

: Table-level Lock: 테이블 안의 row에 대해서 나중에 어떤 row-level락을 걸 것 이라는 의도를 알려주기 위해 미리 table-level에 걸어두는 lock; SELECT … LOCK IN SHARE MODE 이 실행되면

1. Intention Shared Lock (IS) 이 테이블에 걸림 2. row-level 에 S-Lock 이 걸림

: SELECT … FOR UPDATE, INSERT, DELETE, UPDATE 이 실행되면

1. intention exclusive lock (IX) 이 테이블에 걸림 2. row-level 에 X-Lock 이 걸림

: IS, IX 락은 여러 트랜잭션에서 동시에 접근 가능하지만, row-level 의 실제 락인 S, X 락에서 접근 제어를 하게 됨: LOCK TABLES, ALTER TABLE, DROP TABLE 이 실행될 때는 IS, IX 를 모두 block하는 table-level 락이 걸린다. 즉 IS, IX lock 을 획득 하려는 트랜잭션은 대기상태로 빠짐

row-level 및 Table-level 에서 두번 Lock 하는 이유 ?

A 트랜잭션에서 이미 테이블에 대해 락이 걸려있는데, B 트랜잭션에서 해당 테이블의 특정 row에 lock을 거는것을 원천적으로 방지 할 수 있다. (반대의 경우도 마찬가지)
EX) row-level의 write이 일어나고 있을때 테이블 스키마가 변경되서는 안된다. write query의 경우 이미 IX 락을 획득한 상태이기 때문에 해당 테이블의 스키마가 변경되는것을 막을 수 있다.

	X	IX	S	IS
X	O	O	O	O
IX	O	X	O	X
S	O	O	X	X
IS	O	X	X	X

락이 적용되는 상황에 따른 분류

Row-level Lock

테이블 row 마다 걸리는 Lock

S-Lock , X-Lock

Record Lock

DB index record에 걸리는 Lock

개별 인덱스 레코드에 S-Lock 혹은 X-Lock 을 설정
primary key, unique key 로 조회해서 하나의 인덱스 레코드(=row)에만 lock 을 거는 것

Gap Lock

DB index record 의 gap 에 걸리는 Lock

gap : index 중에 DB 에 실제 record 가 없는 부분

EX ) id column 만 있는 테이블인데 인덱스가 걸려 있고, id = 3 인 row 와 id = 7 인 row 가 있을 때

    Index table               Database
-------------------          ---------
| id  | row addr  |          |  id   |
-------------------          ---------
|  3  | addr to 3 |--------->|   3   |
|  7  | addr to 7 |--------->|   7   |
-------------------          ---------

id <= 2 , 3< id < 7, 8 <= id 에 해당하는 부분에는 index record 가 없음
이 부분이 index record 의 gap

레코드 간의 gap 에 새로운 Insert 가 되는 현상을 방지하는 lock
최초 레코드의 이전, 마지막 레코드의 이후의 gap 에도 lock 을 설정
record lock 은 이미 존재하는 row가 변경 되지 않게 보호 하는 반면, gap lock 은 조건에 해당하는 새로운 row 가 추가 되는 걸 방지
id column 만 있는 테이블인데 인덱스가 걸려 있고, id = 3 인 row 와 id = 5, id = 7인 row 가 있을 때

SELECT * FROM tbl WHERE id BETWEEN 5 AND 5 FOR UPDATE;
## 3 < id < 5 (최초레코드 이전 gap) [gap x-lock]
## 5 < id < 7 (마지막 레코드 이후 gap) [gap x-lock]

Next-key Lock

record lock 과 gap lock 을 함께 사용하는 Lock

REPEATABLE READ 에서 phatom read를 막기위해 탐색시 next-key lock을 사용
id column 만 있는 테이블인데 인덱스가 걸려 있고, id = 3 인 row 와 id = 5, id = 7인 row 가 있을 때

SELECT * FROM tbl WHERE id BETWEEN 3 AND 5 FOR UPDATE;
## id = 3, 5 [record x-lock]
## id < 3 (최초레코드 이전 gap) [gap x-lock]
## 3 < id < 5 [gap x-lock]
## 5 < id < 7 (마지막 레코드 이후 gap) [gap x-lock]

Insert Intention Lock

insert 구문 실행시 획득하는 특수한 형태의 gap lock

INSERT될 row에 대해서 exclusive lock을 걸기 전에 먼저 insert intention lock을 건다
EX) pk=3, pk=6의 레코드가 존재하는 테이블이 존재
- A 트랜잭션에서 pk=5에 INSERT, B 트랜잭션에서 pk=4에 INSERT시도
- 만약 일반적인 gap lock 사용한다면:
  - A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 gap lock 걸림
  - B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 gap lock이 걸려있기때문에 A가 트랜잭션이 완전히 종료될 때 까지 기다려야 한다.
  - 대기시간 존재!
- Insert Intention Lock 사용시:
  - A트랜잭션이 pk=5를 INSERT하는 과정에서 pk=3~5에는 insert intention lock 걸림
  - B트랜잭션이 pk=4에 INSERT 시도시 pk=3~5에 insert intention lock이 걸려있더라도 pk가 겹치지 않기때문에 바로 진행 가능
  - 대기시간 없음!
  - 실제 InnoDB의 동작 방식

Auto-INC Lock

여러 트랜잭션이 동시에 실행될때 AUTO_INCREMENT 컬럼의 값을 일관성 있게 증가시키기 위해 필요한 lock

Lock 이 해제되는 타이밍

transaction 이 모두 commit 되거나 rollback 될 때 함께 unlock

트랜잭션 격리 수준 (isolation Level)

동시에 여러 트랜잭션이 실행된 경우 어떻게 동작할지를 명시

Dirty Read (비커밋 읽기)
트랜잭션에서 커밋되기 전 작업 내용을 다른 트랜잭션에서 볼 수 있음
EX) A 라는 트랜잭션에서 id : 22, name: 이름을 insert 하고 커밋하지 않은 상태에서 B 라는 트랜잭션이 id: 22 를 select 하는 경우 읽을 수 있음. 이때 A 트랜잭션에서 커밋되지 않고 롤백되는 경우 데이터 정합성에 문제가 생김
👉 가능한 이유👈
InnoDB 엔진이 transaction 을 커밋하는 방식 때문에 가능. InnonDB 는 일단 commit 이 되지 않더라도 실행한 모든 쿼리를 DB 에 적용. 즉, 특별히 log를 보고 특정 시점의 snapshot을 복구하는 consistent read를 하지 않고 그냥 해당 시점의 DB 를 읽으면 dirty read
🌟 consistent read(일관적 읽기)
select 시 현재 DB 값이 아닌 특정 시점의 DB snapshot 을 읽어 오는 것
이 snapshot 은 commit 된 변화만이 저장된 것을 의미
InnoDB 엔진은 각 쿼리를 실행할 때마다 실행한 쿼리의 log를 차곡차곡 저장한다. 그리고 나중에 consistent read를 할 때 이 log를 통해 특정 시점의 DB snapshot을 복구하여 가져온다.
Non-Repeatable Read (비반복 읽기)
한 트랜잭션에서 같은 쿼리를 두번 실행시 그 사이 다른 트랜잭션이 값을 수정, 삭제 해서 두 쿼리의 결과가 상이하게 나타남
Phantom Read (가상 읽기)
한 트랜잭션 안에서 일정 범위의 레코드를 두번 이상 읽을 때, 첫번째 쿼리에서 없던 레코드가 두번째 쿼리에서 나타나는 현상

격리 수준은 해당 문제가 발생하는지에 따라 분류

분리 레벨	Dirty Read	Non-Repeatable Read	Phantom Read
READ UNCOMMITEED	발생	발생	발생
READ COMMITTED	-	발생	발생
REPEATABLE READ	-	-	발생
SERIALIZABLE	-	-	-

READ UNCOMMITTED
- SELECT 쿼리를 실행 할 때 다른 트랜잭션에서 커밋되지 않은 데이터를 읽을 수 있음
- Dirty Read, Non-Repeatable Read, Phantom Read 발생
- 오라클은 이 레벨 지원 X
READ COMMITTED
- 커밋 완료된 데이터만 읽을 수 있는 격리수준
- Non-Repeatable Read, Phantom Read 발생
- DB2, SQL Server, Sybase의 경우 Shared Lock을 이용해서 구현, 하나의 레코드를 읽을 때 Lock을 설정하고 해당 레코드에서 빠지는 순간 Lock해제 => Select 문으로 읽을 때만 해당 Row 에 대해 S Lock 이 걸림
- Oracle은 Lock을 사용하지 않고 쿼리시작 시점의 Undo 데이터를 제공하는 방식으로 구현
- 매번 read 마다 consistent read(일관적 읽기)를 통한 새로운 snapshot 을 생성
  ➡️ Non-Repeatable Read, Phantom Read 발생
- Record lock 만 사용하고 Gap lock 사용 X
REPEATABLE READ
- 하나의 트랜잭션에서 반복되는 SELECT 는 항상 같은 값을 조회
- 처음 read 한 시간을 기록하고 그 이후 모든 select 마다 처음 시점을 기준으로 consistent read 수행
  ➡️ Non-Repeatable Read 발생 X
- 트랜잭션이 완료 될 때까지 해당 데이터에 대한 갱신, 삭제가 불가능 (해당 row 에 Shared Lock 이 걸림)
- InnoDB 엔진에서 사용하는 기본 격리 수준
- DB2, SQL Server의 경우 트랜잭션 고립화 수준을 Repeatable Read로 변경하면 읽은 데이터에 걸린 공유 Lock을 커밋할 때까지 유지하는 방식으로 구현 => 트랜잭션이 커밋 될 때까지 Select 문으로 읽은 Row 에 S Lock 이 걸림
- Oracle은 이 레벨을 명시적으로 지원하지 않지만 for update절을 이용해 구현 가능,
SERIALIZABLE
- 트랜잭션이 완료 될 때까지 그 영역에 해당하는 데이터 수정(UPDATE) 및 입력(INSERT) 불가능
- 기본적으로 REPEATABLE READ 와 동일하지만 SELECT 쿼리가 전부 SELECT ... FOR SHARE로 자동 변경
  -> 모든 select row 에 대해 S Lock 이 걸리고 X Lock (update, delete 등 불가능)
  -> S Lock 걸린 row 에 update, delete 시도 시 deadlock
- 동시성이 가장 떨어지고 쉽게 Dead Lock 에 걸릴 수 있음
- 많이 사용하지 않음

💎 Rails 에서 트랜잭션 사용하는 법

ActiveRecord::Base.transaction do 
  ## 예외 발생 시 롤백 
rescue => e
 	## 예외 발생한 경우 처리 
end

transaction(isolation: :?)

:read_uncommited / :read_commited / :repeatable_read / :serializable

🌟 save, save! 메서드 차이

save : 저장 성공 여부를 true, false 로만 리턴

save! : 저장이 실패할 경우 예외 발생

하기 때문에 트랜잭션 안에서는 save! 를 사용해야함

☕️ 스프링에서 트랜잭션 사용하는 법

@Transactional

스프링에선 해당 어노테이션을 메소드, 클래스 위에 사용

교착상태 (DeadLock)

DeadLock 이란 ?

: 두 트랜잭션이 각각 Lock을 설정하고 다음 서로의 Lock에 접근하여 값을 얻어오려고 할 때 이미 각각의 트랜잭션에 의해 Lock이 설정되어 있기 때문에 양쪽 트랜잭션 모두 영원히 처리가 되지않게 되는 상태

EX)

트랜잭션 A가 Resoucre1 데이터를 수정 (Resoucre1 에 X Lock) -> Resocure2 데이터 수정

트랜잭션 B가 Resoucre2 데이터를 수정 (Resoucre2 에 X Lock) -> Resocure1 데이터 수정

하려고 할 때 A는 Resource2 에 베타 Lock 을 B 는 Rescoure1 에 베타 Lock 을 걸려고 하는데 이미 베타 Lock 이 걸려 있으니 영원히 풀리지 않음

=> 그렇기 때문에 교착상태 (DeadLock) 이 발생하는 경우 DBMS 가 한 트랜잭션에 에러를 일으키면서 문제를 해결

DeadLock 발생 조건

한 시스템 내 네가지 조건이 동시에 성립할 때 발생 => 하나라도 성립하지 않도록 만든다면 교착 상태 해결 가능

상호 배제
한번에 하나의 프로세스만 해당 자원을 사용할 수 있다. 즉, 자원을 동시에 사용할 수 없는 경우
➡️ 여러 개의 프로세스가 공유 자원을 사용하도록 함
점유 대기
자원을 붙잡은 상태로 다른 자원을 기다리고 있다
자원을 최소한 하나 보유하고, 다른 프로세스에 할당된 자원을 점유하기 위해 대기하는 프로세스가 존재해야 한다
➡️ 프로세스가 실행되기 전 필요한 모든 자원을 할당
비선점
이미 할당된 자원을 강제로 빼앗을 수 없다
➡️ 자원을 점유하고 있는 프로세스가 다른 자원을 요구할 때 점유하고 있는 자원을 반납하고, 요구한 자원을 사용하기 위해 기다리게 한다.
순환 대기
대기 프로세스의 집합이 순환 형태로 자원을 대기해야 한다
➡️ 자원에 고유한 번호를 할당하고, 번호 순서대로 자원을 요구하도록 한다

낙관적 락(optimistic Lock)과 비관적 락(pessimistic Lock)

낙관적 락 (optimistic Lock)

: 충돌이 발생하지 않는다고 낙관적으로 가정: DB 가 제공하는 락 기능이 아니라 appllication 에서 제공하는 버전 관리 기능을 사용; version 등의 구분 컬럼으로 충돌을 예방; 트랜잭션을 커밋하는 시점에 충돌을 알 수 있음; 최종 업데이트 과정에서만 락을 점유하기 때문에 락 점유 시간을 최소화하여 동시성을 높일 수 있다

UPDATE DUAL SET VERSION = VERSION + 1 WHERE VERSION = 1

두 트랜잭션(A, B)이 거의 동시에 어떤 ROW를 변경하려고 하는 상황
A가 살짝 먼저 접근하고 바로 뒤이어 B가 접근한다.
A가 해당 ROW 와 version 를 UPDATE (선수치기)
B가 커밋 시점에 해당 ROW를 업데이트 하려고 version 을 체크해 보니.. 처음과 다른경우
어플리케이션은 ObjectOptimisticLockingFailureException 을 발생시키고 첫번째 A의 커밋만 적용하여 정합성을 유지한다.
실패된 커밋은 Application level 에서 후처리

💎 루비에서 낙관적 락 (optimistic Lock) 사용하기

테이블에 lock_version 필드 추가
1 t.integer :lock_version, default: 0
➡ 기본값은 0 으로 설정해줘야 함
➡ ActiveRecord::StaleObjectError => e 예외 발생

☕️ JPA에서 낙관적 락 (optimistic Lock) 사용하기

@Version

비관적 락 (perssimistic Lock)

: Repeatable Read, Serializableable 정도의 격리성에서 가능: 트랜잭션이 시작될 때 Shared Lock 또는 Exclusive Lock을 걸고 시작하는 방법; DB 가 제공하는 락 사용; 데이터 수정 즉시 트랜잭션 충돌을 알 수 있음; 교착 상태와 같은 문제 야기

###

낙관적 락 vs 비관적 락

낙관적 락 (optimistic lock) 은 트랜잭션을 필요로 하지 않음

클라이언트가 서버에 정보를 요청
서버에서는 정보를 반환
클라이언트에서 이 정보를 이용하여 수정 요청
서버에서는 수정 적용 ( 충돌 감지 가능 )

의 경우에도 충돌 감지를 할 수 있음. 비관적 락 (perssimistic lock) 같은 경우에는 2번에서 해당 row 에 S-Lock 이 걸리고, 3번에서는 해당 row 에 X-Lock 이 날라가기 때문에 트랜잭션을 유지 할 수 없음

낙관적 락이 성능적으로도 좋음

=> 충돌이 많이 일어나지 않을 것이라고 보여지는 곳에 사용하면 좋은 성능 기대 가능

하지만 낙관적 락의 최대 단점은 롤백임. 만약 충돌이 났다고 한다면 이를 해결하려면 개발자가 수동으로 롤백처리를 한땀한땀 해줘야합니다. 비관적 락이라면 트랜잭션을 롤백하면 끝나는 작업이지만 낙관적 락은 그렇지 않음.

오늘회 재고

결제 트랜잭션 시작 시 order_payment_request.update!(payment_status: '결제처리중') 으로 optimistic lock 발생
재고는 native query

  
ActiveRecord::Base.connection.exec_query(
<<-SQL
UPDATE #{stock_table}
SET assigned_quantity = assigned_quantity + #{product_unit.quantity.to_i}
WHERE id = #{stock.id}
SQL
)

  
# 하나의 트랜잭션안에서 바라보는 데이터는 커밋되지 않은 데이터이다.
# 이 함수는 update 문이 실행된 이후 반영된 내용이 있는지 체크하고 처리한다.
# update가 실행될때 stock, daily_stocks 에는 row 락이 걸려있는 상태이다.
ActiveRecord::Base.transaction(requires_new: true) do
@result = ActiveRecord::Base.connection.exec_query(
<<-SQL
SELECT quantity - assigned_quantity
FROM #{table_name}
WHERE id = #{stock.id}
SQL
)
en	`j

Propagation.REQUIRES_NEW로 설정되었을 때에는 매번 새로운 트랜잭션을 시작한다.(새로운 연결을 생성하고 실행한다.) 만약, 호출한 곳에서 이미 트랜잭션이 설정되어 있다면(기존의 연결이 존재한다면) 기존의 트랜잭션은 메서드가 종료할 때까지 잠시 대기 상태로 두고 자신의 트랜잭션을 실행

레퍼런스

트랜잭션, 트랜잭션 격리수준(Isolation Level)

트랜잭션 수준 읽기 일관성

트랜잭션의 격리 수준(isolation Level)이란?

[MySQL] 트랜잭션의 격리 수준(isolation level)

Lock으로 이해하는 Transaction의 Isolation Level

Mysql innoDB Lock, isolation level과 Lock 경쟁

[MySQL]MySQL 벼락치기(5) - 갭락(Gap Lock)과 넥스트 키 락(Next-Key Lock)

MySQL InnoDB lock & deadlock 이해하기