✏️트랜잭션과 잠금⭐⭐
- 트랜잭션 : 작업의 완전성을 보장해 주는 것, 즉, 논리적인 작업 셋을 모두 완벽하게 처리하거나 처리하지 못할 경우에 원상태로 복구해서 작업의 일부만 적용되게끔 하는 현상이 발생하지 않게 만들어주는 기능
- 잠금(Lock) : 공유 자원에 대한 동시성을 제어해주는 것
- 트랜잭션(Transaction) : 데이터의 정합성을 보장해주는 것
- 데이터의 정합성 : 어떤 데이터들이 서로 일치하는 상태
- 격리 수준 : 하나의 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.
- 여기까지만 놓고 보면
fdpk는 기본키고 처음에 DML문으로 3을 집어 넣어줬기 때문에 마지막에 실행항 1, 2, 3 중 3이 중복된다는 오류가 발생한다. 하지만 실행 결과를 SELECT문으로 조회하면 아래와 같은 결과를 볼 수 있다.
- 쿼리문 둘 다 기본키 중복 오류가 발생했음에도 불구하고 첫 번째 테이블의 경우 INSERT문이 실행되면서 1, 2가 남아 있는 것을 확인할 수 있고 두 번째 테이블의 경우 3만 남아 있는 것을 확인할 수 있다.
- MyISAM의 경우 위와 같이 부분 업데이트 현상이 발생하면 실패한 쿼리로 인해 남은 레코드를 다시 삭제하는 재처리 작업이 필요할 수 있다. 실행하는 쿼리가 하나뿐이라면 재처리 작업은 간단하겠지만 만약 2개 이상의 쿼리가 실행되는 경우라면 실패에 대한 재처리 작업을 처리하는 쿼리를 짜는 데 엄청난 시간을 소모하게 될 것이다.
- 결론적으로 본다면 MyISAM의 경우 트랜잭션이 지원되지 않는 것이고 InnoDB의 경우 트랜잭션이 지원된다고 볼 수 있다.
✏️트랜잭션 예시와 이를 통해 알 수 있는 주의사항 정리⭐⭐
- 게시판의 CRUD를 예로 들어 트랜잭션의 범위를 어떻게 가져가는 것이 좋을지 알아보자.
1) 처리 시작
-> 데이터베이스 커넥션 생성
-> 트랜잭션 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
<- 트랜잭션 종료(Commit)
<- 데이터베이스 커넥션 반납
10) 처리 완료
- 실제로 많은 개발자가 데이터베이스 커넥션을 생성하는 코드를 1번과 2번 사이에 구현하며 그와 동시에 START TRANSACTION 명령으로 트랜잭션을 시작한다. 그리고 9번과 10번 사이에서 트랜잭션을 커밋하고 커넥션을 종료한다.
- 커넥션을 생성 : 커넥션 풀(Connection Pool)로 가져올 수 있다.
- 커넥션을 반납 : 커넥션 풀(Connection Pool)로 반납한다.
- 실제로 RDBMS에 데이터를 저장하는 작업(트랜잭션)은 5번부터 시작된다.
- 그래서 2번과, 3번, 4번 작업이 아무리 빨리 처리된다고 하더라도 DBMS의 트랜잭션에 포함시킬 필요는 없다.
- 일반적으로 데이터베이스 커넥션의 개수는 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용 가능한 커넥션의 여유 분은 줄어들 것이다. 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수 있다.
- 큰 위험은 8번 작업에 존재한다. 메일 전송이나 FTP(File Transfer Protocol) 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등의 작업은 어떻게 해서든 DBMS 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐만 아니라 DBMS 서버까지 위험해지는 상황이 발생하게 될 것이다.
또한, 이 처리 절차에는 DBMS 작업이 크게 4가지가 있다. 사용자가 입력한 정보를 저장하는 5번, 6번 작업은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번 작업은 저장된 데이터 단순 확인 및 조회이므로 트랜잭션에 포함될 필요는 없다. 그리고 9번 작업은 5, 6번 트랜잭션에 함께 묶지 않아도 무방해 보인다. 7번 역시 단순 조회하고 본다면 트랜잭션을 별도로 사용하지 않아도 된다.
위의 내용들을 정리하여 트랜잭션의 범위를 다시 가져간다면 아래와 같이 가져갈 수 있다.
1) 처리 시작
2) 사용자의 로그인 여부 확인
3) 사용자의 글쓰기 내용 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
-> 데이터베이스 커넥션 생성
-> 트랜잭션 시작
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
<- 트랜잭션 종료(Commit)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
-> 데이터베이스 커넥션 생성
-> 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
<- 트랜잭션 종료(Commit)
<- 데이터베이스 커넥션 반납
10) 처리 완료- 프로그램의 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화돼 있는 프로그램 범위를 최소화해야 한다는 것이다.
- 네트워크 작업이 있는 경우에는 반드시 트랜잭션에서 배제해야 한다.
- 네트워크 작업을 트랜잭션에서 배제해야 하는 주요 이유들은 아래와 같다.
- 원자성 보장의 어려움 : 네트워크 회선을 이용하는 네트워크 작업의 경우 외부 시스템에 의존하기 때문에 실패할 가능성이 높다. 그렇기에 실패 시 롤백이 매우 복잡하거나 불가능할 수 있기 때문에 배제해야 한다.
- 긴 트랜잭션을 가져가면 데드락 위험도 증가하게 된다.
- 네트워크 지연으로 인해 트랜잭션이 길어지면 그만큼 DB 리소스를 오래 점유하게 되면서 성능 저하가 발생하게 된다.
- 트랜잭션의 범위를 정하는 간단한 기본 원칙은 아래와 같다.
- 기본 원칙 : 데이터베이스 작업만 트랜잭션에 포함시키기
✏️MySQL 엔진의 잠금(Lock)⭐⭐
- MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.
- MySQL 엔진 레벨 잠금의 경우 모든 스토리지 엔진에 영향을 미친다.
- 하지만 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지 않는다.
- 글로벌 락(Global Lock)
- 글로벌 락은 FLUSH TABLES WITH READ LOCK 명령으로 획득할 수 있으며, MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
- 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML문을 실행할 경우 글로벌 락이 해제될 때까지 해당 문장들이 대기 상태로 남는다.
- 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체 범위이멸 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.(MySQL 서버 전 범위이기 때문에)
- 주의할 점❗
- 글로벌 락을 거는 FLUSH TABLES WITH READ LOCK 명령은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
- 읽기 잠금 : 데이터 읽기를 위한 잠금, 어느 세션에서나 동일한 데이터 조회를 보장한다.
- 쓰기 잠금 : 데이터 쓰기를 위한 잠금, 쓰기 락이 걸리면 다른 세션에서 락이 걸린 데이터를 조회하거나 수정할 수 없다.
- 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스용으로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋다.
- 테이블 락(Table Lock)
- 테이블 락은 개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 테이블의 락을 획득할 수 있다.
- 네임드 락(Named Lock)
- 네임드 락은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
- 네임드 락은 단순히 사용자가 지정한 문자열(String)에 대해 획득하고 반납(해제)하는 잠금이다.
- 메타 데이터 락(Metadata Lock)
- 데이터베이스 객체(이를테면 테이블이나 뷰)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
✏️InnoDB 스토리지 엔진의 잠금😥
- 레코드 락(Record Lock)
- 레코드 자체만을 잠그는 것을 레코드 락이라고 하며, 다른 상용 DBMS의 레코드 락과 동일한 역할을 한다.
- InnoDB 스토리지 엔진은 레코드 자체가 아니라 인덱스의 레코드를 잠근다.
- 인덱스가 하나도 없는 테이블이더라도 내부적으로 자동 생성된 클러스터 인덱스를 이용해 잠금을 설정한다.
- 갭 락(Gap Lock)
- 레코드 자체가 아니라 레코드와 바로 인접한 레코드 사이의 간격만을 잠그는 것을 의미한다.
- 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다.
- 넥스트 키 락(Next Key Lock)
- 레코드 락과 갭 락을 합쳐 놓은 형태의 잠금을 의미한다.
✏️MySQL의 격리 수준⭐⭐
- 트랜잭션의 격리 수준(isolation level)이란, 여러 트랜잭션이 동시에 처리될 때, 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
- 격리 수준은 크게 'READ UNCOMMITTED', 'READ COMMITTED', 'REPEATABLE READ', 'SERIALIZERABLE'의 4가지로 나뉜다.
- READ UNCOMMITTED
- Transaction1에서 트랜잭션을 시작하고 바로 INSERT문을 통해 데이터를 삽입한다.
- Transaction1의 작업이 커밋되기도 전에 Transaction2에서 SELECT문을 통해 데이터를 조회한다.
- 만약, Transaction1에서 문제가 생겨 롤백한다고 하더라도 Transaction2는 여전히 정상적인 데이터라고 생각을 하고 조회를 하게 된다.
- 이처럼, 어떤 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있는 상태를 더티 리드(Dirty Read)라고 한다. 해당 격리 수준은 정합성에 문제가 많은 격리 수준이므로 MySQL을 사용한다면 최소한 READ UNCOMMITTED 이상의 격리 수준을 사용할 것을 권장한다.
- READ COMMITTED
- READ COMMITTED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이다. 이 격리 수준에서는 READ UNCOMMITTED와 같은 격리 수준에서 발생하는 더티 리드(Dirty Read)는 발생하지 않는다.
- 어떤 트랜잭션에서 데이터를 변경했더라도 Commit이 완료된 상태의 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.
- 2개의 행이 존재하는 상황에서 Transaction1이 실행되고 이후 UPDATE문을 실행시켜 제주에서 부산으로 데이터를 바꾼다.
이 때, Transaction2에서 222번 데이터를 조회할 때, 바뀐 데이터인 제주가 아니라 부산으로 조회가 된다.- 최종적으로 Transaction1에서 작업한 내용이 커밋이 되어야만 다른 트랜잭션에서 새롭게 변경된 값을 참조할 수 있게 된다.
- READ COMMITTED 격리 수준에서 NON-REPEATABLE READ 부정합의 문제가 발생한다.
- NON-REPEATABLE READ
- Transaction A에서 x = 1인 데이터를 읽는다고 가정하자. 이 때 아무것도 없었다고 가정하면 아무런 데이터를 읽을 수 없다.
- 그런데 Transaction B에서 x = 1인 데이터를 썼다고 가정하자. 이후 커밋까지 완료했다.
- 다시 Transaction B에서 x = 1인 데이터를 읽는다고 가정하면 아까와 달리 x = 1인 데이터가 존재하는 것을 확인할 수 있다.
- Transaction A가 하나의 트랜잭션 내에서 똑같은 SELECT 쿼리를 호출했을 때 항상 같은 결과를 가져와야 한다는 REPEATABLE READ 정합성 문제가 발생하는 것이다.
- REPEATABLE READ
- REPEATABLE READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
- InnoDB 스토리지 엔진은 트랜잭션이 ROLLBACK될 가능성에 대비해 변경되기 전 레코드를
Undo 공간에 백업해두고 실제 레코드 값을 변경한다.- REPEATABLE READ는 Undo 영역에 백업된 이전 데이터를 이용해 동일 트랜잭션 내에서는 동일한 결과를 보여줄 수 있도록 보장한다.
- 모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호(순차적으로 증가)를 가지며 Undo 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션의 번호가 포함되어 있다. 그리고 Undo 영역의 백업된 데이터는 InnoDB 스토리지 엔진이 불필요하다고 판단하는 시점에 주기적으로 삭제한다.
- Undo 영역에 백업된 데이터가 하나만 존재하는 것은 아니다. 장시간 트랜잭션을 종료하지 않으면 Undo 영역에 백업된 데이터의 양이 어마어마하게 저장이 되면서 MySQL 서버의 성능이 저하될 수 있다.
- REPEATABLE READ 격리 수준에서도 다음과 같은 부정합이 발생할 수 있다.
- PHANTOM READ
- 사용자 B에서 트랜잭션을 시작해
SELECT ... FOR UPDATE문을 실행하면 결과 레코드로 조회되는 레코드의 개수는 1개가 된다. 이 때,SELECT ... FOR UPDATE문은 특정 Record나 Row를 다른 트랜잭션에서 수정하지 못하도록 잠그는 기능을 수행한다.- 사용자 A에서 또 다른 트랜잭션을 시작해 INSERT문으로 새로운 데이터를 삽입한다. 그리고 커밋한다.
- 사용자 B에서
SELECT ... FOR UPDATE문으로 조회하게 되면 아까와 동일한 쿼리문을 호출했으나 이번엔 조회된 레코드의 개수는 2개가 된다.- 이처럼, 다른 트랜잭션에서 수행한 변경 작업에 의해 레코드가 보였다 안보였다 하는 현상을 PHANTOM READ라고 한다.
SELECT ... FOR UPDATE문은 Undo 영역에서 잠금을 할 수 없다. 따라서 해당 쿼리문을 호출하게 되면 Undo 영역의 데이터를 가져오는 것이 아니라 현재 레코드의 값을 가져오게 되는 것이다.
- SERIALIZABLE
- 가장 단순한 격리 수준이면서 동시에 가장 엄격한 격리 수준이다.
