MySQL lock (1) 테이블에 대한 잠금(table lock)

 

 

간단하게 테이블에 대한 S lock과 X lock에 대해서 알아보겠습니다.

S는 shared 이고, X는 exclusive 를 의미하는데, 줄여서 많이 사용됩니다.

 

보통 흔히 S lock과 X lock은 row lock에 대해서 많이 언급됩니다. 

 

하지만, table lock이 매우 이해하기 쉽기 때문에, 먼저 설명하겠습니다.

 

 

 

먼저 테스트용 데이터베이스와 테이블을 생성합니다.

create database test_db_210926;
use test_db_210926;

CREATE TABLE users (
    id INTEGER AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
INSERT INTO users (username, created_at) VALUES ('Kenton_Kirlin', '2017-02-16 18:22:10.846');

 

 

 

S lock 테스트

터미널을 2개 열어서 MySQL 에 접속합니다.

 

S lock을 테이블에 대해서 적용합니다.

SET AUTOCOMMIT=0;
START TRANSACTION;
lock table users read;

 

 

다른 터미얼에서 users 테이블에 대해서 쿼리를 실행하는데, 아래와 같이 모두 실패합니다.

S lock인 상태에서  읽기만 가능하며 변경, 삭제 또는 추가할 수 없습니다.

SET AUTOCOMMIT=0;
START TRANSACTION;
INSERT INTO users (username, created_at) VALUES ('Andre_Purdy85', '2017-04-02 17:11:21.417');
/* SQL 오류 (1099): Table 'users' was locked with a READ lock and can't be updated */
/* 영향 받은 행: 0  찾은 행: 0  경고: 0  지속 시간 0 of 1 쿼리: 0.000 초 */
DELETE FROM users;
/* SQL 오류 (1099): Table 'users' was locked with a READ lock and can't be updated */
/* 영향 받은 행: 0  찾은 행: 0  경고: 0  지속 시간 0 of 1 쿼리: 0.000 초 */
UPDATE users SET created_at = '2021-02-16 18:22:10.846';
/* SQL 오류 (1099): Table 'users' was locked with a READ lock and can't be updated */
/* 영향 받은 행: 0  찾은 행: 0  경고: 0  지속 시간 0 of 1 쿼리: 0.000 초 */

 

또한, 트랜잭션이 테이블을 S lock으로 잠근 후에는 다른 트랜잭션이 X Lock으로 테이블을 잠그거나 추가, 삭제 또는 수정할 수 없습니다.

 

 

 

X lock 테스트

이번에는 X lock 테스트를 진행해보겠습니다.

먼저, users 테이블에 X lock을 겁니다.

SET AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLE users WRITE;

 

이 상태에서 다른 터미널에서 user 테이블을 SELECT합니다.

SET AUTOCOMMIT=0;
START TRANSACTION;
SELECT * FROM users;

SELECT구문은 X lock이 release되지 않았기 때문에, blocking 되는 것을 알 수 있습니다.

 

 

X lock락이 release되지 않은 상태에서는 다른 lock 또한 blocking 됩니다.

LOCK TABLE users READ;

 

 

처음에 users테이블에 X lock을 건 트랜잭션에서 레코드를 변경하는 작업을 마치고, lock을 release합니다.

SET AUTOCOMMIT=0;
START TRANSACTION;
LOCK TABLE users WRITE;
UPDATE users SET created_at = now();
UNLOCK TABLE;

 

 

blocking 되었던 트랜잭션은 테이블에 lock이 해제되면서, 쿼리가 실행됩니다.

6분 넘게 blocking 되었던 것을 알 수 있습니다.

 

 

table lock 에대한 lock 처리를 표로 정리하였습니다.

트랜잭션 1	트랜잭션 2	트랜잭션 2의 Lock 획득여부
S	S	O
S	X	X
X	S	X
X	X	X