본 게시물의 내용과 이미지는 도서 Real MySQL의 내용을 재구성하여 작성되었습니다. 저자, 출판사에 의해 저작권 문제 발생시 게시물이 비공개 될 수 있음을 알립니다.
- 포스팅에 포함된 Query 결과는 AWS EC2 FreeTier(CentOS7.2) 환경에 MySQL v5.7.17 에서 실행한 결과를 포함하였습니다. 실행환경에 따라 Query 실행시간이나 RDBMS 실행계획이 다를 수 있습니다.
LOCK IN SHARE MODE와 FOR UPDATE
InnoDB 테이블에 대해서 레코드를 SELECT 할 때 레코드에 아무런 잠금도 걸지 않는다. SELECT 쿼리를 이용해 읽은 칼럼의 값을 애플리케이션에서 가공해서 다시 업데이트하고자 할 때는 다른 트랜잭션이 그 칼럼의 값을 변경하지 못하게 해야 할 때도 있다. 이럴 때는 레코드를 읽으면서 강제로 잠금을 걸어둘 필요가 있는데, 이를 사용하는 명령이 LOCK IN SHARE MODE와 FOR UPDATE 다.
이 두 가지 명령은 전부 AUTO-COMMIT이 비활성화(OFF)된 상태 또는 BEGIN 명령이나 START TRANSACTION 명령으로 트랜잭션이 시작된 상태에서만 잠금이 유지된다.
- LOCK IN SHARE MODE는 SELECT 된 레코드에 대해 읽기 잠금을 설정하고 다른 세션에서 해당 레코드를 변경하지 못하게 한다.
- FOR UPDATE 옵션은 쓰기 잠금을 설정하고, 다른 트랜젝션에서 그 레코드를 변경하는 것뿐만 아니라 읽지도 못하게 한다.
LOCK IN SHARE MODE나 FOR UPDATE 명령은 SELECT 쿼리 문장의 마지막에 추가해서 다음 예제와 같이 사용하면 된다.
위와 같은 쿼리로 잠긴 레코드는 COMMIT 이나 ROLLBACK 명령과 함께 해제되는데, 그 밖에 잠금만 해제하는 명령은 없다. FOR UPDATE나 LOCK IN SHARE MODE 명령을 사용한 쿼리는 잠금 경합을 꽤 많이 유발하고, 때로는 데드락을 일으키는 경우도 많다.
SELECT INTO OUTFILE
SELECT INTO .. OUTFILE 명령은 SELECT 쿼리의 결과를 화면으로 출력하는 것이 아니라 파일로 저장할 수 있다. SELECT INTO .. OUTFILE 명령은 테이블 단위로 데이터를 덤프받아서 적재하거나 엑셀 파일이나 다른 DBMS로 옮길 때 유용하게 사용될 수 있다. 물론, SELECT INTO .. OUTFILE 명령의 여러 옵션을 이용해 필요한 포맷의 데이터를 쉽게 만들 수 있다.
주의점 3가지.
- SELECT 결과는 MySQL 클라이언트가 아니라 MySQL 서버가 기동중인 장비의 디스크로 저장된다.
- SELECT INTO .. OUTFILE 명령의 결과를 저장할 파일, 그리고 파일이 저장되는 디렉터리는 MySQL 서버를 기동중인 운영체제의 계정이 쓰기 권한을 가지고 있어야 한다.
- 이미 동일한 디렉터리에 동일 이름의 파일이 있을 때는 SELECT INTO .. OUTFILE 명령은 기존 파일을 덮어쓰지 않고 에러를 발생시키고 종료한다.
SELECT INTO .. OUTFILE 은 OUTFILE 옵션 뒤에는 결과를 저장할 파일 경로와 이름을 명시하고, FIELDS 옵션에는 칼럼의 구분자를 LINES 옵션에는 각 레코드의 구분자를 명시한다.
예제로 생성된 파일은 다음과 같다.
추가적으로 칼럼 값이 복잡한 문자열 값을 가지고 있다면 OPTIONALLY ENCLOSED BY 옵션을 사용해주는 것이 좋다. OPTIONALLY ENCLOSED BY 라는 옵션을 사용하면 옵션에 정의된 문자로 각 칼럼의 값을 감싸서 파일에 저장한다.
위의 쿼리에서는 OPTIONAL ENCLOSED BY '"' 옵션을 통해 칼럼값을 쌍따옴표로 감싸서 저장했다. 만약 칼럼의 값이 쌍따옴표를 가지고 있을때는 똑같이 충돌이 발생할 수 있는데 이를 위해 "ESCAPED BY '"' " 옵션을 사용했다.
