SELECT ... FOR UPDATE - NO WAIT & SKIP LOCKED

1. 들어가며

 

예전 시간에 SELECT ... FOR UPDATE와 함께 MySQL의 다양한 락들에 대해 알아보았습니다. 오늘은 SELECT ... FOR UPDATE라는 무거운 친구를 다양하게 튜닝해볼 수 있는 NO WAIT와 SKIP LOCKED에 대해 알아보겠습니다.

 

2. SELECT ... FOR UPDATE NOWAIT

정의

잠금 대상 레코드가 이미 다른 세션에 의해 잠겨있는 경우, 잠금을 대기하는 것이 아니라 바로 에러를 반환하는 것을 말합니다.

ERROR 3572 (HY000): Statement aborted because lock(s) could not be acquired immediately and NOWAIT is set.

 

• 쿼리의 최대 잠금 시간인 InnoDB lock time을 0으로 하는 것과 동일한 효과입니다. (물론 해당 시간을 0으로 할 수 없고 최소 1입니다.)
• 트랜잭션 내에서 NOWAIT 쿼리를 실행하여 에러가 반환되더라도, 롤백되는 것이 아니라 열어둔 트랜잭션은 그대로 유지되는 특성을 가집니다.

예시와 함께 살펴볼까요?

 

• 4번 유저에 대한 정보를 NOWAIT로 가져오는데, 다른 트랜잭션에서 해당 트랜잭션 커밋 전에 같은 데이터를 요청할 경우 기다리는 것이 아니라 에러를 바로 반환합니다.
• 즉 불필요하게 잠금을 오래 확인하지 않고 바로 나올 수 있다는 장점이 있습니다.

3. SELECT … FOR UPDATE SKIP LOCKED

정의

• 잠금 대상 레코드 중에 다른 세션에 의해 이미 잠금이 걸려 있는 레코드는 스킵하고, 잠금이 걸려있지 않은 레코드를 잠그고 반환
• 따라서 잠금 대상 레코드가 비결정적(Non-deterministic)으로 정해집니다.
  • 즉, 쿼리 실행 시 어떤 레코드가 잠금이 걸릴지 예측하기 어렵습니다.
• 잠금 대상 레코드들이 모두 잠금이 걸려있는 경우에는 빈 결과를 반환합니다.
  • 반환되는 결과 데이터는 없다하더라도, 경우에 따라 Gap-Lock을 점유할 수 있습니다.
• ORDER BY & LIMIT 절과 함께 많이 사용됩니다.

예시

 

• 선착순으로 coupon을 제공해야 될 때 SKIP LOCKED 쿼리를 실행하면 id1 row가 반환됩니다.
• 다른 트랜잭션에서 요구하면 1은 잠금이기에 id2 row를 반환합니다.
• skip locked가 없었다면? session 2에서는 session 1을 기다리거나 최대 잠금 시간을 대기해 에러를 반환합니다. 즉 동시성이 떨어지게 됩니다. 이를 극복하기 위해 사용합니다.

JOIN과 함께 사용하기

SELECT c.coupon_no, e.name
FROM coupon c
INNER JOIN event e ON e.id = c.event_id
WHERE c.event_id=1
  AND c.is_used='N'
ORDER BY c.id
LIMIT 1
FOR UPDATE SKIP LOCKED

 

위와 같은 쿼리를 두 세션에서 실행하면 어떻게 될까요?

• 두번째 세션에서는 단 하나의 쿼리도 반환받지 못합니다.
• 그 이유는 join하는 이벤트 테이블에 대해 이미 잠금이 걸려있기 때문입니다. 1:N일 때 1에 잠금이 걸려 있으므로 N으로 가기 전에 바로 반환되는 것맂이다. NOWAIT와 SKIP LOCKED 모두 마찬가지입니다.

해결법: FOR UPDATE of <table>을 붙여 동시성을 높일 수 있습니다. 위 쿼리에서는 event table에 대한 잠금이 필요하지 않으므로 coupon만 잠그는 것입니다.