[MYSQL] Transaction And Lock (트랜잭션과 잠금) (MyISAM / InnoDB)

MYSQL 에서 동시성에 영향을 미치는 잠금과 트랜잭션에 대해서

 

 

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

 

잠금과 트랜잭션은 서로 비슷한 개념 같지만 잠금은 동시성 제어하기 위한 기능이고 트랜잭션은 데이터의 정합성을 보장하기 위한 기능이다. 하나의 회원 정보 레코드를 여러 커넥션에서 동시에 변경하려고 하는데 잠금이 없다면 하나의 데이터를 어러 커넥션에서 동시에 변경할 수 있게 된다. 결과적으로 해당 레코드의 값은 예측할 수 없는 상태가 된다. 잠금은 여러 커넥션에서 동시에 변경할 수 있게 된다. 결과적으로 해당 레코드의 값은 예측할 수 없는 상태가 된다. 잠금은 여러 커넥션에서 동시에 동일한 자원 ( 레코드나 테이블 ) 을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 한다. 격리 수준이하는 것은 하나의 트랜잭션 내에서 또는 여러 트랜잭션 내에서 또는 여러 트랜잭션 간의 작업 내용을 어떻게 공유하고 차단할 것인지를 결정하는 레벨을 의미한다.

 

트랜잭션

많은 사용자들이 데이터 베이스 서버에서 트랜잭션이 개발자에게 얼마나 큰 혜택을 제공하는지 자주 잊어버리는것 같다. 지금은 많이 달라졌지만 여전히 MYSQL 서버에서는 MyISAM 이나 MEMORY 스토리지 엔진이 더 빠르다고 생각하고는 한다. 하지만 사실은 MyISAM 이나 MEMORY 같이 트랜잭션을 지원하지 않는 스토리지 엔진의 테이블이 더 많은 고민거리를 만들어 낸다.

 

MYSQL 에서의 트랜잭션

트랜잭션은 꼭 여러 개의 변경 작업을 수행하는 쿼리가 조합되었을 때만 의미있는 개념은 아니다. 트랜잭션은 하나의 논리적인 작업 셋에 하나의 쿼리가 있든 두 개 이상의 쿼리가 있든 관계없이 논리적인 작업 셋 자체가 100% 적용되거나 ( COMMIT 을실행했을 때 ) 아무것도 적용되지 않아야 ( ROLLBACK 또는 트랜잭션을 ROLLBACK 시키는 오류가 발생했을 때 ) 함을 보장해주는 것이다. 

 

아래와 같이 MyISAM 엔진 테이블과 INNODB 엔진 테이블을 각각 생성 후 '3' 값을 대입한다.

 

mysql > CREATE TABLE tab_myisam ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=MyISAM;
mysql > INSERT INTO tab_myisam ( fdpk ) VALUUES ( 3 );

mysql > CREATE TABLE tab_innodb ( fdpk INT NOT NULL, PRIMARY KEY (fdpk) ) ENGINE=INNODB;
mysql > INSERT INTO tab_innodb ( fdpk ) VALUUES ( 3 );

 

후에 아래와 같이 INSERT 문을 실행하면 기본키 중복 에러가 발생한다.

 

mysql > INSERT INTO tab_myisam ( fdpk ) VALUUES ( 1 ), ( 2 ), ( 3 );
ERROR 1062 (23000) : Duplicate entity '3' for key 'PRIMARY'
mysql > INSERT INTO tab_innodb ( fdpk ) VALUUES ( 1 ), ( 2 ), ( 3 );
ERROR 1062 (23000) : Duplicate entity '3' for key 'PRIMARY'

 

스토리지 엔진에 따라 트랜잭션 발생 여부가 다르기 때문에 결과도 다르다.

 

MEMORY 스토리지 엔진을 사용하는 테이블도 MyISAM 테이블과 동일하게 작동한다.

InnoDB 는 쿼리 중 일부라도 오류가 발생하면 전체를 원 상태로 만든다는 트랜잭션의 원칙대로 INSERT 문장을 실행하기 전 상태로 그대로 복구했다.

MyISAM 테이블에서 발생하는 이러한 현상을 부분 업데이트라고 표현하며 이러한 부분 업데이트 현상은 테이블의 정합성을 맞추는데 상당히 어려운 문제를 만들어낸다.

 

트랜잭션 사용 주의사항

트랜잭션 또한 DBMS 의 커넥션과 동일하게 꼭 필요한 최소의 코드에만 적용하는 것이 좋다. 이는 프로그램 코드에서 트랜잭션 범위를 최소화 하라는 의미다.

 

사용자의 게시판 업로드 과정을 과장해서 예를 들어 보겠다.

 

순서	로직	트랜잭션
1	처리 시작	시작
2	로그인 확인
3	게시판 내용 오류 확인
4	첨부 파일 확인 및 저장
5	게시판 내용 DB 저장
6	첨부 파일 DB 저장
7	저장된 내용 DB 조회
8	게시물 등록에 대한 알림 메일 발송
9	알림 메일 발송 이력을 DB 에 저장
10	처리 완료	종료

 

위와 같이 트랜잭션을 실행할 경우에는 문제점이 아래와 같다.

 

• 트랜잭션은 실제로 1번 로직에서 시작을 하고 있지만 데이터베이스에 데이터를 저장하는 시점은 5번 로직부터 시작되는 것을 확인할 수 있다. 그래서 5번 이전에 로직이 아무리 빨리 처리된다고 해도 트랜잭션 로직에 포함시킬 필요가 전혀 없다.
• 일반적으로 데이터베이스의 커넥션의 개수가 제한적이라서 각 단위 프로그램이 커넥션을 소유하는 시간이 길어질 수록 사용 가능한 여유 커넥션은 줄어든다.
• 그리고 어느 순간에는 각 단위 프로그램에서 커넥션을 가져가기 위해 기다려야 하는 상황이 발생할 수도 있다.
• 더 큰 위험은 8번 작업이다. 메일 전송이나 FTP 파일 전송작업 등은 네트워크를 통해 원격 서버와 통신하는 등 작업은 어떻게해서는 DBMS 의 트랜잭션 내에셔 제거하는 것이 좋다. 프로그램이 실행하는 동안 메일 서버와 통신할 수 없는 상황이 발생한다면 웹 서버뿐만 아니라 DBMS 서버까지 위험해지는 상황이 발생해질 수 있다.

위에 문제점을 보완해서 아래와 같이 수정할 수 있다.

 

순서	로직	트랜잭션
1	처리 시작
2	로그인 확인
3	게시판 내용 오류 확인
4	첨부 파일 확인 및 저장	시작
5	게시판 내용 DB 저장
6	첨부 파일 DB 저장	종료
7	저장된 내용 DB 조회
8	게시물 등록에 대한 알림 메일 발송	시작
9	알림 메일 발송 이력을 DB 에 저장	종료
10	처리 완료