잠금 이슈

잠금 방법

MySQL은 MyISAM 및 MEMORY 테이블에 대해서는 테이블-레벨 잠금을 사용하며, BDB 테이블에 대해서는 페이지-레벨 잠금을, 그리고 InnoDB 테이블에 대해서는 열-레벨 잠금을 사용한다.

대부분의 경우에, 여러분은 특정 어플리케이션에 대해서 가장 최선의 잠금 방식을 경험적으로 선택할 수 있으나, 주어진 잠금 방식이 다른 타입에 비해 우수하다고 단정짓는 것은 매우 어렵다. 사용하는 어플리케이션에 따라서 서로 다른 잠금 방식을 사용할 수 있다.

스토리지 엔진을 열 레벨 잠금과 함께 사용하기 위해서는, 여러분이 사용하는 어플리케이션 및 그것이 사용하는 선택 및 업데이트에는 어떤 것들이 있는지를 잘 살펴 보아야 한다. 예를 들면, 대부분의 웹 어플리케이션들은 많은 선택은 하지만, 상대적으로 적은 삭제를 하며, 업데이트는 주로 키 값을 기반으로 실행하고, 열 삽입은 몇몇 특정 테이블에 대해서만 실행을 한다. MySQL의 MyISAM 설정은 이런 용도에 잘 들어 맞게 튜닝된 것이다.

MySQL에서의 테이블 잠금은 테이블 레벨 잠금을 사용하는 스토리지 엔진에 대해서 데드락-프리 (deadlock-free)가 된다. 쿼리를 시작할 때 항상 모든 필요한 잠금을 한번에 요청하고 항상 동일한 순서로 테이블을 잠금으로써 데드락을 없앨 수가 있다.

WRITE 잠금을 위해 MySQL이 사용하는 테이블 잠금 방식은 아래와 같다:

• 테이블에 아무런 잠금도 존재하지 않으면, 테이블에 쓰기 잠금을 추가한다.
• 그렇지 않으면, 쓰기 잠금 큐에 잠금 요청을 넣어 둔다.

READ 잠금 동작을 위한 테이블 잠금 방식은 다음과 같다:

• 테이블에 쓰기 잠금이 존재하지 않으면, 읽기 잠금을 테이블에 추가한다.
• 그렇지 않으면, 일기 잠금 큐에 잠금 요청을 넣어 둔다

잠금이 해제가 되면, 그 잠금은 쓰기 잠금 큐에 있는 쓰레드가 사용할 수 있게 되며 그 다음에는 읽기 잠금 큐에 있는 쓰레드가 사용할 수 있게 된다. 이것은 여러분이 테이블에 대해서 많은 업데이트를 했다면, SELECT 명령문은 더 이상의 업데이트가 생기기 않을 때까지 기다리게 된다는 것을 의미하는 것이다.

여러분은 Table_locks_waited 및 Table_locks_immediate 상태 변수를 검사함으로써 시스템 상의 테이블 잠금 경쟁을 분석할 수가 있다:

mysql> SHOW STATUS LIKE 'Table%';

+-----------------------+---------+

| Variable_name         | Value   |

+-----------------------+---------+

| Table_locks_immediate | 1151552 |

| Table_locks_waited    | 15324   |

+-----------------------+---------+

만일 MyISAM 테이블이 중간에 어떠한 자유 블럭 (free block)도 가지고 있지 않다면, 열 삽입은 항상 데이터 파일의 맨 마지막에 이루어진다. 이와 같은 경우에는, 자유롭게 INSERT 및 SELECT 명령문을 잠금 없이 MyISAM 테이블 용으로 동시에 사용할 수 있다. 즉, 여러분은 다른 클라이언트가 MyISAM 테이블을 읽는 동안에도 이 테이블 안에 열을 삽입할 수가 있다는 것이다.

만일 동시 삽입이 불가능한 상태에서 많은 수의 INSERT 및 SELECT 동작을 한 테이블에서 실행하고자 한다면, 여러분은 임시 테이블 temp_table에 열을 삽입하고 난 후에 이 임시 파일에 있는 열을 가지고 실제 테이블을 업데이트 하면 된다. 아래의 코드를 가지고 실행하면 된다:

mysql> LOCK TABLES real_table WRITE, insert_table WRITE;

mysql> INSERT INTO real_table SELECT * FROM insert_table;

mysql> TRUNCATE TABLE insert_table;

mysql> UNLOCK TABLES;

InnoDB는 열 레벨의 잠금을 사용하고 BDB는 페이지 레벨의 잠금을 사용한다. 이 두 가지 스토리지 엔진의 경우, 데드락이 발생할 가능성이 있는데, 그 이유는 이 엔진들은 트랜젝션이 시작되는 시점이 아닌SQL 명령문을 실행하는 동안 자동으로 잠금을 얻어내기 때문이다.

열 레벨 잠금의 장점으로는 다음과 같은 것이 있다:

• 많은 쓰레드에서 서로 다른 열을 가져올 때 충돌이 거의 일어나지 않음.
• 롤백 (rollback)에 대한 변경이 거의 없음.
• 오랜 시간 동안 하나의 열에 대한 잠금이 가능함.

반면에, 열 잠금의 단점은 다음과 같다:

• 페이지 레벨 또는 테이블 레벨 잠금 보다 더 많은 메모리가 필요함.
• 테이블 대부분에서 이 잠금을 사용하게 되면 보다 많은 수의 잠금이 이루어지기 때문에, 페이지 레벨 또는 테이블 레벨의 잠금에 비해 속도가 느려지게 된다.
• 데이터 대부분에 대해서 GROUP BY를 자주 사용하거나 또는 테이블 전체에 대한 스캔을 자주 실행해야 하는 경우에는 다른 두 가지의 잠금보다 현격한 성능 저하가 생긴다.

아래의 경우에는, 테이블 잠금이 나머지 두 개의 잠금 방식보다 훨씬 성능이 좋게 된다:

• 테이블에 대한 대부분의 명령문이 읽기 연산인 경우.
• 읽기 및 쓰기가 혼재되어 있으며, 이때 쓰기는 업데이트 또는 하나의 키 읽기와 함께 패치 (fetch)될 수 있는 단일 열에 대한 삭제인 경우:
  

  UPDATE tbl_name SET column=value WHERE unique_key_col=key_value;
  
  DELETE FROM tbl_name WHERE unique_key_col=key_value;
  
  
  
  

• SELECT는 INSERT 명령문과 동시에 연결이 되어 있고, UPDATE 또는 DELETE 명령문과는 거의 연결 되지 않은 경우
• 어떠한 라이터 (writer)도 없이 많은 수의 스캔 또는 GROUP BY 동작이 전체 테이블에서 이루어지는 경우

보다 높은 레벨의 잠금을 사용하면, 서로 다른 형태의 잠금을 지원함으로써 어플리케이션을 보다 쉽게 튜닝할 수가 있는데, 그 이유는 잠금으로 인한 오버 헤드가 낮은 레벨 (lower-level) 잠금보다 덜 생기기 때문이다.

Table 잠금 이슈

매우 빠른 잠금을 위해서 MySQL은 InnoDB 및 BDB를 제외한 모든 스토리지 엔진에 대해서는 테이블 잠금을 사용한다.

InnoDB 및 BDB 테이블의 경우에는LOCK TABLES를 가지고 명확하게 테이블을 잠그는 경우에만 테이블 잠금을 사용한다. 이러한 스토리지 엔진에 대해서는 LOCK TABLES를 사용하지 말 것을 권장하는데, 그 이유는 페이지 트랜젝션 분리 (isolation)를 위해서 InnoDB는 자동 열 레벨 잠금을 그리고 BDB 는 자동 레벨 잠금을 사용하기 때문이다.

대형 테이블의 경우, 대부분의 어플리케이션에 대해서는 열 잠금 보다는 테이블 잠금이 보다 좋기는 하지만, 아래와 같이 몇 가지의 예외적인 경우가 존재한다:

• 테이블 잠금은 많은 수의 쓰레드가 하나의 테이블에서 동시에 읽기를 실행할 수 있도록 해 주지만, 만일 하나의 쓰레드가 테이블에 쓰기를 하고자 한다면, 그 쓰레드는 테이블에 대해서 배타적인 접속 (exclusive access)를 우선 가져야만 한다. 업데이트를 하는 동안에는, 모든 다른 쓰레드는 업데이트가 끝날 때까지 대기를 해야만 한다.
• 일반적으로, 테이블 업데이트가 테이블 복원 보다 더 중요하기 때문에 우선 순위가 높게 매겨진다. 이것은 테이블에 대해서 많은 양의 SELECT 실행이 있다고 하더라도 테이블에 대한 업데이트는 “굶음 (starved)”이 되지 말아야 한다는 것을 의미한다.
• 테이블 잠금은 디스크가 꽉 차서 쓰레드를 진행 시키기 전에 여유 공간이 필요하기 때문에 쓰레드가 대기를 해야 하는 경우에는 문제의 원인이 되기도 한다. 이와 같은 경우, 문제 테이블에 접속을 하고자 하는 모든 쓰레드는 사용 가능한 디스크 공간이 생길 때까지 대기 상태가 된다.

테이블 잠금은 아래와 같은 시나리오 아래에서도 단점을 가지게 된다:

• SELECT명령문을 어떤 클라이언트가 요청 한다.
• 그런 후에 다른 클라이언트 하나가 동일한 테이블에서 UPDATE를 실행한다. 이 클라이언트는 SELECT가 끝날 때까지 대기를 한다.
• 또 다른 클라이언트 하나가 동일한 테이블에서 별도의 SELECT 명령문을 입력한다. UPDATE 는 SELECT 보다 우선 순위가 높기 때문에, 이 SELECT 명령문은 UPDATE가 마칠 때까지, 그리고 처음의 SELECT가 끝날 때까지 대기를 한다.

아래의 사항들은 테이블 잠금에 의해 야기되는 경쟁 상황을 피하거나 또는 절감 시킬 수 있는 여러 가지 방법을 설명해 주는 것들이다:

• 짧은 시간 동안만 테이블을 잠그도록 하기 위해 SELECT 명령문이 빠르게 구동되도록 만든다. 이렇게 하기 위해서는 요약 테이블을 생성해야 할 것이다.
• mysqld를 --low-priority-updates와 함께 시작한다. 이것은 테이블을 업데이트 (수정)하는 모든 명령문에 대해서 SELECT 명령문 보다 낮은 우선 순위를 할당해 준다. 이렇게 하면, 앞의 시나리오에서 나오는 두 번째 SELECT 명령문은 UPDATE 명령문 보다 먼저 실행이 되며, 처음 SELECT가 끝날 때까지 대기를 할 필요가 없게 된다.
• SET LOW_PRIORITY_UPDATES=1 명령문을 사용하면, 특정 접속에서 입력된 모든 업데이트에 대해서는 낮은 우선 순위를 가지도록 지정할 수가 있다.
• LOW_PRIORITY 속성을 사용하면 특정 INSERT, UPDATE, 또는 DELETE 명령문이 낮은 우선 순위를 가지도록 할 수 있다.
• HIGH_PRIORITY 속성을 사용하면 특정 SELECT 명령문이 높은 우선 순위를 가지도록 만들 수가 있다.
• mysqld이 max_write_lock_count 시스템 변수에 대해서 낮은 우선 순위를 가지고 시작하도록 해서 MySQL로 하여금 특정 수의 열 삽입이 이루어진 테이블을 기다리는 모든 SELECT 명령문의 우선 순위를 임시적으로 높이도록 만들 수가 있다. 특정 수의 WRITE 잠금을 실행한 후에 READ 잠금을 허용한다.
• 만일 여러분이 SELECT와 연결되어 있는 INSERT에 문제를 가지고 있다면, MyISAM 테이블 변환 (switching)을 고려해 볼만 한데, 이것은 동시 SELECT 및 INSERT 명령문을 지원한다.
• 만일 여러분이 동일한 테이블에서 삽입과 삭제를 혼용해서 사용한다면, INSERT DELAYED 가 매우 큰 도움을 줄 것이다.
• 만일 SELECT 및 DELETE 명령문 혼용에 있어서 어떠한 문제가 발생한다면, DELETE에 대한 LIMIT 옵션이 도움이 될 것이다.
• SQL_BUFFER_RESULT를 SELECT 명령문과 함께 사용하는 것이 테이블 잠금 기간을 줄이는데 도움이 될 것이다.
• mysys/thr_lock.c에 있는 잠금 코드를 변경 시키면 단일 큐를 사용할 수가 있다. 이렇게 하면, 쓰기 잠금 및 읽기 잠금은 동일한 우선 순위를 가지게 되며, 몇몇 어플리케이션 실행에 도움이 되기도 한다.

아래의 내용은 MySQL에서 테이블 잠금에 대해 고려해야 할 몇 가지 팁 들을 나열한 것이다:

• 동시 사용자의 수는 동일한 테이블에서 많은 수의 열을 조사할 필요가 있는 선택을 업데이트와 함께 사용하지 않으면 아무런 문제가 되지 않는다.
• 여러분은 LOCK TABLES를 사용해서 속도를 개선 시킬 수가 있는데, 그 이유는 단일 잠금 내에 있는 많은 업데이트는 잠금이 없는 업데이트 보다 속도가 빠르기 때문이다. 한 테이블의 내용을 여러 개의 테이블에 분산 시키는 것도 도움이 된다.
• 만일 여러분이 MySQL 테이블 잠금과 함께 성능상의 문제가 발생한다면, 여러분이 사용하고 있는 테이블을 InnoDB 또는 BDB 테이블로 전환해서 성능을 개선하도록 시도해 본다.

동시 삽입

MyISAM 테이블의 경우, 테이블 중간에 삭제된 열이 없다면, SELECT 명령문이 구동 중에 있는 동안에도 열을 추가하기 위한 동시 삽입을 사용할 수가 있다.

동시 삽입을 사용할 수 있는 경우에는, INSERT 명령문에 대해서 DELAYED 모디파이어 (modifier)를 사용할 필요가 거의 없다.

여러분이 바이너리 로그를 사용하고 있다면, 동시 삽입은 CREATE ... SELECT 또는 INSERT ... SELECT 명령문에 대해서는 정상 삽입 (normal insert)으로 변환된다. 이것은 백업 동작이 진행되는 동안 로그를 적용 시킴으로써 여러분이 테이블의 정확한 복사본을 재 생성할 수 있게 끔 해 준다.

만일 여러분이 동시 삽입 조건을 만족 시키는MyISAM 테이블과 함께CONCURRENT를 지정한다면 (즉, 테이블 중간에 아무런 프리 블럭 (free block)도 없는 경우), LOAD DATA INFILE를 사용해서 LOAD DATA가 실행되는 동안에 다른 쓰레드가 테이블에서 데이터를 추출할 수 있도록 해 준다. 이 옵션을 사용하면 LOAD DATA의 성능에 다소 영향을 줄 수가 있는데, 비록 다른 쓰레드가 동시에 테이블을 사용하는 경우에도 가능하게 된다.