DBMS와 트랜잭션

Uicheon·2024년 10월 28일

DBMS 격리수준 트랜잭션 항해 항해플러스백엔드

개미 날개 기르기

목록 보기

1/2

1. 질문

만약에 락을 걸지 않고, 트랜잭션도 걸지 않고, 한 유저에 업데이트 쿼리를 동시에 따닥 날리면 동시에 실행이 되나요?

남들이 Yes라고 할때 당당히 No라고 외치는 새77l,,

여기서 트랜잭션도 걸지 않고는 트랜잭션 커밋을 하지 않고 플러쉬를 한 중간 상태라고 정의한다.

그럼 실제로 수행해보자.

MySQL

트랜잭션을 manual로 변경뒤, 동시에 한 레코드에 업데이트 요청을 날린다.

[40001][1205] Lock wait timeout exceeded; try restarting transaction

MySQL은 조금 더 명시적으로 Lock wait timeout exceeded라고 표현해준다.

으잉? 왜 그럴까?

답은 X-lock이 걸려 있어서다!

2. Database Lock의 종류

1. 공유락 (S-lock, Shared Lock)

데이터 읽을 때 사용하는 lock
공유락 끼리는 여러 사용자가 동시에 읽기 가능
공유락이 먼저 설정된 데이터에 배타락 불가능

2. 배타락 (X-lock, Exclusive Lock)

데이터 변경시 사용되는 lock
트랜잭션이 완료될 때 까지 유지된다 (e.g. select for update)
배타락이 적용 된다면, 다른 리소스는 접근 못하고 대기
배타락은 이미 다른 트랜잭션 내에서 사용하고 있는 데이터에 접근해 Lock을 설정할 수 없음

"아하! 위 1번에서는 같은 레코드에 업데이트 요청을 걸었고, 커밋하기 이전에 다른 리소스가 접근하려 해서 에러가 발생했구나!"

3. DB 격리 수준

결국 오고야 마는가..

위의 문제들을 정확히! 이해하려면 결국 DB의 격리 수준에 대해 알아야만 한다.

DB 격리 수준은 DBMS마다 다르다.
MySQL도 스토리지 엔진을 MyISAM을 쓰는지, InnoDB를 쓰는지, PostgreSQL을 쓰는지, DBMS마다, DBMS의 버전마다 다를 것이다.

아래 샘플 테이블과 함께 굉장히 유명한 4가지 격리 수준을 같이 알아보자.

create table hhplus.concert
(
    id   int auto_increment
        primary key,
    name varchar(255) not null
);

아래 실습은 모두 MySQL InnoDB 기준이다.

1. Read Uncommitted

가장 약한 격리수준이다. 이름에서 알 수 있듯이, 커밋되지 않은 것조차 읽는 격리 수준이다.

커밋되지 않은 구문을 읽으면 어떤 문제가 생길까?

그림으로 상황을 재현해보면, 트랜잭션1이 시작되고, update 구문을 날린 뒤, 아직 커밋하지 않은 변경된 행에 접근해서 읽어왔다.

사실, tx1이 그대로 commit 된다면 큰 문제는 없을 것이다.
하지만 롤백 된다면? tx2는 잘못된 정보를 가지고 로직을 수행할 것이다.

이처럼 어떤 트랜잭션에서 처리한 작업이 완료(commit)되지 않았는데도 다른 트랜잭션에서 볼 수 있는 현상을 더티 리드(Dirty Read)라고 한다.

더티 리드가 허용되는 격리 수준이 Read Uncommitted이다. 어떤 RDBMS에서는 인정도 안해주는 정합성에 문제가 많은 격리 수준이다. (~~오X클~~)

Read Uncommitted - 오손 읽기 부정합을 발생시키는 실습

-- tx1
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

update concert set name='콘서트101' where id=1;

--tx 2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

select * from concert where id =1;

tx1이 커밋되지 않았음에도, Dirty Read(오손 읽기)가 발생하여 이름이 콘서트101로 바뀐 모습

2. Read Committed

오라클 DBMS에서 기본적으로 제공하는 격리 수준이며, 온라인 서비스에서 가장 많이 선택되는 격리 수준이다. 어떤 트랜잭션에서 데이터를 변경했다 하더라도, COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.

Read Committed에서는 어떤 트랜잭션이 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수 없다.

Read Committed - 오손 읽기 부정합이 없음을 보이는 실습

-- tx1
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

update concert set name='콘서트101' where id=1;

-- tx2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

select * from concert where id =1;

tx1이 커밋되지 않았으므로, Dirty Read(오손 읽기)는 발생하지 않았다.

여기서 Read Committed 격리 수준에서도 Non-Repeatable Read가 불가능하다라는 부정합 문제가 있다.

아니. 커밋된것만 잘 읽으면 됐지, 도대체 Non-Repeatable Read 가 뭘까!

아래 다이어그램을 잠시 보자

TX2가 먼저 트랜잭션을 시작했고, TX1이 트랜잭션을 이후에 시작하고 먼저 커밋했다. TX2는 한 트랜잭션에 처음 읽은 콘서트의 이름(name)과 두번째 읽은 콘서트의 이름이 다른 것을 봐야만 한다.

이게 의도된 동작이 맞는가? 아니다!

Read Committed - 반복 읽기 불가(Non-Repeatable Read) 부정합을 보이는 실습

-- tx1, tx2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- tx2
begin;
select * from concert where id =1; -- 콘서트1 반환
-- tx1
begin;
update concert set name='콘서트101' where id=1;
commit;
-- tx2
select * from concert where id =1; -- 콘서트101 반환

Repeateable Read란?

사용자가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야한다.

🤔 음..이게 꼭 필요할까? 라고 생각한다면,
다른 트랜잭션에서 입금, 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회한다고 가정해보자.
그런데 'Repeatable Read'가 보장되지 않기 때문에, 총합을 계산하는 SELECT 쿼리는 실행될 때 마다 다른 결과를 가져올 것이다.
중요한 것은 사용중인 트랜잭션 수준에 의해 실행하는 SQL 문장이 어떤 결과를 가져오게 되는지 정확히 예측할 수 있어야 한다는 것이다

트랜잭션 내에서 실행되는 SELECT 문장과 트랜잭션 없이 실행되는 SELECT 차이

Read Committed 수준에서는 트랜잭션 내에서 실행되는 SELECT 문장과, 트랜잭션 외부에서 실행되는 SELECT 문장의 차이가 별로 없다. 하지만 Read Repeatable 격리 수준에서는 기적으로 SELECT 쿼리 문장도 트랜잭션 범위내에서만 작동한다. 즉, BEGIN 명령으로 트랜잭션을 시작한 상태에서 온종일 동일한 쿼리를 반복해서 실행해봐도, 동일한 결과만 보게 된다. (아무리 다른 트랜잭션에서 그 데이트러를 변경하고 COMMIT을 실행하더라도 말이다)

3. Repeatable Read

Repeatable Read는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용된다. 바이너리 로그를 가진 MySQL 서버에서는 최소 Repeatable Read 격리 수준 이상을 사용해야한다. 이 격리수준에서는 Read Committed 격리 수준에서 발생하는 Non-Repeatable Read 부정합이 발생하지 않는다.

InnoDB 스토리지엔진은 트랜잭션이 Rollback 될 가능성에 대비해 변경되기 전 레코드를 언두(Undo)공간에 백업해두고, 실제 레코드 값을 변경한다. 이런 변경 방식을 MVCC라고 한다.

모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호(순차값)을 가지며, 언두 영억에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함돼 있다. 그리고 언두 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.

Repeateable Read 격리 수준에서는 MVCC를 보장하기 위해, 실행중인 트랜잭션 가운데 가장 오래된 트랜잭션 번호보다 트랜잭션 번호가 앞선 언두 영역 데이터의 삭제는 할 수 없다. 그렇다고 가장 오래된 트랜잭션 번호 이전의 트랜잭션에 의해 변경된 모든 언두 데이터가 필요한 것은 아니다. 더 정확하게는 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존되어야 한다.

아래 그림과 함께 이해해보자

위의 반복 읽기 불가 부정합 예시와 다르게, 트랜잭션이 열린 상태에서 SELECT 요청을 하고 있으므로, 현재 트랜잭션 번호보다 작은 번호에서 변경된 것만 보게 된다.

그림에서는 언두 영역의 백업 데이터가 하나처럼 보이지만, 사실 하나의 레코드에 대해 백업이 하나 이상 존재할 수 있다.
한 사용자가 트랜잭션을 시작한 뒤, 장시간 트랜잭션을 종료하지 않으면, 언두 영역이 백업된 데이터로 무한정 커질수도 있다.

Repeatable Read - 반복 읽기 부정합이 없음을 보이는 실습

-- tx1, tx2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- tx2
begin;
select * from concert where id =1; -- 콘서트1 반환
-- tx1
begin;
update concert set name='콘서트101' where id=1;
commit;
-- tx2
select * from concert where id =1; -- 콘서트1 반환

두 쿼리 모두 "콘서트1"을 반환한다

Repeatable Read 수준에서도 Phantom Read 부정합이 발생할 수 있다.
tx1이 테이블에 삽입하고, 도중에 tx2가 SELECT FOR UPDATE쿼리로 테이블을 조회할 때 어떤 결과가 나오는지 보자.

다시. 사용자가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때는 항상 같은 결과를 가져와야한다.

Repeatable Read - 유령 읽기(Phantom-Read) 부정합을 보이는 실습

-- tx1, tx2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- tx2
begin;
select * from concert where id>=2 for update; -- 콘서트2 반환
-- tx1
begin;
insert into concert values (null, '콘서트3');
commit;
-- tx2
select * from concert where id>=2; -- 콘서트2, 콘서트3 반환

일반적인 MVCC를 지원하는 RDBMS는 위와 같이 작동하지만, MySQL은 다릅니다.

근데 MySQL 공식문서 - 격리수준에서, 갭 락/넥스트 키 락을 이용해 Phantom Read 부정합이 없도록 한다고 합니다.

그래서 실제 insert into ... 구문에서 락이 걸려버립니다.

Phantom Read를 재현하려면 다음 순서와 같이 실행해야 합니다.

tx2 - select
tx1 - insert
tx2 - select for update

-- tx1, tx2
SELECT @@SESSION.transaction_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- tx2
begin;
select * from concert where id>=2 -- 콘서트2 반환
-- tx1
begin;
insert into concert values (null, '콘서트3');
commit;
-- tx2
select * from concert where id>=2 for update; -- 콘서트2, 콘서트3 반환

4. Serializable

가장 단순하고 가장 엄격하다. 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어진다. InnoDB 테이블에서 기본적으로 순수한 SELECT (INSERT ... SELECT 또는 CREATE TABLE) 은 아무런 레코드 잠금도 설정하지 않고 실행된다.

InnoDB 메뉴얼에서 자주 나타나는 Non-locking consistent read(잠금이 필요 없는 일관된 읽기라는 말이 이를 의미하는 것이다.

하지만 트랜잭션 수준이 Serializable로 설정되면 읽기 작업도 공유 잠금(S-lock)을 얻어야하며, 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다.

즉, 한 트랜잭션에 읽고 쓰는 레코드를 다른 트랜잭션에서는 절대 접근할 수 없을 것이다.

하지만 InnoDB 스토리지 엔진에서는 갭 락과 넥스트 키 락 덕분에 Repeatable Read 격리 수준에서도 이미 "Phantom Read"가 발생하지 않기 때문에" 굳이 Serializable
을 사용할 필요성은 없어보인다.

Uicheon

컨셉입니다~

다음 포스트