MariaDB 트랜잭션 처리

마리아 DB

엔진

Mysql, MariaDB는 InnoDB를 사용하고 있음.

MVCC(Multi-Version Concurrency Control) 방식으로 인해 논리피터블 리드와 유사한 상황이 발생 가능.

MVCC?

• Multi Version Concurrency Control의 약자로 다중 버전 동시성 제어라고 한다.
• Locking의 성능 문제 때문에 탄생하게 되었다.
• Lock을 사용하지 않고 일관된 읽기를 제공하는 것이 주 목적이다.
• 사용자는 MVCC에서 데이터에 접근할 때 Snapshot을 읽는다.
• 이 Snapshot에 대해선 변경이 완료(commit) 될 때 까진 다른 사용자는 볼 수 없다.
• 사용자가 데이터를 업데이트하면 새로운 버전의 데이터를 버퍼에 넣는다.
• 이전 버전의 데이터는 undo영역에 생성된다.
• 사용자는 latest version의 데이터만 읽는다.
• locking 방식을 사용하지 않아 성능 상의 이점이 있다.
• 사용하지 않는 데이터(not latest data)를 정리하는 시스템이 필요하다.
• 데이터 버전이 충돌하게 되면 애플리케이션에서 처리해야 한다.

---

Update, Delete 쿼리 중 타 트랜잭션 처리 시나리오

• 세팅
  • DB커넥션 1 - Manual_commit (Repeatable read)
  • 커넥션 2 - Auto_commit
  • 마리아 DB
• 테이블 (DDL)

CREATE TABLE `USER_CHARGE` 
( `USER_NO` bigint unsigned NOT NULL COMMENT '유저 번호',
  `CHARGE_AMOUNT` bigint unsigned DEFAULT '0' COMMENT '충전금',
   PRIMARY KEY (`USER_NO`) 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

 

• 데이터 세팅 (DML)

INSERT INTO USER_CHARGE (USER_NO, CHARGE_AMOUNT) VALUES (1, 5000); 
INSERT INTO USER_CHARGE (USER_NO, CHARGE_AMOUNT) VALUES (2, 15000); 
INSERT INTO USER_CHARGE (USER_NO, CHARGE_AMOUNT) VALUES (3, 25000);

 

 

참고되는 블로그 내용을 확인 했을 때, MVCC의 특징을 통해서 다른 쓰레드의 트랜잭션은 COMMIT 전의 데이터(undo 로그)를 보게되는것을 확인 하였다.

• UNDO 로그 테이블

 

 

• 트랜잭션 A의 Update/Delete 쿼리 중, 트랜잭션 B의 데이터 확인

트랜잭션 A

-- 1) 데이터 업데이트 
UPDATE USER_CHARGE SET 
CHARGE_AMOUNT = CHARGE_AMOUNT + 1000 
WHERE USER_NO = 1;

-- 2) 데이터 확인, 5000 + 1000으로 6000 
SELECT * 
FROM USER_CHARGE 
WHERE USER_NO = 1;

-- 4) COMMIT
COMMIT;

트랜잭션 B

-- 3) 데이터 확인, 5000
SELECT *
FROM USER_CHARGE
WHERE USER_NO = 1;

-- 5) 데이터 확인, 6000
SELECT *
FROM USER_CHARGE
WHERE USER_NO = 1;

 

여기서 궁금한것은 사내에서 주로 사용하는 현재 상태를 확인하고 업데이트를 진행하는 로직이 어떤식으로 데이터 정합성을 이루는지 이다. 그래서 다른 쓰레드의 트랜잭션이 UPDATE를 했을 때에 대한 테스트를 진행하고 간단하게 아래처럼 세팅하여 확인을 해보았다.

 

 

• 트랜잭션 A의 Update/Delete 쿼리 중, 트랜잭션 B의 데이터 확인 + Update

트랜잭션 A

-- 1) 데이터 업데이트
UPDATE USER_CHARGE SET
CHARGE_AMOUNT = CHARGE_AMOUNT + 1000
WHERE USER_NO = 1;

-- 2) 데이터 확인, 6000 + 1000으로 7000
SELECT *
FROM USER_CHARGE
WHERE USER_NO = 1;

-- 5) COMMIT
COMMIT;

트랜잭션 B

-- 3) 데이터 확인, 6000
SELECT *
FROM USER_CHARGE
WHERE USER_NO = 1;

-- 4) 데이터 업데이트, 5)번의 COMMIT이 되기전까지 Lock
-- 추가로 COMMIT이 되고 나서는 업데이트 대상 없음, 1)번에서 충전금이 7000원이 되었기 때문
UPDATE USER_CHARGE SET 
CHARGE_AMOUNT = 6000 + 3000
where CHARGE_AMOUNT = 6000
AND   USER_NO = 1;

-- 6) 데이터 확인, 7000
SELECT *
FROM USER_CHARGE
WHERE USER_NO = 1;

• COMMIT을 기다리는 애처로운 트랜잭션 B..

 

---

이유?

1. 트랜잭션 A가 USER_CHARGE 테이블에서 USER_NO = 1인 행을 조회하고, 그 데이터를 수정 (UPDATE).
   그러나 커밋하지 않은 상태임으로, 트랜잭션 A가 업데이트하려는 행에 대해 갱신 락(Update Lock, X-lock)을 설정.
2. 트랜잭션 B는 같은 USER_CHARGE 테이블의 같은 행을 업데이트하려고 시도하나,
   트랜잭션 A가 이미 해당 행에 대해 레코드 락을 걸고 커밋하지 않은 상태이기 때문에, 트랜잭션 B는 트랜잭션 A의 락 해제(커밋 또는 롤백)를 기다림.
   • COMMIT 전 락 확인
     

     

   • LOCK_STATUS
     메타 데이터 잠금 하위 시스템의 잠금 상태로 아래와 같다.
     
     • PENDING (대기 중)
       현재 트랜잭션이 잠금을 요청했지만, 다른 트랜잭션이 이미 해당 리소스에 대한 잠금을 가지고 있어 대기하고 있는 상태입니다. 잠금이 해제되거나 타임아웃이 발생할 때까지 기다리게 됩니다.
     • GRANTED (허가됨)
       요청한 트랜잭션이 잠금을 성공적으로 획득한 상태입니다. 해당 리소스에 대한 접근이 허용되었으며, 다른 트랜잭션은 대기해야 합니다.
     • VICTIM (희생자)
       트랜잭션 충돌이나 교착 상태(Deadlock)를 해결하기 위해, 특정 트랜잭션이 중단되어야 할 때, 중단된 트랜잭션을 희생자로 표시하는 상태입니다. 일반적으로 데이터베이스 시스템이 교착 상태 해결을 위해 트랜잭션을 강제로 중단할 때 발생합니다.
     • TIMEOUT (시간 초과)
       트랜잭션이 일정한 시간 내에 잠금을 획득하지 못하여, 시간 초과가 발생한 상태입니다. 이 경우, 시스템은 대기 중인 트랜잭션을 중단하고 롤백할 수 있습니다.
     • KILLED (종료됨)
       시스템이나 사용자가 트랜잭션을 강제로 종료한 상태입니다. 강제 종료된 트랜잭션은 더 이상 실행되지 않으며, 롤백이 진행될 수 있습니다.

락 설명

• 레코드 락(갱신 락):트랜잭션 A가 UPDATE를 실행하면서 해당 행에 대해 레코드 락 설정하여, 트랜잭션 B가 동일한 행을 업데이트하려고 시도할 때, 트랜잭션 A의 갱신 락이 아직 해제되지 않았기 때문에 트랜잭션 B는 대기 상태로 전환.
  트랜잭션이 수정하려는 특정 행에 대해 걸리며, 이는 기본 키 또는 유니크 키를 기준으로 동작.
• 인텐트 락(Intent Lock): 인텐트 락은 테이블 수준에서 트랜잭션이 행 락을 걸기 전에 설정하는 락. row에 대해서 어떤 row-level에 락을 걸 건지 미리 table-level에 걸어두는 락.
  트랜잭션 A가 특정 행에 대해 갱신 락을 걸기 위해 인텐트 락도 병행 사용. 트랜잭션 B는 이 락을 감지하고 해당 행에 대한 갱신 락을 대기하는 상태로 전환.

---

결론

트랜잭션 A가 커밋되지 않은 상태에서 트랜잭션 B가 동일한 데이터를 수정하려 할 때, 트랜잭션 A는 갱신 락을 설정하여 해당 행의 수정을 막음.

트랜잭션 B는 트랜잭션 A의 락 해제가 될 때까지(커밋 또는 롤백) 대기 상태가 되며, 이를 갱신 락 대기 이라 부름.

따라서, 트랜잭션 B에서는 갱신 락에 의해서 데이터 무결성을 지킬 수 있는것으로 확인 할 수 있음.

 

---

참고

https://velog.io/@juy4556/DatabaseMVCC와-InnoDB기반의-MySQL