데브러너 (DevRunner)

10.11.1 Internal Locking Methods

이 절에서는 내부 락, 즉 여러 세션이 테이블 내용을 두고 경합할 때 이를 관리하기 위해 MySQL 서버 내부에서 수행되는 락킹에 대해 설명합니다. 이러한 유형의 락킹은 완전히 서버에 의해 수행되며 다른 프로그램이 관여하지 않기 때문에 internal이라고 합니다. 다른 프로그램에 의해 MySQL 파일에 대해 수행되는 락킹에 대해서는 Section 10.11.5, “External Locking”을 참조하십시오.

Row-Level Locking

MySQL은 InnoDB 테이블에 대해 row-level locking을 사용하여 여러 세션의 동시 쓰기 접근을 지원하므로, 이러한 테이블은 다중 사용자, 높은 동시성, OLTP 애플리케이션에 적합합니다.

단일 InnoDB 테이블에 대해 여러 동시 쓰기 작업을 수행할 때 데드락을 방지하려면, 트랜잭션 시작 시 수정될 것으로 예상되는 각 로우 그룹에 대해 SELECT ... FOR UPDATE 문을 실행하여 필요한 락을 미리 획득해야 합니다. 이는 데이터 변경 문이 트랜잭션에서 나중에 오더라도 마찬가지입니다. 트랜잭션이 둘 이상의 테이블을 수정하거나 락하는 경우, 각 트랜잭션 내에서 해당 문을 동일한 순서로 실행하십시오. 데드락은 심각한 오류라기보다는 성능에 영향을 미치는 문제에 가깝습니다. InnoDB는 기본적으로 데드락 조건을 자동으로 감지하고, 영향받은 트랜잭션 중 하나를 롤백하기 때문입니다.

동시성이 매우 높은 시스템에서는, 많은 스레드가 동일한 락을 대기할 때 데드락 감지 때문에 속도가 느려질 수 있습니다. 어떤 경우에는 데드락 감지를 비활성화하고, 데드락이 발생했을 때 트랜잭션 롤백을 위해 innodb_lock_wait_timeout 설정에 의존하는 것이 더 효율적일 수 있습니다. 데드락 감지는 innodb_deadlock_detect 구성 옵션을 사용하여 비활성화할 수 있습니다.

Row-level locking의 장점:

서로 다른 세션이 서로 다른 로우에 접근할 때 락 충돌이 적습니다.
롤백 시 변경해야 할 내용이 적습니다.
단일 로우를 오랜 시간 동안 락할 수 있습니다.

Table-Level Locking

MySQL은 MyISAM, MEMORY, MERGE 테이블에 대해 table-level locking을 사용하며, 한 번에 하나의 세션만 이러한 테이블을 업데이트할 수 있도록 허용합니다. 이러한 락킹 수준 때문에 이 스토리지 엔진들은 읽기 전용, 읽기 위주, 또는 단일 사용자 애플리케이션에 더 적합합니다.

이러한 스토리지 엔진들은 항상 필요한 모든 락을 쿼리 시작 시 한 번에 요청하고, 항상 테이블을 동일한 순서로 락함으로써 데드락을 피합니다. 이 전략의 대가는 동시성이 감소한다는 점입니다. 테이블을 수정하려는 다른 세션은, 현재의 데이터 변경 문이 끝날 때까지 기다려야 합니다.

Table-level locking의 장점:

상대적으로 적은 메모리 요구량 (로우 락킹은 락된 각 로우 또는 로우 그룹당 메모리가 필요함)
테이블의 큰 부분에 대해 사용될 때는 하나의 락만 관여하므로 빠릅니다.
데이터의 큰 부분에 대해 GROUP BY 연산을 자주 수행하거나 전체 테이블을 자주 스캔해야 할 때 빠릅니다.

MySQL은 테이블 쓰기 락을 다음과 같이 부여합니다:

테이블에 락이 없으면, 쓰기 락을 걸어 줍니다.
그렇지 않으면, 락 요청을 쓰기 락 큐에 넣습니다.

MySQL은 테이블 읽기 락을 다음과 같이 부여합니다:

테이블에 쓰기 락이 없으면, 읽기 락을 걸어 줍니다.
그렇지 않으면, 락 요청을 읽기 락 큐에 넣습니다.

테이블 업데이트에는 테이블 조회보다 더 높은 우선 순위가 부여됩니다. 따라서 락이 해제되면, 해당 락은 먼저 쓰기 락 큐의 요청에, 그 다음 읽기 락 큐의 요청에 사용 가능합니다. 이는 테이블에 대한 SELECT 작업이 매우 많은 경우에도 테이블 업데이트가 “starved”되지 않도록 보장합니다. 그러나 테이블에 대한 업데이트가 많으면, SELECT 문은 더 이상 업데이트가 없을 때까지 대기합니다.

읽기 및 쓰기 우선 순위를 변경하는 방법에 대한 정보는 Section 10.11.2, “Table Locking Issues”를 참조하십시오.

시스템에서 테이블 락 경합을 분석하려면, Table_locks_immediate 및 Table_locks_waited 상태 변수를 확인하십시오. 이 변수들은 각각 테이블 락 요청이 즉시 허용된 횟수와, 대기해야 했던 횟수를 나타냅니다:

1mysql> SHOW STATUS LIKE 'Table%';
2+-----------------------+---------+
3| Variable_name         | Value   |
4+-----------------------+---------+
5| Table_locks_immediate | 1151552 |
6| Table_locks_waited    | 15324   |
7+-----------------------+---------+

Performance Schema 락 테이블 역시 락킹 정보를 제공합니다. Section 29.12.13, “Performance Schema Lock Tables”를 참조하십시오.

MyISAM 스토리지 엔진은 특정 테이블에 대해 리더와 라이터 사이의 경합을 줄이기 위해 동시 삽입(concurrent insert)을 지원합니다. MyISAM 테이블의 데이터 파일 중간에 빈 블록이 없으면, 로우는 항상 데이터 파일 끝에 삽입됩니다. 이 경우, MyISAM 테이블에 대해 락 없이 INSERT와 SELECT 문을 자유롭게 혼용할 수 있습니다. 즉, 다른 클라이언트가 테이블을 읽는 동시에 MyISAM 테이블에 로우를 삽입할 수 있습니다. 테이블 중간의 로우가 삭제되거나 업데이트되면 홀(hole)이 생길 수 있습니다. 홀(hole)이 있는 경우, 동시 삽입은 비활성화되지만, 새 데이터로 모든 홀(hole)이 채워지면 자동으로 다시 활성화됩니다. 이 동작을 제어하려면 concurrent_insert 시스템 변수를 사용하십시오. Section 10.11.3, “Concurrent Inserts”를 참조하십시오.

LOCK TABLES를 사용하여 명시적으로 테이블 락을 획득하면, READ 락 대신 READ LOCAL 락을 요청하여, 테이블을 락한 상태에서도 다른 세션이 동시 삽입을 수행할 수 있도록 할 수 있습니다.

동시 삽입이 불가능한 테이블 t1에 대해 많은 INSERT 및 SELECT 작업을 수행하려면, 임시 테이블 temp_t1에 로우를 삽입한 다음, 임시 테이블의 로우를 사용해 실제 테이블을 업데이트할 수 있습니다:

1mysql> LOCK TABLES t1 WRITE, temp_t1 WRITE;
2mysql> INSERT INTO t1 SELECT * FROM temp_t1;
3mysql> DELETE FROM temp_t1;
4mysql> UNLOCK TABLES;

Choosing the Type of Locking

일반적으로, 다음과 같은 경우에는 테이블 락이 row-level 락보다 우수합니다:

테이블에 대한 대부분의 문이 읽기입니다.
테이블에 대한 문이 읽기와 쓰기 혼합이며, 쓰기는 한 번의 키 읽기로 가져올 수 있는 단일 로우에 대한 업데이트 또는 삭제입니다:

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

동시 INSERT 문과 결합된 SELECT 문이 있고, UPDATE 또는 DELETE 문은 매우 적은 경우.
라이터 없이 전체 테이블에 대해 많은 스캔 또는 GROUP BY 연산을 수행하는 경우.

보다 높은 수준의 락을 사용하면, 락 오버헤드가 row-level 락보다 적기 때문에 서로 다른 유형의 락을 지원함으로써 애플리케이션을 더 쉽게 튜닝할 수 있습니다.

Row-level locking 이외의 옵션:

(MySQL에서 동시 삽입에 사용되는 것과 같은) 버저닝(versioning)을 사용하면, 하나의 라이터와 동시에 여러 리더가 존재할 수 있습니다. 이는 데이터베이스 또는 테이블이 접근이 시작된 시점에 따라 서로 다른 데이터 뷰를 지원한다는 의미입니다. 이에 대한 다른 일반적인 용어로는 “time travel,” “copy on write,” “copy on demand”가 있습니다.
Copy on demand는 많은 경우 row-level locking보다 우수합니다. 그러나 최악의 경우, 일반적인 락 사용보다 훨씬 더 많은 메모리를 사용할 수 있습니다.
Row-level 락을 사용하는 대신, MySQL의 GET_LOCK() 및 RELEASE_LOCK()이 제공하는 것과 같은 애플리케이션 수준 락을 사용할 수 있습니다. 이러한 락은 어드바이저리(advisory) 락이므로, 서로 협조하는 애플리케이션 간에서만 동작합니다. Section 14.14, “Locking Functions”를 참조하십시오.