Real MySQL 8.0 - MySQL 트랜잭션과 잠금

공부목적!

 

트랜잭션

1. 작업의 완전성을 보장해 주는 것. (논리적인 작업 셋을 모두 완벽하게 처리하거나 처리하지 못할 경우에는 원 상태로 복구해 서 작업의 일부만 적용되는 현상이 발생하지 않게 만들어주는 기능.)

2. 데이터 정합성을 보장하기 위한 기능.

 

• CS에서 트랜잭션 = "더이상 분할이 불가능한 업무처리의 단위" 즉, 한번에 수행되어야 할 일련의 연산모음.

특징 : ACID

원자성 (Atomicity) : 트랜잭션이 데이터베이스에 모두 반영되건, 혹은 아예 반영되면 안됨.

일관성 (Consistency) : 작업 처리 결과가 항상 일관성 있어야 한다.

독립성 (Isolation) : 어떤 하나의 트랜잭션이라도, 다른 트랜잭션의 연산에 끼어들 수 없단느 점을 가리킨다.

영구성 (Durability) : 결과는 영구적으로 반영되어야 한다.

 

Commit : 모든 작업들을 정상 처리하겠다고 확정하는 명령어. 처리 과정을 DB에 영구 저장하겠다는 의미. 하나의 트랜잭션 과정이 종료되는것.

 

Roll-Back : 발생한 변경사항을 취소하는 명령어. 트랜잭션 시작되기 이전의 상태로 돌아간다.

AUTO-COMMIT ?

MySQL은 디폴트로 auto commit이 on으로 설정 되어 있다. 이는 세미콜론을 찍을 때마다, DB에서 자동으로 commit 해주는 시스템.

 

Lock(잠금)

1. 여러 커넥션에서 동시에 동일한 자원(레코드나 테이블)을 요청할 경우, 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할.

2. 동시성 제어위한 기능.

 

격리수준

1. 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨.

 

 

MySQL 에서의 트랜잭션

트랜잭션 = 100% commit || Rollback

InnoDB = Transaction 지원 / MyISAM = Transaction 미지원

1. InnoDB와 MyISAM 스토리지 엔진을 사용하는 각각의 테이블을 만듬.

2. 각각의 테이블에 3 값을 넣어줌.

3. 그런 다음 각각의 테이블에 1, 2, 3을 넣어줌. 

4. 두 테이블 모두 키 중복 오류로 쿼리 실패.

5. InnoDB의 경우, 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 Insert 문장 실행 전 상태로 복구.  /  MyISAM의 경우 1, 2는 값이 들어갔지만, 3이라는 키 값과 동일한 값을 가져서 거기서 멈추게 됨. (Rollback 없음.)

부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있다. 이에 따라 실행하는 쿼리가 한개가 아니라, 두개 이상의 쿼리가 실행되면 재처리 작업은 상당한 고민거리가 되는 것이고, 이러한 것들은 트랜잭션으로 해결할 수 있다.

 

주의 사항

1. 트랜잭션의 작업 과정은 최소화 되어야 한다. -> DB 커넥션은 개수가 제한적이라, 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질 수록 사용 가능한 여유 커넥션의 개수는 줄어들 것. 그렇게 되면 어느 순간엔, 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황 발생.

 

2. 트랜잭션 단위 안에서 메일 전송, 혹은 FTP 파일 전송 작업과 같은 네트워크 원격 서버 통신 작업은 그 과정안에서는 제거하는 것이 좋다. 혹시 네트워크 문제가 생기면 웹 서버 뿐 아니라 DBMS 서버까지 위험해지는 상황 발생.

 

3. 서격이 비슷한 것들끼리 트랜잭션 작업을 묶어서 진행할 수 있도록 해야된다. 

 

 

 

MySQL 엔진의 잠금

 

글로벌 락 

1. FLUSH TABLES WITH READ LOCK  명령어로 획득할 수 있음.

2. MySQL 에서 제공하는 잠금 중 가장 범위가 큼. 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 DB가 다르더라도 동일하게 영향을 미친다.

3. 한 세션에서 글로벌 락을 획득하면, 다른 세션에서 SELECT를 제외한 대부분의 DDL 문장이나, DML 문장을 실행하는 경우, 글로벌 락이 해제될 때 까지, 해당 문장이 대기 상태로 남음.

 

 

FLUSH? 이게 뭘까

 

 

MySQL 8.0부터는 InnoDB가 기본 스토리지 엔진으로 채택되면서 조금 더 가벼운 글로벌 락의 필요성 생김. 그래서 Xtrabackup이나, Enterprise Backup과 같은 백업 툴들의 안정적인 실행을 위해 백업 락이 도임됨.

 

 

테이블 락

1. 개별 테이블 단위로 설정되는 잠금.

2. LOCK TABLES table_name [ READ | WRITE ] 명령으로 특정 테이블 락 획득 가능. ->UNLOCK TABLE 로 테이블 잠금 해제 가능

3. 명시적인 락(이게 뭘까 명시적인?) 락은 크게 사용할 필요 없음

4. 묵시적인 테이블 락은 테이블에 데이터를 변경하는 쿼리를 실행하면 발생. MySQL 서버가 데이터 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용. 자동 락 자동 해제

5. 그러나 InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반의 잠금 제공

6. 즉, InnoDB 테이블에 테이블 락이 설정은 되지만, 데이터 변경(DML) 쿼리에서는 무시되고, 스키마를 변경하는 쿼리(DDL)의 경우에만 영향을 미친다.

 

DML VS DDL ???

 

네임드 락 (문자열을 잠근다는 것이 뭐지)

1. GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.

2. 락을 거는 대상이 테이블이나, 레코드 또는 AUTO_INCREMENT와 같은 데이터베이스 객체가 아니라  단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금.  --> 자주 사용 X

3. 배치 프로그램에서 한꺼번에 많은 레코드를 변경하는 쿼리는 자주 데드락의 원인이 된다. 

(데드락?? )

4. 이 경우 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 걸고 쿼리를 실행하면 해결.

 

 

메타데이터 락

1. 데이터베이스 객체(테이블이나 뷰)의 이름이나 구조를 변경하는 경우 획득하는 잠금.

2. 테이블이름 바꾸거나 할 때 자동으로 얻는 락.

 

인덱스와 잠금

1. InnoDB의 잠금은 레코드를 잠그는 것이 아닌, 인덱스를 잠그는 방식으로 처리됨. 즉, 변경해야 할 레코드를 찾기 위해 검색한 인덱스의 레코드를 모두 락을 걸어야 함.

2. 근데 UPDATE 쿼리를 사용할 때 적절히 인덱스가 준비돼 있지 않다면 각 클라이언트 간의 동시성이 상당히 떨어지고 한 세션에서 UPDATE 작업을 하는 중에 다른 클라이언트는 그 테이블을 업데이트하지 못하고 기다려야 하는 상황이 발생.

3. 즉, 만일 테이블에 인덱스가 하나도 없다면 이런 경우 풀 스캔을 하는데 이 과정에서 모든 레코드를 잠그게 된다. 이로 인해 InnoDB에서 인덱스 설계가 중요한 이유이다.

 

 

MySQL의 격리 수준

1. 격리 수준은 크게 'READ UNCOMMITED' / 'READ COMMITED' / 'REPEATABLE READ' / 'SERIALIZABLE' 4가지이다.

	DIRTY READ	NON-REPEATABLE READ	PHANTOM READ
READ UNCOMMITED	O	O	O
READ COMMITED	X	O	O
REPEATABLE READ	X	X	O(InnoDB는 없음)
SERIALIZABLE	X	X	X

 

READ UNCOMMITED

1. 각 트랜젝션에서의 변경 내용이 COMMIT이나 ROLLBACK 여부에 상관없이 다른 트랜잭션에서 보이는 격리 수준을 뜻.

 

2. 문제점은 예를 들어 사용자 A와 사용자 B가 비슷한 시기에 INSERT와 SELECT를 하는데, A가 EMP_NO=5에 INSERT를 했을 때 B가 EMP_NO=5를 SELECT하면 A가 인서트한 값을 가져간다. 근데, 그 과정에서 A의 처리과정 중 오류가 생겨 롤백이 된다하더라도 이미 B는 A가 INSERT한 값을 가져갔으므로, 정상적인 상태라고 생각하고 작업이 진행될 것이라는 것.

3. 더티 리드 : 어떤 트랜잭션에서 처리한 작업이 완료되지 않았는데 다른 트랜잭션에서 볼 수 있는 현상

4. 정합성에 문제가 많은 격리 수준.

 

READ COMMITED

1. 오라클에서 기본으로 사용되는 격리 수준.

2. 어떤 트랜잭션에서 데이터를 변경했더라도 COMMIT이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있음.

3. 예를 들면 A가 EMP_NO = 5에 값을 넣었는데, B가 EMP_NO=5인 값을 가져가려고 하는 상황일 때 A의 트랜잭션이 커밋이 되지 않았다면, B는 A의 트랜잭션이 진행되는 상황에서 언두 영역에 백업된 레코드에서 가져온 것. 커밋이 되기전까지 변경내역을 가져올 수 없기 때문.

4. 여기서도 부정합의 문제 발생. -> B가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 실행했을 때 항상 같은 결과를 가져와야 한다는 'REPEATABLE READ" 정합성에 어긋난다는 것.

5. 웹 프로그램에서는 큰 문제가 아닐 수 있지만, 하나의 트랜잭션에서 동일 데이터를 여러 번 읽고 변경하는 작업이 금전적인 처리와 연결되면 문제가 될 수 있다.

6. 정합성이 깨지게 되면 버그 발생 시 찾아내기가 쉽지 않다는 단점이 있다.

 

 

REPEATABLE READ

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

2. InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를 언두 공간에 백업해두고 실제 레코드 값을 변경(MVCC 방식) -> 이러한 방식을 통해 동일 트랜잭션 내에서 동일한 결과를 보여줄 수 있게 보장

3.  READ COMMITED 격리 수준도 MVCC를 이용해 COMMIT 되기 전 데이터를 보여주지만 둘의 차이점은 언두 영역의 백업된 레코드의 여러 버전 가운데 몇 번째 이전 버전까지 찾아 들어가야 하느냐에 있음.

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

5. 특정 트랜잭션 번호의 구간 내에서 백업된 언두 데이터가 보존돼야 한다.

6. 작동 방식

  6-1.사용자 B가 INSERT 구문을 했을 때 트랜잭션의 번호가 10으로 시작했는데, 사용자 A도 UPDATE를 할 때 가지는 트랜잭션 번호는 12인 경우로 예시를 들 수 있다.

  6-2. A는 EMP_NO = 500000의 값을 업데이트 했는데, 그 때 B가 EMP_NO = 500000을 SELECT 했을 때 A가 업데이트를 하고 커밋까지 수행을 한 상태에서 A가 커밋을 하든 안하든 B가 가져갈 수 있는 값은 A가 업데이트한 값이 아니다. B가 10번 트랜잭션 안에서 실행되는 모든 SELECT 쿼리는 트랜잭션 번호가 10보다 작은 트랜잭션 번호에서 변경한 것만 보게 된다. 

  6-3. 이 경우 처럼 정합성은 확보할 수 있지만, 장시간 트랜잭션이 끝나지 않는다면 이렇게 되면 백업 데이터가 무한정 커지고 MySQL 서버 처리 성능이 떨어질 수 있다.

7. 부정합이 발생할 수도 있다.

  7-1 같은 상황일 때 B가 트랜잭션을 먼저 시작했고, A가 UPDATE가 아니라, INSERT를 걸었을 때 SELECT .. FOR UPDATE를 A가 트랜잭션이 끝나기 전과 후에 결과 값이 다를 수 있다. 이러한 현상을 PHANTOM READ(PHANTOM ROW) 라고 한다.

  7-2. 이렇게 되는 이유는, SELECT ... FOR UPDATE 쿼리는 SELECT 하는 레코드에 쓰기 잠금을 걸어야 하는데, 언두 레코드에는 잠금을 걸 수 없다. 그로 인해, 언두 영역의 변경 전 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는것이다.

 

 

SERIALIZABLE

1. 가장 단순하고 엄격한 격리 수준.

2. 동시 처리 성능도 다른 트랜잭션 격리 수준보다 떨어짐. 

3. InnoDB 테이블에서 기본 SELECT 작업은 아무 잠금 설정하지 않고 실행되지만, SERIZALIZABLE 격리 수준에서는 읽기 작업도 읽기 잠슴을 획득해야 하며 동시에 다른 트랜잭션은 그러한 레코드를 변경하지 못하게 된다. 

4. 한 트랜잭션에서 읽고 쓴느 레코드를 다른 트랜잭션에서는 절대 접근할 수 없다.

5. InnoDB 스토리지 엔진은 갭 락과 넥스트 키 락 덕분에 REPEATABLE READ 격리 수준에서도 이미 'PHANTOM READ'가 발생하지 않기 때문에 굳이 SERIALIZABLE을 사용할 필요는 없다.