CS ‐ 면접을 위한 RDBMS(MySQL) (1) - dnwls16071/Backend_Study_TIL GitHub Wiki

📚 트랜잭션(Transaction) 흐름⭐

1) 처리 시작
   => 데이터베이스 커넥션 생성
   => 트랜잭션 시작
2) 사용자 로그인 여부 확인
3) 사용자 글쓰기 내용 오류 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 내용을 DBMS에 저장
7) 저장된 내용 또는 기타 정보(첨부 파일)를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
9) 알림 메일 발송 이력을 DBMS에 저장
   <= 트랜잭션 종료(Commit)
   <= 데이터베이스 커넥션 반납
10) 처리 완료

위의 처리 절차 중에서 DBMS 트랜잭션 처리에 좋지 않은 영향을 미치는 부분을 나눠서 살펴보자.

• 데이터베이스 커넥션 개수는 제한적이어서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질수록 사용가능한 여유 커넥션 개수는 줄어들게 된다. 그렇게 되면 어느 순간에 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.
• 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 작업의 경우 DBMS 트랜잭션 내에서 제거하는 것이 좋다. 프로그램이 실행되는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버 뿐만 아니라 DBMS 서버까지도 위험해지기 때문이다.
• 항상 정답이 있는 것은 아니겠지만 처리를 달리 한다면 이렇게도 할 수 있다.

1) 처리 시작
2) 사용자 로그인 여부 확인
3) 사용자 글쓰기 내용 오류 발생 여부 확인
4) 첨부로 업로드된 파일 확인 및 저장
   => 데이터베이스 커넥션 생성(또는 커넥션 풀에서 가져오기)
   => 트랜잭션 시작
5) 사용자 입력 내용을 DBMS에 저장
6) 첨부 파일 정보를 DBMS에 저장
   <= 트랜잭션 종료(Commit)
7) 저장된 내용 또는 기타 정보를 DBMS에서 조회
8) 게시물 등록에 대한 알림 메일 발송
   => 트랜잭션 시작
9) 알림 메일 발송 이력을 DBMS에 저장
   <= 트랜잭션 종료(Commit)
   <= 데이터베이스 커넥션 종료(또는 커넥션 풀에 반납)
10) 처리 완료

• 가장 중요한 점은 프로그램 코드가 데이터베이스 커넥션을 가지고 있는 범위와 트랜잭션이 활성화되어 있는 프로그램 범위를 최소화해야 한다는 점이다.
• 또한 프로그램 코드에서 라인 수는 한두 줄이라고 하더라도 네트워크를 필요로 하는 작업의 경우 반드시 트랜잭션에서 배제해야 한다.

📚 글로벌 락(Global Lock)

• 글로벌 락(Global Lock)은 MySQL에서 제공하는 잠금 가운데 가장 범위가 크다.
• 한 세션에서 글로벌 락을 획득하면 다른 세션에서 SELECT를 제외한 대부분의 DDL, DML을 실행하는 경우 글로벌 락이 해제될 때까지 해당 문장이 대기 상태로 남게 된다.
• 글로벌 락이 영향을 미치는 범위는 MySQL 서버 전체이며, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.
• FLUSH TABLES WITH READ LOCK은 실행과 동시에 MySQL 서버에 존재하는 모든 테이블을 닫고 잠금을 건다.
• 결론적으로 글로벌 락은 MySQL 서버의 모든 테이블에 큰 영향을 미치기 때문에 웹 서비스용으로 사용되는 MySQL 서버에서는 가급적 사용하지 않는 것이 좋다.

📚 테이블 락(Table Lock)

• 테이블 락(Table Lock)은 개별 테이블 단위로 설정되는 잠금이며 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.
  • 명시적으로는 LOCK TABLES table_name [ READ | WRITE ] 특정 테이블의 락을 획득할 수 있다.
  • 명시적으로 획득한 락을 UNLOCK TABLES 명령으로 락을 반납할 수 있다.
  • 묵시적으로는 MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.
  • 묵시적으로는 쿼리가 실행되는 동안 락을 자동으로 획득했다가 쿼리가 완료된 후 자동 해제된다.
  • InnoDB 테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공하기 때문에 단순 데이터 변경 쿼리를 날린다고 하더라도 묵시적 테이블 락이 설정되지 않는다.

📚 네임드 락(Named Lock)

• 네임드 락(Named Lock)은 GET_LOCK() 함수를 이용해 임의의 문자열에 대해 잠금을 설정할 수 있다.
• 네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금으로 네임드 락은 자주 사용되진 않는다.

📚 메타데이터 락(Metadata Lock)

• 메타데이터 락은 데이터베이스 객체(테이블이나 뷰 등)의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.
• RENAME TABLE tab_a TO tab_b와 같이 테이블 이름을 변경하는 경우 자동으로 획득하는 잠금이다.

📚 MySQL의 격리 수준⭐

• 트랜잭션 격리 수준(isolation level)이란, 여러 트랜잭션이 동시에 처리될 때 특정 트랜잭션이 다른 트랜잭션에서 변경하거나 조회하는 데이터를 볼 수 있게 허용할지 말지를 결정하는 것이다.
• READ_UNCOMMITED, READ_COMMITED, REPEATABLE_READ, SERIALIZABLE

READ_UNCOMMITED

• 각 트랜잭션에서의 변경 내용이 commit이든 rollback이든 상관없이 다른 트랜잭션에서 보이기 때문에 데이터 정합성을 맞추기가 어렵다는 문제가 발생한다.
• 이처럼 어떤 트랜잭션에서 처리한 작업이 완료되지 않았음에도 불구하고 다른 트랜잭션에서 볼 수 있는 현상을 Dirty Read라고 한다.
• 최소한 READ_COMMITED 이상의 격리 수준을 사용하는 것을 권장한다.

READ_COMMITED

• READ_COMMITED는 오라클 DBMS에서 기본으로 사용되는 격리 수준이며 온라인 서비스에서 가장 많이 선택되는 격리 수준이다.
• 어떤 트랜잭션에서 데이터를 변경했더라도 commit이 완료된 데이터만 다른 트랜잭션에서 조회할 수 있기 때문이다.
• READ_COMMITED 격리 수준에서는 어떤 트랜잭션에서 변경한 내용이 커밋되기 전까지는 다른 트랜잭션에서 그러한 변경 내역을 조회할 수는 없기 때문이다.

REPEATABLE_READ

• REPEATABLE_READ는 MySQL의 InnoDB 스토리지 엔진에서 기본으로 사용되는 격리 수준이다.
• InnoDB 스토리지 엔진은 트랜잭션이 Rollback될 가능성에 대비해 변경되기전 레코드를 언두(Undo) 공간에 백업해두고 실제 레코드 값을 변경한다.
• 모든 InnoDB 트랜잭션은 고유한 트랜잭션 번호를 가지며, 언두 영역에 백업된 모든 레코드에는 변경을 발생시킨 트랜잭션 번호가 포함되어있다.
• 한 사용자가 BEGIN으로 트랜잭션을 시작하고 장시간의 트랜잭션을 종료하지 않으면 언두 영역이 백업된 데이터로 인해 무한정 커질 수 있다.
• 이렇게 언두에 백업된 레코드가 많아지면 MySQL 서버의 처리 성능이 떨어질 수 있다.
• 다른 트랜잭션에서 수행한 변경 내용에 의해 레코드가 보였다 안보였다 하는 현상을 PHANTOM READ라고 한다.

📚 인덱스(Index)⭐

• DBMS에서 인덱스는 데이터의 저장(INSERT, UPDATE, DELETE) 성능을 희생하고 그 대신 데이터 읽기 속도를 높이는 기능이다.
• SELECT 쿼리 문장의 WHERE 조건절에 사용되는 칼럼이라고 해서 전부 인덱스로 생성하면 데이터 저장 성능이 떨어지고 인덱스 크기가 비대해져 오히려 역효과만 불러올 수 있다.