트랜잭션

트랜잭션은 더 이상 나눌 수 없는 데이터베이스 처리의 최소 단위이다. 계좌 이체의 경우 잔액 감소와 증가가 동시에 이루어져야 하기 때문에 정상적으로 완료될 경우에만 커밋을 하고, 오류가 발생할 경우 처음 상태 혹은 세이브포인트로 롤백을 하는 것이다(세이브포인트 이전은 오류가 없음이 확실하므로). 이처럼 한 단위로 이루어져야 하는 작업을 처리하거나, 데이터베이스 서버에 여러 요청이 들어올 경우 데이터의 완전성을 보장하기 위해 사용한다.

트랜잭션은 ACID 조건을 충족해야한다. 원자성(Atomic)은 트랜잭션 작업을 일부분만 커밋하는 것이 아닌, 모두 실패하거나 모두 반영되어야 한다는 것이다. 트랜잭션 수행 결과 데이터를 테이블이 아닌 임시영역(롤백 세그먼트)에 저장하다가 한번에 커밋하는 것으로 원자성을 보장한다. 일관성(Consistency)은 트랜잭션 수행 전후 DB 상태가 일관적이어야 한다는 것이다. 무결성 제약조건(기본키, 외래키, 도메인 제약조건 등)을 만족하는 것뿐만 아니라 계좌이체시 두 계좌의 총합이 같아야 한다는 조건도 일관성이다. 고립성(Isolation)은 트랜잭션이 동시에 수행되지만, 서로 영향을 받지 않고 독립적이어야 한다는 것이다. 지속성(Durability)은 트랜잭션이 정상 커밋된 경우 영구적으로 내용을 데이터베이스에 기록해야한다는 것이다. 만약 부분 완료(끝까지 성공은 했으나 커밋은 안됨)된 경우 취소해야한다는 것이다.

여러 개의 트랜잭션을 병행 처리할 때 나타날 수 있는 read-write 이상현상(Read Phenomena)이 있고, 이를 해결하기 위해 고립수준(Isolation Level)을 설정할 수 있다. 이때, 고립수준이 높을 수록 일관성(Consistency)은 높아지나 동시성(Concurrency)은 떨어진다. 가장 낮은 고립수준은 Read Uncommitted 으로, 아직 커밋하지 않은 데이터를 다른 트랜잭션이 읽을 수 있다. 하지만 Dirty Read 가 가능해, 트랜잭션 T1 에서 업데이트한 값을 T2 에서 읽었는데 T1 이 롤백된 경우, T2 가 읽은 값은 잘못된 값이 된다. 이를 해결하기 위해 Read Committed 고립수준을 사용하여 한 트랜잭션이 커밋한 데이터만 읽도록 허용할 수 있다. 하지만 T1 에서 같은 값을 두번 읽는 사이에 T2 에서 해당 값을 업데이트 할 경우 T1 이 읽은 두 값이 다른 Non-Repeatable Read 문제가 발생한다(select - update - select). 읽고 있는 데이터는 수정/삭제가 불가능하도록 Repeatable Read 고립수준을 설정할 수 있다. (Read Committed 는 INSERT/UPDATE/DELETE 에서 lock 이 걸리고 SELECT 가 대기한다. Repeatable Read 는 SELECT 를 하는 트랜잭션에서 lock 을 건다) 하지만 삽입은 여전히 가능하므로 T1 이 값을 두번 읽는 사이에 레코드가 추가되면, T1 의 첫 실행에는 없던 데이터가 두 번째에는 생기는 Phantom Read 가 발생한다. 이를 해결하기 위해 Serializable 고립 수준으로 트랜잭션을 완벽히 고립시킬 수 있지만, 동시성이 저하되어 현실적으로는 불가능에 가깝다.

한편 동시에 쓰기를 접근하는 write-write 의 경우에는 무조건 문제가 발생하며, 이는 read-write 처럼 선택적 허용이 아닌 lock 을 이용해 한 트랜잭션만 write 를 수행하도록 해야한다. 갱신손실(Lost update)은 T1 이 갱신을 완료하기 전 T2 가 값을 읽고 T1 이후에 저장을 해서 값을 덮어씌우는 경우이다. 모순성(Inconsistency)은 T1 이 x,y 값을 각각 읽고 갱신하는 사이에 T2 에서 x,y 모두 갱신을 해서 T1 의 x 는 갱신전, y 는 갱신후 값을 읽는 경우이다. 연쇄복귀(Cascading rollback)는 T1 에서 갱신을 했다가 롤백을 하려고 하는데 T2 가 갱신한 값을 읽고 기록해서 T2 의 연산은 롤백이 불가능한 경우이다.

데이터베이스를 갱신하는 과정에서 장애가 발생한 경우, 장애 발생 이전의 데이터로 회복을 해야하는데, 주기적으로 덤프(dump)를 만들거나 변경 연산을 작성해 놓은 로그(log) 파일을 통해 복구를 한다. 로그는 데이터의 변경사항을 데이터베이스에 직접 기록하기 전에 임시로 저장해 놓는데, 트랜잭션이 성공적으로 종료되면 REDO(재실행)으로 DB 에 기록하고, 중단됐다면 UNDO(취소)를 한다. UNDO 의 경우 트랜잭션의 START 는 있지만 COMMIT 은 없는 상태에서 장애가 발생했기 때문에, 트랜잭션이 변경한 내용을 이전 값으로 원상복구한다. 즉시갱신(immediate update)는 갱신데이터를 로그에 기록하는 작업과 버퍼의 데이터를 DB에 옮기는 작업이 동시에 진행될 수 있다. 지연갱신(Deferred update)는 갱신데이터를 로그에 기록하는 작업이 끝나면 부분완료가 되고, 그 후에 데이터베이스로 데이터를 옮긴다. 회복 시 많은 양의 로그를 처리하는데 시간이 오래걸리므로 특정 시간마다 검사를 하여 문제 없이 데이터베이스에 기록되었음을 확인하는 체크포인트를 만들 수 있다.

• lock: read 는 공유락(LS, Shared Lock), write 는 배타락(LX, Exclusive Lock). 공유락이 걸린 데이터는 읽을 수 있지만 쓸수는 없고, 배타락은 읽기도 불가능하다.

정규화

정규화는 관계형 DB 에서 중복을 최소화하도록 데이터베이스를 설계하는 것이다. 중복을 최소화하면서 데이터베이스 변경시 발생할 수 있는 이상 현상을 제거할 수 있다. 갱신이상은 한 레코드를 변경했는데 다른 곳에 중복이 있어서 전체적인 변경이 이루어지지 않은 경우이다. 삽입이상은 새 래코드를 추가 할 때, 아직 컬럼 값이 정해지지 않아 null 값이 비허용인 곳에 추가를 하지 못하는 경우이다. 삭제이상은 레코드를 삭제할 때, 삭제되지 말아야하는 레코드까지 삭제되었을 경우이다.

1차 정규화는 레코드마다 컬럼값이 1개만(원자값) 있어야 한다. 2차 정규화는 테이블의 모든 컬럼이 기본키에 대응되는 것, 즉 완전함수 종속을 만족하는 것이다. 학생, 나이, 과목 컬럼이 있을 때 학생, 과목은 그 자체로 하나의 기본키인데, 나이는 학생에만 종속되므로 학생,나이, 학생,과목 테이블로 분류를 해야한다. 3차 정규화는 기본키’만’ 다른 컬럼들을 결정할 수 있는 것이다. 학생, 우편번호, 도시 가 있을 때, 모든 컬럼은 학생에 종속되어 2차 정규화를 만족하지만 우편번호역시 도시를 결정할 수 있다. 즉, 도시가 우편번호에 종속되는 이행함수 종속이 발생한다. 다음으로 BCNF(Boyce and Codd Normal Form)는 모든 결정자가 후보키가 되도록 테이블을 분해한다. 학생, 과목, 교수, 학점 테이블이 있고, 만약 교수가 한 과목만 가르친다고 하자. 학생, 과목 기본키로 교수를 결정하는 한편, 교수는 과목을 결정하지만 후보키가 아니기 때문에 테이블을 분리해야한다.

인덱싱

테이블의 레코드를 하나씩 비교하면서 검색하기보다, 미리 어디에 데이터가 있는지 저장해 놓는 목차를 만들어 속도를 향상시킨 것이 인덱싱이다. 예를들어 이름 컬럼으로 인덱스를 지정할 경우, 값을 오름차 순으로 정렬하여 레코드의 위치를 찾을 수 있도록 한다. SELECT 뿐만 아니라 UPDATE, DELETE 때에도 해당 데이터를 찾아야하므로 효과를 미친다. 하지만 INSERT 될 때에는 인덱스 테이블에도 값이 추가되고, 정렬을 다시해야 하므로 추가 시 속도가 저하된다. UPDATE, DELETE 를 할 때에도 인덱스 테이블 갱신을 해야하므로, 변경이 빈번할 경우 오버헤드로 역효과가 발생할 수 있다.

트리거, 프로시저, 함수

트리거는 테이블에 어떤 작업이 발생했을 때 미리 정해놓은 활동이 자동으로 실행되는 작업이다. 예를들어 회원이 서비스를 탈퇴하여 레코드를 삭제할 경우, 영영 회원의 활동내용에 대한 정보는 찾을 수 없게 된다. 그래서 삭제된 회원을 모아놓은 테이블에 레코드를 추가하려고 하는데, 매번 일일이 명령어를 설정하기는 힘들다. 이럴 때 트리거를 설정하여 새 테이블에 삭제된 회원 정보를 옮기는 과정을 자동화 할 수 있다. 일관적인 데이터를 만들 수 있다.

프로시저는 일련의 작업 단위를 하나로 묶어 편리하게 사용하는 것이다. 데이터를 조작하고 저장하는 처리를 할 때 주로 프로시저를 사용한다(ex.전체 직원 급여 향상). 이와 비교해서 함수는 어떤 작업을 처리하기 위해 필요한 기능을 말하며, 파라미터로 넘어온 값의 체크(ex.password validation)나 변형(ex.datetime 문자열 형식)을 반환하게 된다.

조인, 뷰

뷰는 보안을 위해 테이블의 일부만 가져올 때, 또는 여러 테이블로부터의 쿼리를 나타낼 때 생성하는 가상의 테이블이다. 테이블이 생성되는 것이 아니라 쿼리만 저장되어 매번 실행하는 것이지만, 테이블처럼 편리하게 조회할 수 있으며 뷰를 기반으로 또다른 뷰를 생성할 수도 있다. 하지만 뷰를 갱신하는 데에 제약이 따르며, 한번 생성한 뷰의 정의는 수정할 수 없다. 또 뷰를 위한 인덱스는 따로 생성되지 않기 때문에, 뷰 생성시 where 로 레코드를 거르면 기본 테이블의 인덱싱을 활용할 수 없다. 하지만 매번 전체 테이블을 JOIN 하는 것보다는 where 로 범위를 좁히고 JOIN 명령을 수행하는 것이 낫기 때문에 원하는 작업에 따라 판단을 해야한다.

조인은 둘 이상의 테이블을 공유하는 컬럼 값으로 연결하여 데이터를 검색한다. INNER JOIN 은 두 테이블에서 키 값이 공통된 경우만 가져온다. OUTER JOIN 은 두 테이블의 모든 값을 가져오되, 공통된 키 값이 있으면 함께 표시한다. LEFT JOIN 은 왼쪽(FROM A LEFT OUTER JOIN B의 경우 A 테이블) 테이블의 모든 값을 가져오고, WHERE B.ID IS NULL 을 설정해주면 A 값 중 B 값을 제외한 데이터를 가져온다. RIGHT 은 그 반대의 테이블에 대한 작업을 진행한다. 조인을 할 때 전체 테이블을 가져와서 비교를 하는 경우 당연히 속도가 느려지기 때문에, 서브쿼리를 두어 필요한 컬럼과 키 컬럼만 가져오는 것으로 작성하면 좋다.