[MySQL] 쿼리를 의도적으로 지연시키기. (sleep function)

Transaction 이나 Lock 이 걸리는지를 테스트 해 보기 위해서,

또는 대규모의 접속 상황을 가정해서, 동시에 insert 를 한다던지의 상황을 만들어내기 위해,

쿼리를 의도적으로 지연시키고 싶은 경우가 있다.

이럴때 MySQL 에서는 sleep function을 사용하면 된다.

[참고] http://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_sleep

[사용 예] http://dev.kthcorp.com/2012/03/14/mysql-table-lock/

MySQL 온라인 매뉴얼을 보면 초 단위로 지연시킬 수 있다고 하는데, 실제로 해보면, 주어진 초보다 좀 더 지연되는것 같다.

SELECT * FROM {테이블 이름} WHERE SLEEP(3)=0;

위와 같이 하면 

3초 쉬었다가 조회하라는 의미인데, 6초 정도 있다가 조회된다. (내 Desktop에서 테스트 했는데, 컴퓨터가 느려서 그런건가...? ㅜ.ㅜ)
=> 아래 댓글 주신 분의 도움으로, 레코드 당 정해진 초 동안 지연된다는 것을 알았다.
SELECT * FROM CODE_INFO WHERE SLEEP(1)=0 AND CODE_ID LIKE 'AU%' ;
조회된 결과 record가 3개 있었고, query 출력 결과는 2.995 sec 였다.
 SLEEP(duration)
Sleeps (pauses) for the number of seconds given by the duration argument, then returns 0. If SLEEP() is interrupted, it returns 1. The duration may have a fractional part. This function was added in MySQL 5.0.12.

sleep function 의 반환값은 정상적으로 지연된 경우는 0 을 반환하고, sleep 이 interrupt 된 경우는 1을 반환한다고 한다.

SELECT 를 먼저 하고, 나서 그 결과를 몇 초 있다가 보여지는 것인지, 아니면 몇 초 있다가 SELECT 를 하고 그 결과를 보여주는지 궁금해서 테스트를 해 봤다.

A 세션에서 

SELECT * FROM T_USER_INFO WHERE SLEEP(5)=0;

위의 쿼리 실행하고,

B 세션에서

T_USER_INFO 에 INSERT 하고, COMMIT 함.

A 세션의 반환되는 값을 보니, 

B 세션에서 INSERT 하기 전의 결과가 출력됐다.

그렇다면... SELECT 를 먼저 하고 나서, 몇 초 지연했다가, 그 결과를 보여주는 것이구나...

☞ Transaction Mode가 어떻게 되는지 먼저 확인이 필요하다.

MySQL의 Transaction Default Mode는 Oracle 보다도 더 엄격한, 읽기 일관성을 유지해주는 모드이다. (REPEATABLE-READ)

[관련 post] 

[MySQL] Transaction Isolation Level 변경시키기.

http://bryan7.tistory.com/105

정확한 테스트를 위해서 MySQL 5.6 Command Line Client 창을 2개 띄워놓고, 동일하게 다음과 같은 Script를 실행시켰다.

-- DEFAULT 가 AUTOCOMMIT TRUE 인 상태라 안 해줘도 된다.

set autocommit=TRUE;

SELECT @@AUTOCOMMIT;

-- 1

SELECT @@TX_ISOLATION;

-- REPEATABLE-READ

SET TX_ISOLATION='READ-COMMITTED';

SELECT @@TX_ISOLATION;

-- READ-COMMITTED

COMMIT;

MySQL 5.6 Command Line Client 창 #2에서 

SELECT NOW(), USER_ID, USER_NAME, SYSDATE() FROM TB_USER WHERE SLEEP(5)=0 AND USER_ID='user01';

위 SQL문을 실행하고 나서 바로,MySQL 5.6 Command Line Client 창 #1에서 

UPDATE TB_USER SET USER_NAME='user01_new' WHERE USER_ID='user01';

SQL문을 실행.

결과는?

값이 안 바뀌었다. 결론은? SELECT를 먼저 하고, 일정 시간 지연시킨 후에 결과를 보여주는 것이 맞다.

참고로, ORACLE 에서도 sleep procedure 가 있었다. DBMS_LOCK.sleep()

google 에서 조회해 보니, Oracle 의 DBMS_LOCK.sleep()  procedure 도 파라미터로 주는 시간과 실제 지연되는 시간이 차이가 무지 많이 난다는 불평이 많았다.