트랜잭션

MySQL에서의 트랜잭션

트랜잭션은 쿼리의 개수와 상관없이 논리적인 작업 셋 자체가 100% 적용되거나(COMMIT을 실행했을 때) 아무것도 적용되지 않아야 함(ROLLBACCK)을 보장해 주는 것이다.

트랜잭션 관점에서 InnoDB 테이블과 MyISAM 테이블의 차이

• MyISAM (트랜잭션 지원 X 스토리지 엔진)

  create table tab_myisam (fdpk int not null, primary key (fdpk)) engine = MyISAM;
  insert into tab_myisam (fdpk) values (3);

  MyISAM 스토리지 엔진 테이블을 생성하고 데이터를 INSERT 한다.

  이후 이미 INSERT 한 데이터(3)를 포함하여 데이터를 추가로 INSERT 한다.
  
  

  insert into tab_myisam (fdpk) values (1), (2), (3);

  실행 결과
  Error Code: 1062. Duplicate entry '3' for key 'tab_myisam.PRIMARY'
  
  

  select * from tab_myisam;

  
  해당 테이블의 데이터를 조회 해보면 오류가 발생했음에도 1과 2가 INSERT된 상태로 남아있는 것을 확인할 수 있다.
  
  
  

• InnoDB (트랜잭션 지원 스토리지 엔진)

  create table tab_innodb (fdpk int not null, primary key (fdpk)) engine = INNODB;
  insert into tab_innodb (fdpk) values (3);
  
  -- AUTO COMMIT 활성화
  set autocommit = on;

  InnoDB 스토리지 엔진 테이블을 생성하고 데이터를 INSERT 한다.

  이후 이미 INSERT 한 데이터(3)를 포함하여 데이터를 추가로 INSERT 한다.

  insert into tab_innodb (fdpk) values (1), (2), (3);

  Error Code: 1062. Duplicate entry '3' for key 'tab_innodb.PRIMARY'

  select * from tab_innodb;

  
  트랜잭션이 적용되어 오류가 발생한 명령에 대해 전체를 명령을 실행하기 전 상태로 복구되었음을 확인할 수 있다.

주의사항

트랜잭션은 꼭 필요한 최소의 코드에만 적용하는 것이 좋다.

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

아래 내용은 사용자가 게시판에 게시물을 작성한 후 저장 버튼을 클릭했을 때

서버에서 처리하는 내용을 순서대로 정리한 것이다.

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

위의 처리 절차 중 DBMS의 트랜잭션 처리에 좋지 않은 영향을 미치는 부분이 존재하며 그 내용은 아래와 같다.

• DBMS에 실제로 데이터를 저장하는 작업은 5번부터 시작된다.

  따라서 2번과 3번, 4번의 절차가 아무리 빨리 처리된다고 하더라도 DBMS 트랜잭션에 포함시킬 필요는 없다.
  

• 메일 전송이나 FTP 파일 전송 작업 또는 네트워크를 통해 원격 서버와 통신하는 등과 같은 작업은 DBMS 트랜잭션 내에서 제거하는 것이 좋다.

• 위의 처리 절차에는 DBMS의 작업이 크게 4개가 있다.

  5, 6번 작업은 반드시 하나의 트랜잭션으로 묶어야 하며, 7번 작업은 트랜잭션에 포함할 필요는 없다.

  9번 작업은 성격이 다르기 때문에 이전 트랜잭션에 함께 묶지 않아도 무방해 보인다.

  이러한 작업은 별도의 트랜잭션으로 분리하는 것이 좋다.

이러한 문제점을 고려하여 처리 절차를 수정하려면 아래와 같이 변경할 수 있다.

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

MySQL 엔진의 잠금

MySQL에서 사용되는 잠금은 크게 스토리지 엔진 레벨과 MySQL 엔진 레벨로 나눌 수 있다.

MySQL 엔진 레벨의 잠금은 모든 스토리지 엔진에 영향을 미치지만, 스토리지 엔진 레벨의 잠금은 스토리지 엔진 간 상호 영향을 미치지는 않는다.

MySQL 엔진에서는 테이블 데이터 동기화를 위한 테이블 락 이외에도 테이블 구조를 잠그는 메타데이터 락(Metadata Lock) 그리고 사용자의 필요에 맞게 사용할 수 있는 네임드 락(Named Lock) 잠금 기능도 제공한다.

글로벌 락

영향을 미치는 범위는 MySQL 서버 전체이다.

작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미친다.

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

테이블 락

개별 테이블 단위로 설정되는 잠금이며, 명시적 또는 묵시적으로 특정 테이블의 락을 획득할 수 있다.

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

  명시적으로 획득한 잠금은 UNLOCK TABLES 명령으로 잠금을 반납할 수 있다.
  

• 묵시적 테이블 락

  MyISAM이나 MEMORY 테이블에 데이터를 변경하는 쿼리를 실행하면 발생한다.

  데이터가 변경되는 테이블에 잠금을 설정하고 데이터를 변경한 후, 즉시 잠금을 해제하는 형태로 사용된다.

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

네임드 락

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

네임드 락은 단순히 사용자가 지정한 문자열에 대해 획득하고 반납하는 잠금이다.

메타데이터 락

데이터베이스 객체의 이름이나 구조를 변경하는 경우에 획득하는 잠금이다.

메타데이터 락은 명시적으로 획득하거나 해제할 수 있는 것이 아니고 RENAME TABLE tab_a TO tab_b 같이 테이블의 이름을 변경하는 경우 자동으로 획득하는 잠금이다.