정리중 - 3

• Transcation
  논리적인 작업 단위로, 전부 처리(commit)되거나 처리되지 않는(rollback) 원자성을 보장해야 한다.
• Lock
  서로 다른 작업에서 같은 자원이 동시에 필요하면 스레드간의 경합(Race)가 일어난다.
  이 때 각자의 작업에 영향이 끼치지 않도록 동시성을 보장하기 위해 Thread Lock을 걸어 스레드를 대기시킨다.

 

MySQL에서 사용하는 Lock은 2가지가 있다.

- MySQL Engine Level Lock

- Storage Engine Level Lock

 

📌 스토리지 엔진 레벨 락

스토리지 엔진 레벨 (InnoDB)에서 제공하는 Lock은 2가지 방식이 있다.

별다른 설정을 하지 않는다면 비관적 Lock (Pessimistic locking)을 기본으로 사용한다.

• 비관적인(Pessimistic) lock
  ➡ 아무튼 트랜잭션의 충돌이 발생한다고 가정하고, 일단 락을 걸고 쿼리를 수행하는 방법
• 낙관적인(Optimistic) lock
  ➡ 일단 락 없이 쿼리 수행을 진행하고, 완료되었을 때 다른 트랜잭션에서 충돌이 있었는지 확인하고, 문제가 있다면 충돌 난 트랜잭션을 롤백하는 방식 (버전 정보를 이용하여 업데이트 하는 방법)

대규모 트래픽을 처리하는 서비스는 무결성을 살짝 포기하더라도 lock을 최소화하여 성능을 높이기 위해 낙관적인 Lock으로 변경하여 사용하기도 한다.

 

 

@ Lock을 거는 범위

또한 InnoDB는 Lock을 거는 범위를 상세하게 지정할 수 있다. MyISBM과 다르게 레코드 락도 가능.

• Record Lock
  SELECT col FROM table WHERE col=10 FOR UPDATE; 일 때 col=10 인 레코드에만 락을 건다고 이해하면 된다.
  다만 실제 테이블의 레코드에 Lock을 거는게 아니라, 인덱스 레코드(=인덱스)에 Lock을 건다.
  만약 따로 생성한 인덱스가 없다면 InnoDB가 자체적으로 생성한 클러스터 인덱스를 이용해 Lock을 건다.
  
  
• Gap Lock (범위, 중간 갭 Lock)
  인덱스 레코드와 인접한 앞/뒤 공간에 Lock을 건다. 단독으로 사용되지는 않고 뒤에 설명할 Next Key Lock와 사용.
  Gap Lock은 외래키나 중복키를 검사하는 경우가 아니라면 보통 REPEATABLE_READ 이상의 격리수준에서 발생한다.
  다만 기본 키나 유니크 키에 의한 변경 작업은 Gap Lock 없이 Record Lock으로 인덱스 레코드에만 락을 건다. 
• Next Key Lock
  Record lock + Gap lock 의 형태이다. 인덱스 레코드도 잠구고, 앞 뒤 갭도 잠근다.
  SELECT c1 FROM t WHERE c1 BETWEEN 10 AND 20 FOR UPDATE; 일 때 c1=15 와 같은 동작을 하는 트랜잭션이 있다면 막는다. 반드시 인덱스 범위에만 락을 거는게 아니라, [인덱스 범위 사이에 있는 갭]을 같이 락을 건다.
  예를 들어 c1=10, c1=8 이렇게 2가지 레코드에 Lock 이 걸렸다면, 중간에 있는 c1=9 작업도 Lock으로 막혀있다.
  
  굳이 변태같은 Next key lock을 사용하는 이유는 REPEATABLE_READ의 사용을 강제하기 때문이고, 이를 사용하는 이유는 복제를 위한 바이너리 로그 때문이다.
  MySQL5.1 이후에는 바이너리 로그가 활성화되면 REPEATABLE_READ 이상의 레벨을 강제한다. 그래서 Next Key Lock으로 

• Auto Increment lock
  MySQL에서 자동 증가하는 숫자 값을 얻기위해 Auto Increment를 정의 하는 경우가 있다. 이 떄 여러 스레드가 동시에 Insert를 못하게 걸어주는 Lock이다.

 

 

@ InnoDB는 인덱스 레코드 락입니다.

InnoDB의 레코드락은 '인덱스 레코드' 락이다. 이는 매우 큰 차이인데, 아래의 예제를 통해 알아보자.

 

우리의 신입개발자 A는 first_name 칼럼에만 인덱스를 적용하였고, 아래와 같은 쿼리를 작성하였다.

이 때 [ first_name = 'KIM' ]인 레코드는 300건

        [ first_name = 'KIM' , last_name = 'WON' ]인 레코드는 단 1건이라고 가정해보자.

SELECT COUNT(*) FROM employees WHERE first_name='KIM'; -- 300건 조회된다고 가정
SELECT COUNT(*) FROM employees WHERE first_name='KIM' AND last_name='WON'; -- 1건 조회된다고 가정
UPDATE employees SET hire_date=NOW() WHERE first_name='KIM' AND last_name='WON'; -- 어디에 락이 걸릴까?

first_name 칼럼에만 인덱스가 적용되어있다고 가정해보자. 3번째 Update 쿼리에서 Lock이 걸리는 레코드는 몇 개일까?

 

정답은 300개의 레코드이다. 실제 레코드가 아니라 인덱스 레코드에 락이 걸리기 때문.

[ first_name = 'KIM' , last_name = 'WON' ]인 사람은 단 1건이지만, 인덱스에 Next key Lock을 거는 기본 정책상 first_name에 락이 걸리므로 300건 전체에 Lock을 걸게되고, 실제 수정은 단 1건만 이루어지는 것이다.

만약 인덱스를 아예 만들지 않았다면 어떨까? 이 경우 InnoDB는 레코드 단위의 Next Key Lock을 거니까 괜찮다고 착각하고 있다면, 실제 동작은 테이블 전체 레코드를 잠그게 된다.

 

레코드 단위의 Lock을 제공하지 않았다면, 잠금의 대상이 테이블이라서 문제의 원인이 쉽게 발견되고, 해결할 수 있다.

하지만 레코드 수준 잠금은 해당 쿼리, 해당 레코드가 많이 사용되지 않는다면 잘 발견되지 않는다.

 

그래서 MySQL5.1부터는 information_schema안에 있는 INNODB_TRX에서 레코드 잠금와 잠금 대기에 대한 조회가 가능했었다. 참고로 MySQL8.0 부터는 기존의 스키마 테이블을 제거(Deprecated)하고 performance_scheman의 data_locks, data_lock_waits 라는 새로운 테이블로 정보를 제공한다.

 

 

@ 서브쿼리 SELECT LOCK, 데드락

InnoDB 데드락 발생 경우

[MySQL]MySQL 벼락치기(6) - 트랜잭션과잠금(2)

1. 단순 조회는 당연히 락이 걸리지 않습니다. 다른 트랜잭션이 변경중이라면 실제 값이 아닌 Undo 레코드를 읽습니다.

SELECT ... FROM

 

2. 하지만 아래와 같은 경우는 어떨까요? 서브쿼리의 Select는 Shared Lock이 걸립니다.

아래의 3쿼리가 이 순서대로 일어난다면 영원히 해결되지 않는 데드락 상태에 빠지게 됩니다.

# session 1 - 유저 테이블과 accounts 테이블에서 계정을 삭제하는 세션
DELETE 
FROM user_table
WHERE account_id = 99; # X-LOCK 

# session 2 - VIP 를 찾아서 옵션을 업데이트하는 세션
UPDATE user_table 
SET vip_flag = 1 
WHERE account_id in (SELECT account_id FROM accounts WHERE amount >= 100000000) # S-LOCK 

# session 1 - 유저테이블 쿼리는 날렸으니, 이제 accounts 테이블에 접근해볼까..
DELETE 
FROM accounts 
WHERE account_id = 99; # id=99 인 레코드가 있다고 가정.

해당 쿼리는 데드락이 걸립니다. 아예 동작하지 않습니다.

 

session 1: account_id 를 인덱스로 사용하기 때문에, 인덱스 레코드 account_id=99 에 Lock을 겁니다.

session 2: accounts 테이블에 잔액이 1억원 이상인 유저를 수정하는 쿼리를 날립니다.

- 서브쿼리에서 accounts의 많은 레코드와 갭에 Shared Lock을 겁니다. 서브쿼리는 Undo 로그를 사용하지 않아요.

- 여기서 session2는 앞에서 session1의 account_id=99 인덱스 때문에 user_table 락이 걸려서 대기 상태가 됩니다.

 

session1 : 이후 account_id가 99인 레코드를 accounts에서 지우는 쿼리를 날렸습니다. 하지만 Session2가 서브쿼리에 accounts Lock을 걸어버려서 대기하게 됩니다.

 

 

📌 MySQL 엔진 레벨 락

• 글로벌 락
  모든 테이블에 락을 겁니다. DB 덤프를 따는 등의 정말 특수한 경우가 아니라면 사용하지 않습니다.
• 테이블 락
  각 테이블 단위로 Lock을 겁니다. InnoDB에서 레코드 락이 제공되므로 DML에서는 거의 사용되지 않고, DDL 처럼 스키마 자체를 바꿀 때 종종 사용합니다.
• Name 락
  테이블 이름, 뷰 이름등 스키마 객체의 이름을 변경하는 경우 자동으로 락을 겁니다.
• 유저 락
  사용자가 지정한 문자열에 락을 겁니다. 여러 서버에서 설정 값을 동기화하는 용도로 종종 사용합니다.