17) NoWait & Skip Locked , 18) Union vs Union All

select for update NOWAIT

• 잠금 대상 레코드가 이미 다른 세션에 의해 잠겨있는 경우, 잠금을 대기하는 것이 아니라 바로 에러를 반환한다.
  • Statement aborted because locks could not be acquired immediately and NOWAIT is set.
• innodb_lock_wait_timeout 옵션을 0으로 설정한 것과 유사한 효과 (옵션의 기본값은 50)
• 트랜잭션 내에서 NOWAIT 쿼리를 실행하여 에러가 반환되더라도, 열어둔 트랜잭션은 그대로 유지된다.

불필요하게 잠금을 오래기다리지 않고 바로 결과를 확인하고 끝낼수 있다.

 

select for update SKIP LOCKED

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

session1에서 잠금 id =1 row는 skip하고 id=2 row를 return한것을 확인할 수 있다.

 SKIP Locked with join 을 사용한 경우이다. 

1번째 row를 session 이 lock하고있으니 skip locked 이니 session2는 2번째 row를 가져올것으로 기대하지만 그렇지않다.

왜냐하면 event row가 락이 걸려있기 때문이다. 조건을 만족하는 row가 0개 반환이된다. 사실상 skip locked가 의미가 없게되는것이다.

왼쪽은 session1에 의해 event table도 잠금이 걸려있어서 session 2가 대기하다가 timeout이 난것이다.

오른쪽은 of c를 통해서 coupon만 잠금을 걸게되어 eventable 은 문제없이 쿼리수행이 가능한 경우이다.

---

Union

• 2개 이상 쿼리 결과의 합집합
• union은 결과 집합의 레코드 확장
• join은 결과 집합의 컬럼 확장

join의 경우 인덱스를 잘 활용하면 임시테이블을 통간 가공작업이 필요하지 않지만 유니온의 경우 아무리 인덱스를 잘 사용해서 최적화 한다해도 임시테이블을 사용한다.

Even if indexes are present and well optimized for each individual query, the results still need to be merged into a single result set, and this is where temporary tables come into play.

 

 

union all vs union distinct

• all 은 중복과 상관없이 다 가져다주지만 distinct 는 교집합으로 중복은 제거해서 가져다 준다.
• all은 가공과정이 없어서 별도로 정렬하거나 임시테이블에 저장하는 작업이 필요하지 않다.

mysql에서 중복을 제거하는 방법이다.

• 쿼리의 결과와 동일한 형태의 임시테이블을 생성한다. 모든 column으로 구성된 unqie index도 같이 만든다.
• 결과집합을 row들을 select하면서 임시테이블에 동일한 레코드가 있는지 확인하고 없으면 insert한다.
• 레코드의 중복여부는 PK나 UniqueIndex가 아닌 레코드의 모든 컬럼 값 조합으로 중복 여부를 확인한다.
  • union distinct 쿼리는 column개수가 많아질수록 성능이 떨어질 수 밖에 없다.
  • column을 최대한 적게 가져와서 union distinct 를 수행해야한다.

중복되는 row가 없는 상태인데 ALL 이 훨씬 빠르다.

union 이라고 쓰면 distinct로 동작한다.