개발 학습 실습실

이 사이트의 실습은 자바스크립트를 켜야 움직입니다. 아래는 같은 내용을 글로 적은 것입니다.

공부 › DB › 트랜잭션과 잠금

DB의 교착 상태

두 트랜잭션이 서로를 기다리면 DB는 어떻게 끝내나

트랜잭션 A는 계좌 1에서 2로 100을, 트랜잭션 B는 계좌 2에서 1로 50을 옮깁니다. 둘이 행을 서로 반대 순서로 잠그면 서로를 기다리게 됩니다. 자바 스레드라면 그대로 멈춰 버리지만, MySQL InnoDB는 이 상황을 스스로 찾아내 한쪽을 롤백시키고 오류를 돌려줍니다.

실행으로 대조 · MySQL 8.4.11에서 세션 두 개로 열 가지 조합을 실행해 대조

이 실습에 나오는 개념

교착 상태(Deadlock)
둘 이상의 스레드(또는 트랜잭션)가 서로 상대가 가진 자원을 기다리느라 영원히 진행하지 못하는 상태다. A가 자원 1을 잡고 2를 기다리는데 B가 자원 2를 잡고 1을 기다리는 경우가 전형적이다. 자원을 항상 같은 순서로 잡도록 하면 예방할 수 있다.
교착 감지와 희생 트랜잭션
데이터베이스가 서로의 락을 기다리는 트랜잭션들의 고리를 스스로 찾아내어 그중 하나를 강제로 롤백시키는 기능이다. MySQL InnoDB는 트랜잭션이 락을 기다리게 될 때 누가 누구를 기다리는지를 따라가 고리가 있는지 검사하고, 고리가 있으면 한 트랜잭션을 골라 통째로 롤백시킨 뒤 오류 1213(Deadlock found)을 돌려준다. 이때 롤백되는 쪽을 희생 트랜잭션이라고 부르며, InnoDB는 고친 행이 적은 작은 트랜잭션을 고르려 한다. 살아남은 쪽은 오류 없이 계속 진행한다. 희생된 트랜잭션이 한 일은 모두 취소됐으므로, 애플리케이션이 그 트랜잭션을 처음부터 다시 실행해야 한다.
락 대기 시간 초과
다른 트랜잭션이 쥔 락이 풀리기를 정해진 시간까지 기다렸는데도 얻지 못해 그 문장이 실패하는 것이다. MySQL InnoDB에서는 innodb_lock_wait_timeout(기본 50초)이 지나면 오류 1205(Lock wait timeout exceeded)를 돌려준다. 교착과 달리 서로 기다리는 고리가 없어도 생기며, 상대가 트랜잭션을 오래 열어 두는 것이 흔한 원인이다. 기본 설정에서는 기다리던 문장 하나만 취소되고 트랜잭션은 그대로 살아 있으므로, 앞에서 고친 내용과 락이 남는다. 그래서 이 오류를 받으면 직접 롤백한 뒤 다시 시도할지 정해야 한다.
공유 락과 배타 락
공유 락(S)은 읽기를 위한 잠금으로 여러 트랜잭션이 동시에 가질 수 있다. 배타 락(X)은 쓰기를 위한 잠금으로 한 트랜잭션만 가질 수 있으며, 배타 락이 걸린 데이터에는 다른 트랜잭션이 공유 락도 배타 락도 걸 수 없다.
트랜잭션
여러 데이터 변경 작업을 하나의 단위로 묶어, 전부 반영되거나(커밋) 전부 취소되게(롤백) 하는 것이다. 계좌 이체에서 출금과 입금 중 하나만 반영되는 중간 상태를 막는다. 데이터베이스가 정합성을 지키는 가장 기본적인 수단이다.

이렇게 바꿔 보세요

처음 설정으로 실행하면

  1. A: UPDATE … - 100 WHERE id = 1: 트랜잭션 A가 계좌 1에서 100을 뺍니다. 행을 고치려면 그 행에 배타 락을 걸어야 하고, 이 락은 A가 커밋하거나 롤백할 때까지 풀리지 않습니다.
  2. B: UPDATE … - 50 WHERE id = 2: 트랜잭션 B가 계좌 2에서 50을 뺍니다. 계좌 2의 행에 배타 락을 겁니다. 아직은 서로 다른 행이라 아무도 기다리지 않습니다.
  3. A: UPDATE … + 100 WHERE id = 2: A가 계좌 2에 100을 더하려 합니다. 그 행의 락을 B가 쥐고 있어 기다립니다. A는 계좌 1의 락을 쥔 채로 기다립니다.
  4. B: UPDATE … + 50 WHERE id = 1: B가 계좌 1에 50을 더하려 합니다. 그 행의 락은 A가 쥐고 있는데, A는 B가 끝나기를 기다리는 중입니다. A는 B를, B는 A를 기다리는 고리가 생겼습니다. 그대로 두면 둘 다 영원히 진행하지 못합니다.
  5. InnoDB의 교착 감지: InnoDB는 트랜잭션이 락을 기다리게 되면 누가 누구를 기다리는지를 따라가 고리가 있는지 검사합니다. 고리를 찾았으므로 둘 중 하나를 골라 롤백시킵니다. 두 트랜잭션이 한 일의 양이 같을 때 어느 쪽을 고르는지는 문서에 정해져 있지 않습니다. 이 화면은 B가 골라진 경우를 보입니다. 대조한 MySQL에서는 30번 모두 나중에 기다리기 시작한 B였습니다.
  6. B: 오류 1213 (Deadlock found when trying to get lock; try restarting transaction)을 받습니다. 기다리던 문장만이 아니라 B의 트랜잭션 전체가 롤백됩니다. 앞에서 계좌 2의 잔액을 고친 것도 취소되고 쥐고 있던 락이 모두 풀립니다. 락 대기 시간 제한(기본 50초)을 채우지 않고 곧바로 일어납니다.
  7. A: 기다리던 문장(UPDATE … + 100 WHERE id = 2)이 실행됩니다. B가 롤백되면서 락이 풀렸기 때문입니다. A는 오류를 받지 않았고, 교착이 있었다는 것도 알지 못합니다.
  8. A: COMMIT: A의 이체(계좌 1에서 2로 100)가 반영됩니다. 잔액은 계좌 1이 900, 계좌 2가 1100입니다.
  9. B: ROLLBACK을 보내고 끝냅니다. 이미 InnoDB가 롤백시킨 뒤라 더 취소할 것은 없습니다. B의 이체(계좌 2에서 1로 50)는 어디에도 반영되지 않았습니다. 사용자의 요청 하나가 사라진 셈입니다.

실행 결과

코드

-- account 테이블: 계좌 1과 계좌 2의 잔액은 1000씩

-- 트랜잭션 A: 계좌 1에서 2로 100을 옮긴다
START TRANSACTION;
UPDATE account SET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- 트랜잭션 B: 계좌 2에서 1로 50을 옮긴다 (A와 같은 때에 실행)
START TRANSACTION;
UPDATE account SET balance = balance - 50 WHERE id = 2;
UPDATE account SET balance = balance + 50 WHERE id = 1;
COMMIT;

-- 오류를 받은 쪽은
ROLLBACK;                          -- 하고 끝낸다

이 모형의 전제

열 가지 조합(순서 세 가지에서 의미가 있는 설정만 남긴 것)을 이 PC의 MySQL 8.4.11(InnoDB, 격리 수준 REPEATABLE READ, innodb_deadlock_detect 켜짐, innodb_rollback_on_timeout 꺼짐)에서 세션 두 개로 화면과 같은 순서로 실행해, 어느 쪽이 어떤 오류 코드를 받는지, 오류 직후 그 트랜잭션에 변경과 락이 남아 있는지, 최종 잔액이 화면과 일치하는 것을 확인했습니다. 교착에서는 오류 1213이 몇 밀리초 만에 왔고(락 대기 제한은 20초로 둔 상태), 오류를 받은 세션이 자기가 고쳤던 행을 다시 읽으면 원래 값 1000이었으며 쥐고 있는 레코드 락이 0개였습니다. 락 대기 시간 초과는 기본값 50초를 기다리는 대신 그 세션의 innodb_lock_wait_timeout을 2초로 줄여 실행했고, 2초 뒤 오류 1205가 온 다음에도 그 세션이 고친 값 950과 레코드 락 1개가 남아 있었습니다. 일의 양이 같은 교착에서 어느 쪽이 롤백되는지는 문서가 정해 두지 않았으므로 결과로 단정하지 않습니다. 30번 되풀이했을 때 30번 모두 나중에 기다리기 시작한 B가 롤백됐고, B가 다른 행 50개를 먼저 고친 경우에는 30번 모두 A가 롤백됐습니다. "작은 트랜잭션을 고르려 한다"와 "교착은 트랜잭션 전체를, 락 대기 시간 초과는 그 문장만 롤백한다"는 MySQL 8.4 참조 설명서 17.7.5.2 Deadlock Detection과 17.20.5 InnoDB Error Handling의 설명과도 맞습니다. 서버 설정을 바꾸면 달라지는 것(교착 감지를 끈 경우, innodb_rollback_on_timeout을 켠 경우)은 다루지 않았습니다. 두 트랜잭션이 정확히 엇갈리는 순서를 가정했으며, 실제로는 타이밍에 따라 교착 없이 지나가는 경우가 더 많습니다.

앞 실습: 재고 차감과 락 · 다음 실습: Undo와 Redo 로그

이 실습에 나오는 개념

이 실습에서 보는 것

설정

코드


      
    


    

      
그림

      

      

    


    

      

      

      

    


  


  

    

      
실행 비교

      

      

    

    

      
이렇게 바꿔 보세요