〰️ 흐름제어
-
CASE
- CASE value
WHEN[compare_value]THENresult
WHEN[compare_value]THENresult ...
ELSEresult
END - CASE와 END로 이루어져 있음. WHEN 조건에 존재하면 result, 존재하지 않으면 ELSE, ELSE 문이 없을 경우 Null을 return함.
-- 흐름 제어 : case 문 SELECT 컬럼1, 컬럼2, 컬럼3 CASE 컬럼4 WHEN [비교값1] THEN 결과값1 WHEN [비교값2] THEN 결과값2 ELSE 결과값3 END FROM 테이블명; -- 실습 -- post 테이블에서 1번 user는 first author, 2번 user는 second author SELECT id, title, contents, CASE author_id WHEN 1 THEN 'first author' WHEN 2 THEN 'second author' ELSE 'others' END as author_id FROM post; -- author_id 가 있으면 그대로 출력 -> else author_id, 없으면 '익명 사용자'로 출력되도록 post 테이블 조회 SELECT id, title, contents, CASE WHEN author_id IS NULL THEN '익명 사용자' ELSE 'author_id' END as author_id FROM post; - CASE value
-
IF()
IF(a, b, c)- 만약 a가 참이면 b를 반환하고, 거짓이면 c를 반환합니다.
- SELECT IF(0 < 1, 'yes', ‘no’);
-
IFNULL()
-
IFNULL(a, b) -
만약 a의 값이 NULL이 아니면 a 그 자체를 반환하고, NULL이면 b를 반환
-- 위 case 문을 IFNULL 구문으로 변환 SELECT id, title, contents, IFNULL(author_id, '익명 사용자') FROM post; -- IF문으로 변환 SELECT id, title, contents, IF(author_id is null, '익명 사용자', 'others') as author_id FROM post; -
저장 프로시저(stored procedure)
- 데이터베이스에 저장되어 실행할 수 있는 하나 이상의 SQL 문의 집합.
- 특성 : SQL 문을 미리 컴파일하여 저장 => 여러번 재사용 가능하며, 서버의 부하를 줄이고 성능을 향상시킴.
-- stored 프로시저를 활용한 트랜잭션 테스트 DELIMITER // CREATE PROCEDURE InsertPostAndUpdateAuthor() BEGIN DECLARE exit handler for SQLEXCEPTION BEGIN ROLLBACK; END; -- 트랜잭션 시작 START TRANSACTION; -- UPDATE 구문 UPDATE author SET post_count = post_count + 1 where id = 1; -- INSERT 구문 insert into post(title, author_id) values('hello world java', 5); -- 모든 작업이 성공했을 때 커밋 COMMIT; END // DELIMITER ; -- 프로시저 호출 CALL InsertPostAndUpdateAuthor(); -
✉️ 트랜잭션 (TRANSACTION)
-
정의
INSERT INTOpost()…
UPDATEauthor set…- 하나의 논리적인 작업 단위로 처리되어야 하는 하나 이상의 SQL 문의 집합
⇒ 두 쿼리가 반드시 같이 성공하거나 같이 실패해야 하는 경우에, 하나의 논리적인 단위로 묶을 수가 있는데 그것이 트랜잭션 !
- 예시1) 은행 계좌 간에 이체를 할 때, 금액을 A 계좌에서 빼고 B 계좌에 더하는 두 가지 연산은 한 작업의 단위로 처리되어야 하는 트랜잭션.
- 예시2) 주문을 하고 order 테이블에 주문을 생성하고, item 테이블에서 재고까지 빼주는 작업이 한 단위로 처리되어야 하는 트랜잭션.
-
commit : 확정 짓는 행위
COMMIT명령은 한 트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 저장.
-
rollback : 둘 중 하나라도 실패한다면 → 모두 실패 처리
ROLLBACK명령은 트랜잭션의 변경사항을 모두 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리는 것.
-- author 테이블에 post_count라고 컬럼(int) 추가 alter table author modify column post_count int default 0; -- post에 글 쓴 후에, author 테이블에 post_count 값에 +1 => 트랙잭션 start transaction; update author set post_count = post_count+1 where id = 1; insert into post(title, author_id) values('hello world java', 5); commit; -- 또는 rollback; -
ACID 원칙
- 데이터베이스 관리 시스템(DMS)에서 트랜잭션이 신뢰성과 일관성을 유지하기 위해 지켜야하는 네 가지 주요 속성.
- Atomicity (원자성) / Consistency (일관성) / Isolation (고립성) / Durability (지속성)
💻 DB 동시성 / 격리 수준
1) dirty_read ⇒ 격리 수준 read uncommitted → read committed
2) phantom_read ⇒ 격리 수준 read committed → repeatable read
3) ⭐️⭐️insert / update가 됐음에도 트랜잭션에 반영되지 않는 문제
→ a) SELECT 는 허용 = 공유 lock(select for share)
→ b) SELECT 도 허용X = 배타 lock(select for update)
-
DB 동시성 : 트랜잭션이 동시에 실행 됐을 때 발생할 수 있는 문제와 관련한 상황을 DB 동시성 문제라고 한다.
-
DB 격리 수준 : DB 동시성 문제를 해결하기 위한 격리수준
⇒ DB 동시성 이슈 : SELECT(검증) - INSERT - UPDATE 가 동시에 일어남
→ 이를 방지하기 위해 DB 격리 처리.
격리 수준을 높이면 정확도는 높일 수 있으나 성능이 저하됨. -
DB 동시성 관련 실무 해결책
-
위와 같은 동시성 이슈는 일반적이지는 않은 상황이지만, 쇼핑몰이벤트 또는 예매 시스템에서는 빈번하게 발생할수 있는 가능성 존재
-
Spring에서의 전략
- 낙관적 lock
- 버전정보 활용하여 update시에 정합성 체크
- 비관적 lock
- 공유락
- lock - PESSIMISTIC_READ
- 다른 트랜잭션이 동일한 데이터를 읽을 수 있지만, 데이터를 수정할 수는 없도록 하는 잠금.
- 배타락
- lock - PESSIMISTIC_WRITE
- 특정행에 대해 lock을 걸어 read조차 막음으로서 update시에 발생하는 이슈 원천 차단.
- 공유락
- 낙관적 lock
-
queue사용
- 이벤트, 예매 상황에서 고려될수 있는 아키텍처
- 요청 자체를 queue에 넣어서 순차적으로 처리
→ spring으로 넘어가기 전에 queue에서 순차 정리
-
Redis 사용
- 싱글스레드 기반 key-value 시스템
- 재고관리를 redis에서 하고 추후 rdb에 update하는 방식 활용가능. 더 빠르다!
-
📌 today
흐름제어(CASE, IFNULL ...) 개념을 제대로! 배우게 되면서,
도전해볼 수 있는 SQL 문제들이 늘어났다. 수업 진행과 동시에
내가 풀지 못한 SQL 문제를 풀 수 있는 방법들이 떠올랐다 !
문제를 통해 복습해보며 확실히 익혀두어야겠다.
특히나 오늘 수업에서 가장 눈이 반짝였던 파트는 DB 동시성 / 격리 수준 파트였다.
선착순 쿠폰 다운로드 이벤트나, 콘서트 예매 티켓팅과 같은 서비스에 사용되는 시스템이 이런 방식이었다니! 수업을 들으면서 배달의 민족 선착순 이벤트 때 개발자가 3일 밤을 새고 집에 못 갔다더라~ 하는 썰도 생각나고 그랬다 ㅎㅎ
저도 집에 안 가는 멋진 개발자 할래요~~!
그러기 위해 SQL 문제 풀이를 통해 확실하게 복습하겠다 !
