Oracle/PHP 환경의 트랜잭션 관리

저자 Harry Fuecks

 

오라클 데이터베이스의 강력한 기능을 이용하여 “many-to-many” 관계를 관리하는 방법을 설명합니다.

아티클 관련 다운로드: Oracle Database 10g Zend Core for Oracle Apache HTTP Server 1.3 and later

 

게시일: 2005년 11월

 

트랜잭션을 이용하면 데이터베이스에 변경사항을 안정성 있게, 그리고 일관성 있게 적용하는 한편으로, “many-to-many” 관계를 관리하거나 다수의 배치를 배치 실행하는 등의 작업을 효과적으로 관리할 수 있습니다.

 

이번 연재에서는 트랜잭션 관리의 필요성과, PHP OCI 익스텐션에서 오라클 트랜잭션 서비스를 이용하는 방법을 설명하기로 합니다.

트랜잭션의 필요성

오라클 기반의 웹 로깅 애플리케이션을 이용하여 블로그 데이터 입력 정보를 관리하는 경우를 생각해 봅시다. 데이터베이스 스키마에는 “blogs” 테이블과 “categories” 테이블이 존재하며, 두 테이블은 “blogs_to_categories” 테이블을 통해 N:N 관계로 연결됩니다. 테이블 정의를 위한 쿼리는 아래와 같습니다:

CREATE SEQUENCE blog_id_seq
INCREMENT BY 1;
/

CREATE TABLE blogs (
id NUMBER PRIMARY KEY,
title VARCHAR2(200),
date_published DATE,
text CLOB
);
/

CREATE SEQUENCE category_id_seq
INCREMENT BY 1;
/

CREATE TABLE categories (
id NUMBER PRIMARY KEY,
name VARCHAR2(30) UNIQUE
);
/

CREATE TABLE blogs_to_categories (
blog_id INTEGER NOT NULL
REFERENCES blogs(id),
category_id INTEGER NOT NULL
REFERENCES categories(id),
PRIMARY KEY (blog_id, category_id)
);
/

 

블로그 저자는 자신의 블로그 기록을 "php," "programming," "web"과 같은 카테고리로 분류할 수 있습니다. 또 블로그를 조회하면서 “날짜 기준”의 디폴트 옵션 대신 다른 보기 옵션을 사용할 수도 있습니다.

 

블로그 저자가 기존 기록에 대한 카테고리 정보를 업데이트하는 경우를 생각해 봅시다 기록의 카테고리를 “web”에서 “oracle”로 변경하려면 blogs_to_categories 테이블에 대한 DELETE 작업과 INSERT 작업을 수행해야 할 것입니다.

이를 위한 SQL 구문이 아래와 같습니다:

-- Delete all existing category links to this blog entry
DELETE FROM blogs_to_categories WHERE blog_id = 5

-- Insert a single link between a blog and a category
INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
(5, 2)

 

INSERT 쿼리는 블로그 레코드와 연결된 각 카테고리 별로 반복 수행됩니다.

 

만일, DELETE 쿼리가 수행된 직후 웹 서버와 데이터베이스 간의 연결에 장애가 발생해서 INSERT 작업이 정상적으로 수행되지 못했다면 어떻게 될까요? 그렇다면 해당 블로그 기록은 카테고리 분류가 존재하지 않는 상태로 남게 될 것입니다.

 

이러한 문제를 방지하기 위해서는 트랜잭션을 이용해 SQL 구문을 실행해야 합니다. 트랜잭션은 다수의 구문을 하나의 엔티티로써 처리할 수 있게 합니다.

 

ACID 트랜잭션

ACID란, 트랜잭션 관리를 위해 데이터베이스에 구현되어야 하는 기본적인 속성의 약자를 의미합니다:

 

Atomicity(원자성)?트랜잭션은 완결되거나 또는 전혀 수행되지 않아야 한다. 트랜잭션의 부분적인 완료는 허용되지 않는다.

Consistency(일관성)?데이터베이스 제약조건은 어떤 상황에서도 준수되어야 한다.

Isolation(분리성)?트랜잭션은 다른 트랜잭션의 영향을 받지 않아야 한다. 예를 들어, 트랜잭션 A가 blogs_to_categories의 특정 카테고리에 대해 INSERT 작업을 수행하는 도중에, 트랜잭션 B가 해당 카테고리를 삭제하는 것을 허용하지 않아야 한다.

Durability(지속성)?트랜잭션이 완료(커밋)되는 경우, 데이터는 손실되지 않아야 한다 (장애로부터 안전해야 한다).

커밋(commit)이란 트랜잭션을 완료 처리하고 트랜잭션에 의해 발생한 변경 내역을 데이터베이스에 저장하는 것을 의미합니다. 롤백(rollback)은 트랜잭션을 취소한 경우 트랜잭션이 실행되기 이전의 상태로 데이터베이스를 되돌리는 것을 의미합니다. 다시 말해, 에러가 발생한 경우에는 트랜잭션 롤백을, 성공한 경우에는 트랜잭션 커밋을 수행하게 됩니다.

 

트랜잭션은 동시 수행되는 다수의 쿼리가 서로를 간섭하지 않은 상태에서 안정적으로 실행됨을 보장하고, 시스템 장애로부터 데이터 손실을 방지할 수 있게 합니다. 특히, 오라클은 로우에 대한 쓰기 작업이 수행되는 동안에도 다른 사용자들이 해당 로우를 읽을 수 있도록 허용합니다. 사용자는 쓰기 작업이 수행되기 이전의 데이터를 확인할 수 있습니다. 다시 말해, INSERT 또는 UPDATE 구문이 수행되는 동안에도 트랜잭션이 커밋되고 변경 내역이 적용되기 전까지 기존의 데이터를 확인하는 것이 가능합니다. 이처럼 쓰기 작업은 읽기 작업을 블로킹하지 않으며, 따라서 성능 병목 현상을 방지할 수 있습니다.

 

PHP 환경의 오라클 트랜잭션

 

오라클 환경을 위한 PHP 익스텐션은 트랜잭션에 관련하여 두 가지 모드를 지원합니다. 디폴트인 “autocommit” 모드는 모든 구문이 실행될 때마다 (다시 말해 oci_execute() 함수가 호출될 때마다) 자동으로 커밋을 수행합니다.

 

“커밋 모드(commit mode)”는 OCIExecute()함수의 두 번째 매개변수에 의해 결정됩니다. 이 매개변수에는 OCI_COMMIT_ON_SUCCESS (디폴트 autocommit 모드) 또는 OCI_DEFAULT oci_commit() 또는 oci_rollback()에 대한 명시적인 호출이 발생할 때까지 대기)의 값이 사용됩니다.

 

먼저, categories 테이블에 몇 가지 데이터를 입력하는 과정에서 제약사항을 위반하는 경우 어떤 현상이 발생하는지 살펴보기로 합시다:

<?php
$conn = oci_connect("SCOTT","TIGER") or die;

// Some categories
$categories = array(
"php",
"oracle",
"programming",
"web",
"this string is too long for the name column"
);

$sql = "INSERT INTO categories
(id, name)
VALUES
(category_id_seq.nextval,:category)
";

$stmt = oci_parse($conn,$sql);

foreach ( $categories as $category ) {

oci_bind_by_name($stmt,":category", $category,30);

// Note the OCI_DEFAULT parameter
if ( !oci_execute($stmt,OCI_DEFAULT) ) {

// If we have a problem, rollback then die
oci_rollback($conn);
die;
}

}

// If we got this far, it"s OK to commit
oci_commit($conn);// etc.
?>

 

위 코드는 name 컬럼의 최대 길이(30 문자)를 위반하고 있으며, 따라서 실패할 수 밖에 없습니다. 코드를 실행하면 아래와 같은 에러 메시지를 확인하게 됩니다:

Warning: oci_execute() [function.oci_execute]: OCIStmtExecute:
ORA-01461: can bind a LONG value only for insert into a LONG
column in /home/harry/public_html/create_categories.php on line
25

 

categories 테이블의 컨텐트를 조회하면 테이블에 아무런 데이터도 입력되어 있지 않음을 확인할 수 있습니다. 위의 스크립트에서 oci_execute() 함수에 전달되는 OCI_DEFAULT 매개변수를 삭제한 후 다시 실행해 봅시다. 30문자를 넘는 category name을 제거하고

스크립트를 다시 실행하면 데이터가 성공적으로 입력됩니다.

트랜잭션이 시작된 이후 명시적으로 커밋 또는 롤백을 수행하기 전까지의 과정에서 oci_execute()를 호출할 때마다, OCI_DEFAULT 상수를 매개변수로 사용하는 것을 잊지 말아야 합니다. 이와 같이 하지 않는 경우, oci_execute() 는 디폴트 모드로 실행되며 따라서 트랜잭션이 시작된 이후 실행된 모든 쿼리가 커밋되어 버립니다. 하지만 (CREATE, DROP ALTER 등의) DDL 구문의 경우는 oci_execute() 함수에 전달되는 매개변수와 상관없이 언제나 autocommit 모두로 실행됩니다.

또 PHP 스크립트의 실행이 종료되고 나면, 실행 중이었던 모든 트랜잭션이 오라클에 의해 자동으로 롤백 처리된다는 점을 주의하시기 바랍니다. 디폴트 autocommit 모드에서는 신경 쓸 필요가 없겠지만, 스크립트가 메모리 또는 시간 제약으로 인해 갑자기 실행 종료되어 버리는 경우, 모든 오픈 트랜잭션이 롤백 처리된다는 점을 조심할 필요가 있습니다. 이러한 동작 방식은 임시 연결 또는 퍼시스턴트 연결(persistent connection) 모두에 적용됩니다.

 

아래 코드는 전체 쿼리를 하나의 작업 단위로 처리하지 않는 경우 흔히 발생할 수 있는 문제를 예시하고 있습니다. 아래 스크립트는 (HTML 폼을 통해 POST 방식으로 전달된 것으로 가정되는) 샘플 데이터를 받아 3 개의 SQL 구문을 실행합니다. 먼저 blogs 테이블에 하나의 로우를 INSERT하고, 테이블의 레코드 수를 카운트하여 사용자에게 피드백 정보를 전달합니다. 카운트 쿼리를 수행하는 과정에서 oci_execute() 함수에 OCI_DEFAULT 상수가 전달되지 않았음을 주목하십시오. 마지막으로 2개의 category ID를 blogs_to_categories 테이블에 INSERT하여 categories 테이블과 blogs 테이블을 연결하려 시도합니다. 두 category ID 중 하나가 categories 테이블에 존재하지 않으므로, 오라클은 참조 제약조건 위반을 감지하고 에러를 발생시킵니다.

<?php
$conn = oci_connect("SCOTT","TIGER") or die;

/* Start by inserting a blog entry */

// An array representing a submitted blog entry
$blog = array(
"id"=>NULL,
"title"=>"Hello World!",
"text"=>"This is my first blog entry",
"categories" => array(1,999) // Note second ID does not exist
);

$sql = "INSERT INTO blogs
(id,title,date_published,text)
VALUES
(blog_id_seq.nextval,:title,SYSDATE,:text)
RETURNING
id INTO :id ";

$stmt = oci_parse($conn,$sql);

oci_bind_by_name($stmt,":title", $blog["title"],200);
oci_bind_by_name($stmt,":id", $blog["id"],32);
oci_bind_by_name($stmt,":text", $blog["text"]);

// Note OCI_DEFAULT - begin a transaction
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);
}

/* Now count the total number of blog entries */

$sql = "SELECT COUNT(*) AS num_entries FROM blogs";

$stmt = oci_parse($conn,$sql);

// Automatically populates the $num_entries variable
oci_define_by_name($stmt,"NUM_ENTRIES",$num_entries);

// Whoops forgot the OCI_DEFAULT!
oci_execute($stmt);

oci_fetch($stmt);

$result = "Blog added. There are now $num_entries blogs ";

/* Now insert the categories related to the entry */

$sql = "INSERT INTO blogs_to_categories
(blog_id, category_id)
VALUES
(:blog_id, :category_id)";

$stmt = oci_parse($conn,$sql);

// Loop through the categories
foreach ( $blog["categories"] as $category_id ) {

oci_bind_by_name($stmt,":blog_id", $blog["id"],32);
oci_bind_by_name($stmt,":category_id", $category_id,32);

// Note OCI_DEFAULT again
if ( !oci_execute($stmt,OCI_DEFAULT) ) {
oci_rollback($conn);
exit(1);

}

}

oci_commit($conn);
echo $result

?>

 blogs 테이블의 로우 카운트를 위해 SELECT 구문을 실행하는 과정에서, 스크립트는 autocommit 모드를 사용함을 명시하고 있습니다. 따라서 첫 번째 INSERT 구문과 두 번째 SELECT 구문은 실행 후 즉시 커밋 됩니다. 또 세 번째 구문과 함께 새로운 트랜잭션이 시작되며, category ID를 INSERT하는 과정에서 참조 키 제약조건 위반으로 인해 롤백이 발생합니다. 결과적으로 blog 엔트리는 입력되었지만 관련된 category 정보는 소실되어 버리고 말았습니다. 이 3가지 쿼리는 하나의 작업 단위로써 실행되었어야만 합니다. 마지막에 참조 키 제약조건 위반이 발생했을 때 모든 INSERT 작업을 롤백 처리하고 사용자에게 category 정보가 올바르지 않으므로 다시 입력하라는 메시지가 전달되어야 합니다.

이와 같이 하려면 스크립트 내의 모든 oci_execute()호출 과정에 OCI_DEFAULT 상수를 추가해 주어야 합니다. 아래와 같이 변경함으로써 문제를 해결할 수 있습니다: