# [ 살펴보기 ] Sql - 트랜잭션

트랜잭션은 여러 SQL 구문을 실행할 때 하나의 그룹으로 묶어 오든 구문이 성공했을 때 변경사항을 적용하거나 하나라도 정상적으로 처리되지 않았을 때 모든 SQL를 구문을 실패로 처리하여 무효화 시킨다

예를들어 A라는 계좌에서 B라는 계좌로 입금을 하는 상황에서 A라는 계좌에서 돈은 빠져 나갔지만 B라는 계좌로 돈이 입금되지 않았다면 원하는 작업이 정상적으로 처리되었다고 할 수 없다. 이럴 때 A라는 계좌에서 돈이 빠져나가고 B라는 계좌에 돈이 입금이 되어야 비로서 입금행위가 올바르게 완료된 것으로 취급되어야 하고 만약 A계좌로 부터 출금 혹은 B계좌로 입금 둘 중 하나라도 실패한다면 입,출금하는 행위는 처음부터 없었던 걸로 취소되어야 한다.

위의 과정과 같이 트랜잭션을 통해 특정 SQL 구문의 그룹이 모두 정상적으로 처리되면 commit통해 결과를 적용하고 그렇지 않다면 rollback을 통해 SQL 실행 결과를 되돌린다.

테스트는 Mysql 기준으로 진행한다. 트랜잭션을 살펴보기 전에 autocommit 모드와 관련해서 간단히 살펴보고 넘어가자.

Autocommit 모드가 active 상태일 때 insert, update, delete와 같은 sql statement를 실행하면 그 변화가 자동으로 commit되어 영구적으로 적용된다. 만약 autocommit 상태가 inactive 상태라면 rollback을 통해 이전에 실행했던 sql 구문의 결과를 되돌릴 수 있다. ( Mysql은 default로 autocommit 모드가 active 상태이다 )

그리고 아래의 예제와 같이 `START TRANSACTION` 구문을 통해 트랜잭션을 시작하면 autocommit이 inactive 상태에서 sql 구문을 실행하고 commit을 통해 변경 사항을 영구 적용하거나 rollback을 통해 변경 이전 상태로 돌아갈 수 있다.

다음 예를 살펴보자.

```sql
DELIMITER //
CREATE PROCEDURE test_transaction()
BEGIN
  
  DECLARE test_name VARCHAR(50);
  
  START TRANSACTION;
  UPDATE test_table 
  SET name = "test updated name1"
  WHERE id = 1;
 
  SELECT name INTO test_name
  FROM test_table
  WHERE id = 1;
  
  IF test_name = "test updated name1" THEN
	UPDATE test_table 
	SET name = "test updated name2"
	WHERE id = 2;
    
    SELECT 'Update Succeeded' AS result;
    COMMIT;
  ELSE
	ROLLBACK;
    SELECT 'Update Failed' AS result;
  END IF;
  
END //
DELIMITER ;
```

위의 예제는 transaction 테스트를 위해 test\_transaction이라는 stored procedure를 하나 생성한다. 그리고 START TRANSACTION 구문을 통해 트랜잭션을 명시적으로 시작하고 procedure 내부에서 실행되는 SQL구문의 결과가 autocommit 되지 않게 하며 조건에 따라 commit을 하거나 rollback 처리를 할 수 있다.

위의 예제는 test\_table에서 id가 1인 행의 name을 'test updated name1'로 우선 변경한다. 그리고 test\_table에서 id가 1인 데이터를 select하고 그 결과가 변경된 내용과 일치하면 id가 2인 name도 변경하고 commit을 통해 id 1과 id2의 변경 사항을 영구 저장한다.

만약 select한 결과가 변경된 내용과 일치하지 않으면 rollback시켜 id가 1인 데이터의 update 결과를 되돌린다

procedure는 다음과 같이 call statement를 통해 실행할 수 있다

```sql
CALL test_transaction()
```

트랜잭션에서 주의할 점은 START TRANSACTION이후 commit이나 rollback 이전에 새로운 START TRANSACTION 구문을 시작하면 이전의 START TRANSACTION 에서 처리하던 SQL statement의 결과는 commit된다. 다음 예를 살펴보자

```sql
DELIMITER //
CREATE PROCEDURE test_transaction()
BEGIN
  
  DECLARE test_name VARCHAR(50);
  
  START TRANSACTION;
  UPDATE test_table 
  SET name = "test updated name11"
  WHERE id = 1;
  
  ROLLBACK;
   
  SELECT name INTO test_name
  FROM test_table
  WHERE id = 1;

  
  IF test_name = "test updated name11" THEN
	UPDATE test_table 
	SET name = "test updated name22"
	WHERE id = 2;
    
    SELECT 'Update Success' AS result;
    COMMIT;
  ELSE
    SELECT 'Update Failed' AS result;
  END IF;
  
END //
DELIMITER ;
```

위의 procedure를 실행하면 result는 Update Failed이 된다 그 이유는 id가 1인 데이터를 업데이트 하고 바로 rollback을 통해 update 구문의 결과를 되돌렸기 때문이다. 하지만 다음 예제는 어떨까?

```sql

DELIMITER //
CREATE PROCEDURE test_transaction()
BEGIN
  
  DECLARE test_name VARCHAR(50);
  
  START TRANSACTION;
  UPDATE test_table 
  SET name = "test updated name11"
  WHERE id = 1;
 
  START TRANSACTION;
  
  ROLLBACK;
   
  SELECT name INTO test_name
  FROM test_table
  WHERE id = 1;

  
  IF test_name = "test updated name11" THEN
	UPDATE test_table 
	SET name = "test updated name22"
	WHERE id = 2;
    
    SELECT 'Update Success' AS result;
    COMMIT;
  ELSE
    SELECT 'Update Failed' AS result;
  END IF;
  
END //
DELIMITER ;
```

똑같은 예제에서 rollback이전에 START TRANSACTION 구문을 하나 더 추가했다. 위의 SQL Query의 결과는 Update Success가 된다. 왜냐하면 Rollback이 실행되기 전 새로운 START TRANSACTION으로 인해 이전의 SQL 실행결과가 자동으로 commit되었기 때문이다.

## 세이브포인트

만약 rollback을 통해 commit되기 전의 모든 query를 취소하는 것이 아니라 특정한 포인트를 지정하여 그곳으로 rollback하고 싶을 때는 세이브 포인트를 이용할 수 있다. 다음 예제를 살펴보자

```sql

DELIMITER //
CREATE PROCEDURE test_transaction()
BEGIN
  
  DECLARE test_name VARCHAR(50);
  
  START TRANSACTION;
  UPDATE test_table 
  SET name = "test updated name11"
  WHERE id = 1;
  
  SAVEPOINT before_update_user; /* savepoint 선언 */
  
  UPDATE test_table 
  SET name = "test updated name22"
  WHERE id = 2;

  SELECT name INTO test_name
  FROM test_table
  WHERE id = 2;
  
  IF test_name = "test updated name2" THEN
	UPDATE test_table 
	SET name = "test updated name33"
	WHERE id = 3;
    
    SELECT 'Update Success' AS result;
    COMMIT;
  ELSE
	ROLLBACK TO SAVEPOINT before_update_user; /* savepoint로 rollback */
	COMMIT;
	SELECT 'Only User1 is updated' AS result;
  END IF;
  
END //
DELIMITER ;
```

위의 예제에서는 savepoint before\_update\_user를 생성하고 만약 if statement의 조건이 맞기 않으면 before\_update\_user save point로 rollback하고 commit한다. 그러면 savepoint 선언문 이전에 실행되었던 SQL 구문의 결과는 commit이 되고 savepoint 선언문 이후에 실행되었던 SQL 구문의 결과는 rollback이 된다.

위의 예제를 기준으로 id가 1인 데이터는 업데이트가 적용되고 id가 2인 데이터는 업데이트 되기 전으로 rollback 되는 것이다.