SUBJECT: Database의 transaction의 이해와 활용

• References

  • web

  • text book

    • Learning sql 3rd edition/Alan Beaulieu/9781492057611
    • SQL level up/Mic/9788968482519

Multiuser Databases

transactions

SQL 구문들을 그룹화하여, 그룹 전체를 성공, 실패하도록 관리하는 매커니즘.

• 고객이 영화를 새로 대여하는 상황
• 고객이 대여한 것을 반납하는 상황

정답은, 어떻게 DBMS가 locking을 다루는 지에 달려있다.

Locking

read lock, write lock:
	Database Writer는 write lock을 DBMS로부터 습득해야 데이터를 수정할 수 있고, Database Reader는 read lock을 DBMS로부터 습득해야 데이터를 읽을 수 있다. Read lock은 동시에 여러 유저에게 전달될 수 있지만, Write lock은 각 테이블에 하나만 발행될 수 있고, 발행된 동안 read requests는 막힌상태로 대기한다. pro 순차적으로 잘 반영된 데이터를 보장할 수 있다. con 많은 요청을 처리할 때, write 요청의 처리 속도에 따라 전체적인 속도가 좌우된다.
versioning:	Database Writers는 write lock을 요청하고 습득해야 데이터를 수정할 수 있지만, Database Reader는 어떠한 타입의 lock도 필요로 하지 않는다. 대신, 서버는 Reader의 쿼리가 시작된 시점에서 종료되는 시점사이에 일어나는 어떠한 수정도 반영되지 않은, 균일한 view의 데이터를 보장해야한다. 이 접근법은 versioning이라고 알려져 있다. pro 많은 요청을 허용할 수 있다. con 특정 read 요청이 길어질 경우 그 사이 많은 수정사항이 종료되었다면 version과 version사이에 차이가 많이 벌어진다.

• Microsoft SQL Server: 첫 번째, read lock, write lock strategy
• Oracle Server: 두 번째, versioning strategy
• MYSQL SERVER: 선택가능, strategy depend on storage engine

What is transaction?

transaction

복수의 SQL구문들을 그룹화 하여, 전부 성공 혹은 전혀 성공하지 못하도록 하는 장치이다. (atomicity)

transaction은 정의상 아래 4가지 property로 그 기능과 원칙을 충족해야 한다.

• atomicity

  transaction으로 그룹화된 연산은 최종 결과에 의해 전부 실패되거나 성공되어야 한다는 특성이다.

• durability

  durability는 반드시 영속적인 기록장치에 저장되도록 보장함을 의미하는 속성이다.

• isolated:

  transaction끼리 서로 영향을 주지 영향을 주지 않도록 관리되어야 한다.

  (이는 최종적으로 transaction integrity가 언제 적용되야 하는지까지 이어진다.)

• consistency:

  DBMS가 허용하는 방식, 제약사항을 충족한 상태로만 Data에 영향을 주어야 한다.

  (cascade, constraints, triggers 등의 사전 조건)

Starting a transaction

DBMS는 transaction의 생성을 아래 두 가지 방법 중 하나의 방법으로 다룬다.

connection session as transaction:
	연결 세션 자체를 트랜젝션으로 처리한다. 활성화된 transaction은 항상 DB session과 연관되어 있기 때문에, 명시적으로 transaction을 시작하는 필요나 수단이 준비되지 않는다. 진행중인 transaction이 종료되면, 서버는 자동으로 나의 session에 대한 새로운 transaction을 준비한다. Oracle의 경우 이 방법을 선택하고 있다. Note 이 경우 단일 SQL구문이라도 transaction 처리되어, 나중에라도 이 요청을 rollback이 가능하다.
no transaction without issuing:
	명시적으로 실행하지 않으면, 개별적으로만 처리한다. 명시적으로 transaction을 실행하지 않는다면, 개별적인 SQL구문들은 자동적으로 개별적인 독립된 commit이 진행될 것이다. transaction을 실행하기 위해서, 처음 command를 issue해야 한다. MSSQL, MYSQL의 경우 이 방법을 선택하고 있다. Note 한번 입력이 종료되면, 추후 이 요청을 취소하여 되돌리는 것은 불가능하다.

#MSSQL
SET IMPLICIT_TRANSACTIONS ON

#MYSQL
SET AUTOCOMMIT=0

Important

이 경우 모든 SQL명령은 transaction의 group scope로 포함되며,

반드시 명시적인 commit이나 rollback이 되어야 한다.

Ending a Transaction

Transaction의 실행방법은 아래와 같았다.

• 설정에 의한 자동 Transaction처리

  # MYSQL
  SET AUTOCOMMIT=0;
  /* SELECT QUERY 생략 */
  /* INSERT QUERY 생략 */
  COMMIT;
  

• SQL구문에서 명시적으로 Transaction실행

  START TRANSACTION;
  /* SELECT QUERY 생략 */
  /* INSERT QUERY 생략 */
  COMMIT;
  

어떤 방식으로 시작되었건 간에, 전달하는 명령으로서 공통적으로는 아래 두 명령으로 종결되어야 한다.

• COMMIT;
• ROLLBACK;

위 두 가지 말고도 transaction이 종료될 수 있는 상황이 존재한다.

Transaction Savepoints

START TRANSACTION;
UPDATE rental
SET return_date = CURRENT_TIMESTAMP()
WHERE rental_id = 1;

SAVEPOINT before_update_rental;

UPDATE rental
SET update_date = CURRENT_TIMESTAMP()
WHERE rental_id = 1;

ROLLBACK TO SAVEPOINT before_update_rental;
COMMIT;