[SQL] MySQL 잠금(1) - MySQL 엔진 레벨 잠금

DeMar_Beom·2023년 10월 17일

sql 글로벌 락 네임드 락 메타데이터 락 잠금 테이블 락

SQL

목록 보기

11/17

MySQL 잠금

MySQL 서버에서의 잠금

MySQL서버의 잠금은 MySQL엔진 레벨 잠금과 스토리지 엔진 레벨잠금으로 구분

MySQL 엔진 레벨 잠금

MySQL엔진 레벨 잠금은 모든 스토리지엔 영향을 미침
스토리지 엔진 레벨 잠금은 스토리지 엔진 간 영향을 미치지 않음
MySQL엔진 레벨 잠금의 종류
✅ 글로벌 락(GLOBAL LOCK)
✅ 메타데이터 락(METADATA LOCK)
✅ 네임드 락(NAMED LOCK)
✅ 테이블 락(TABLE LOCK)

글로벌 락

MySQL엔진 레벨 잠금에서 미치는 영향 범위가 MySQL서버 전체로, 작업 대상 테이블이나 데이터베이스가 다르더라도 동일하게 영향을 미침
FLUSH TABLES WITH READ LOCK 명령으로 글로벌 락 획득 가능
그러나 InnoDB스토리지 엔진은 기본적으로 트랜잭션을 지원하기 때문에 이를 사용할 필요 없어짐

글로벌 락 예시

product라는 테이블에서 FLUSH TABLES WITH READ LOCK 명령어를 실행 후 INSERT, UPDATE, DELETE문을 실행

결과 Error Code: 1223. Can't execute the query because you have a conflicting read lock오류가 발생

해제 명령어로는 unlock tables;
SELECT * FROM project.products;
FLUSH TABLES WITH READ LOCK;
insert into products values(4, "Product D", 50.00);
update products set product_name = 'Product D' where product_id = 4;
delete from product where product_name = 'Product C';
unlock tables;

테이블 락

개별 테이블 단위로 설정되는 잠금으로 특정 테이블 단위로 잠금
명시적 방법 과 묵시적 방법 으로 분류

명시적 방법

특정 테이블의 락을 설정
LOCK TABLES table_name [READ | WRITE]; 명령어를 통해 락을 획득

READ

테이블에 대한 읽기 락 획득
다른 세션이 동시에 같은 테이블에 대한 읽기 락 획득가능
테이블에 대한 내용은 읽을 수 있지만, 수정 불가
동시에 다른 세션이 해당 테이블에 대해서 쓰기 락 획득 불가

WRITE

테이블에 대한 쓰기 락 획득
다른 세션이 동시에 같은 테이블에 대한 읽기 락, 쓰기 락 획득 불가
쓰기 락을 획득한 세션은 테이블의 내용을 읽고 수정 가능

테이블 락 예시

products테이블에 WRITE 락을 걸고 INSERT, UPDATE, DELETE, ALTER구문 실행

모두 정상적으로 실행됨 -> 로컬에서 저 혼자 사용하는 서버에서 진행하다 보니 쓰기락을 획득한 세션이 저로 되어있습니다.... (원래는 이러면 안됩니다)
lock tables products WRITE;
INSERT INTO products values(5, "Product E", 70.00);
update products set product_name = 'Product D' where product_id = 4;
delete from product where product_name = 'Product C';
alter table products modify product_name varchar(100);
unlock tables;
이번에는 READ락을 걸고 INSERT, UPDATE, DELETE, ALTER구문 실행

READ락을 걸었더니 DML, DDL구문 모두 실행이 안되었음
lock tables products READ;

묵시적 방법

MyISAM이나 MEMORY테이블에 데이터를 변경하는 쿼리를 실행하면 발생되지만 InnoDB테이블의 경우 스토리지 엔진 차원에서 레코드 기반 잠금을 제공
InnoDB에서는 테이블 락이 설정되나 데이터 변경 DML쿼리에서는 무시되고 DDL의 경우 영향을 미침

네임드 락

네임드 락은 GET_LOCK()함수를 통해 문자열에 대해 잠금 실행
테이블이나 레코드에 잠금이 아닌 특정 문자열에 대해 잠금 실행
배치 프로그램에서 한 번에 많은 레코드를 변경하는 쿼리 같은 경우 데드락이 자주 발생하여 동일 데이터를 변경하거나 참조하는 프로그램끼리 분류해서 네임드 락을 실행

네임드 락 예시
SELECT GET_LOCK('lock1',10);
'lock1' 이름을 가진 네임드 락을 설정하려는 쿼리입니다.

GET_LOCK() 함수는 네임드 락을 설정하려고 시도하고, 10초 동안 락을 얻으려고 시도합니다.

성공하면 1을 반환하고, 실패하면 0을 반환합니다.
SELECT IS_FREE_LOCK('lock1'), IS_USED_LOCK('lock1');
IS_FREE_LOCK() 및 IS_USED_LOCK() 함수를 사용하여 'lock1' 이름의 네임드 락의 상태를 확인하는 쿼리입니다.

IS_FREE_LOCK('lock1')은 'lock1' 락이 현재 자유롭게 사용 가능한지 여부를 확인하며, 사용 가능한 경우 1을 반환하고, 그렇지 않으면 0을 반환합니다.

IS_USED_LOCK('lock1')는 'lock1' 락이 현재 사용 중인지 여부를 확인하며, 사용 중이면 1을 반환하고, 그렇지 않으면 0을 반환합니다.
SELECT IS_FREE_LOCK('lock2'), IS_USED_LOCK('lock2');
마찬가지로, 'lock2' 이름을 가진 네임드 락의 상태를 확인하기 위한 쿼리입니다.

IS_FREE_LOCK('lock2')와 IS_USED_LOCK('lock2') 함수를 사용하여 'lock2' 락의 상태를 확인하며, 각 함수는 해당 락의 상태에 따라 1 또는 0을 반환합니다.