Concurrency Control with PostgreSQL
PostgreSQL에서 동시성 제어에 관한 글입니다.
이 글은 제가 Linuweb에 원고로 제출한 내용입니다.
주로 PostgreSQL의 메뉴얼을 허접한 영어실력으로 번역하는데에 치중 한 것으로 잘못된 내용이 포함되어 있을 수 있습니다.
인터넷을 사용하는 사람이라면 누구나 한번쯤은 온라인으로 상품을 구매 하거나 차표를 예약 하거나 인터넷뱅킹등을 사용해본 경험이 있을 것이다. 만약, 어떤 쇼핑몰에서 고객들에게 인기가 높은 특정 상품을 온라인으로 100개만 한정판매한다고 해보자. 한 고객이 한번에 하나의 상품만을 구매한다고 가정할때 100명이 주문신청을 하게 되면 될 것이다. 겉으로는 아무 문제가 없어 보인다. 하지만, 상품이 한개만 남아있을 경우를 생각해보자. A라는 사람은 재고가 1인것을 확인하고 상품을 구매 하기 위해 주문 신청을 하고 결제를 할 것이다. 이때 A라는 사람이 주문신청을 하고 결제를 하는 순간 잠깐 전화가 와서 결제완료 버튼을 클릭하기 전이라고 해보자. 이러는 순간에 B라는 사람 역시 이 상품의 재고가 1인것을 확인하고 주문신청을 하였다고 해보자. 이때 B는 A가 결제완료 버튼을 클릭하지 않아 아직 거래가 성사되지 않았으므로 (물론 A가 주문을 하는지 안하는지 B는 모른다.) 별 문제 없이 구매를 할 수가 있다. 전화를 끊고 이제 결제버튼을 클릭한 A는 분명히 재고가 있어서 구매를 하려 했는데, 재고가 없다는 화면을 보게 되거나 에러가 발생했다는 화면을 보게 될 것이다.
상식적으로는 A가 먼저 주문신청을 했으니 B는 구매 신청을 못하게 해야 할 것이다. 하지만, A가 중간에 주문을 취소 할 수도 있고, B는 이미 쇼핑몰을 떠났거나하여 이 제품을 구매할 기회를 잃어 버리게 된다.
이러한 문제는 어떻게 해결을 해야 할까? PostgreSQL은 MVCC(Multiversion Concurrency Control : 다중버전 동시 제어) 라는 특별한 방법을 사용하여 데이터의 일관성을 유지한다.
MVCC에 대해서 알아보기 전에 먼저 트랜잭션(Transaction)에 대해서 알아보자.
1. 트랜잭션 (Transaction)
트랜잭션은 모든 데이터베이스의 기본적인 구성 개념이다. 트랜잭션의 가장 중요한 포인트는 여러단계의 질의를 하나로 묶어서 모두 실행이 되게 하던지, 아니면 모두 취소를 시키는 것이다. 트랜잭션의 중간단계는 외부에 보이지 않으며 만약 트랜잭션이 완료되기 전에 에러가 발생하여 실패를 하게 되면 진행되었던 모든 단계의 질의는 취소가 되며 데이터베이스에 영향을 미치지 않게 된다. 트랜잭션은 매우 중요한 기능이다.
예를 들어서, 여러 고객의 계좌를 관리하는 은행 데이터베이스를 생각해보자. A라는 사람이 B의 계좌에 10,000원을 입금하는 과정을 생각해보자. 단순히 생각하면 SQL 명령은 다음과 같을 것이다.
accounts : 계좌 테이블 , balance : 잔액 컬럼, name : 고객 이름
I. A의 계좌에서 10,000원을 인출한다.
: UPDATE accounts SET balance = balance – 10000 WHERE name = ‘A’;II. B의 계좌에 10000원을 입금한다.
: UPDATE accounts SET balance = balance + 10000 WHERE name = ‘B’;
여기서 SQL 명령들의 상세한 동작은 중요한것이 아니다. 정작 중요한 부분은 이 예제에서와 같이 다소 단순한 동작을 완료하기 위해 여러개의 개별적인 업데이트 명령이 실행된다는 것이다. 만약 위의 예제에서 A의 계좌에서 10,000원이 인출되고 난 후 B의 계좌에 10,000원이 입금 되려 하기 전에 시스템에 문제가 생겨서 B의 계좌에 입금이 되지 못했다고 해보자. 이런 경우에 B는 입금을 받지 못했고, A의 잔액만 줄어들게 되어 버리는 일이 발생 하게 된다. 다시 A의 계좌에 인출된 금액을 채워넣어야 하지만, 이 작업은 생각보다 쉽지만은 않을 것이다. 하지만 트랜잭션을 이용하면 이러한 문제를 쉽게 복구 할 수 있다.
2. 트랜잭션의 시작과 종료
트랜잭션을 만들려면 BEGIN 과 COMMIT 명령어 사이에 SQL 명령어들을 넣으면 된다. 만약 BEGIN 명령어를 선언하지 않으면 PostgreSQL에서는 실제로는 모든 SQL 명령어를 트랜잭션으로 간주하여 각각의 SQL 명령문은 사실상 BEGIN 과 COMMIT 으로 묶이어 실행 된다. BEGIN 과 COMMIT 으로 둘러쌓인 SQL명령들의 그룹을 트랜잭션 블록(transaction block)이라고 부르기도 한다.
위의 은행 계좌 예제를 트랜잭션을 이용하여 다시 구성하면 다음과 같다.
BEGIN;
UPDATE accounts SET balance = balance – 10000 WHERE name = ‘A’;
UPDATE accounts SET balance = balance + 10000 WHERE name = ‘B’;COMMIT;
BEGIN 명령은 트랜잭션의 시작을 선언하는 것이고 COMMIT 명령은 트랜잭션을 정상적으로 종료하는 것을 의미한다. 일단 트랜잭션이 COMMIT 되면 데이터베이스에 대한 갱신이 확정 되기 때문에 취소 할 수가 없다. 만일 트랜잭션을 중간에 취소 시키고 싶다면, COMMIT 명령어 대신에 ROLLBACK 명령을 사용하면 된다. ROLLBACK 을 하게 되면 ROLLBACK 명령이 내려지기 전까지 트랜잭션에서 실행된 사항들이 모두 없었던 것으로 돌아가게 된다. 추가적으로 SAVEPOINT 명령을 사용하면 ROLLBACK 시에는 조금더 세밀한 제어가 가능하다. SAVEPOINT를 지정하면 ROLLBACK TO 명령을 이용해 SAVEPOINT 까지만 되돌아 가는 것이 가능하다. 앞서 살펴본 은행 계좌 트랜잭션에 SAVEPOINT 를 적용해 보자. A의 계좌에서 10,000원을 인출하여 ‘C’로 입금을 했어야 했는데, ‘B’의 계좌로 잘못 입금되었다는 것을 발견했다고 가정하면, 위의 트랜잭션은 다음과 같이 변경을 하여 올바르게 수정 할 수 있다.
BEGIN;
UPDATE accounts SET balance = balance – 10000 WHERE name = ‘A’;
SAVEPOINT my_savepoint;
– savepoint 지정
UPDATE accounts SET balance = balance + 10000 WHERE name = ‘B’;
– C의 계좌로 입금을 해야 하는데 B의 계좌로 잘못 되었다는것을 발견했다.
ROLLBACK TO my_savepoint;
– my_savepoint 로 되돌아간다.
UPDATE accounts SET balance = balance + 10000 WHERE name = ‘C’;
COMMIT;
3. 트랜잭션의 격리 레벨 (Transaction Isolation)
다음은 트랜잭션이 복수 사용자가 동시에 접속을 하여 실행 될때를 생각해보자. 한순간에 하나의 트랜잭션만 실행이 된다면 별 다른 문제가 없겠지만, 실제로는 동시에 여러 사용자가 접근을 하게 된다. 따라서 여러 트랜잭션이 동시에 실행 될 수도 있고 각각의 트랜잭션은 서로 영향을 받게 되어 심각한 문제가 발생 할 수도 있는 것이다.
표준 SQL은 동시에 동작하는 트랜잭션간에 발생 할 수 있는 3가지 현상에 의하여 4가지의 트랜잭션 격리 레벨을 정의해 놓았다. 이 현상들은 다음과 같다.
dirty read : 트랜잭션에서 commit 하지 않은 데이터를 다른 트랜잭션에서 읽는 경우에 발생.
nonrepeatable read : commit 하지 않은 트랜잭션의 영향을 받지는 않지만, commit 한 데이터의 값이 다른 트랜잭션에서 영향을 미치게 되는 현상.
phantom read : 조건을 만족하는 데이터를 가져왔을 때 다른 트랜잭션에 의하여 질의를 다시 실행 했을때 변경된 데이터가 리턴되는 현상. 없던 데이터가 나타나거나 있던 데이터가 사라질 수도 있다.
앞서 언급한 4가지의 트랜잭션 격리 레벨과 그에 대응하여 발생하는 현상은 다음 표와 같다.
트랜잭션 격리 레벨 Dirty Read Nonrepeatable Read Phantom Read Read uncommitted 가능 가능 가능 Read committed 불가능 가능 가능 Repeatable read 불가능 불가능 가능 Serializable 불가능 불가능 불가능
PostgreSQL에서는 위의 4가지 표준 트랜잭션 격리 레벨 모두를 사용할 수 있다. 그러나 내부적으로는 Read Committed 와 Serializable 격리 수준으로 구분된다. 즉, Read uncommitted 레벨을 선택 하면 실제로는 Read committed 레벨로 동작하고, Repeatable read 레벨을 선택하면 Serializable 레벨로 동작하게 되는 것이다. PostgreSQL이 단지 두가지의 트랜잭션 격리 레벨을 제공하는 이유는 앞에서 잠깐 언급한 MVCC(Multiversion Concurrency Control) 구조에 매핑 시키는 가장 현명한 방법이기 때문이다. 자세한 내용은 다음 절에서 알아보기로 하자.
참고로, 트랜잭션 격리 레벨을 설정 하기 위해서는 SET TRANSACTION 명령어를 사용하면 된다.
3.1 Read Committed Isolation Level
Read Committed 는 PostgreSQL에서 기본으로 설정되는 격리 레벨이다. Read Committed 레벨에서는 SELECT 질의는 질의가 시작되는 순간의 데이터베이스의 상태가 조회된다. 같은 트랜잭션상에서 같은 조건으로 두개의 SELECT 명령어를 실행했을때 만약 다른 트랜잭션에서 첫번째 SELECT 명령을 내리는 동안 데이터를 갱신했다면 다른 결과가 조회되는 경우가 생기게 된다.
즉 nonrepeatable read 는 발생을 할 수가 있다는 얘기다. 읽기 트랜잭션에서는 아직 commit 되지 않았음에도 불구하고 다른 트랜잭션에서 commit 되어 갱신된 데이터를 볼 수 있다는 것을 의미한다.
Read Committed 모드는 속도도 빠르고 사용하기에도 편리하여 많은 응용프로그램에 적당하다. 그러나 복잡한 쿼리를 실행하는 응용프로그램에서는 알아본 바와 같은 문제가 생기기 때문에 격리 레벨을 높여서 Serializable 모드로 변경을 한다면 nonrepeatable read 역시 발생을 금지 시켜야 한다.
3.2 Serializable Isolation Level
Read Committed 레벨에서는 commit 하지 않은 트랜잭션의 영향은 받지 않지만 commit 한 데이터의 값은 영향을 받는 다는 사실을 앞에서 알아보았다. PostgreSQL은 이와 같은 문제를 해결 하기 위해 다른 트랜잭션이 commit 한 값의 영향조차 받지 않도록 Serializable 격리 레벨을 제공한다. Serialzable은 가장 엄격한 격리 레벨이다. Read Committed 모드와는 달리 읽기 트랜잭션은 동시에 수행되는 다른 트랜잭션이 commit 한 데이터의 값과 상관없이 언제나 같은 데이터 결과만 볼 수 있다. UPDATE,DELETE,SELECT FOR UPDATE 명령들도 마찬가지로 동작한다.
두개의 트랜잭션이 있고, 그중에 하나의 트랜잭션의 격리 레벨이 Serializable 이며 나중에 실행된 트랜잭션이라고 해보자. Serializable 트랜잭션은 먼저 실행된 트랜잭션이 commit 을 하거나 roll back 을 하여 트랜잭션을 종료할때까지 기다린다. 만약 먼저 실행된 트랜잭션이 roll back을 하게 되면 먼저 실행된 트랜잭션의 갱신 작업은 모두 취소가 되고 serializable 트랜잭션은 자신의 갱신작업을 진행하게 된다. 하지만 먼저 실행된 갱신 트랜잭션이 UPDATE,DELETE,SELECT FOR UPDATE 의 명령을 내리고 commit 하였을 경우에 serializable 트랜잭션은 다음과 같은 에러 메시지를 출력하면서 취소가 되어 버린다.
ERROR : could not serialize access due to concurrent update
이것은 Serializable 트랜잭션이 시작된 후로는 다른 트랜잭션에 의해 갱신된 데이터를 수정 할수가 없기 때문이다.
응용프로그램이 이 에러 메시지를 받게 되면 현재 트랜잭션을 취소하고 다시 처음부터 시작을 시도 한다. 약간의 시간이 흐르면 트랜잭션은 먼저 commit 되어 갱신된 부분을 보기 때문에 논리적으로 충돌이 발생하지 않는다. 갱신 트랜잭션에서만 이와 같은 현상이 일어 나게 되며, 읽기 트랜잭션 에서는 절대로 충돌이 발생하지 않는다.
Serializable 모드는 각각의 트랜잭션이 일관된 데이터를 볼 수 있게 해주기 때문에 보다 엄격한 제어에 유용하다. 하지만 여기저기에서 동시 갱신을 하게 되는경우 연속적으로 실행을 하기 어렵게 되는 경우가 있어서 트랜잭션이 재시도 되도록 해야 하는 단점이 있다. 이러한 복잡한 트랜잭션을 재실행 하는데 드는 비용은 꽤 중요하다고 할 수 있다. 주로 갱신 트랜잭션이 논리적으로 많이 포함되어있거나 Read Committed 모드에서 잘못된 데이터를 얻는 경우에 사용하기를 추천한다.
4. MVCC (Multiversion Concurrency Control)
앞서 살펴본 결과를 보면 트랜잭션 격리 레벨을 지정하여 각각의 트랜잭션은 commit 되기를 기다리지 않고 접근을 할 수 있다는 사실을 깨달을 수 있다. 데이터베이스를 골치 아프게 만드는 여러 가지 이유중의 하나가 대부분 갱신처리 중에는 갱신이 되는 데이터에 대해서 다른 트랜잭션이 접근을 할 수 없어 기다려야 된다는 사실이다. 대부분의 데이터베이스가 이와 같은 방식을 사용하고 이것을 락(lock) 이라고 한다. 락을 이용하지 않고 마음대로 하게 내버려둔다면 결과는 불보듯 뻔한 일이 될 것이다. PostgreSQL도 역시 락을 사용하긴 하지만, MVCC라는 독특한 기법을 사용한다. 여기서 Version 이라는 단어의 의미는 어느 한순간의 특정 데이터의 스냅샷(snapshot)이라고 생각하면 된다. PostgreSQL은 특정 데이터가 갱신되었다고 하더라도 갱신전의 데이터를 삭제하지 않고 보존해 둔다. 만약 ‘A’ 라는 데이터를 삭제하기 위한 트랜잭션이 실행중이라고 하자. 나중에 실행을 시작한 트랜잭션이 ‘A’라는 데이터를 읽으려면 삭제가 되었을테니 읽기가 불가능해야 옳을 것이다. 하지만 갱신전의 데이터를 보존하기 때문에 나중에 실행된 트랜잭션의 시점에서는 갱신전의 데이터 , 다시 말해 삭제되기전의 ‘A’데이터를 얻을 수 있는 것이다. 이 기능은 다른 트랜잭션이 락을 이용해 접근을 통제하여 기다려야만 하는 시간을 없애 준다는 면에서 매우 훌륭하다고 할 수 있다. 즉, 락을 피할 수 있게 해준다는 것이다. 대규모 사용자 환경에서 더더욱 뛰어난 성능을 보일 것이 자명하다.
또한 MVCC를 이용하여 데이터베이스가 동작중인 상태에서도 완전한 백업을 하는 것이 가능하다. 이것을 hot backup 이라고 하는데, 데이터베이스의 전체 스냅샷을 얻어서 수많은 트랜잭션이 실행 중이라고 해도 상관없이 백업이 가능하게 되는 것이다.
앞에서 살펴본 문제점들은 참조 트랜잭션과 갱신 트랜잭션을 동시에 실행 할때 발생하는 것들이다. 분명히 PostgreSQL의 MVCC를 이용한 충돌 감지 기능 덕분에 큰 문제 없이 잘 동작 한다는 것을 알게 되었다. 하지만, 동시에 갱신 트랜잭션이 실행 된다면 어떻게 될까?
5. Explicit Locking (명시적 잠금,락)
PostgreSQL은 테이블의 데이터에 동시 접근을 제어하기 위해 여러가지 잠금 모드를 제공한다. 응용프로그램에 의하여 동시에 갱신 작업이 이루어 질 수 있는데, 이럴때는 MVCC가 적절한 해결책을 제시하지 못할 수도 있다. 이럴 경우에는 잠금 기능을 사용해야 한다. 대부분의 PostgreSQL 명령어들은 실행되는 동안 참조된 테이블이 적절하지 못하게 삭제되거나 수정되지 않도록 보장 하기 위해 적당한 잠금 모드를 자동적으로 획득한다. 예를 들어 ALTER TABLE 명령은 같은 테이블에서 다른 명령어나 동작들과 동시에 실행 될 수 없다. 참고로 데이터베이스 서버에 현재 잠금 모드의 목록을 알고 싶다면 pg_locks 시스템 뷰를 이용하면 된다.
먼저 Table 수준의 잠금을 살펴보자. 아래의 목록은 PostgreSQL에서 자동적으로 사용하는 잠금모드 목록이다. 또한 사용자는 “lock” 명령어를 사용하여 명시적으로 잠금 모드를 설정 할 수도 있다. 주의할 점은 목록중에 “row” 라는 단어가 포함되었을지라도 여기서는 테이블 수준에서의 잠금 이라는 점이다. 목록들의 이름을 보면 각각의 잠금모드의 일반적인 용법을 어느정도는 파악 할 수 있다.
ACCESS SHARE
: ACCESS EXCLUSIVE 락 모드와 충돌이 난다. SELECT 와 ANALYZE 명령어가 사용될때 이 잠금 모드가 사용된다. 일반적으로 오직 읽기를 수행하거나, 갱신이 없는 명령어를 수행할때는 이 모드를 획득한다고 보면 된다.
ROW SHARE
: EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. SELECT FOR UPDATE 명령시에 타겟이 되는 테이블에 이 잠금 모드가 설정된다.
ROW EXCLUSIVE
: SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. UPDATE, DELETE, INSERT 명령어가 수행될때 타겟 테이블에 이 잠금 모드가 설정된다. 일반적으로 테이블의 데이터를 갱신하는 명령어들이 수행될 때 이 모드가 설정된다고 생각하면 된다.
SHARE UPDATE EXCLUSIVE
: SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. 이 모드는 동시에 테이블 스키마의 변경이나, VACUUM (FULL 제외) 실행시에 설정되며 테이블을 보호해준다.
SHARE
: ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. 동시에 테이블의 데이터가 변경되지 않도록 테이블을 보호한다. CREATE INDEX 명령 수행시 설정되는 모드이다.
SHARE ROW EXCLUSIVE
: ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. 이 모드는 PostgreSQL 명령어로는 자동적으로 설정되지 않는다. 즉 사용자가 명시적으로 선언하여야 한다.
EXCLUSIVE
: ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE 모드와 충돌이 난다. 이 모드는 동시에 ACCESS SHARE 잠금만 허용하도록 한다. 바꾸어 말하면, 이 모드에서는 오직 읽기 트랜잭션만 동시에 수행 될 수 있다는 뜻이다. SHARE ROW EXCLUSIVE 모드와 마찬가지로 PostgreSQL 명령어로는 자동설정 되지 않고, 명시적으로 잠금을 설정하여야 한다.
ACCESS EXCLUSIVE
: ACCESS EXCLUSIVE 모드를 포함하여 모든 잠금 모드와 충돌이 난다. 가장 엄격한 잠금 모드로서 이 모드로 잠금이 설정되어 동작중인 트랜잭션만 테이블에 접근을 할 수 있다. ALTER TABLE, DROP TABLE, REINDEX, CLUSTER, VACUUM FULL 명령어 수행시 설정된다. 만약 명시적 잠금을 사용하기 위해 LOCK 명령을 사용할 경우 아무런 옵션도 주지 않고 실행 한다면 기본적으로 이 모드가 설정된다는 것을 알아 두자.
충돌이라는 단어가 많이 등장하였는데, 충돌이 난다는 의미는 서로의 진행을 방해하여 잠금이 해제되기를 기다린다는 뜻이다. 만약 ACCESS EXCLUSIVE 모드가 설정되어있는 경우, ACCESS SHARE 모드는 충돌이 난다고 하였다. 즉, ACCESS EXCLUSIVE 잠금으로 인하여 읽기 동작도 못하게 되는 것이다. 위에서 아래로 내려올 수록 좀더 강도가 높아진다는 걸 알 수 있다.
Table 잠금 말고도 행 잠그기가 있다. (Row-Level Lock) 특정 행이 DELETE, UPDATE 와 같이 갱신이 될 때 자동으로 설정이 된다. 잠금이 해제 되기 위해서는 진행중인 트랜잭션이 COMMIT을 하거나, ROLL BACK을 할때까지 기다려야 한다. 행 잠그기는 데이터 질의에는 영향을 주지 않고, 같은 행을 동시에 갱신할때 생기는 문제를 막기 위함이다. 실제로 행을 갱신하지 않게 하기 위해 SELECT FOR UPDATE 문을 사용하면 된다. 특정 행이 잠겨있으면 트랜잭션은 충돌 걱정없이 그 행을 여러번 갱신 할 수 있다. PostgreSQL은 메모리 상의 갱신된 행에 대한 어떠한 정보도 기억하지 않는다. 그런 이유로 한 순간에 잠겨있는 행의 수에는 제한이 없다. 하지만, 행 잠그기는 디스크의 사용을 유발한다. 예를 들어 SELECT FOR UPDATE 명령은 선택된 행들을 수정하고 디스크에 그 정보가 기록된다.
6. Dealocks (데드락, 교착상태)
앞서 살펴본 잠금의 사용은 사용자가 명시적으로 선언을 하여 사용을 할때 데드락을 발생 시킬 가능성이 있다. 예를 들어 두개 또는 그 이상의 트랜잭션이 서로가 원하는 데이터를 서로 블락 시킬때처럼 말이다. 트랜잭션 1이 테이블 A에 EXCLUSIVE 잠금을 설정하고, 트랜잭션 2가 테이블 B에 역시 EXCLUSIVE 잠금을 설정했다고 하자. 그리고 나서 트랜잭션 1이 테이블 B에 EXCLUSIVE 잠금을 시도하고, 트랜잭션 2가 테이블 A에 EXCLUSIVE 잠금을 설정했다고 하면 두개의 트랜잭션 모두 진행을 못하게 하는 상황이 발생하게 된다. 조금더 이해를 쉽게 하기 위해 아래의 표를 살펴 보자.
트랜잭션(T) 테이블 A 테이블 B T1 LOCK TABLE A; T 2 LOCK TABLE B; T 1 LOCK TABLE B; T 2 LOCK TABLE A;
PostgreSQL은 이러한 데드락 상황을 한쪽의 트랜잭션을 취소시켜 다른 트랜잭션이 완료를 할 수 있도록 하는 방식으로 해결을 한다. 물론 어떤 트랜잭션을 취소할지 예상하고 진행하는 일을 결정하는 일은 여러운 일임이 분명하다.
위와 같이 테이블 잠금에서도 데드락을 발생 할 수 있지만, 행 잠그기에서도 데드락이 발생 할 수있다. 이 말은 사용자가 명시적으로 잠금을 설정하지 않더라도 데드락이 발생 할 수 있다는 뜻이다.
한 테이블을 동시에 갱신하는 두개의 트랜잭션이 있는 경우를 생각해보자. 첫번째 트랜잭션이 다음과 같은 동작을 한다.
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
이 명령은 acctnum (account number) 가 11111인 특정 행을 잠그게 된다. 그리고 나서 두번째 트랜잭션이 다음과 같은 동작을 한다.
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance = balance – 100.00 WHERE acctnum = 11111;
두번째 트랜잭션의 첫 UPDATE 명령은 acctnum가 22222인 특정행을 갱신하기 위해 성공적으로 행잠그기를 수행하게 된다. 하지만 두번째 UPDATE 명령은 이미 잠금이 설정 되어있는 행에 갱신을 위해 잠금을 시도하는 것을 알 수 있다. 따라서, 행 잠그기를 성공하기 위해서는 기다려야 한다. 이제 두번째 트랜잭션은 첫번째 트랜잭션이 완료될때까지 기다리는 상태가 되었다. 이 상태에서 첫번째 트랜잭션이 다음을 실행한다.
UPDATE accounts SET balance = balance – 100.00 WHERE acctnum = 22222;
첫번째 트랜잭션이 acctnum가 22222인 행에 갱신을 위한 잠금을 시도하지만 마찬가지로 불가능 하다는 것을 눈치 챘을 것이다. 두번째 트랜잭션이 이미 잠금을 해놨기 때문이다. 역시 첫번째 트랜잭션은 두번째 트랜잭션이 완료 될때까지 기다리게 된다.
두개의 트랜잭션은 서로를 방해하고 기다리는 상태가 되었다. 즉 데드락이 발생한것이다.
PostgreSQL이 자동으로 데드락을 해소 시켜준다고는 하지만, 데드락이 발생한다는 것은 문제가 발생을 했다는 얘기이다. 데드락을 방지하는 가장 좋은 방법은 응용프로그램이 모든 응용프로그램이 순차적으로 트랜잭션의 구문을 수행하도록 생각을 잘해야 한다. 또한 PostgreSQL에 의해 자동으로 잠금모드가 설정되도록 처리 하는 쪽이 또 하나의 방법이다. 트랜잭션의 동작 시간은 최대한 짧게 하는 것이 좋고, 트랜잭션 격리 수준을 높일 필요가 없다면 낮게 설정하는것도 잠금을 줄이는 방법이되므로 데드락 예방에 좋은 방법이다.
중요한것은 트랜잭션이나, 잠금, 데드락등의 원리를 알아야 올바른 응용프로그램의 작성에도 도움이 되며 PostgreSQL의 성능을 최대로 이용할 수 있을 것이다.