2. 트랜잭션과 잠금

Sixhustle·2021년 8월 14일
Database
0

RealMySQL

목록 보기
2/5

4.1 트랜잭션

트랜잭션은 작업을 모두 완벽하게 처리(commit)하거나 또는 처리하지 못할 경우에는 원 상태로 복구(rollback)해서 작업의 일부만 적용되는 현상을 막아주는, 작업의 완전성을 보장해 주는 것

4.1.1 MySQL에서의 트랜잭션

  • 트랜잭션을 지원하는 InnoDB는 Duplicate 3 에러가 발생함으로써 모든 작업을 원 상태로 복구(rollback)했지만,
  • 트랜잭션을 지원하지 않는 MyISAM은 Duplicate 3 에러가 발생해도 (1), (2) 값이 부분 처리(partial update)된 것을 확인할 수 있다.

(참고) MyISAM에서 트랜잭션을 처리하기 위해선 위와 같은 코드가 필요하다고 한다.

4.1.2 주의사항

프로그램 코드에서 트랜잭션의 범위를 최소화하라

게시물을 작성을 서버에서 처리하는 내용을 순서대로 정리한 것이다.
위 절차 중 트랜잭션 처리에 좋지 않은 영향을 끼치는 부분을 파악해보자.

  1. 실제 DBMS에 데이터를 저장하는 작업은 5번부터 시작이기때문에 1번과 2번 사이에서 트랜잭션을 시작할 필요가 없다.
  2. 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 작업은 DBMS 트랜잭션 내에서 제거하는 것이 좋다.
  3. 사용자가 입력한 정보를 저장하는 5번과 6번은 하나의 트랜잭션으로 처리
  4. 7번 작업은 저장된 데이터의 단순 조회이므로 트랜잭션에서 분리

최적의 트랜잭션 설계는 아닐 수 있지만, 아래와 같이 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램의 범위를 최소화하는 것이 좋다.

4.2 MySQL 엔진의 잠금

4.4.1 InnoDB 잠금 방식

낙관적 잠금(Optimistic locking)

  • “각 트랜잭션이 같은 레코드를 변경할 가능성은 희박할 것”이라는 가정
  • 변경 작업을 수행하고, 마지막에 잠금 충돌이 있었는지 확인해 문제가 있다면 Rollback 처리하는 방식을 의미

비관적 잠금(Pessimistic locking)

  • “현재 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다”라고 가정
  • 현재 트랜잭션에서 변경하고자 하는 레코드에 대해 잠금을 획득하고, 변경 작업을 처리하는 방식
  • 높은 동시성 처리에는 비관적 잠금이 유리하며, InnoDB의 잠금 방식

4.4.2 InnoDB의 잠금 종류

InnoDB 스토리지 엔진은 레코드 기반의 잠금 기능을 제공

레코드 락

  • 레코드 자체만을 잠그는 것으로 인덱스의 레코드를 잠근다.
  • 인덱스가 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
  • 보조 인덱스를 이용한 변경 작업은 Next key lock 또는 Gap lock을 사용
  • Primary Key 또는 Unique Index에 의한 변경 작업은 Gap에 대해서는 잠그지 않고, 레코드 자체에만 락을 건다.

갭 락

  • 레코드와 바로 인접한 레코드 사이의 간격만들 잠그는 것이다.
  • 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.
  • 넥스트 키 락의 일부로 사용된다.

넥스트 키 락

  • 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금이다.

자동 증가 락

  • 테이블 수준의 잠금으로 Auto_increment를 지원하기 위함이다.
  • insert/replace 쿼리 문장과 같이 새로운 레코드를 저장하는 쿼리에서만 필요

4.4.3 인덱스와 잠금

  • InnoDB 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리.
  • 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 잠가야 한다.
-- // 예제 데이터베이스의 employees 데이블에는 아래와 같이 first name 칼렁만 
-- // 멤버로 담긴 ix_firstname이라는 인덱스가 준비돼 있다.
-- // KEY ix_firstname (first_name)
-- // employees 테이블에서 first_name=‘Georgi’인 사원은 전체 253명이 있으며,
-- // first_name='Georgi'이고 last_name=‘KIassen’인 사원은 딱 1명만 있는 것을 아래 쿼리로 
-- // 확인할 수 있다.
mysql> SELECT COUNT(*) FROM employees WHERE first_name=‘Georgi’;
+-------+
     253
+-------+

mysql> SELECT COUNT(*) FROM employees WHERE first_name=‘Georgi’ AND last_name=‘KIassen’;
+-------+
       1
+-------+

-- // employees 데이블에서 first_name=‘Georgi’이고 last_name=‘KIassen’인 사원의 
-- // 입사 일자를 오늘로 변경하는 변경하는 쿼리를 실행해보자.
mysql> UPDATE employees SET hire date=NOW() WHERE first_name=‘Georgi’ AND last_name=‘KIassen’

update 문장의 조건에서 인덱스를 이용할 수 있는 조건은 frist_name=‘Georgi’,
last_name칼럼은 인덱스에 없기 때문에 first_name=‘Georgi’ 레코드 253건 모두 잠긴다.

하지만, MySQL 5.1 이후부터는 위와 같이
1. 인덱스만으로 이일치 여부를 판단 : first_name=‘Georgi’ 레코드를 모두 잠근다.
2. 인덱스를 이용하지 않는 나머지 조건의 일치 여부를 판단 : 실제 업데이트 대상이 아니면 1번에서 걸었던 잠금을 해제
3. 최종적으로 first_name=‘Georgi’ AND last_name=‘Klassen’ 레코드에 대해서만 잠금을 가짐

만약, 이 테이블에 인덱스가 없다면, 테이블을 full scan하면서 update 작업을 한다.
이 과정에서 테이블에 있는 30만 건의 모든 레코드를 모두 잠근다.

4.5 MySQL의 격리 수준

격리 수준이란 동시에 여러 트랜잭션이 처리 될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있도록 허용할지 말지를 결정하는 것이다.

  • “DIRTY READ”라고도 하는 READ UNCOMMITTED는 일반적인 데이터베이스에서는 사용하지 않는다.
  • SERIALIZABLE 또한 동시성이 중요한 데이터베이스에서는 사용되지 않는다.
  • 아래로 갈수록 고립 정도가 높아지며, 동시성도 떨어지는 것이 일반적이다.

4.5.1 READ UNCOMMITTED

각 트랜잭션에서의 변경 내용이 commit 또는 rollback 여부에 상관 없이 다른 트랜잭션에서 보여진다.

  • commit되지 않은 상태에서도 조회할 수 있음
  • 위와 같이 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데도 다른 트랜잭션에서 볼 수 있게 되는 현상을 더티 리드(Dirty Read)라 한다.
  • read uncommitted는 데이터 정합성에 문제가 많은 격리 수준이라 read committed 이상의 격리 수준을 사용 권장

4.5.2 READ COMIITED

commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있다.

  • 온라인 서비스에서 가장 많이 선택되는 격리 수준
  • commit 전 데이터를 조회하면 undo 영역의 값을 반환한다.

  • read committed 에서도 “non-repeatable read(reapeatable read가 불가능)”한 부정합 문제가 있다.
  • 하나의 트랜잭션 내에서 똑같은 select쿼리를 실행했을 때는 항상 같은 결과를 가져와야 한다는 reapeatable read정합성에 어긋남
  • 위 문제는 금전적인 처리같은 작업과 연결되면 문제가 될 수 있다.(같은 트랜잭션 내에서 쿼리가 같은 총합을 계산하는 select의 결과가 다른)

4.5.3 REPEATABLE READ

  • 동일 트랜잭션 내에서는 동일한 결과를 보장한다.
  • InnoDB의 격리 수준으로, 바이너리 로그를 가진 MySQL은 최소 REPEATABLE READ 격리 수준 이상을 사용해야 한다.

  • repeatable read 격리 수준에서는 mvcc를 보장하기 위해 실행 중인 트랜잭션 중, 가장 오래된 트랜잭션 번호보다 앞 선 언두 영역의 데이터는 삭제할 수 없다.
  • 사용자 B의 10번의 트랜잭션 안에서 실행되는 모든 select 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호의 변경만 본게 된다.

PHANTOM READ

다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다가 안 보였다가 하는 현상

  • SELECT .. FOR UPDATE나 SELECT .. LOCK IN SHARE MODE로 조회하는 레코드는 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 된다. 왜? 언두 레코드에는 잠금을 걸 수 없기 때문에

4.5.4 SERIALIZABLE

한 트랜잭션에서 읽고 쓰는 레코드들은 다른 트랜잭션에서는 절대 접근할 수 없다.

  • PHANTOM READ 문제가 발생하지 않는다.
  • InnoDB의 경우 REPEATABLE READ 에서도 PHANTOM READ가 발생하지 않는다 —> 왜? 왜 발생안함? --> InnoDB의 경우 Consistent Read방식으로 Snapshot을 사용하기 때문에

References

  • RealMySQL

정리가 더 필요한 부분

  • 4.4.2 InnoDB의 잠금 종류 : Gap lock, Next key lock
  • 4.5 isoloation : InnoDB repeatable read에서 phantom read가 발생하지 않는 이유가 무엇인지
profile
Sixhustle
이전 포스트

1. 아키텍처

다음 포스트

3. 인덱스

0개의 댓글