Loading...
MySQL 9.5 Reference Manual 9.5의 10.11.2 Table Locking Issues의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
InnoDB table은 row-level locking을 사용하므로, 여러 세션과 애플리케이션이 같은 table을 동시에 읽고 쓰더라도 서로를 기다리게 하거나 일관성 없는 결과를 만들지 않습니다. 이 스토리지 엔진을 사용할 때는 LOCK TABLES statement 사용을 피해야 합니다. 이는 추가적인 보호를 제공하지 않고 대신 동시성을 줄이기 때문입니다.
자동 row-level locking 덕분에 이러한 table은 가장 중요한 데이터를 담는 가장 바쁜 데이터베이스에 적합하며, table을 lock/unlock할 필요가 없으므로 애플리케이션 로직도 단순해집니다. 따라서 InnoDB 스토리지 엔진이 MySQL의 기본값입니다.
MySQL은 InnoDB를 제외한 모든 스토리지 엔진에서 (page, row, column locking 대신) table locking을 사용합니다. locking operation 자체의 오버헤드는 크지 않습니다. 하지만 한 번에 하나의 세션만이 table에 write할 수 있기 때문에, 다른 스토리지 엔진으로 최적의 성능을 내려면 주로 자주 쿼리되지만 insert나 update는 드물게 발생하는 table에 사용하는 것이 좋습니다.
table을 만들 때 InnoDB를 사용할지 다른 스토리지 엔진을 사용할지 결정할 때, 다음과 같은 table locking의 단점을 염두에 두어야 합니다:
Table locking은 여러 세션이 동시에 table을 읽을 수 있게 하지만, 어떤 세션이 table에 write하기를 원한다면 먼저 exclusive access를 획득해야 하므로, 다른 세션들이 그 table 사용을 끝낼 때까지 기다려야 할 수 있습니다. update가 수행되는 동안 이 특정 table에 접근하기를 원하는 다른 모든 세션은 update가 끝날 때까지 기다려야 합니다.
Table locking은 어떤 세션이 디스크 공간이 가득 차서, 계속 진행하려면 free space가 생기기를 기다리는 상황에서 문제를 일으킵니다. 이 경우, 해당 problem table에 접근하려는 모든 세션도 더 많은 디스크 공간이 확보될 때까지 waiting 상태로 놓이게 됩니다.
실행 시간이 오래 걸리는 SELECT statement는 그 동안 다른 세션이 table을 update하지 못하게 하여, 다른 세션이 느리거나 반응하지 않는 것처럼 보이게 합니다. 한 세션이 update를 위해 table에 대한 exclusive access를 기다리는 동안, 다른 세션이 발행한 SELECT statement가 그 뒤에 줄을 서게 되어, read-only 세션에 대해서도 동시성이 감소합니다.
다음 항목들은 table locking으로 인해 발생하는 contention을 피하거나 줄이는 몇 가지 방법을 설명합니다:
Setup 시 CREATE TABLE ... ENGINE=INNODB를 사용하거나, 기존 table에 대해 ALTER TABLE ... ENGINE=INNODB를 사용하여 table을 InnoDB 스토리지 엔진으로 전환하는 것을 고려하십시오. 이 스토리지 엔진에 대한 자세한 내용은 Chapter 17, The InnoDB Storage Engine을 참조하십시오.
SELECT statement가 더 빠르게 실행되도록 최적화하여, table이 lock되는 시간을 더 짧게 만드십시오. 이 작업을 위해 summary table을 생성해야 할 수도 있습니다.
mysqld를 --low-priority-updates 옵션과 함께 시작하십시오. Table-level locking만 사용하는 스토리지 엔진(MyISAM, MEMORY, MERGE 등)의 경우, 이는 table을 update(수정)하는 모든 statement의 priority를 SELECT statement보다 낮게 설정합니다. 이 경우, 앞의 시나리오에서 두 번째 SELECT statement가 UPDATE statement보다 먼저 실행되며, 첫 번째 SELECT가 끝날 때까지 기다리지 않습니다.
특정 connection에서 발행되는 모든 update를 low priority로 실행하도록 지정하려면, low_priority_updates server system variable을 1로 설정하십시오.
특정 INSERT, UPDATE, DELETE statement의 priority를 낮추려면 LOW_PRIORITY attribute를 사용하십시오.
특정 SELECT statement의 priority를 높이려면 HIGH_PRIORITY attribute를 사용하십시오. Section 15.2.13, “SELECT Statement”를 참조하십시오.
max_write_lock_count system variable에 낮은 값을 설정하고 mysqld를 시작하여, 특정 횟수만큼 table에 write lock이 발생한 후(예: insert operation), 해당 table에 대해 기다리고 있는 모든 SELECT statement의 priority를 일시적으로 높이도록 MySQL에 강제할 수 있습니다. 이렇게 하면 일정 횟수의 write lock 이후 read lock이 허용됩니다.
혼합된 SELECT와 DELETE statement에 문제가 있는 경우, DELETE의 LIMIT 옵션이 도움이 될 수 있습니다. Section 15.2.2, “DELETE Statement”를 참조하십시오.
SELECT statement와 함께 SQL_BUFFER_RESULT를 사용하면 table lock 지속 시간을 짧게 만드는 데 도움이 될 수 있습니다. Section 15.2.13, “SELECT Statement”를 참조하십시오.
Table 내용을 별도의 table로 분할하면, 하나의 table의 column에 대해 쿼리를 실행하는 동안 다른 table의 column에 update를 제한할 수 있어 도움이 될 수 있습니다.
mysys/thr_lock.c의 locking code를 단일 queue를 사용하도록 변경할 수도 있습니다. 이 경우 write lock과 read lock은 동일한 priority를 가지게 되며, 일부 애플리케이션에는 도움이 될 수 있습니다.
10.11.1 Internal Locking Methods
10.11.3 Concurrent Inserts
