[MySQL] 데드락 및 데이터베이스 Lock(Shared Lock, Exclusive Lock, Record Lock)에 대하여 -1

들어가기 전 

이번 포스팅에서는 데드락과 데이터베이스 Lock에 대해서 알아보겠습니다.

트랜잭션 격리 수준에 대한 내용은 아래 포스팅을 참고하시면 됩니다.

 

https://hoestory.tistory.com/86

[MySQL] 트랜잭션 격리 수준

 

데드락이란?

데드락은 교착상태를 의미하고 두 개 이상의 작업이 서로 상대방의 작업이 끝나기 만을 무한정 기다리고 있기 때문에 결과적으로 아무것도 완료되지 못하는 상태를 의미합니다.

 

https://ko.wikipedia.org/wiki/%EA%B5%90%EC%B0%A9_%EC%83%81%ED%83%9C

위의 이미지를 보면 R1(자원 1)은 P1(프로세스 1)을 점유하고 있는 상태에서 P2(프로세스 2)를 점유하려고 요청하고 있습니다.

그런데 P2(프로세스 2)는 R2(자원 2)가 점유하고 있고 R2(자원 2)는 P1(프로세스 1)을 요청하고 있습니다.

이와 같은 상황으로 인해 R1과 R2는 무한 대기상태에 머물고 있습니다.

이것을 데드락이라고 합니다.

 

데드락이 발생하기 위한 조건 4가지

아래에서 설명하는 4가지의 조건이 만족할 때 데드락이 발생합니다.

• 상호배제 : 하나의 자원에 하나의 프로세스만 접근할 수 있습니다.
• 점유 대기 : 최소한 하나의 자원을 점유하고 있으면서 다른 프로세스에 할당되어 사용하고 있는 자원을 추가로 점유하기 위해 대기하는 프로세스가 존재해야 합니다.
• 비선점 : 다른 프로세스에 할당된 자원은 강제로 빼앗을 수 없습니다.
• 순환대기(원형대기) : 프로세스 집합에서 순환 형태로 자원을 대기하고 있어야 합니다.

 

지금까지 데드락에 대해서 알아보았습니다. 이제 데이터베이스 Lock에 대해서 알아보겠습니다.

 

초기 세팅

 

테이블 생성 및 데이터 삽입

 

create table lock_test_db(
    id bigint auto_increment primary key ,
    name varchar(255),
    age int,
    birth date,
    nickname varchar(255)
);

insert into lock_test_db(name, age, birth, nickname)
values
    ('테스터1', 25, '2000-11-15', '테스터 닉네임'),
    ('hoestory', 27, '1998-10-15', 'hoestory blog');

 

 

쿼리 실행 시 락 정보 확인 쿼리

 

SELECT * FROM performance_schema.data_locks;

 

만약 위와 같이 실행을 했는데 아래와 같은 메시지가 발생할 경우 performance_schema에 권한 설정을 해줘야 합니다.

 

 

에러 메시지 

 

Access denied for user '유저명'@'호스트주소' to database 'performance_schema'

 

 

performance_schema 권한 설정

 

GRANT SELECT ON performance_schema.* TO '유저명'@'호스트주소';

 

위 쿼리는 root 권한을 가진 계정으로 설정을 해줘야 합니다. 그럼 아래와 같은 쿼리를 실행시키면 이미지에 나와있는 거처럼 락 정보를 확인할 수 있습니다.(이미지에 있는 컬럼은 data_locks에 일부분입니다.)

 

SELECT ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID, THREAD_ID, LOCK_TYPE, LOCK_MODE, LOCK_DATA 
FROM performance_schema.data_locks;

 

 

데드락 정보 확인 쿼리

 

SHOW ENGINE INNODB STATUS;

 

만약 위와 같은 쿼리를 실행했는데 아래와 같은 에러메시지가 발생하는 경우 권한 설정을 해줘야 합니다.

 

에러 메시지 

 

 Access denied; you need (at least one of) the PROCESS privilege(s) for this operation

 

 

권한 설정

 

GRANT PROCESS ON *.* TO 'tomorrow'@'localhost';
FLUSH PRIVILEGES;

 

 

위 쿼리는 또한 root 권한을 가진 계정으로 설정을 해줘야 합니다. 다시 쿼리를 실행시키면 이미지에 나와있는 거처럼 데드락 정보를 확인할 수 있습니다. 만약 설정을 했음에도 불구하고 실행이 안되면 데이터베이스 툴을 종료했다가 다시 시작하시면 됩니다.

 

 

이와 같이 status에 데드락 정보를 확인할 수 있습니다. Status에 대한 값은 아래 예제를 진행하면서 설명하겠습니다.

 

*참고*

데이터베이스 Lock에 대한 모든 예제는 AutoCommit을 비활성화한 상태에서 진행됩니다.

 

AutoCommit 비활성화

 

아래 쿼리를 실행시키면 AutoCommit을 비활성화할 수 있습니다.

 

SET AUTOCOMMIT = 0; // 1은 활성화 상태

 

 

지금까지 데드락, 락 정보를 확인할 수 있는 방법에 대해서 알아보았습니다. 예제를 통해서 데드락을 알아보겠습니다.

 

# session-1
begin; #1
SELECT * FROM lock_test_db WHERE birth = '1998-10-15' FOR UPDATE; #2
UPDATE lock_test_db SET age = 27 WHERE birth = '1998-01-06';#5 
rollback ;
commit;


//session-2

begin ; #3 
SELECT * FROM lock_test_db WHERE birth = '1998-01-06' FOR UPDATE ;#4
UPDATE lock_test_db SET age = 27 WHERE birth = '1998-10-15'; #6
rollback ;
commit ;

 

 

자원 1(2번)은 프로세스 1(session-1)을 점유하고 있고 자원 2(4번)는 프로세스 2(session-2)를 점유하고 있습니다.

자원 2가 자원 1이 점유하고 있는 프로세스점유하려고 요청을 하고 있고 자원 1은 자원 2가 점유하고 있는 프로세스를 점유하려고 요청하고 있습니다.

이때 데드락이 발생하는데 아래 쿼리를 통해서 데드락에 대한 정보를 확인할 수 있습니다.

 

SHOW ENGINE INNODB STATUS;

 

 

위 쿼리를 실행시키면 Status에 저장된 데드락 정보를 확인할 수 있습니다.

 

데드락 정보

#============ 생략 ============
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-10-20 19:58:55 0x16e15b000
*** (1) TRANSACTION:
TRANSACTION 5759920, ACTIVE 13 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 28, OS thread handle 6162739200, query id 1510618 localhost 127.0.0.1 tomorrow updating
/* ApplicationName=DataGrip 2024.2.2 */ UPDATE lock_test_db SET age = 27 WHERE birth = '1998-10-15'

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 216 page no 1517 n bits 1000 index idx_birth of table `tomorrow`.`lock_test_db` trx id 5759920 lock_mode X
Record lock, heap no 466 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8f9c26; asc   &;;
 1: len 8; hex 8000000000000002; asc         ;;


*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 216 page no 1516 n bits 440 index idx_birth of table `tomorrow`.`lock_test_db` trx id 5759920 lock_mode X waiting
Record lock, heap no 370 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 3; hex 8f9d4f; asc   O;;
 1: len 8; hex 8000000000000001; asc         ;;


*** (2) TRANSACTION:
TRANSACTION 5759919, ACTIVE 40 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1128, 4 row lock(s)
MySQL thread id 27, OS thread handle 6160510976, query id 1510628 localhost 127.0.0.1 tomorrow updating
/* ApplicationName=DataGrip 2024.2.2 */ UPDATE lock_test_db SET age = 27 WHERE birth = '1998-01-06'

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 216 page no 1516 n bits 440 index idx_birth of table `tomorrow`.`lock_test_db` trx id 5759919 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;


#============ 생략 ============

 

 

지금까지 데드락, 락 정보를 확인할 수 있는 방법에 대해서 알아보았습니다. 데이터베이스의 락, 종류에 대해 알아보고 예제를 통해서 락을 다뤄보겠습니다.

 

데이터베이스 Lock이란?

 

순차적으로 트랜잭션의 처리할 수 있는 기능을 제공하여 데이터의 무결성과 일관성을 지킬 수 있습니다.

그런데 락이 걸려도 순간과 그렇지 않은 순간을 구별해서 락을 잘 활용해야 합니다.

그렇지 못할 경우 서비스를 이용하는 사용자들에게 불편함을 제공할 수 있습니다.

 

 

Lock 종류

• Shared Lock
• Exclusive Lock
• Record Lock
• Gap Lock

 

Shared Lock(공유락)

Shared Lock은 데이터를 읽을 때 락을 겁니다. 하나의 트랜잭션이 데이터에 접근하여 공유락이 걸렸을 때 다른 공유락이 접근이 가능하지만 Exclusive Lock은 접근하지 못합니다.

 

공유락을 거는 방법은 아래와 같습니다.

 

A 트랜잭션

 

SELECT * FROM lock_test_db FOR SHARE

 

 

B 트랜잭션

 

SELECT * FROM lock_test_db

 

위와 같이 A 트랜잭션에서 "FOR SHARE"를 사용하여 lock_test_db에 Shared Lock이 걸립니다.

아래 쿼리를 통해 Shared Lock이 걸려있는지를 확인할 수 있습니다.

 

SELECT * FROM performance_schema.data_locks;

 

쿼리를 실행시키면 아래와 같은 이미지의 결괏값을 확인할 수 있습니다.

 

 

"LOCK_MODE"의 컬럼값을 확인해 보면"S"인 확인할 수 있습니다.

"S"는 Shared Lock을 의미합니다.

 

Exclusive Lock(배타적 락)

Exlcusive Lock은 데이터를 생성, 수정, 삭제이 일어날 때 발생하는 락입니다.

공유락과 공유락끼리 동시에 접근이 가능하지만  Exclusive Lock은 다른 트랜잭션에서 절대 접근하지 못합니다.

 

배타적 락이 획득되는 시점

• 조회 시점에 배타적 락 획득
• INSERT, UPDATE, DELETE 쿼리로 인한 배타적 락 획득

 

조회 시점에 배타적 락 획득

 

SELECT * FROM lock_test_db FOR UPDATE;

 

위와 같이 SELECT 쿼리에 "FOR UPDATE"를 붙여 조회 시점에 배타적 락을 획득할 수 있습니다.

배타적 락을 획득했는지 확인 방법은 아래쿼리를 통해 알 수 있습니다.

그럼 이미지에 LOCK_MODE 컬럼을 확인하면 배타적 락을 획득한 것을 확인할 수 있습니다.

 

SELECT OBJECT_NAME, LOCK_MODE FROM performance_schema.data_locks;

 

 

 

 

 

조회시점에 배타적 락을 획득하여 다른 클라이언트로부터 들어오는 INSERT, UPDATE, DELETE의 쿼리가 실행이 되지 못하고 대기상태에 있습니다.

그리고 조회시점에 배타적 락의 작업이 끝나고 커밋을 하면 대기상태에 있던 쿼리가 실행이 되고 실행되는 시점에 배타적 락을 획득하고 커밋 후 락이 해제됩니다.

 

INSERT, UPDATE, DELETE 쿼리로 인한 배타적 락 획득

 

UPDATE lock_test_db SET name = 'hi' WHERE name = 'bye';

 

 

위와 같이 UPDATE 쿼리를 실행하면 아래 이미지와 같이 배타적 락을 획득한 것을 확인할 수 있습니다.

 

 

 

Record Lock(레코드 락)

레코드 락은 인덱스에 락을 겁니다. 특정 트랜잭션이 레코드에 Insert, Update, Delete 하고 있을 때 다른 트랜잭션으로부터 INSERT, UPDATE, DELETE 쿼리가 발생하는 것을 예방합니다.

 

 

레코드 락을 예제로 알아보기 전에 먼저 인덱스를 생성하겠습니다.

 

 

인덱스 생성

CREATE INDEX idx_birth ON lock_test_db(birth)

 

 

 

위에서 생성한 인덱스가 적용된 컬럼(birth)으로 조건절에 활용하여 Update 쿼리를 실행하게 되면 조건에 해당하는 레코드에 락을 겁니다.

그럼 조건절인 birth = '1998-10-15'에 해당하는 레코드는 잠기게 되어서 다른 트랜잭션에서 접근할 수 없습니다.

그리고 Lock을 획득하고 있던 트랜잭션이 작업 완료가 되면 Lock을 해제하면 대기하고 있던 다른 트랜잭션의 쿼리가 실행됩니다.

'1998-10-15'에 해당하지 않은 레코드들은 Insert, Update, Delete가 가능합니다.

 

아래 쿼리를 통해서 락에 대한 정보를 확인할 수 있습니다.

SELECT ENGINE_TRANSACTION_ID, 
       OBJECT_NAME,
       INDEX_NAME,
       LOCK_TYPE,LOCK_MODE,
       LOCK_DATA,
       LOCK_STATUS
FROM performance_schema.data_locks;

 

 

 

 

 

 

ClientA에서는 '1998-10-15'에 해당하는 레코드를 락을 걸고 ClientB에서는 '1998-01-01'에 해당하는 레코드에 락을 걸어서 서로 대기하지 않고 각자의 작업을 수행합니다.