MariaDB Lock(실전)

여기서는 실제 테스트 한 내용을 토대로 작성하였습니다.
MariaDB에서 락이 걸리는 상황에 대해서 이야기 해보겠습니다.

테스트 환경

• MariaDB 버전 : 10.4.21-MariaDB-1:10.4.21+maria~focal
• Isolation : REPEATABLE READ
• 트랜잭션 갯수 : 2개

테스트에 사용되는 테이블 스키마

create table users
(
    id         int auto_increment
        primary key,
    first_name varchar(300) not null,
    last_name  varchar(300) not null,
    email      varchar(300) not null,
    gender     varchar(300) not null,
    ip_address varchar(300) not null,
    a          int          null,
    constraint users_pk
        unique (first_name, a)
);

create index users_email_index
    on users (email);

create index users_first_name_last_name_index
    on users (first_name, last_name);

create index users_ip_address_index
    on users (ip_address);

용어 설명

• T1 : 트랜잭션1
• T2 : 트랜잭션2

목차

1. SELECT FOR UPDATE 
2. INSERT INTO ... SELECT
3. INSERT 문에서의 Lock
4. DELETE 문에서의 Lock
5. DEAD LOCK
6. SELECT LOCK IN SAHRED MODE
7. Shared/Exclusive Lock에 대한 단순 SELECT

1. SELECT FOR UPDATE 

다음과 같은 경우 Lock이 발생합니다.

SELECT FOR UPDATE 문은 SELECT 문을 실행하면서 락을 걸어 다른 세션에서 해당 레코드를 변경하지 못하도록 합니다.
이 락은 행(row) 레벨에서 락을 걸 때 사용합니다.

SELECT FOR UPDATE는 다음과 같은 경우에 사용될 수 있습니다.

• 트랜잭션 내에서 특정 행에 대한 업데이트 작업을 수행해야 하는 경우
• 두 개 이상의 트랜잭션이 동시에 같은 행을 업데이트 하려고 할 때 충돌을 방지하기 위해 락을 걸어야 하는 경우

또한, 동일 트랜잭션에서 동일한 행에 대해 여러 번 SELECT FOR UPDATE를 실행하면 데드락이 발생할 수 있습니다.

 

테스트 1

• T1 에서 SELECT FOR UPDATE 를 실행합니다.
• T2 에서 SELECT LOCK IN SHARE MODE 를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;

-- WHERE id = 1 을 다른 트랜잭션에서 변경하지 못하도록 Exclusive Lock을 획득 했습니다.

-- T2
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- T1 에서 WHERE id = 1 행(row)에 대해 Exlusive Lock 을 걸어놨기 때문에
-- Shared Lock을 요청했지만 획득할 수 없으므로 대기 상태에 빠지게 됩니다.

테스트 2

• T1 에서 SELECT FOR UPDATE 를 실행합니다.
• T2 에서 UPDATE 쿼리를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- T1에서 WHERE id = 1 행에 대한 Exclusive Lock을 획득합니다.


-- T2
BEGIN;
UPDATE users
SET first_name = 'test'
WHERE id = 1;
-- T2에 WHERE id = 1 에 대한 Exclusive Lock을 획득하려 하지만
-- T1에서 해당 레코드를 Exclusive Lock으로 잠궈놨기 때문에 Lock을 획득하지 못하고
-- 대기상태에 빠지게 됩니다.

테스트 3

• T1 에서 SELECT FOR UPDATE 를 실행합니다.
• T2 에서 SELECT 쿼리를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;
-- WHERE id = 1 에 대한 Exclusive Lock을 획득합니다.

-- T2
BEGIN;
SELECT * FROM users WHERE id = 1;
-- WHERE id = 1 에 대해 Exclusive Lock이 걸려 있지만,
-- Share Mode로 락을 획득하려는 것이 아니기 때문에
-- 단순 SELECT 쿼리는 WHERE id = 1 에 대한 결과를 바로 반환 받게 됩니다. 대기하지 않습니다.

2. INSERT INTO ... SELECT

INSERT INTO ... SELECT 문은 다른 트랜잭션에서 동일한 레코드를 변경하지 못하도록 락을 걸어 INSERT 작업을 수행합니다.

테스트 1

• T1 에서 INSERT INTO SELECT 를 실행합니다.
• T2 에서 INSERT INTO SELECT 를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
INSERT INTO users (first_name, last_name, email, ip_address, gender)
SELECT first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
 -- users 테이블에 신규로 추가되는 레코드에 Exclusive 락을 겁니다.
 
 -- T2
BEGIN;
INSERT INTO users (first_name, last_name, email, ip_address, gender)
SELECT first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
-- users 테이블에 신규로 추가되는 레코드에 Exclusive 락을 겁니다.
-- 여기서 T2는 대기하지 않습니다.
-- 이유는 PK인 id는 자동증가 처리 되기 때문에 현재 T2는 T1이 락을 건 레코드에
-- Exclusive 락을 획득하려는 것이 아니라, 새로운 레코드에서 락을 획득하기 때문입니다.

테스트 2

• T1 에서 INSERT INTO SELECT 를 실행합니다.
• T2 에서 INSERT INTO SELECT 를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
INSERT INTO users (id, first_name, last_name, email, ip_address, gender)
SELECT 9000, first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
-- user 테이블의 PK인 id를 9000으로 Insert 하겠다고 명시했습니다.
-- id가 9000으로 들어갈 레코드를 Exclusive락으로 잡급니다.

-- T2
BEGIN;
INSERT INTO users (id, first_name, last_name, email, ip_address, gender)
SELECT 9000, first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
-- user 테이블의 PK인 id를 9000으로 Insert 하겠다고 명시했습니다.
-- id가 9000으로 들어갈 레코드를 Exclusive락을 획득하려고 요청하지만,
-- 이미 T1에서 id가 9000인 레코드에 Exclusive락을 획득한 상태라 대기하게 됩니다.

테스트 3

• T1 에서 INSERT INTO SELECT 를 실행합니다.
• T2 에서 UPDATE 를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
INSERT INTO users (id, first_name, last_name, email, ip_address, gender)
SELECT 9001, first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
-- id 9001 레코드에 Exclusive Lock을 획득합니다.

-- T2
BEGIN;
UPDATE users
SET first_name = 1
WHERE id = 9001;
-- id 9001 레코드에 Exclusive Lock을 획득하려 하지만
-- 이미 T1에서 id 9001 레코드에 Exclusive Lock을 걸어둔 상태라 대기하게 됩니다.

테스트 3

• T1 에서 INSERT INTO SELECT 를 실행합니다.
• T2 에서 SELECT IN SHARE MODE 를 실행합니다.
• 락이 걸리는지 확인합니다.

-- T1
BEGIN;
INSERT INTO users (id, first_name, last_name, email, ip_address, gender)
SELECT 9002, first_name, last_name, email, ip_address, gender
  FROM users2
 WHERE id = 1;
-- T1 에서 id 9002 를 insert 합니다. 9002 row에는 Exclusion Lock을 획득합니다.
 
 --T2
BEGIN;
SELECT * FROM users where id = 9002 LOCK IN SHARE MODE;
-- T2 에서 id 9002에 Shared Lock을 요청하지만,
-- 이미 T1에서 Exculsion Lock을 걸었기 때문에 대기하게 됩니다.

3. INSERT 문에서의 Lock

동일한 PK로 Insert하는 경우 행에 대해 Lock이 걸리게 됩니다.

테스트1

• INSERT 문에서 락이 걸리는 경우(PK)

--T1
BEGIN;
INSERT INTO (id, name) VALUES (1, 'junseok');
-- 아이디 1값을 갖는 레코드에 락이 걸립니다.(아직 커밋하지 않았기 때문에)

--T2
BEGIN;
INSERT INTO (id, name) VALUES (1, 'dongyoung');
--동일한 아이디에 INSERT를 하려고 하니 Lock이 걸린 상태여서 대기하게 됩니다.

--T1
Commit;
-- T1이 Commit 됩니다.
-- 그러면 T1의 Lock이 해제되면서 TB가 Exclusion Lock을 획득하게 되는데
-- 이때, Duplicate entry '1' for key 'PRIMARY' 라는 에러메세지와 함께 Insert가 실패합니다.

테스트2

• INSERT 문에서 락이 걸리는 경우(Unique Key)

--T1
BEGIN;
INSERT INTO test_db.users (first_name, last_name, email, gender, ip_address, a)
VALUES ('Kelsey', 'Williams', 'kwilliams0@google.pl', 'Male', '130.73.50.240', 2);
-- T1에서 first_name=Kelsey, a=2 인 레코드에 락이 걸리게 됩니다.

--T2
BEGIN;
INSERT INTO test_db.users (first_name, last_name, email, gender, ip_address, a)
VALUES ('Kelsey', 'Williams', 'kwilliams0@google.pl', 'Male', '130.73.50.240', 2);
-- 여기서도 UniqueKey에 해당하는 (first_name=Kelsey, a=2) 로 레코드를 저장하려고 하면,
-- 이전에 락이 걸려있기 때문에 대기하게 됩니다.

 

4. DELETE 문에서의 Lock

두개의 트랜잭션에서 동일한 레코드에 Exclusive Lock을 획득하려는 경우,
먼저 Exclusive Lock을 획득한 트랜잭션이 종료될 때 까지 대기상태에 빠지게 됩니다.

Test

-- T1
BEGIN;
DELETE FROM users WHERE id = 1;
-- id 1번 레코드에 Exclusive Lock을 획득합니다.

-- T2
BEGIN;
DELETE FROM users WHERE id = 1;
-- T1에서 id 1번 레코드에 Exclusive Lock을 걸었는데,
-- T2에서 동일한 레코드에 Exclusive Lock을 획득하려고 하니 T2는 대기상태에 빠집니다.

5. DEAD LOCK

DEAD LOCK이란 2개 이상의 트랜잭션이 서로 락을 요청하고 무한대기 상태에 빠지는 것을 말합니다.

Test

두개의 트랜잭션이 DEAD 락에 걸린 예시입니다.

-- T1
BEGIN;
UPDATE users
SET first_name = 1
WHERE first_name = 'Kelsey';
-- T1이 first_name = 'Kelsey' 레코드에 Exclusive Lock을 획득합니다.

-- T2
BEGIN;
UPDATE users
SET first_name = 1
WHERE first_name = 'Dugald';
-- T2가 first_name = 'Dugald 레코드에 Exclusive Lock을 획득합니다

-- T1
UPDATE users
SET first_name = 1
WHERE first_name = 'Dugald';
-- T1은 first_name = 'Dugald' 를 수정하려고 시도합니다.
-- 하지만 first_name = 'Dugald' 는 T2에 의해 잠겨져 있습니다.
-- 따라서 T1은 first_name = 'Dugald' 행(row)에 대해 대기 중입니다.

-- T2
UPDATE users
SET first_name = 1
WHERE first_name = 'Kelsey';

-- T2는 first_name = 'Kelsey' 를 수정하려고 시도합니다.
-- 하지만 first_name = 'Kelsey' 행(row)은 T1에 의해 잠겨져 있습니다.
-- 따라서 T2는 first_name = 'Kelsey' 행(row)에 대해 대기 중입니다.

-- T1과 T2는 서로 Lock을 요청하고 대기하면서 DeadLock에 빠집니다.
-- 이 상태에서는 두 트랜잭션 중 하나를 롤백해야 합니다.

---

 

6. SELECT LOCK IN SAHRED MODE

TEST1

T1에서 Shared Lock을 획득하고 T2에서 Exclusive Lock을 획득

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- WHERE id = 1 행에 대해서 Shared Lock을 획득합니다.

-- T2
BEGIN;
UPDATE users
SET first_name = 1
WHERE id = 1;
-- WHERE id = 1 행에 대해서 Shared Lock이 걸려있기 때문에
-- Exclusive Lock을 획득할 수 없어 대기상태가 됩니다.

TEST2

T1에서 Shared Lock을 획득하고 T2에서 Shared Lock을 획득

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- WHERE id = 1 행에 대해서 Shared Lock을 획득합니다.

-- T2
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;
-- T1에서 Shared Lock을 걸었지만, Shared Lock은 다른 트랜잭션에서의 Shared Lock을 허용하기 때문에
-- 대기하지 않고, Shared Lock을 획득하게 됩니다.

-- T2
SELECT * FROM users WHERE id = 1;
-- 당연히 해당 쿼리도 Shared Lock이 걸린 레코드지만 곧바로 결과를 반환합니다.

7. Shared/Exclusive Lock에 대한 단순 SELECT

단순 SELECT 의 경우, Exclusive Lock과 Shared Lock으로 잠겨 있는 레코드를 읽을 수 있는지 확인해 보겠습니다.

Test1

Exclusive Lock + SELECT

-- T1
BEGIN;
SELECT * FROM users where id = 2 FOR UPDATE;
-- Exclusive Lock을 획득합니다.

-- T2
BEGIN;
SELECT * FROM users WHERE id = 2;
-- Exclusive Lock으로 잠겨 있는 레코드에 접근하지만
-- 대기상태에 빠지지 않고 즉시 결과를 반환합니다.

Test2

Shared Lock + SELECT

-- T1
BEGIN;
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE ;
-- T1에서 Shared Lock을 획득합니다.

-- T2
BEGIN;
SELECT * FROM users WHERE id = 1;
-- Shared Lock으로 잠겨 있는 레코드에 접근하지만
-- 대기상태에 빠지지 않고 바로 쿼리 결과를 반환합니다.

Lock 허용 범위

Shared Lock 

• SharedLock O
• Exclusive Lock X

Exclusive Lock

• SharedLock X
• Exclusive Lock X

> 여기서 Exclusive Lock은 SharedLock은 잠그지만, 단순 Select 는 실행 가능하다.

---

참고

1. 현재 트랜잭션의 격리레벨 확인

SELECT @@GLOBAL.tx_isolation;

2. AutoCommit인 상태에서 Lock을 획득 방법

BEGIN;
START TRANSACTION;