트랜잭션

성공적으로 완료가 되어 반영되는 것을 커밋이라 하고, 문제가 하나로도 발생되어 원래의 상태로 돌아가는 것을 롤백이라 한다. 이를 통해 데이터의 일관성을 유지할 수 있다.
여러 RDB에서는 Stored Procedure (저장함수)하는 개념을 제공하여 해결을 돕고 있다.

DELIMITER //

CREATE PROCEDURE TransferMoney(IN account_id1 INT, IN account_id2 INT)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    -- 하나의 쿼리라도 에러가 발생하면 롤백!
    ROLLBACK;
  END;

  START TRANSACTION;
  UPDATE bank_accounts SET balance = balance - 1000 WHERE account_id = account_id1;
  UPDATE bank_accounts SET balance = balance + 1000 WHERE account_id = account_id2;
  -- 두 UPDATE 쿼리 모두 성공하면 커밋
  COMMIT;

END//

DELIMITER ;

이렇게 송금 계좌와 수신 계좌의 ID만 알면 Stored Procedure는 일관적으로 동작할 수 있다.
메시와 호날두의 사례를 적용하여 Stored Procedure 호출을 할 수 있다.
메시 계좌의 ID는 291036, 호날두 계좌의 ID는 918225번

CALL TransferMoney(918225, 291036);

ACID 특성

• Atomicity
  • 데이터베이스의 모든 트랙잭션은 원자성을 보장한다. 수행이 되든지, 안 되든지.
  • 애매한 상태는 존재하지 않는다.
  • 각각의 단계도 마치 하나의 단계인 것처럼 같이 움직여야 한다.
• Consistency
  • 트랜잭션이 완료되면 데이터의 일관성이 보장된다.
  • 무결성 제약을 깨뜨리는 트랜잭션은 실행되지 않는다.
• Isolation
  • 트랜잭션이 일단 수행이 되면 다른 트랜잭션으로부터 영향을 받지 않고 수행된다.
  • 호날두가 메시에게 100만원을 보내는 트랜잭션이 완료되기 전까진 다른 트랜잭션은 호날두의 계좌에서 100만원이 인출된 잔액을 볼 수 없다.
• Durability
  • 트랜잭션이 성공적으로 수행되면 이 결과는 데이터베이스에 영원히 반영된다.

Isolation 특성 살펴보기

1. Serializable (가장 높은 Level)
   1) 하나의 트랜잭션이 완료될 때까지 다른 트랜잭션은 해당 상품의 재고에 접근할 수 없음을 의미
   2) 고객 A가 결제를 완료할 때까지 고객 B는 재고를 확인하거나 구매할 수 없다.
   3) 일관성은 완벽하게 유지되지만 고객 B는 기다려야 하고 성능도 느려진다.
   4) 고객이 많다면 성능 문제가 발생할 수 있다.

2. Repeatable Read (MySQL 기본 설정)

   한 트랜잭션 내에서 같은 데이터를 반복해서 읽어도 동일한 결과를 보장

   1) 고객 A와 고객 B가 동시에 같은 상품의 재고를 확인하고 구매를 시도할 수 있다.
   2) 하지만 실제로 재고를 감소시키는 업데이트는 한 명의 고객만 성공할 수 있다.
   3) 고객 A가 먼저 결제를 완료하면, 고객 B는 실패하거나 잠금 대기 상태에 빠질 수 있다.
   4) 데이터 일관성은 유지되지만, 고객 B는 결제 실패나 지연을 경험할 수 있다.
   5) 성능은 Serializble보다 좋지만 동시 접근으로 인한 충돌 처리가 필요하다.

3. Read Committed (MySQL을 제외한 다른 DB들의 기본 설정)

   한 트랜잭션 내에서 커밋 완료된 데이터만 읽을 수 있는 것을 보장

   1) 고객 B가 고객 A의 트랜잭션이 커밋된 후에만 그 변경된 데이터를 읽을 수 있다.
   2) 즉, 고객 B는 자신의 트랜잭션 중 언제든 고객 A의 결제로 인해 변경된 재고를 볼 수 있다.
   3) Dirty Read는 방지되지만 Non-Repeatable Read 문제가 발생할 수 있다.
   4) 각 쿼리마다 가장 최근에 커밋된 데이터를 읽기 때문에 성능과 일관성이 절충된다.

   • Non-Repeatable Read란?

   트랜잭션 내에서 같은 데이터를 여러 번 읽을 때, 그 사이에 다른 트랜잭션이 해당 데이터를 수정하거나 삭제하여 이전에 읽은 데이터와 다른 결과를 얻게 되는 현상을 말해요. 운이 좋아 해당 데이터가 커밋 전이라면 문제가 없으나 커밋이 되었다면 골치가 아프겠죠.

   예를 들자면, 이런 상황이 발생할 수 있어요.

   1. 고객 A의 트랜잭션:
      1. 고객 A가 상품의 재고를 확인합니다. 재고는 1개입니다.
      2. 고객 A가 상품을 장바구니에 추가하고 결제 과정을 시작합니다.
      3. 결제 직전, 고객 A가 재고를 다시 확인합니다.
   2. 고객 B의 트랜잭션 (고객 A의 1.b번과 1.c번 사이에 발생):
      1. 고객 B가 상품을 구매하고 결제를 완료합니다.
      2. 재고가 0으로 업데이트됩니다.
   3. 고객 A의 트랜잭션 계속:
      1. (계속) 이때 재고가 0개로 변경되어 있습니다.

4. Read Uncommitted (가장 낮은 Level)

   한 트랜잭션 내에서 커밋 완료되지 않은 데이터도 읽을 수 있다.

   1) 고객 B가 고객 A의 결제가 끝나기 전에 변경된 재고를 확인할 수 있다.
   2) 즉, 고객 A가 아직 결제를 완료하지 않았는데도 고객 B는 변경된 재고 상태를 볼 수 있다.
   3) 고객 A의 트랜잭션이 롤백되더라도 고객 B는 잘못된 재고 정보를 바탕으로 결정을 내릴 수 있다.
   4) 이로 인해 Dirty Read 문제가 발생할 수 있고, 일관성이 깨진다. 하지만 성능은 좋다.

   • Dirty Read란?

   트랜잭션이 커밋되기 전 커밋되지 않은 변경사항을 읽는 것을 말합니다.

   예를 들자면, 이런 상황이 발생할 수 있어요.

   1. 고객 A의 트랜잭션:
      1. 고객 A가 상품의 재고를 확인합니다. 재고는 5개입니다.
      2. 고객 A가 3개의 상품을 구매하기로 결정하고 결제 과정을 시작합니다.
      3. 시스템이 재고를 2개로 업데이트합니다 (5 - 3 = 2).
      4. 결제 처리 중 문제가 발생하여 잠시 대기 상태입니다 (아직 커밋되지 않음).
   2. 고객 B의 트랜잭션 (고객 A의 트랜잭션이 완료되기 전에 발생):
      1. 고객 B가 동일 상품의 재고를 확인합니다.
      2. 시스템은 현재 재고가 2개라고 보여줍니다 (더티 리드 발생).
      3. 고객 B는 재고가 2개밖에 없다고 생각하고 빠르게 2개를 모두 구매하기로 결정합니다.
         1. 실제로는, 존재하지 않았던 (커밋되지 않은) 재고 상태를 보고 구매 결정을 내리게 된거죠.
         2. 이렇게 되면 잘못된 정보는 불필요한 구매 압박을 경험하게끔 해요.
   3. 고객 A의 트랜잭션 (계속):
      1. 결제 처리 중 문제로 인해 고객 A의 트랜잭션이 롤백됩니다.
      2. 재고는 다시 5개로 되돌아갑니다.

데이터베이스 보안 - 접근제어

접근제어는 특정 사용자가 데이터베이스의 어느 부분에 접근할 수 있는지를 정의하는 보안 메커니즘이다.

• 인증(Authentication)

CREATE USER 'messi'@'localhost' IDENTIFIED BY 'goat';

이 쿼리는 localhost에서 접근할 수 있는 messi라는 이름의 새로운 사용자를 등록하고 'goat'라는 비밀번호로 이 사용자를 인증하겠다는 쿼리이다. MySQL 접속 시 ID: messi, PW: goat를 입력하여 인증할 수 있다.

• 인가(Authorization)

GRANT SELECT, INSERT, DELETE ON soccer.* TO 'messi'@'localhost';

이 쿼리는 messi라는 유저에게 soccer라는 데이터베이스에서는 어떤 테이블이건 SELECT, INSERT, DELETE할 권한을 허락한다는 의미이다. UPDATE 쿼리는 수행할 수 없고, soccer를 제외한 다른 데이터베이스에선 아무런 권한이 없다.

사용자마다 항상 최소 권한의 법칙을 적용하여 해당 사용자가 컨트롤할 수 있는 영역에 대해서만 명확하게 권한을 주어야 한다.

데이터베이스 보안 - SQL Injection

프로그램에서 쿼리 인자를 입력할 땐 반드시 Prepared statement를 사용해야 한다.

웹에서 사용자 이름과 비밀번호를 입력받아 로그인을 처리하는 쿼리

"SELECT * FROM Users WHERE Username='" + username + "' AND Password='" + password + "';"

여기서 악의적인 공격자가 admin'; -- 와 같은 값을 username에 넘기면 쿼리는 이렇게 된다.

"SELECT * FROM Users WHERE Username='admin'; --' AND Password='...';"

--는 SQL에서 주석을 나타내므로, ' AND Password='...' 부분은 실행되지 않게 된다.
즉 이 쿼리는 비밀번호 검사를 생략하게 되고, 공격자가 관리자 계정에 액세스할 수 있게 된다.
이러한 공격 방법을 SQL Injection이라고 한다.

Prepared statement라는 개념으로 이러한 취약점에 대비할 수 있다.

const mysql = require('mysql');
const connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'user',
  password : 'password',
  database : 'database'
});

connection.connect();

const username = "admin'; --";
const password = "password";

// 아래의 쿼리가 Prepared statement 입니다. 원래 인자값의 위치가 ?로 대체되어 있죠.
const sql = "SELECT * FROM Users WHERE Username = ? AND Password = ?";
connection.query(sql, [username, password], function(error, results, fields) {
  if (error) {
    throw error;
  }
  console.log(results);
});

connection.end();

connection.query 함수에 인자(username, password)를 따로 입력하게 되는 형태이다. 이렇게 되면 사용자 입력이 SQL 코드로 해석되는 것을 방지할 수 있어 공격을 방어할 수 있다.