트랜잭션과 잠금

진성대·2025년 3월 13일
mysql
0

SQL

목록 보기
7/10

RealMySQL 책을 읽고 노션에 정리한 글을 옮긴 것입니다.

  • 잠금 - 동시성 제어 (서로 다른 세션이 한 데이터에 접근해서 결과를 변경하는 것을 막아준다. 한 세션에 한 데이터만 변경할 수 있도록)
  • 트랜잭션 - 데이터 정합성 (데이터 변경이나 처리가 트랜잭션 처음부터 끝까지 일관성을 유지할 수 있도록 한다)

1. 트랜잭션

  • MyISAM, Memory 스토리지 엔진 - 트랜잭션 지원 안한다. InnoDB 만 지원

    1.1 MySQL에서의 트랜잭션

    • MyISAM 스토리지 vs InnoDB 스토리지의 트랜잭션 차이

      mysql> CREATE TABLE tab_myisam (fdpk INT NOT NULL, PRIMARY KEY (fdpk) ENGINE=MyISAM;
mysql> INSERT INTO tab_myisam (fdpk) VALUES (3);

mysql> CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ENGINE=INNODB;
mysql> INSERT INTO tab_innodb (fdpk) VALUES (3);

--// AUTO-COMMIT 활성화
mysql> SET autocommit=ON;

mysql> INSERT INTO tab_myisam (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000) : Duplicate entry '3'  for key 'PRIMARY'

mysql> INSERT INTO tab_innodb (fdpk) VALUES (1), (2), (3);
ERROR 1062 (23000) : Duplicate entry '3'  for key 'PRIMARY'

mysql> SELECT * FROM tab_myisam;
+------+
| fpdk |
+------+
|    1 |
|    2 |
|    3 |
+------+

mysql> SELECT * FROM tab_innodb;
+------+
| fpdk |
+------+
|    3 |
+------+

    • MyISAM 스토리지 엔진과 InnoDB 스토리지 엔진 둘다 프라이머리 키 중복 오류로 INSERT 가 실패했다.

    • MyISAM 스토리지 엔진은 쿼리가 실패 했음에도 불구하고 ‘1’ 과 ‘2’ 가 INSERT 됐는데, 그 이유는 1, 2 차례대로 저장하고 ‘3’을 저장하는 순간 중복 키 오류가 발생한 것이다. 하지만 1, 2 는 이미 저장됐기 때문에 1, 2는 그대로 나두고 종료를 한다.

    • InnoDB 는 트랜잭션을 지원하기 때문에 1, 2 를 진행하고 3 을 진행하는 와중에 오류가 발생시 실행 전 상태로 복구한다.

    • MyISAM 의 트랜잭션 없이 저장되는 것을 부분 업데이트라고 한다. 부분 업데이트 된것을 롤백 처리하려면 코드가 길어지고 더러워진다.

    • DMS 트랜잭션 처리에 좋지 않은 영향을 미치는 부분

      • 커넥션 소유 하는 시간이 길어질수록 다른곳에 사용가능한 커넥션의 개수가 줄어든다.
      • 원격 서버와의 통신하는 작업은 제거하는 것이 좋다. 무한정 기다릴 경우가 발생시 해결할 수 없다.
      • 트랜잭션 분리하는 것이 좋다.

2. MySQL 엔진의 잠금

  • MySQL 엔진 레벨의 잠금은 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 MySQL 엔진에 영향을 미치지 못한다.

    2.1 글로벌 락

    • SQL 자체를 락한다. 다른 데이터베이스도 영향을 받는다.

    • MyISAM, MEMORY 스토리지 과 연관된 테이블을 mysqldump로 백업 받으려고 할때 글로벌 락을 사용한다.

    • 글로벌 락을 걸기 전에 진행 되고 있는 쓰기 작업을 다 완료된 후에 글로벌 락이 작동한다. 글로벌 락을 실행전에 테이블을 플러시 한다. 즉 관련된 데이터들을 디스크로 플러시한다.

    • SELECT 쿼리도 종료될 때까지 기다린다 - 왜?

      • SELECT 쿼리도 데이터와 잠금에 영향을 미친다.
        • SELECT 쿼리는 비록 데이터를 변경하지 않지만, 읽기 작업 중에는 해당 데이터에 대한 일관성을 유지해야한다.
        • 만약 FLUSH TABLES WITH READ LOCK 이 SELECT 쿼리 도중에 읽기 잠금을 강제로 설정하면
          • SELECT 쿼리가 참조하고 있는 데이터와 메타데이터가 디스크로 플러시 될 수 있다.
            • SELECT 쿼리가 참조 중이 데이터는 InnoDB 버퍼 풀에 적재된 데이터 페이지나 메타데이터를 참조한다.
            • 이 데이터 페이지가 더티 페이지라면, 아직 디스크에 동기화 되지 않은 상태일 수 있다.
          • SELECT 쿼리가 읽는 도중에 데이터가 변경되지 않도록 보장할 수 없다.
      • 읽기 일관성을 보장하기 위해 기다린다.
        • FLUSH TABLES WITH READ LOCK 은 실행 중인 모든 쿼리가 종료된 후에야 읽기 잠금을 설정한다.
          • SELECT 쿼리에서 읽기 작업이 끝나야 데이터가 안전하게 플러시되고 읽기 잠금이 설정된다.
          • SELECT 쿼리가 실행중인 데이터를 참조하고 있을 수 있기 때문에, 데이터의 일관성을 깨뜨리지 않기 위해 기다린다.

    • 글로벌 락은 MySQL 서버의 모든 작업을 멈추기 때문에 좀 더 나은 상태를 위해서 8.0 버전 부터는 조금 더 가벼운 글로벌 락의 필요성이 생겼다. 백업 락이 도입 됐다.

    • 백업락을 획득하면, 데이터베이스 및 테이블 등 모든 객체 생성 및 변경, 삭제가 안되며 REPAIR TABLE과 OPTIMIZE TABLE 명령, 사용자 관리 및 비밀번호 변경이 되지 않는다.

      2.2 테이블 락

    • 개별 테이블 단위로 설정되는 잠금이며 , 묵시적 경우, 명시적인 경우가 있다.

    • 명시적인 테이블락은 LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블을 락 할 수 있다.

      • 테이블 락은 MyISAM 스토리지 테이블 뿐 아니라 InnoDB 스토리지 테이블도 가능하다.

    • 묵시적인 테이블 락은 MyISAM이나 MEMORY 스토리지 테이블이 데이터 변경할때 사용된다. 자동적으로 테이블에 락을 걸어서 데이터를 변경후 자동으로 해제한다.
      - InnoDB 스토리지 엔진은 레코드 기반 락이 있기 때문에 데이터 변경 쿼리 만으로 테이블 락을 걸지 않는다. DML 이 아닌 DDL (스키마 변경) 인 경우에 테이블 락을 건다.

      2.3 네임드 락

    • GET_LOCK() 함수를 통해 임의의 문자열에 대한 잠금을 설정할 수 있다.

      -- 세션 1에서 'batch_update' 락을 설정
SELECT GET_LOCK('batch_update', 10);

-- 락을 보유한 상태에서 대량 작업 수행
UPDATE my_table SET column1 = 'new_value' WHERE column2 > 100;

-- 작업 완료 후 락 해제
SELECT RELEASE_LOCK('batch_update');
      -- 세션 2에서 'batch_update' 락을 요청
SELECT GET_LOCK('batch_update', 10);

-- 세션 1이 락을 해제하기 전까지 대기하거나, 10초가 초과되면 실패
-- 락을 획득한 후 작업 수행
UPDATE my_table SET column1 = 'another_value' WHERE column2 <= 100;

-- 작업 완료 후 락 해제
SELECT RELEASE_LOCK('batch_update');

      결국 batch_update 라는 이름으로 락을 걸었고 그 사이에 있는

      UPDATE my_table SET column1 = 'new_value' WHERE column2 > 100; 의 작업에 관한거는 다른 세션에서 건드릴 수 없다.

      2.4 메타데이터 락

    • 데이터베이스 객체(대표적으로 테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금

3. InnoDB 스토리지 엔진 잠금

  • 레코드 기반 잠금 방식 탑재
  • InnoDB 스토리지 엔진에서 사용하는 잠금을 MySQL 명령으로 보는게 불가능하지만 점차 나아지고 있는 추세이다.

    3.1 InnoDB 스토리지 엔진의 잠금

    • 레코드 기반 잠금 → 페이지 잠금 → 테이블 잠금 으로 업데이트 되는 식으로 작동 되지 않는다.

      3.1.1 레코드 락

      • 레코드 자체만을 잠근다. 하지만 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
      • 인덱스의 레코드를 잠근다는거는 InnoDB 스토리지의 버퍼 풀에 잇는 데이터 페이지의 구조가 b-tree로 되어있기 때문이다.
      • b-tree의 리프 노드에 (index, data…) 형태로 인덱스 엔트리가 형성되어있다. 그 인덱스 엔트리를 잠근다는 뜻이다.
      • PK, Unique Key 에 대해서 레코드 락을 걸고, 보조 인덱스는 넥스트 키 락, 갭 락을 사용한다.

      3.1.2 갭락

      • 레코드와 레코드 사이에 간격을 잠그는것.
      • INSERT 하기 위해 자리를 비워두기 위해서 사용된다.
      • 갭락은 넥스트 키 락의 일부로 사용된다.

      3.1.3 넥스트 키 락

      • 레코드락 + 갭락 을 합쳐 놓은 형태

      • REPEATABLE READ 와 같은 높은 일관성을 유지하면서도 팬텀 리드(Phantom Read) 를 방지하기 위해 석례된 독특한 잠금 매커니즘이다.

      • 특정 레코드(인덱스 엔트리) 와 그 앞 뒤 간격(갭) 을 잠그는 방식이다.팬텀 리드란?

        팬텀 리드는, 트랜잭션이 동일한 SELECT 쿼리를 두 번 실행했을 때, 처음 실행 시에는 없던 새로운 레코드가 두 번째 실행에서 나타나는 현상을 말합니다.

        예시: 팬텀 리드가 발생하는 경우

        -- 트랜잭션 1
START TRANSACTION;
SELECT * FROM orders WHERE id > 3;

-- 트랜잭션 2 (동시에 실행)
START TRANSACTION;
INSERT INTO orders (id, product_name, quantity) VALUES (4, 'Elderberry', 20);
COMMIT;

-- 트랜잭션 1이 다시 실행
SELECT * FROM orders WHERE id > 3;
        • 첫 번째 SELECT에서는 id > 3에 해당하는 레코드가 없었지만, 트랜잭션 2에서 새로운 레코드 id = 4가 삽입된 후, 두 번째 SELECT에서 새롭게 나타납니다.
        • 이처럼, 동일한 쿼리에서 새로운 레코드가 갑자기 나타나는 것이 팬텀 리드입니다.

        넥스트 키 락이 팬텀 리드를 방지하는 방식

        넥스트 키 락은 레코드와 갭(Gap)을 잠금으로 보호하여, 다른 트랜잭션이 해당 범위에 레코드를 삽입하거나 수정하지 못하도록 막습니다. 이를 통해 팬텀 리드가 발생하지 않습니다.

      3.1.4 자동 증가 락

      • AUTO_INCREMENT 칼럼이 사용된 테이블에 동시에 여러 레코드가 INSERT 됐을때, 레코드의 자동증가가 중복되지 않고 순차적으로 증가 되도록 하기 위해 AUTO_INCREMENT 락을 사용한다. 이 락을 할때는 테이블 수준의 잠금을 건다.
      • InnoDB 의 다른 잠금과 달리 트랜잭션과 관련 없이 INSERT나 REPACE 문장에서 AUTO_INCREMENT 값을 가져오는 순간만 락이 걸렸다가 즉시 해제된다.

      3.2 인덱스와 잠금

    • InnoDB의 잠금은 레코드를 잠그는것이 아니라 인덱스를 잠그는 것이다.

    • PK 1개이므로 PK 인덱스 엔트리가 잠길 수 있고 Secondary Index 는 여러개일 수 있기 때문에 복수로 잠길 수 도 있다.

      // first_name 칼럼만 맴버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
// KEY ix_firstname (first_name), last_name 인덱스는 없음
// employees 테이블에서 first_name='Georgi' 인 사원은 전체 253명이 있으며, 
// first_name='Georgi' 이고 last_name='Klassen' 인 사원은 1명만 있는 것을 
// 아래 쿼리로 확인할 수 있다.
mysql> SELECT count(*) FROM employees WHERE first_name='Georgi';
253

mysql > SELECT COUNT(*) FROM employees WHERE first_name='Georgi' AND last_name='Klassen';
1

// employees 테이블에서 first_name='Georgi' 이고 last_name='Klassen'인 사원의 입사일자를 
// 오늘로 변경하는 쿼리를 실행해보자.
mysql> UPDATE employees SET hire_date=NOW() WHERE first_name='Georgi' 
AND last_name = 'Klassen';
// 이렇게 실행하면 UPDATE 를 하기 위해서 first_name='Georgi' 인 애들253 개가 레코드 락이 걸린다
// last_name 이란 인덱스가 없기 때문에 253 개가 잠금이 걸리는것이다.

      3.3 레코드 수준의 잠금 확인 및 해제

4. MySQL 의 격리 수준

DIRTY READNON-REPEATABLE READPHANTOM READ
READ UNCOMMITTED발생발생발생
READ COMMITTED없음발생발생
REPEATABLE READ없음없음발생(InnoDB 는 없음)
SERIALIZABLE없음없음없음

4.1 READ UNCOMMITTED

  • 트랜잭션에서의 변경 내용이 COMMIT 이 나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보인다.

KakaoTalk_20250123_212121643_01.jpg

  • 사용자 A 는 emp_no 가 500000이고 first_name 이 “Lara” 인 새로운 사원을 INSERT 한다
  • 사용자 A 가 COMMIT 을 하지 않았지만 사용자 B 는 emp_no 이 “500000”인 사원을 SELECT 할 수 있다.
  • 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 그 내용을 볼 수 있는거를 dirty read 라고 한다.

4.2 READ COMMITTED

  • dirty read 같은 상황이 발생하지 않는다.
  • 어떤 트랜잭션에서 데이터 조작중에 다른 트랜잭션에서 관여를 할 수 없다.

KakaoTalk_20250123_212121643_02.jpg

  • 사용자 A 가 emp_no 가 50000 인 first_name 이 “Lara” 의 이름을 Toto 로 변경을 했다.

  • 그 즉시 버퍼 풀에 있는 더티페이지에 emp_no 가 50000 인 first_name 을 “Toto”로 생성한다.

  • 기존에 “Lara” 는 롤백이나 다른 트랜잭션의 참고를 위해 언두 로그에 emp_no 가 50000 인 값의 first_name이 “Lara”인 것을 생성한다.

  • 사용자 B 는 트랜잭션을 생성해서 emp_no 가 50000 인 값을 읽으면 언두 로그에 있는 값을 읽게 된다.

  • 사용자 A 가 COMMIT 을 하는 경우에 다른 트랜잭션이 emp_no = 500000 의 값을 호출하면 그 후에는 “Toto” 값을 읽게 된다.

  • READ COMMITTED 에서”NON-REPEATABLE READ”라는 부정합의 문제가 있다.

KakaoTalk_20250123_212125366.jpg

  • 사용자 B 가 트랜잭션이 시작되고 나서 Toto 라는 first_name 을 읽을때는 없는데 그 사이에 사용자 A 트랜잭션에서 이름을 Toto 라고 반복하고 COMMIT 을 한 경우 다시 사용자 B 가 Toto 를 읽으면 값이 가져와진다.
  • 사용자 B 가 트랜잭션 종료 안한 상태, 반복적으로 값을 읽을때 그 사이에 다른 트랜잭션에 의해 값이 변경됐을 경우 부정합의 문제가 발생한다.

예_ 다른 트랜잭션에서 입금과 출금 처리가 계속 진행될 때 다른 트랜잭션에서 오늘 입금된 금액의 총합을 조회 하는 경우

  • 이 경우 REPEATABLE READ 가 보장되지 않기 때문에 총합을 계산하는 SELECT 쿼리는 실행될 때마다 다른 결과를 가져오게 된다.

4.3 REPEATABLE READ

  • InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.

KakaoTalk_20250123_212121643_03.jpg

  • 트랜잭션 번호 6번으로 emp_no 가 50000인 값의 first_name 을 Lara로 Insert 했다.

  • 사용자 B 가 트랜잭션 10번 번호로 생성해서 emp_no=50000 인 값을 SELECT 했다.

  • 사용자 A가 사용자 B 트랜잭션이 아직 COMMIT 되지 않은 상태에서 트랜잭션 12번 번호로 생성해서 emp_no 가 500000 d인 값의 first_name 을 Toto 로 변경하고 COMMIT 을 했다.

  • 언두로그에 이전 값을 복사하였으며 생성시켰던 트랜잭션 번호 6번도 그대로 언두로그에 생성된다.

  • 사용자 B 가 다시 emp_no 가 50000인 값을 조회 했을때 10번 트랜잭션은 자기 트랜잭션 번호보다 높은 값의 데이터를 조회하는게 아니라 낮은 값 즉 언두로그에 있는 값을 조회한다.

  • REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다.

KakaoTalk_20250123_214705084.jpg

  • 이러한 PHANTOM READ 상황을 해결하기 위해서 넥스트 키 락 이 존재한다.
  • 설명을 거지같이 하는거 방법은 존재한다.
  • SELECT … FOR UPDATE 로 인덱스 범위를 잠그면 조건에 해당하는 레코드(Record Lock)와 그 범위(Gap Lock)이 모두 잠긴다.
    • INSERT 는 갭 잠금에 의해 막힌다.
  • INSERT 가 막히는 이유
    • 갭 잠금은 팬텀 리드를 방지하고, 트랜잭션의 일관성을 보장하기 위한 메커니즘이다.
  • 갭 잠금이 비활성화 되는 경우
    • READ COMMITTED 격리 수준에서는 갭 잠금이 비활성화되며, INSERT 가 허용된다.

4.4 SERIALIZABLE

  • InnoDB 테이블에서 기본적으로 순수한 SELECT 작업은 아무런 레코드 잠금도 설정하지 않고 실행된다.
profile
진성대
주니어 개발자
이전 포스트

MySQL 아키텍처

다음 포스트

인덱스

0개의 댓글