내맘대로 정리하는 Real MySQL #4장) 트랜잭션과 잠금 - 잠금 편

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을 위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

잠금

• 동시성을 제어하기 위한 기능
• 여러 커넥션에서 동시에 동일한 자원을 요청할 경우 순서대로 한 시점에는 하나의 커넥션만 변경할 수 있게 해주는 역할을 수행
• MySQL 엔진 레벨 잠금과 스토리지 엔진 레벨 잠금 두 가지 종류가 있다.

MySQL 엔진의 잠금

1. 글로벌 락
   • FLUSH TABLES WITH RAED LOCK 명령으로만 획득할 수 있으며, 한 세션에서 글로벌 락을 획득하면 MySQL 서버 전체에 락이 걸린다. 다른 세션은 SELECT을 제외한 대부분의 DDL 문장이나 DML 문장 실행을 대기하게 된다.
2. 테이블 락
   • 개별 테이블 단위로 설정되는 잠금
   • 명시적 잠금 : LOCK TABLES table_name [READ | WRITE], 해제 : UNLOCK TABLES
   • 온라인 작업에 상당한 영향을 미치기 때문에 특별한 상황이 아니면 애플리케이션에서 거의 사용할 필요가 없다.
   • 묵시적 잠금은 MyISAM 혹은 MEMORY 테이블에 데이터를 변경하는 쿼리가 실행되면 발생한다.
   • InnoDB는 레코드 기반의 잠금을 제공하기 때문에 DDL이 발생하는 경우에만 테이블 락이 걸리게 된다.
3. 유저 락
   • GET_LOCK() 함수를 이용해 임의로 잠금을 설정할 수 있는데, DB 객체에 대해 잠금을 거는 것이 아니라, 사용자가 지정한 문자열에 대해 잠금을 걸고 해제하는 방식이다
   • 한꺼번에 많은 레코드를 변경하는 쿼리의 경우 데드락의 원인이 될 수 있기 때문에 이럴때 유저 락을 활용할 수 있다.
4. 네임 락
   • 데이터베이스 객체(대표적으로 테이블 or 뷰)의 이름을 변경하는 경우 획득하는 잠금이다. 명시적으로 획득하거나 해제할 수 없고 RENAME TABLE 등의 명령어를 사용했을때 잠금되었다 해제된다.

MyISAM, MEMORY 스토리지 엔진 잠금

• 읽기 잠금 : 테이블에 쓰기 잠금이 걸려 있지 않으면 읽기 잠금을 획득하고 읽기 작업을 시작할 수 있다.
• 쓰기 잠금 : 테이블에 아무런 잠금이 걸려 있지 않아야면 쓰기 잠금을 획득할 수 있고, 그렇지 않다면 다른 잠금이 해제될 때까지 기다려야 한다.

테이블 잠금 확인법 : SHOW OPEN TABLES FROM db LIKE '%s';

• In_use : 해당 테이블을 잠그고 있는 클라이언트의 수 + 그 테이블의 잠금을 기다리는 클라이언트의 수
• Name_locked : 네임 락 여부

어떤 클라이언트의 커넥션이 잠금을 기다리고 있는지 확인하기 : SHOW PROCESSLIST;

• State가 비어 있다면, 현재 잠금을 걸고 있다는 것
• State가 Locked라면 잠금을 기다리고 있는 것

InnoDB 스토리지 엔진의 잠금

• 비관적 잠금 : "내가 변경하고자 하는 레코드를 다른 트랜잭션에서도 변경할 수 있다"라는 비관적인 전제를 가지고 레코드를 변경하기 전에 아무도 못들어오게 잠궈두는 방식 (InnoDB가 사용하는 방식)

• 낙관적 잠금 : 트랜잭션이 같은 레코드를 변경할 가능성은 희박하다고 가정하고, 일단 잠금 없이 변경한 후에 마지막에 충돌이 있었는지 확인하여 ROLLBACK처럼 처리하는 방법

• 레코드 락 : 레코드만을 잠그는 것. 중요한 차이는 InnoDB의 경우 인덱스의 레코드를 잠그게 된다. 보조 인덱스를 이용한 변경 작업은 넥스트 키 락이나 갭 락을 사용하거나 PK 혹은 유니크 인덱스에 의한 변경작업은 레코드 락을 건다.

• 갭 락 : 레코드 자체 뿐아니라 바로 인접한 레코드 사이의 간격만을 잠그는 것. 갭 락의 역할은 레코드와 레코드 사이의 간격에 새로운 레코드가 생성되는 것을 제어하는 것이다. 이것은 실존하는 것이 아니라 개념일뿐이며, 넥스트 키 락의 일부로 사용된다.

• 넥스트 키 락 : 레코드 락과 갭 락을 합쳐 놓은 형태이다. STATEMENT 포맷의 바이너리 로그를 사용하는 MySQL 서버에서는 REPEATABLE_READ 격리 수준을 사용해야 한다. InnoDB의 갭 락이나 넥스트 락은 바이너리 로그에 기록되는 쿼리가 슬레이브에서 실행될 때 마스터에서 만들어 낸 결과와 동일한 결과를 만들어내도록 보장하는 것이 주 목적이다. 그런데 의외로 넥스트 키 락과 갭 락으로 인해 데드락이 발생하거나 다른 트랜잭션을 기다리게 만드는 일이 자주 발생한다.

• 자동 증가 락 : 자동 증가하는 숫자 값을 추출하기(Auto Increment) 위한 락으로 명시적으로 획득/해제할 수 없다.

innodb_autoinc_lock_mode 옵션 설명

• innodb_autoinc_lock_mode = 0 : 동일한 잠금 방식으로 모든 INSERT 문장은 자동 증가 락을 가진다
• innodb_autoinc_lock_mode = 1 : 건수를 예측할 수 있는 경우 훨씬 가볍고 빠른 래치(뮤텍스)를 이용해 처리한다. 건수를 예측할 수 없는 경우는 자동증가락을 사용한다.
• innodb_autoinc_lock_mode = 2 : 항상 경량화된 래치를 사용하게 되며, 때문에 하나의 INSERT 문장으로 INSERT되는 레코드이더라도 연속된 값을 보장하지는 않는다. 복제를 사용하는 경우 마스터/슬레이브의 자동 값이 달라질 수도 있다.

인덱스와 잠금

first_name 하나를 인덱스로 가지고 있으며 (first_name, last_name)에 ('Georgi', 'Klassen')이 들어 있는 테이블에 UPDATE employees SET hire_date = NOW() WHERE first_name = 'Georgi' AND last_name = 'Klassen'; 라는 명령을 실행한다고 하자.

이 경우 실제 변경되는 레코드는 하나이지만, InnoDB 잠금은 레코드를 잠그는 것이 아니라 인덱스를 잠그는 방식으로 처리해 검색한 인덱스의 레코드를 모두 잠가야 한다. first_name이 Georgi 인 레코드는 모두 잠기는 것이다.

만약, 테이블에 인덱스가 하나도 없다면 테이블을 풀 스캔하면서 UPDATE 작업을 하는데 외 과정에서 테이블에 있는 모든 레코드를 잠그게 된다. 때문에 MySQL InnoDB에서 인덱스 설계가 중요하다.

트랜잭션 격리 수준과 잠금

InnoDB에서 불필요한 레코드를 모두 잠그는 넥스트 키 락을 필요하게 만드는 주 원인은 바로 복제를 위한 바이너리 로그 때문이다. 아직 많이 사용되지는 않지만 레코드 기반의 바이너리 로그를 사용하거나 바이너리 로그를 사용하지 않는 경우는 InnoDB의 갭 락이나 넥스트 키 락의 사용을 대폭 줄일 수 있다. (사용자의 쿼리 요청을 동시에 더 많이 처리할 수 있다)

MySQL 5.1 이상에서 다음 조합으로 대부분의 갭 락이나 넥스트 키 락을 제거할 수 있다.

• 바이너리 로그 비활성화, 트랜잭션 격리 수준 READ-COMMITTED
• 레코드 기반의 바이너리 로그 사용, innodb_locks_unsafe_for_binlog = 1, 트랜잭션 격리 수준을 READ-COMMITTED로 설정

레코드 잠금 확인 (MySQL 5.1 이상)

• SHOW PROCESSLIST;명령어를 사용하면 현재 실행중인 쿼리 목록을 볼 수 있다.
• INFORMATION_SCHEMA라는 DB에 INNODB_TRX, INNODB_LOCKS, INNODB_LOCK_WAITS 라는 테이블을 사용하면 트랜잭션 상태를 쉽게 확인할 수 있다.

SELECT
  r.trx_id waiting_trx_id,
  r.trx_mysql_thread_id waiting_thread
  r.trx_query waiting_query,
  b.trx_id blocking_trx_id,
  b.trx_mysql_thread_id blocking_thread,
  b.trx_query blocking_query
FROM information.schema.innodb_lock_waits w
  INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

waiting.. 칼럼은 잠금을 기다리는 트랜잭션이나 프로세스이며, blocking... 칼럼은 잠금을 해제하지 않아 다른 트랜잭션을 막고 있는 트랜잭션이나 프로세스이다.

위의 쿼리로 실행되어 나온 정보들을 바탕으로 트랜잭션 상세 정보를 보고 싶다면 아래 쿼리를 쓸 수 있다.

SELECT * FROM innodb_trx WHERE trx_id = '검색해서 나온 trx_id'