[MySQL][Lock] LOCK TABLES 그리고 UNLOCK TABLES 문법

■ LOCK TABLES 그리고 UNLOCK TABLES 문법

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES

MySQL은 클라이언트 세션이 테이블에 액세스하기 위해 다른 세션과 협력하거나 세션에 독점 액세스가 필요한 기간 동안 다른 세션이 테이블을 수정하지 못하도록 하기 위해 명시적으로 테이블 잠금을 획득할 수 있도록 합니다. 세션은 자체 잠금만 획득하거나 해제할 수 있습니다. 한 세션은 다른 세션에 대한 잠금 또는 다른 세션이 보유한 잠금 해제를 획득할 수 없습니다.

 

잠금은 트랜잭션을 에뮬레이트하거나 테이블을 업데이트 할 때 속도를 높이는 데 사용될 수 있습니다. 이에 대한 자세한 내용은 테이블 잠금 제한 사항 및 조건에 나와 있습니다.

 

LOCK TABLES는 현재 클라이언트 세션에 대한 테이블 잠금을 명시적으로 얻습니다. 기본 테이블 또는 뷰에 대해 테이블 ​​잠금을 얻을 수 있습니다. LOCK TABLES 권한과 각 오브젝트에 대한 SELECT 권한이 있어야합니다.

 

뷰 잠금의 경우, LOCK TABLES는 뷰에 사용된 모든 기본 테이블을 잠그려는 테이블 세트에 추가하고 자동으로 잠급니다. MySQL 5.7.32부터 LOCK TABLES는 뷰 정의자가 뷰의 기본 테이블에 대한 적절한 권한을 가지고 있는지 확인합니다.

 

LOCK TABLES를 사용하여 테이블을 명시적으로 잠그면 트리거에 사용된 모든 테이블도 LOCK TABLES 및 트리거에 설명된대로 내재적으로 잠깁니다.

 

UNLOCK TABLES는 현재 세션이 보유한 모든 테이블 잠금을 명시적으로 해제합니다. LOCK TABLES는 새 잠금을 획득하기 전에 현재 세션에서 보유한 모든 테이블 잠금을 내재적으로 해제합니다.

 

UNLOCK TABLES의 또 다른 용도는 FLUSH TABLES WITH READ LOCK 문으로 획득한 전역 읽기 잠금을 해제하여 모든 데이터베이스의 모든 테이블을 잠글 수 있게하는 것입니다.(Veritas와 같은 시간 내에 스냅 샷을 찍을 수있는 파일 시스템이 있는 경우 백업을 얻는 매우 편리한 방법입니다.)

 

테이블 잠금은 다른 세션의 부적절한 읽기 또는 쓰기로부터 보호합니다. WRITE 잠금을 보유한 세션은 DROP TABLE 또는 TRUNCATE TABLE과 같은 테이블 레벨 작업을 수행 할 수 있습니다. READ 잠금을 보유한 세션의 경우 DROP TABLE 및 TRUNCATE TABLE 조작이 허용되지 않습니다.

 

다음 설명은 TEMPORARY 테이블이 아닌 테이블에만 적용됩니다. TEMPORARY 테이블에는 LOCK TABLES가 허용되지만 무시됩니다. 다른 잠금의 영향에 관계없이 테이블은 작성된 세션에서 자유롭게 액세스 할 수 있습니다. 다른 세션이 테이블을 볼 수 없으므로 잠금이 필요하지 않습니다.

+ 테이블 잠금 획득

+ 테이블 잠금 해제

+ 테이블 잠금 및 트랜잭션의 상호 작용

+ 잠금 테이블 및 트리거

+ 테이블 잠금 제한 및 조건

 

■ 테이블 잠금 획득

현재 세션 내에서 테이블 잠금을 획득하려면 메타 데이터 잠금을 획득하는 LOCK TABLES 문을 사용합니다.

 

다음과 같은 잠금 유형을 사용할 수 있습니다.

READ [LOCAL] lock:

+ 잠금을 보유한 세션은 테이블을 읽을 수 있지만 쓸 수는 없습니다.

+ 여러 세션이 동시에 테이블에 대한 READ 잠금을 획득할 수 있습니다.

+ 다른 세션은 READ 잠금을 명시적으로 얻지 않고도 테이블을 읽을 수 있습니다.

+ LOCAL 수정자는 잠금이 유지되는 동안 다른 세션에서 충돌하지 않는 INSERT문 (동시 삽입)을 실행할 수 있게합니다. 그러나 잠금을 유지한 상태에서 서버 외부의 프로세스를 사용하여 데이터베이스를 조작하려는 경우 READ LOCAL을 사용할 수 없습니다. InnoDB 테이블의 경우 READ LOCAL은 READ와 동일합니다.

 

[LOW_PRIORITY] WRITE lock:

+ 잠금을 보유한 세션은 테이블을 읽고 쓸 수 있습니다.

+ 잠금을 보유한 세션만 테이블에 액세스 할 수 있습니다. 잠금이 해제될 때까지 다른 세션은 액세스 할 수 없습니다.

+ WRITE 잠금이 유지되는 동안 다른 세션 블록에 의한 테이블 잠금 요청.

+ LOW_PRIORITY 수정자는 효과가 없습니다. 이전 버전의 MySQL에서는 잠금 동작에 영향을 주었지만 더 이상 사용되지 않으며 사용하면 경고가 발생합니다. 대신 LOW_PRIORITY없이 WRITE를 사용합니다.

 

WRITE 잠금은 일반적으로 READ 잠금보다 우선 순위가 높아 업데이트가 가능한 빨리 처리되도록합니다. 즉, 한 세션이 READ 잠금을 획득한 후 다른 세션이 WRITE 잠금을 요청하면 후속 READ 잠금 요청은 WRITE 잠금을 요청한 세션이 잠금을 획득하고 해제할 때까지 대기합니다. max_write_lock_count 시스템 변수의 작은 값에 대해서는 이 정책에서 예외가 발생할 수 있습니다 (8.11.4 절“메타 데이터 잠금”참조).

 

테이블의 다른 세션에서 보유한 잠금으로 인해 LOCK TABLES 문이 대기해야하는 경우 모든 잠금을 확보 할 수 있을 때까지 차단됩니다.

 

잠금이 필요한 세션은 단일 LOCK TABLES 문에서 필요한 모든 잠금을 획득해야합니다. 이렇게 얻은 잠금이 유지되는 동안 세션은 잠긴 테이블에만 액세스 할 수 있습니다. 예를 들어, 다음 명령문 순서에서 t2가 LOCK TABLES 문에 잠겨 있지 않기 때문에 t2에 액세스하려는 경우 오류가 발생합니다.

mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------
| COUNT(*) |
+----------+
|        3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES

INFORMATION_SCHEMA 데이터베이스의 테이블은 예외입니다. 세션이 LOCK TABLES로 얻은 테이블 잠금을 보유하는 동안에도 명시적으로 잠그지 않고 액세스 할 수 있습니다.

 

동일한 이름을 사용하는 단일 쿼리에서 잠긴 테이블을 여러 번 참조 할 수 없습니다. 대신 별명을 사용하고 테이블과 각 별명에 대해 별도의 잠금을 확보합니다.

mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;

잠긴 테이블에 대해 동일한 이름에 대한 두개의 참조가 있기 때문에 첫 번째 INSERT에 오류가 발생합니다. 두 번째 INSERT는 테이블에 대한 참조가 다른 이름을 사용하므로 성공합니다.

 

명령문이 별명을 사용하여 테이블을 참조하는 경우 동일한 별명을 사용하여 테이블을 잠가야합니다. 별명을 지정하지 않고 테이블을 잠그는 것은 작동하지 않습니다.

mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLES

 

반대로 별명을 사용하여 테이블을 잠그면 해당 별명을 사용하여 명령문에서 참조해야합니다.

mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;

 

노트

LOCK TABLES 또는 UNLOCK TABLES는 파티션된 테이블에 적용될때 항상 전체 테이블을 잠 그거나 잠금 해제합니다. 이 명령문은 파티션 잠금 제거를 지원하지 않습니다.

 

■ 테이블 잠금 해제

세션이 보유한 테이블 잠금이 해제되면 모두 동시에 해제됩니다. 세션은 잠금을 명시적으로 해제하거나 특정 조건에서 잠금을 내재적으로 해제할 수 있습니다.

+ 세션은 UNLOCK TABLES를 사용하여 잠금을 명시 적으로 해제할 수 있습니다.

+ 세션이 이미 잠금을 보유한 상태에서 잠금을 획득하기 위해 LOCK TABLES 문을 발행하는 경우 새 잠금이 부여되기 전에 기존 잠금이 내재적으로 해제됩니다.

+ 세션이 트랜잭션을 시작하면 (예 : START TRANSACTION) 암시적 UNLOCK TABLES가 수행되어 기존 잠금이 해제됩니다.

 

클라이언트 세션에 대한 연결이 정상적으로 또는 비정상적으로 종료되면 서버는 세션이 보유한 모든 테이블 잠금을 암시적으로 해제합니다 (트랜잭션 및 비 트랜잭션). 클라이언트가 다시 연결되면 잠금이 더 이상 적용되지 않습니다. 또한 클라이언트에 활성 트랜잭션이 있는 경우, 서버는 연결이 끊어지면 트랜잭션을 롤백하고 다시 연결되면 자동 커밋이 활성화 된 상태에서 새 세션이 시작됩니다. 이러한 이유로 클라이언트는 자동 재 연결을 비활성화 할 수 있습니다. 자동 재 연결이 유효하면 재 연결이 발생하더라도 클라이언트에 통지되지 않지만 테이블 잠금 또는 현재 트랜잭션이 손실됩니다. 자동 재 연결이 사용 불가능한 상태에서 연결이 끊어지면 발행된 다음 명령문에 대해 오류가 발생합니다. 클라이언트는 오류를 감지하고 잠금 재 취득 또는 트랜잭션 재실행과 같은 적절한 조치를 취할 수 있습니다.

 

노트

잠긴 테이블에서 ALTER TABLE을 사용하면 잠금이 해제될 수 있습니다. 예를 들어, 두 번째 ALTER TABLE 조작을 시도하면 테이블 'tbl_name'이 (가) LOCK TABLES로 잠겨 있지 않은 오류가 발생할 수 있습니다. 이를 처리하려면 두번째 변경 전에 테이블을 다시 잠가야 합니다.

 

■ 테이블 잠금과 트랜잭션의 상호 작용

LOCK TABLES 및 UNLOCK TABLES는 다음과 같이 트랜잭션 사용과 상호 작용합니다.

+ LOCK TABLES는 transaction-safe(트랜잭션이 안전)하지 않으며 테이블을 잠그기 전에 모든 활성 트랜잭션을 암시적으로 커밋합니다.

+ UNLOCK TABLES는 LOCK TABLES를 사용하여 테이블 잠금을 획득한 경우에만 모든 활성 트랜잭션을 암시적으로 커밋합니다. 예를 들어, 다음 명령문 세트에서 UNLOCK TABLES는 글로벌 읽기 잠금을 해제하지만 테이블 잠금이 적용되지 않으므로 트랜잭션을 커미트하지 않습니다.

mysql> FLUSH TABLES WITH READ LOCK;
mysql> START TRANSACTION;
mysql> SELECT ... ;
mysql> UNLOCK TABLES;

+ 트랜잭션 시작 (예 : START TRANSACTION 사용)은 암시적으로 현재 트랜잭션을 커밋하고 기존 테이블 잠금을 해제합니다.

+ 읽기 잠금이 있는 FLUSH TABLES는 테이블 잠금이 아닌 전역 읽기 잠금을 획득하므로 테이블 잠금 및 암시적 커밋과 관련하여 LOCK TABLES 및 UNLOCK TABLES와 동일한 동작이 적용되지 않습니다. 예를 들어 START TRANSACTION은 전역 읽기 잠금을 해제하지 않습니다.

+ 트랜잭션을 암시적으로 커미트하는 다른 명령문은 기존 테이블 잠금을 해제하지 않습니다.

+ InnoDB 테이블과 같은 트랜잭션 테이블과 함께 LOCK TABLE 및 LOCK TABLE을 사용하는 올바른 방법은 SET Autocommit = 0(START TABLE 제외)으로 트랜잭션을 시작하고, 트랜잭션을 명시적으로 커밋할 때까지 LOCK TABLE을 호출하지 않는 것입니다. 예를 들어, 테이블 t1에 쓰고 테이블 t2에서 읽어야하는 경우 다음을 수행 할 수 있습니다.

mysql> SET autocommit=0;
mysql> LOCK TABLES t1 WRITE, t2 READ, ...;... do something with tables t1 and t2 here ...
mysql> COMMIT;
mysql> UNLOCK TABLES;

LOCK TABLES를 호출하면 InnoDB는 내부적으로 자체 테이블 잠금을 사용하고 MySQL은 자체 테이블 잠금을 사용합니다. InnoDB는 다음 커밋에서 내부 테이블 잠금을 해제하지만 MySQL이 테이블 잠금을 해제하려면 UNLOCK TABLES를 호출해야합니다. InnoDB는 LOCK TABLES를 호출 직후에 내부 테이블 잠금을 해제하고, 교착 상태가 매우 쉽게 발생할 수 있으므로 autocommit=1이 아니어야 합니다. InnoDB는 autocommit=1 인 경우 내부 테이블 잠금을 전혀 얻지 않으므로 이전 응용 프로그램에서 불필요한 교착 상태를 피할 수 있습니다.

+ ROLLBACK은 테이블 잠금을 해제하지 않습니다.

 

▶︎ 잠금 테이블 및 트리거

LOCK TABLES를 사용하여 테이블을 명시 적으로 잠그면 트리거에 사용 된 모든 테이블도 내재적으로 잠 깁니다.

+ 잠금은 LOCK TABLES 문으로 명시적으로 얻은 잠금과 동일한 시간으로 사용됩니다.

+ 트리거에 사용된 테이블의 잠금은 테이블이 읽기 전용으로 사용되는지 여부에 따라 다릅니다. 그렇다면 읽기 잠금으로 충분합니다. 그렇지 않으면 쓰기 잠금이 사용됩니다.

+ 테이블이 LOCK TABLES로 읽기 위해 명시적으로 잠겨 있지만 트리거 내에서 수정될 수 있으므로 쓰기 위해 잠겨야하는 경우 읽기 잠금 대신 쓰기 잠금이 사용됩니다. 즉, 트리거 내의 테이블 모양으로 인해 암시 적 쓰기 잠금이 필요하므로 테이블에 대한 명시적인 읽기 잠금 요청이 쓰기 잠금 요청으로 변환됩니다.

 

이 명령문을 사용하여 두 개의 테이블 t1과 t2를 잠근다고 가정합니다.

LOCK TABLES t1 WRITE, t2 READ;

 

t1 또는 t2에 트리거가 있으면 트리거 내에서 사용 된 테이블도 잠깁니다. t1에 다음과 같이 정의 된 트리거가 있다고 가정합니다.

CREATE TRIGGER t1_a_ins AFTER INSERT ON t1 FOR EACH ROW
BEGIN
  UPDATE t4 SET count = count+1
      WHERE id = NEW.id AND EXISTS (SELECT a FROM t3);
  INSERT INTO t2 VALUES(1, 2);
END;

 

LOCK TABLES문의 결과는 명령문에 표시되어 t1 및 t2가 잠기고 트리거 내에서 사용되므로 t3 및 t4가 잠긴 것입니다.

+ 쓰기 잠금 요청에 따라 쓰기를 위해 t1이 잠깁니다.

+ t2는 요청이 읽기 잠금에 대한 것이더라도 쓰기 위해 잠겨 있습니다. t2가 트리거 내에 삽입되어 READ 요청이 WRITE 요청으로 변환되기 때문에 발생합니다.

+ t3은 트리거 내에서만 읽기 때문에 읽기 위해 잠겨 있습니다.

+ t4는 트리거 내에서 업데이트 될 수 있으므로 쓰기 위해 잠겨 있습니다.

 

■ 테이블 잠금 제한과 조건들

KILL을 안전하게 사용하여 테이블 잠금을 기다리는 세션을 종료 할 수 있습니다.

LOCK TABLES 및 UNLOCK TABLES는 저장된 프로그램 내에서 사용할 수 없습니다.

setup_xxx 테이블을 제외하고 performance_schema 데이터베이스의 테이블은 LOCK TABLES로 잠글 수 없습니다.

LOCK TABLES문이 적용되는 동안 다음 명령문은 금지됩니다.

CREATE TABLE, CREATE TABLE ... LIKE, CREATE VIEW, DROP VIEW.

저장된 함수 및 프로시저 및 이벤트에 대한 DDL 문

 

일부 작업의 경우 mysql 데이터베이스의 시스템 테이블에 액세스해야 합니다. 예를 들어 HELP문에는 서버 측 도움말 테이블의 컨텐츠가 필요하며 CONVERT_TZ()는 time zone(세계시간) 테이블을 읽어야 합니다. 서버는 명시적으로 잠글 필요가 없도록 읽기 위해 시스템 테이블을 암시적으로 잠급니다. 이 테이블은 방금 설명한대로 처리됩니다.

mysql.help_category

mysql.help_keyword

mysql.help_relation

mysql.help_topic

mysql.proc

mysql.time_zone

mysql.time_zone_leap_second

mysql.time_zone_name

mysql.time_zone_transition

mysql.time_zone_transition_type

LOCK TABLES 문을 사용하여 해당 테이블에 WRITE 잠금을 명시적으로 배치하려면 테이블이 유일한 잠금이어야합니다. 동일한 명령문으로 다른 테이블을 잠글 수 없습니다.

 

일반적으로 모든 단일 UPDATE 문은 원자적이므로 테이블을 잠글 필요는 없습니다. 다른 세션은 현재 실행중인 다른 SQL 문을 방해할 수 없습니다. 그러나 테이블 잠금이 이점을 제공할 수 있는 몇 가지 경우가 있습니다.

+ MyISAM 테이블 세트에서 많은 작업을 실행하려는 경우 사용할 테이블을 잠그는 것이 훨씬 빠릅니다. MySQL은 UNLOCK TABLES가 호출될 때까지 잠긴 테이블의 키 캐시를 플러시하지 않기 때문에 MyISAM 테이블을 잠그면 테이블에 대한 삽입, 업데이트 또는 삭제 속도가 빨라집니다. 일반적으로 키 캐시는 각 SQL문 후에 플러시됩니다.

테이블 잠금의 단점은 세션이 잠금을 보유한 테이블을 포함하여 READ 잠금 테이블을 업데이트할 수 없으며 잠금을 보유한 테이블 이외의 WRITE 잠금 테이블에 액세스할 수 있는 세션이 없다는 것입니다.

+ 비 트랜잭션 스토리지 엔진에 테이블을 사용하는 경우 다른 세션이 SELECT와 UPDATE 사이의 테이블을 수정하지 않도록하려면 LOCK TABLES를 사용해야합니다. 여기에 표시된 예는 안전하게 실행하기 위해 LOCK TABLES가 필요합니다.

LOCK TABLES trans READ, customer WRITE;
SELECT SUM(value) FROM trans WHERE customer_id=some_id;
UPDATE customer
  SET total_value=sum_from_previous_statement
  WHERE customer_id=some_id;
UNLOCK TABLES;

LOCK TABLES가 없으면 다른 세션이 SELECT 및 UPDATE 문의 실행 사이에 새 테이블을 전송 테이블에 삽입 할 수 있습니다.

 

대부분의 경우 연관 업데이트 (UPDATE customer SET value = value + new_value) 또는 LAST_INSERT_ID() 함수를 사용하여 LOCK TABLES를 사용하지 않아도됩니다.

 

경우에 따라 사용자 레벨 권고 잠금 함수 GET_LOCK () 및 RELEASE_LOCK ()을 사용하여 테이블 잠금을 피할 수도 있습니다. 이러한 잠금은 서버의 해시 테이블에 저장되며 속도를 높이기 위해 pthread_mutex_lock () 및 pthread_mutex_unlock ()으로 구현됩니다.