트랜잭션

• 정의
  • 트랜잭션은 DB의 상태를 변경시키기 위해 수행하는 작업 단위
  • 하나의 논리적인 작업 단위로 처리되어야 하는 하나 이상의 SQL 문의 집합
    • 예시1) 은행 계좌 간에 이체를 할 때, 금액을 한 계좌에서 빼고 다른 계좌에 더하는 두 가지 연산은 한 작업의 단위로 처리되어야 하는 트랜잭션
    • 예시2) 주문을 하고 order테이블에 주문을 생성하고, item테이블에서 재고까지 빼주는 작업이 한 단위로 처리되어야 하는 트랜잭션
• commit
  • COMMIT 명령은 한 트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 저장
• rollback
  • ROLLBACK 명령은 트랜잭션의 변경사항을 모두 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리는 것
• START TRANSACTION
  • 트랜잭션의 경계를 명확히 하고, 트랜잭션이 시작되는 시점을 명시
  • COMMIT 명령이 실행될 때까지 작업 반영X
• ACID 원칙
  • 데이터베이스 관리 시스템(DBMS)에서 트랜잭션이 신뢰성과 일관성을 유지하기 위해 지켜야 하는 네 가지 주요 속성
  • Atomicity (원자성)
    • 트랜잭션 내의 모든 작업이 완전히 수행되거나, 전혀 수행되지 않아야 함을 의미
  • Consistency (일관성)
    • 트랜잭션이 수행되기 전과 후의 데이터베이스 상태가 모든 정해진 규칙(예를들어 제약 조건, 데이터 무결성 등)을 만족해야 함을 의미
    • 데이터 무결성이란 데이터의 정확성과 신뢰할수 있는 상태를 의미
  • Isolation (고립성)
    • 각 트랜잭션은 다른 트랜잭션으로부터 독립적으로 수행되어야 함을 의미
    • 즉, 적절한 격리수준으로 동시성 문제가 발생하지 않아야함
  • Durability (지속성)
    • 트랜잭션이 완료되면, 그 결과는 영구적으로 저장되어야 함을 의미

DB 격리수준

• DB 동시성 문제를 해결하기 위한 격리수준
  • DB 동시성 문제는 데이터베이스에 여러 트랜잭션이 동시에 접근할 때, 데이터의 일관성과 정확성이 깨질 수 있는 상황
• Read Uncommitted
  • 가장 낮은 수준의 격리수준
  • 즉, 데이터가 변경되었다면, 커밋되지 않았다 하더라도 읽을 수 있도록 하는 격리수준
  • dirty read 발생 가능
• Read Committed
  • 다른 트랜잭션이 커밋한 데이터만 읽을 수 있는 격리수준.
  • 다만, 나의 트랜잭션에 여러 select 문이 있을 경우에, 그 사이에 다른 트랜잭션에서 update 또는 insert 등을 발생시키고 commit하게 될시 phantom read 또는 non-repeatable-read 발생가능
  • 즉, 트랜잭션내에 같은 읽기를 보장하지 않는 격리수준
• Repeatable Read
  • 한 번 읽은 데이터는 같은 트랜잭션 내에서는 항상 같은 값을 갖도록 하는 격리수준 나의 트랜잭션에서 먼저 read하는 동안 다른 트랜잭션에서는 변경,추가 하더라도 같은 read값을 보장하는 것. -> Non-Repeatable Read과 Phantom read를 해결
  • repeatable read를 하더라도 문제가 발생할 가능성 존재
    • 나의 트랜잭션이 read하는 동안 타 트랜잭션에서 update하게 되면 read해온 값이 최종적으로 틀린 값이 되는 문제 발생(lost update)
      • 예시) 재고가 10개가 남은 상황에서 나의 트랜잭션과 타 트랜잭션이 동시에 read하여 10개로 각각 조회를 하고, 타 트랜잭션이 먼저 -1개를 하여 9개로 update를 실행하고, 나의 트랜잭션도 -1개를 하여 9개로 update하는 상황
    • 대안은 locking
      • 배타락(exclusive lock - select for update)
        • 다른 트랜잭션에서 읽기와 쓰기가 모두 차단. 읽을때부터 lock 실행
        • read에서부터 lock을 걸어 lost update 문제 해결
        • 공유락(shared lock)과 비교
          • 공유 락이 걸린 데이터에 대해서는 다른트랜잭션에서는 읽기 연산(SELECT)만 실행 가능하며, 쓰기 연산은 실행이 불가능
          • 두 다른 트랜잭션이 동시에 read하는 것은 가능하여 lost update 문제 가능성 여전히 존재
• Serializable
  • 동시에 실행되는 여러 트랜잭션들을 순차적으로 실행한 것과 같은 결과를 보장

DB 동시성 관련 실무 해결책(프로그램차원)

• 위와 같은 동시성 이슈는 일반적이지는 않은 상황이지만, 쇼핑몰이벤트 또는 예매 시스템에서는 빈번하게 발생할수 있는 가능성 존재
• Spring에서의 전략
  • 낙관적 lock
    • 버전정보 활용하여 update시에 정합성 체크
    • update item set stock_count = new_count where id=1 and version = 1; 만일, version이 맞지 않다면 0rows affected
  • 비관적 lock
    • 공유락
      • lock - PESSIMISTIC_READ
      • 다른 트랜잭션이 동일한 데이터를 읽을 수 있지만, 데이터를 수정할 수 없도록 하는 잠금
    • 배타락
      • lock - PESSIMISTIC_WRITE
      • select for update와 같은 방식사용
      • 특정행에 대해 lock을 걸어 read조차 막음으로서 update시에 발생하는 이슈 원천 차단.
  • 프로그램의 함수 차원에서 syncronized와 같은 lock기능을 사용하여 한 번에 하나의 스레드만 해당 메서드에 접근할 수 있도록 허용
    • 다만, 이 방식은 db차원의 lock 아니므로 DB에는 동시에 update작업을 시도할 여지가 있어, 여전히 동시성 문제 발생할 수 있는 여지가 있음.
• Redis 사용
  • 싱글스레드 기반 key-value 시스템(인메모리 데이터베이스)
  • 재고관리를 redis에서 하고 추후 rdb에 update하는 방식 활용가능

JOIN

• 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현

• 크게는 INNER JOIN, OUTER JOIN으로 구분

  • INNER JOIN
    • 두 테이블 사이에 지정된 조건에 맞는 레코드만을 반환. 즉, 양쪽 테이블에 모두 해당 조건에 맞는 값이 있어야 결과에 포함
    • ex)author와 post를 inner조인하면 글을 작성한적이 있는 author와 해당 author가 작성한 post정보를 결합하여 조회
    • 가장 일반적인 형태
  • OUTER JOIN
    • 하나의 테이블을 기준으로 해당 테이블의 모든 레코드와 그에 JOIN된 다른 테이블의 일치하는 레코드를 반환
    • 왼쪽테이블이 기준이면 LEFT (OUTER) JOIN, 오른쪽 기준이면 RIGHT (OUTER) JOIN
    • LEFT JOIN이 일반적으로 많이 사용되는 JOIN
    • ex)author의 테이블의 행을 일단 모두 조회하고 author가 작성한 글정보를 조회하고 싶다면, author테이블에 post테이블을 left join

• INNER JOIN

  

  • tableA의 ID와 tableB의 a_id가 일치하는 ON 조건을 만족하는 데이터만 JOIN
  • SELECT * FROM tableA INNER JOIN tableB ON tableA.ID = tableB.A_ID
  • SELECT * FROM tableA AS a INNER JOIN tableB AS b on a.ID = b.a_id;
  • 출력결과
    • TableA의 모든컬럼 + TableB의 모든컬럼
    • 그 중에 ON조건을 만족하는 row만 출력

• LEFT OUTER JOIN

  

  • 문법
    • SELECT * FROM tableA a LEFT JOIN tableB b ON a.id = b.a_id
  • 출력결과
    • TableA의 모든컬럼 + TableB의 모든컬럼
    • A테이블 데이터는 row는 모두 출력 B데이터는 ON 조건에 맞는 데이터만 출력
    • ON조건에 맞지 않는 B데이터는 null로 출력
      • 이때 order by시에 B데이터를 기준으로 order by를 하게 되면 null이 최상단에 위치됨에 주의

• JOIN 특이사항

  • JOIN된 데이터에 WHERE조건
    • where조건을 걸게 되면, ON 조건을 만족하는 데이터중에서도 WHERE문을 만족하는 데이터만 출력
    • SELECT * FROM tableA AS a INNER JOIN tableB AS b on a.ID = b.a_id where a.name like ‘kim%’;

UNION

• 여러 개의 SELECT 문의 결과를 하나의 테이블이나 결과 집합으로 표현
• 각각의 SELECT 문으로 선택된 필드의 개수와 타입은 모두 일치해야함
• UNION은 DISTINCT 키워드를 따로 명시하지 않아도 중복되는 레코드를 제거
  • 중복되는 레코드까지 모두 출력하고 싶다면 UNION ALL
• SELECT 컬럼1, 컬럼2 FROM TABLE1 UNION SELECT 컬럼1, 컬럼2 FROM TABLE2;

서브쿼리

• 기본
  • 서브쿼리(subquery)란 다른 쿼리 내부에 포함되어 있는 SELECT 문을 의미
  • 서브쿼리는 반드시 괄호(())로 감싸져 있어야 함
  • 서브쿼리의 위치
• JOIN과 서브쿼리 비교
• 대부분의 서브쿼리는 join으로 대체가능하고 join을 쓰는것이 성능이 더 좋음

GROUP BY

INDEX

데이터모델링

테이블 간의 참조 관계의 종류

PK 설계시 고려사항

정규화

(실습)주문(order) ERD 설계 및 DB 구축

DB Dump

사용자관리

view

저장 프로시저(stored procedure)

mariadb(mysql) DB엔진의 종류

DB 서버 구성