Loading...
MySQL 9.5 Reference Manual 9.5의 15.3.6 LOCK TABLES and UNLOCK TABLES Statements의 한국어 번역본입니다.
아래의 경우에 피드백에서 신고해주신다면 반영하겠습니다.
감사합니다 :)
1LOCK {TABLE | TABLES} 2 tbl_name [[AS] alias] lock_type 3 [, tbl_name [[AS] alias] lock_type] ... 4 5lock_type: { 6 READ [LOCAL] 7 | WRITE 8} 9 10UNLOCK {TABLE | TABLES}
MySQL은 client 세션이 테이블에 대한 접근을 위해 다른 세션과 협업하거나, 어떤 세션이 테이블에 대한 배타적 엑세스를 필요로 하는 기간 동안 다른 세션이 테이블을 수정하지 못하도록 하기 위해 명시적으로 테이블 락을 획득할 수 있도록 합니다. 하나의 세션은 오직 자기 자신에 대해서만 락을 획득하거나 해제할 수 있습니다. 한 세션이 다른 세션을 대신해 락을 획득하거나, 다른 세션이 보유한 락을 해제할 수는 없습니다.
락은 트랜잭션을 에뮬레이트하기 위해 사용되거나 테이블을 갱신할 때 더 높은 속도를 얻기 위해 사용될 수 있습니다. 이에 대해서는 Table-Locking Restrictions and Conditions에서 더 자세히 설명합니다.
LOCK TABLES는 현재 client 세션에 대해 명시적으로 테이블 락을 획득합니다. 테이블 락은 기본 테이블이나 뷰에 대해 획득될 수 있습니다. 이를 위해서는 LOCK TABLES 권한과, 락 대상 객체 각각에 대한 SELECT 권한이 필요합니다.
뷰 락의 경우, LOCK TABLES는 해당 뷰에서 사용되는 모든 기본 테이블을 락 대상 테이블 집합에 추가하고 이를 자동으로 락합니다. 락 대상이 되는 어떤 뷰의 언더라이잉 테이블에 대해, LOCK TABLES는 뷰 정의자(SQL SECURITY DEFINER 뷰의 경우) 또는 호출자(모든 뷰의 경우)가 해당 테이블에 대해 적절한 권한을 가지고 있는지 확인합니다.
LOCK TABLES로 테이블을 명시적으로 락하는 경우, 트리거에서 사용되는 모든 테이블 역시 LOCK TABLES and Triggers에 설명된 대로 암묵적으로 락됩니다.
LOCK TABLES로 테이블을 명시적으로 락하는 경우, 외래 키 제약 조건으로 연관된 어떤 테이블도 암묵적으로 오픈되고 락됩니다. 외래 키 체크의 경우, 관련 테이블에 대해 공유 읽기 전용 락(LOCK TABLES READ)이 획득됩니다. 캐스케이딩 업데이트의 경우, 연관된 테이블 중 해당 작업에 참여하는 테이블에 대해 공유가 불가능한 쓰기 락(LOCK TABLES WRITE)이 획득됩니다.
UNLOCK TABLES는 현재 세션이 보유한 모든 테이블 락을 명시적으로 해제합니다. LOCK TABLES는 새로운 락을 획득하기 전에 현재 세션이 보유한 모든 테이블 락을 암묵적으로 해제합니다.
UNLOCK TABLES의 또 다른 용도는 FLUSH TABLES WITH READ LOCK 문으로 획득한 글로벌 읽기 락을 해제하는 것입니다. 이 문은 모든 데이터베이스의 모든 테이블을 락할 수 있게 해줍니다. Section 15.7.8.3, “FLUSH Statement”를 참조하십시오. (이는 Veritas 같은, 시점별 스냅샷을 수행할 수 있는 파일 시스템을 사용할 때 백업을 얻는 매우 편리한 방법입니다.)
LOCK TABLE은 LOCK TABLES의 동의어이고, UNLOCK TABLE은 UNLOCK TABLES의 동의어입니다.
테이블 락은 다른 세션에 의한 부적절한 읽기나 쓰기에 대해서만 보호합니다. WRITE 락을 보유한 세션은 DROP TABLE이나 TRUNCATE TABLE과 같은 테이블 수준 연산을 수행할 수 있습니다. READ 락을 보유한 세션의 경우, DROP TABLE 및 TRUNCATE TABLE 연산은 허용되지 않습니다.
다음 설명은 TEMPORARY가 아닌 테이블에만 적용됩니다. LOCK TABLES는 TEMPORARY 테이블에 대해서는 허용되지만 무시됩니다. 이 테이블은, 어떤 다른 락이 유효한지와 관계없이, 이를 생성한 세션 내에서 자유롭게 엑세스될 수 있습니다. 다른 세션에서는 이 테이블을 볼 수 없으므로 락이 필요 없습니다.
현재 세션 내에서 테이블 락을 획득하려면, 메타데이터 락을 획득하는 (Section 10.11.4, “Metadata Locking” 참조) LOCK TABLES 문을 사용하십시오.
사용 가능한 락 타입은 다음과 같습니다:
READ [LOCAL] 락:
락을 보유한 세션은 테이블을 읽을 수 있지만 쓸 수는 없습니다.
여러 세션이 동시에 해당 테이블에 대해 READ 락을 획득할 수 있습니다.
다른 세션은 READ 락을 명시적으로 획득하지 않고도 테이블을 읽을 수 있습니다.
LOCAL 한정자는 락이 유지되는 동안, 다른 세션의 충돌하지 않는 INSERT 문(동시 삽입)이 실행될 수 있도록 합니다. (Section 10.11.3, “Concurrent Inserts” 참조.) 그러나, 서버 외부 프로세스를 사용하여 데이터베이스를 조작할 예정인 경우에는 READ LOCAL을 사용할 수 없습니다. InnoDB 테이블의 경우, READ LOCAL은 READ와 동일합니다.
WRITE 락:
락을 보유한 세션은 테이블을 읽고 쓸 수 있습니다.
락을 보유한 세션만이 테이블에 엑세스할 수 있습니다. 락이 해제되기 전까지 다른 어떤 세션도 접근할 수 없습니다.
다른 세션에서 해당 테이블에 대한 락을 요청하는 경우, WRITE 락이 유지되는 동안 블록됩니다.
WRITE 락은 일반적으로 업데이트가 가능한 한 빨리 처리되도록 READ 락보다 우선순위가 높습니다. 이는 어떤 세션이 READ 락을 획득한 후, 또 다른 세션이 WRITE 락을 요청하면, 이후의 READ 락 요청은 WRITE 락을 요청한 세션이 락을 획득하고 이를 해제할 때까지 대기함을 의미합니다. (이 정책에 대한 예외는 max_write_lock_count 시스템 변수 값이 작을 때 발생할 수 있습니다. Section 10.11.4, “Metadata Locking”을 참조하십시오.)
LOCK TABLES 문이 다른 세션이 보유한 어떤 테이블에 대한 락 때문에 대기해야 하는 경우, 모든 락을 획득할 수 있을 때까지 블록됩니다.
락이 필요한 세션은 필요한 모든 락을 단일 LOCK TABLES 문에서 획득해야 합니다. 이렇게 획득된 락이 유지되는 동안, 해당 세션은 락된 테이블만 엑세스할 수 있습니다. 예를 들어, 다음 문 시퀀스에서, LOCK TABLES 문에서 t2가 락되지 않았으므로, t2에 접근하려는 시도로 인해 에러가 발생합니다:
1mysql> LOCK TABLES t1 READ; 2mysql> SELECT COUNT(*) FROM t1; 3+----------+ 4| COUNT(*) | 5+----------+ 6| 3 | 7+----------+ 8mysql> SELECT COUNT(*) FROM t2; 9ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES
INFORMATION_SCHEMA 데이터베이스의 테이블은 예외입니다. 이들은 LOCK TABLES로 획득한 테이블 락을 세션이 보유하고 있는 동안에도 명시적으로 락하지 않고 엑세스할 수 있습니다.
하나의 쿼리에서 동일한 이름을 사용하여 락된 테이블을 여러 번 참조할 수는 없습니다. 대신 alias를 사용하고, 테이블 및 각 alias에 대해 별도의 락을 획득해야 합니다:
1mysql> LOCK TABLE t WRITE, t AS t1 READ; 2mysql> INSERT INTO t SELECT * FROM t; 3ERROR 1100: Table 't' was not locked with LOCK TABLES 4mysql> INSERT INTO t SELECT * FROM t AS t1;
첫 번째 INSERT의 경우, 락된 테이블에 대해 동일한 이름에 대한 두 개의 참조가 있기 때문에 에러가 발생합니다. 두 번째 INSERT는 테이블에 대한 참조가 서로 다른 이름을 사용하기 때문에 성공합니다.
문에서 alias를 사용해 테이블을 참조하는 경우, 반드시 동일한 alias 이름을 사용하여 테이블을 락해야 합니다. Alias를 지정하지 않고 테이블을 락하는 것은 동작하지 않습니다:
1mysql> LOCK TABLE t READ; 2mysql> SELECT * FROM t AS myalias; 3ERROR 1100: Table 'myalias' was not locked with LOCK TABLES
반대로, alias를 사용하여 테이블을 락한 경우, 문에서 반드시 그 alias를 사용해 테이블을 참조해야 합니다:
1mysql> LOCK TABLE t AS myalias READ; 2mysql> SELECT * FROM t; 3ERROR 1100: Table 't' was not locked with LOCK TABLES 4mysql> SELECT * FROM t AS myalias;
세션이 보유한 테이블 락이 해제될 때, 이들은 모두 동시에 해제됩니다. 세션은 락을 명시적으로 해제할 수 있으며, 특정 조건에서 락이 암묵적으로 해제될 수 있습니다.
세션은 UNLOCK TABLES를 사용해 락을 명시적으로 해제할 수 있습니다.
세션이 이미 락을 보유한 상태에서 락을 획득하기 위해 LOCK TABLES 문을 실행하면, 기존 락은 새로운 락이 부여되기 전에 암묵적으로 해제됩니다.
세션이 트랜잭션을 시작하는 경우(예: START TRANSACTION 사용), 암묵적인 UNLOCK TABLES가 수행되어 기존 락이 해제됩니다. 테이블 락킹과 트랜잭션 간의 상호 작용에 대한 추가 정보는 Interaction of Table Locking and Transactions을 참조하십시오.
Client 세션의 커넥션이 정상 또는 비정상적으로 종료되는 경우, 서버는 해당 세션이 보유한 모든 테이블 락(트랜잭션 테이블 및 비트랜잭션 테이블 모두에 대한 락)을 암묵적으로 해제합니다. Client가 다시 연결하면 그 락은 더 이상 유효하지 않습니다.
추가로, client가 활성 트랜잭션을 가지고 있었다면, 서버는 disconnect 시점에 해당 트랜잭션을 롤백하고, reconnect가 발생하면 새로운 세션은 자동 커밋이 활성화된 상태에서 시작됩니다. 이 때문에, client는 자동 재연결을 비활성화하기를 원할 수 있습니다. 자동 재연결이 유효하면, reconnect가 발생해도 client는 이를 통지받지 못하지만, 모든 테이블 락과 현재 트랜잭션은 손실됩니다. 자동 재연결이 비활성화된 경우, 커넥션이 끊어지면 다음에 실행되는 문에서 에러가 발생합니다. Client는 이 에러를 감지하고, 락을 다시 획득하거나 트랜잭션을 다시 수행하는 등 적절한 조치를 취할 수 있습니다. Automatic Reconnection Control을 참조하십시오.
참고
락된 테이블에 대해 ALTER TABLE을 사용하는 경우, 테이블이 언락될 수 있습니다. 예를 들어, 두 번째 ALTER TABLE 연산을 시도하는 경우, Table 'tbl_name' was not locked with LOCK TABLES 에러가 발생할 수 있습니다. 이를 처리하기 위해, 두 번째 변경 이전에 테이블을 다시 락하십시오. Section B.3.6.1, “Problems with ALTER TABLE”도 참조하십시오.
LOCK TABLES와 UNLOCK TABLES는 다음과 같이 트랜잭션 사용과 상호 작용합니다:
LOCK TABLES는 트랜잭션-세이프가 아니며, 테이블을 락하려 시도하기 전에 활성 트랜잭션을 암묵적으로 커밋합니다.
UNLOCK TABLES는 활성 트랜잭션을 암묵적으로 커밋하지만, 이는 테이블 락을 획득하기 위해 LOCK TABLES를 사용한 경우에만 그렇습니다. 예를 들어, 다음 문 집합에서는, 테이블 락이 유효하지 않기 때문에, UNLOCK TABLES가 글로벌 읽기 락을 해제하긴 하지만 트랜잭션을 커밋하지는 않습니다:
1FLUSH TABLES WITH READ LOCK; 2START TRANSACTION; 3SELECT ... ; 4UNLOCK TABLES;
트랜잭션을 시작하는 경우(예: START TRANSACTION), 현재 트랜잭션이 암묵적으로 커밋되고, 기존 테이블 락이 해제됩니다.
FLUSH TABLES WITH READ LOCK은 테이블 락이 아닌 글로벌 읽기 락을 획득하므로, 테이블 락킹 및 암묵적 커밋과 관련하여 LOCK TABLES 및 UNLOCK TABLES와 동일한 동작의 영향을 받지 않습니다. 예를 들어, START TRANSACTION는 글로벌 읽기 락을 해제하지 않습니다. Section 15.7.8.3, “FLUSH Statement”를 참조하십시오.
트랜잭션을 암묵적으로 커밋하게 만드는 다른 문들은 기존 테이블 락을 해제하지 않습니다. 이러한 문 목록은 Section 15.3.3, “Statements That Cause an Implicit Commit”을 참조하십시오.
InnoDB 테이블 같은 트랜잭션 테이블에 대해 LOCK TABLES 및 UNLOCK TABLES를 사용하는 올바른 방법은, START TRANSACTION가 아니라 SET autocommit = 0으로 트랜잭션을 시작하고, 그 다음에 LOCK TABLES를 호출한 후, 트랜잭션을 명시적으로 커밋할 때까지 UNLOCK TABLES를 호출하지 않는 것입니다. 예를 들어, 테이블 t1에 쓰기를 수행하고 테이블 t2에서 읽기를 수행해야 한다면, 다음과 같이 할 수 있습니다:
1SET autocommit=0; 2LOCK TABLES t1 WRITE, t2 READ, ...; 3... do something with tables t1 and t2 here ... 4COMMIT; 5UNLOCK TABLES;
LOCK TABLES를 호출하면, InnoDB는 내부적으로 자체 테이블 락을 획득하고, MySQL도 자체 테이블 락을 획득합니다. InnoDB는 다음 커밋 시점에 내부 테이블 락을 해제하지만, MySQL이 자신의 테이블 락을 해제하려면 UNLOCK TABLES를 호출해야 합니다.
autocommit = 1을 사용해서는 안 되는데, 이 경우 InnoDB는 LOCK TABLES 호출 직후 내부 테이블 락을 즉시 해제하며, 이로 인해 데드락이 매우 쉽게 발생할 수 있기 때문입니다. InnoDB는 불필요한 데드락을 피하기 위해, autocommit = 1인 경우 내부 테이블 락을 전혀 획득하지 않습니다.
ROLLBACK은 테이블 락을 해제하지 않습니다.LOCK TABLES로 테이블을 명시적으로 락하는 경우, 트리거에서 사용되는 모든 테이블도 암묵적으로 락됩니다:
이러한 락은 LOCK TABLES 문으로 명시적으로 획득한 락과 동시에 획득됩니다.
트리거에서 사용되는 테이블에 대한 락은 해당 테이블이 읽기 전용으로만 사용되는지 여부에 따라 달라집니다. 읽기 전용인 경우 읽기 락이면 충분합니다. 그렇지 않으면 쓰기 락이 사용됩니다.
LOCK TABLES로 테이블을 읽기 전용으로 명시적으로 락했더라도, 해당 테이블이 트리거 내에서 수정될 수 있으므로 쓰기 락이 필요하다면 쓰기 락이 사용됩니다. (즉, 트리거 내에서의 테이블 사용으로 인해 필요한 암묵적인 쓰기 락 때문에, 테이블에 대한 명시적인 읽기 락 요청이 쓰기 락 요청으로 변환됩니다.)
다음과 같이 t1과 t2 두 개의 테이블을 락한다고 가정해 봅시다:
1LOCK TABLES t1 WRITE, t2 READ;
t1이나 t2에 어떤 트리거가 있다면, 해당 트리거 내에서 사용되는 테이블도 락됩니다. 예를 들어, t1에 다음과 같은 트리거가 정의되어 있다고 가정합니다:
1CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW 2BEGIN 3 UPDATE t4 SET count = count+1 4 WHERE id = NEW.id AND EXISTS (SELECT a FROM t3); 5 INSERT INTO t2 VALUES(1, 2); 6END;
이 LOCK TABLES 문의 결과로, 문에 나타나는 t1과 t2가 락되고, 트리거 내에서 사용되는 t3와 t4도 락됩니다:
t1은 WRITE 락 요청에 따라 쓰기 용도로 락됩니다.
t2는 READ 락 요청에도 불구하고 쓰기 용도로 락됩니다. 이는 트리거 내에서 t2에 대해 insert가 수행되므로, READ 요청이 WRITE 요청으로 변환되기 때문입니다.
t3는 트리거 내에서 읽기만 수행되므로 읽기 용도로 락됩니다.
t4는 트리거 내에서 업데이트될 수 있으므로 쓰기 용도로 락됩니다.
테이블 락을 기다리는 세션을 종료시키기 위해 KILL을 안전하게 사용할 수 있습니다. Section 15.7.8.4, “KILL Statement”를 참조하십시오.
LOCK TABLES와 UNLOCK TABLES는 저장 프로그램 내에서 사용할 수 없습니다.
performance_schema 데이터베이스의 테이블은 setup_xxx 테이블을 제외하고는 LOCK TABLES로 락할 수 없습니다.
LOCK TABLES로 생성되는 락의 스코프는 단일 MySQL 서버입니다. 이는 여러 개의 mysqld 인스턴스에 걸쳐 SQL 수준의 락을 강제할 방법이 없는 NDB Cluster와 호환되지 않습니다. 대신 API 애플리케이션에서 락을 강제할 수 있습니다. 자세한 내용은 Section 25.2.7.10, “Limitations Relating to Multiple NDB Cluster Nodes”를 참조하십시오.
LOCK TABLES 문이 유효한 동안, 다음 문은 사용할 수 없습니다: CREATE TABLE, CREATE TABLE ... LIKE, CREATE VIEW, DROP VIEW, 그리고 저장 함수, 프로시저, 이벤트에 대한 DDL 문.
일부 작업에서는 mysql 데이터베이스의 시스템 테이블에 엑세스해야 합니다. 예를 들어, HELP 문은 서버 측 help 테이블의 내용을 필요로 하고, CONVERT_TZ()는 시간대 테이블을 읽어야 할 수 있습니다. 서버는 필요에 따라 이 시스템 테이블을 읽기 용도로 암묵적으로 락하므로, 사용자가 이를 명시적으로 락할 필요가 없습니다. 이 테이블은 다음과 같이 처리됩니다:
1mysql.help_category 2mysql.help_keyword 3mysql.help_relation 4mysql.help_topic 5mysql.time_zone 6mysql.time_zone_leap_second 7mysql.time_zone_name 8mysql.time_zone_transition 9mysql.time_zone_transition_type
LOCK TABLES 문으로 이들 테이블 중 하나에 WRITE 락을 명시적으로 설정하려면, 해당 테이블만을 락해야 하며, 같은 문에서 다른 어떤 테이블도 락할 수 없습니다.
일반적으로 테이블을 락할 필요는 없습니다. 모든 단일 UPDATE 문은 원자적이며, 다른 어떤 세션도 현재 실행 중인 SQL 문에 개입할 수 없기 때문입니다. 그러나, 테이블 락이 유리할 수 있는 몇 가지 경우가 있습니다:
MyISAM 테이블 집합에 대해 많은 작업을 실행하려는 경우, 사용할 테이블을 락하는 것이 훨씬 빠릅니다. MyISAM 테이블을 락하면, MySQL이 UNLOCK TABLES가 호출될 때까지 락된 테이블에 대한 키 캐시를 플러시하지 않으므로, 해당 테이블에 대한 insert, update, delete가 빨라집니다. 일반적으로 키 캐시는 각 SQL 문 후에 플러시됩니다.테이블을 락하는 데 따른 단점은, READ-락이 걸린 테이블은 락을 보유한 세션을 포함해 어떤 세션도 업데이트할 수 없고, WRITE-락이 걸린 테이블은 락을 보유한 세션 이외에는 어떤 세션도 엑세스할 수 없다는 점입니다.
SELECT와 UPDATE 사이에 다른 세션이 테이블을 수정하지 못하도록 보장하려면 LOCK TABLES를 사용해야 합니다. 여기 제시된 예제는 안전하게 실행하려면 LOCK TABLES가 필요합니다:1LOCK TABLES trans READ, customer WRITE; 2SELECT SUM(value) FROM trans WHERE customer_id=some_id; 3UPDATE customer 4 SET total_value=sum_from_previous_statement 5 WHERE customer_id=some_id; 6UNLOCK TABLES;
LOCK TABLES 없이 실행하는 경우, 다른 세션이 SELECT 및 UPDATE 문 실행 사이에 trans 테이블에 새 행을 insert할 수 있습니다.
많은 경우, 상대 업데이트(UPDATE customer SET value=value+new_value)나 LAST_INSERT_ID() 함수를 사용하여 LOCK TABLES의 사용을 피할 수 있습니다.
또한 일부 경우에는 사용자 수준 어드바이저리 락 함수인 GET_LOCK() 및 RELEASE_LOCK()을 사용하여 테이블 락을 피할 수 있습니다. 이러한 락은 서버 내 해시 테이블에 저장되며, 고속을 위해 pthread_mutex_lock() 및 pthread_mutex_unlock()으로 구현됩니다. Section 14.14, “Locking Functions”를 참조하십시오.
락킹 정책에 대한 자세한 정보는 Section 10.11.1, “Internal Locking Methods”를 참조하십시오.
15.3.5 LOCK INSTANCE FOR BACKUP and UNLOCK INSTANCE Statements
15.3.7 SET TRANSACTION Statement