데이터베이스 DEAD LOCK에 대한 간략한 시연

DW(Data Warehouse)를 위한 분산 데이터베이스인 그린플럼(Greenplum)을 다루던 시절에,
코끼리(pgadmin) 창 2개를 열고 시연하던, 트랜잭션(Transaction)과 락(Lock) 매커니즘으로 인한 DEAD LOCK 발생에 대해서 구현해 보았다.

테스트 과정을 간략히 설명하면,
* 시연 트랜잭션 - INSERT + TRUNCATE 수행

1. 2개의 트랜잭션이 들어 온다.
    => INSERT (ROW EXCLUSIVE)는 동시 수행이 수행된다.
2. 1개의 트랜잭션에 INSERT가 끝나고 TRUNCATE가 들어온다.
    => INSERT (ROW EXCLUSIVE) <-> TRUNCATE (ACCESS EXCLUSIVE)는 상호 배재 관계 임으로, 다른 트랜잭션에 수행중인 INSERT가 끝나길 기다린다.
3. 나머지 1개의 트랜잭션에 INSERT가 끝나고 TRUNCATE가 들어온다.
    => 서로 다른 트랜잭션이 끝나길 기다리지만, 영원히 기다려야한다.
4. 코끼리는 DEAD LOCK을 발견 후, 메세지를 남기고 끊어 버린다.

참고 : http://www.postgresql.org/docs/9.4/static/explicit-locking.html

테스트 코드는 다름과 같다.

준비 작업

In [1]:

# 필요한 모듈 로딩
from sqlalchemy import create_engine
from multiprocessing import Pool

In [2]:

def insert_truncate_query(i):
    # 하나의 트랜젝션에 상호배제되는 LOCK을 유도하기 위한 함수
    engine = create_engine('postgresql://chef:fork@cook:5432/cook')
    connection = engine.connect()
    # 트랜잭션 구문 시작
    trans = connection.begin()
    try:
        # 세션 프로세스 아이디 출력
        print(i,connection.execute("select pg_backend_pid() as pid;").fetchall()[0][0])
        # LOCK MODE - ROW EXCLUSIVE 
        connection.execute("insert into target select * from source limit 200000;")
        # LOCK MODE - ACCESS EXCLUSIVE
        connection.execute("truncate target;")
        trans.commit()
    except:
        trans.rollback()
        raise

테스트1 - 단일 수행에 대한 결과

In [3]:

L = ['job I']
with Pool(processes=2) as pool:
    out = pool.map(insert_truncate_query,L)

job I 23183

테스트2 - 동시 수행에 대한 결과

In [4]:

L = ['job I','job II']
with Pool(processes=2) as pool:
    out = pool.map(insert_truncate_query,L)

job II 23196
job I 23197

---------------------------------------------------------------------------
RemoteTraceback                           Traceback (most recent call last)
RemoteTraceback: 
"""
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/usr/local/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 450, in do_execute
    cursor.execute(statement, parameters)
psycopg2.extensions.TransactionRollbackError: deadlock detected
DETAIL:  Process 23196 waits for AccessExclusiveLock on relation 6099669 of database 16384; blocked by process 23197.
Process 23197 waits for AccessExclusiveLock on relation 6099669 of database 16384; blocked by process 23196.
HINT:  See server log for query details.

번역하자면, Job II는 target 테이블에 Truncate를 하기 위해 기다리는데, Job I이 막고 있다. 반대로 Job I의 상황도 같다.

보통 분산 디비의 경우 기술적인 복잡성을 이유로 LOCK 모드와 같은 다양한 제약사항이 있다.
비 오라클 DBA 분들에게 제약사항이란, DB를 좀 더 깊이 이해할 수 있는 계기가 아닐까? 희망적인 메세지를 남겨본다.