트랜잭션의 이해와 JOIN

흐름제어

-- 흐름제어 : 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
from post;

-- author_id가 있으면 그대로 출력, 없으면 익명 출력
select id,title, contents,
case
    when author_id is null then '익명'
    else author_id 
end as author_id 
from post;

-- 위 케이스 문을 ifnull 구문으로 변환
select id,title,contents, ifnull(author_id, '익명') from post;

-- 위 케이스 문을 if 구문으로 변환
select id, title, contents, if(author_id is null,'익명',author_id) from post;

---

database 심화

Transaction

트랜잭션

• 하나의 작업 단위로 처리되어야 하는 SQL문의 집합
• 두 쿼리가 반드시 같이 성공하거나 같이 실패해야 하는 경우에 사용
• 주문 시스템의 경우, 주문 테이블에 주문을 생성하고(insert), 상품 테이블에서 재고를 빼주는 작업(update)이 한 단위로 처리되어야 한다.
• 은행 시스템의 경우, A계좌에서 B계좌로 금액을 송금할 때, A계좌와 B계좌의 금액 update 작업이 한 단위로 처리되어야 한다.
• 트랜잭션 작업이 성공하여 확정 짓는 행위를 commit; 실패하여 모두 취소하는 행위를 rollback;
• commit
  • COMMIT 명령은 한 트랜잭션의 모든 변경사항을 데이터베이스에 영구적으로 저장
• rollback
  • ROLLBACK 명령은 트랜잭션의 변경사항을 모두 취소하고, 데이터베이스를 트랜잭션 시작 이전의 상태로 되돌리는 것
• 각 트랜잭션이 독립되어 있어야 한다. → DB 동시성 이슈 !! DB 격리 수준이 높음 → 하나의 트랜잭션이 작업 중이면 다른 트랜잭션은 DB에 접근할 수 없음 → 정합성⬆️ 성능 ⬇️

-- author 테이블에 post_count 컬럼 추가
ALTER table author add column post_count int default 0;

-- post에 글을 쓴 후에, author 테이블에 post_count 값에 +1 -> 트랜잭션
start transaction ;
update author set post_count = post_count+1 where id = 3;
insert into post(title,author_id) values('hello world java', 13);
commit;
-- 또는
rollback;

-- 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();

DB 동시성 이슈

트랜잭션이 동시에 실행됐을 때 발생할 수 있는 문제

- dirty read

read uncommitted 격리수준을 → read committed 격리수준으로 올려야 함

- phantom read

한 트랜잭션이 같은 조회쿼리를 여러 번 실행했을 때, 그 중간에 다른 트랜잭션에서 새로운 데이터를 추가/삭제하여 다르게 나타나는 문제

read committed 격리수준을 →repeatable read 격리수준으로 올려야 함

- Non-Repeatable Read

한 트랜잭션에서 동일한 조회 쿼리를 두 번 이상 실행할때에, 그 중간에 다른 트랜잭션에서 데이터를 수정하여 한 트랜잭션의 결과가 다르게 나타나는 문제

Repeatable Read 격리수준이면 해결

DB 격리수준

• read uncommitted

커밋되지 않은 데이터 변경도 읽을 수 있는 격리 수준

• read committed

커밋된 데이터 변경만 읽을 수 있는 격리 수준

• repeatable read

한번 읽은 데이터는 같은 트랜잭션 내에서 같은 값을 유지하도록 하는 격리 수준

→나의 트랜잭션이 read하는 동안 타 트랜잭션에서 update하게 되면 read해온 값이 달라지는 문제 발생

= 실제 DB 값과 달라진다

→ 이를 locking으로 해결한다.

LOCKING

• 공유락 (shared lock)

다른 트랜잭션에서 select 즉 read 하는 것은 허용한다.

BUT lost update 문제 가능성 존재

ex)상품주문의 최종 수량이 1개 -> transaction에 read && update가 있을때 -> 내 tran에서 1 read -> 타 트랜잭션이 1 read -> 내 tran에서 0으로 update -> 타 tran에서 0으로 update -> 최종 수량에 오류 발생

• 배타락(exclusive lock - select for update)

값을 read하는 것에서부터 lock을 걸어 lost update 문제 해결

엄격하게 제어함으로 동시성 이슈를 없애지만 성능은 좋지 못함

실무에서 동시성 해결하는 방법

• queue사용

콘서트 예매, redis 보다 느림 But 사용자에게 몇번째 사용자인지 안내 가능

• Redis 사용

재고관리를 redis에서 하고 추후 rdb에 update하는 방식 활용

-- dirty read 동시성 이슈 실습
-- 한 트랜잭션이 다른 트랜잭션이 수정 중인 데이터를 읽을 수있는 문제
-- 워크벤치에서 auto_commit 해재 후 update 실행 -> commit이 안된 상태 (워크벤치에는 임시저장 상태)
-- 터미널을 열어서 select 했을 때 위 변경사항이 변경됐는지 확인

--phantom read 동시성 이슈 실습
-- 워크벤치에서 시간을 두고 2번의 select가 이루어지고, 터미널에서 중간에 insert 실행-> 2번의 select 결과값이 동일한지 확인
start transaction;
select * from author;
do sleep(15);
select * from author;
commit;
-- 터미널
insert into author(name,email) values ('kim', 'jip@want');

-- lost update 이슈를 해결하기 위한 공유락 (shared lock)
start transaction;
select post_count from author where id=1 lock in share mode;
do sleep(15);
select post_count from author where id=1 lock in share mode;
commit;
-- 터미널
select post_count from author where id=1 lock in share mode;
update author set post_count=0 where id=1;

-- 배타적 잠금 (exclusive lock) : select for update
-- select 부터 잠금
start transaction;
select post_count from author where id=1 for update;
do sleep(15);
select post_count from author where id=1 for update;
commit;
-- 터미널
select post_count from author where id=1 for update;
update author set post_count=0 where id=1;

JOIN

inner join

두 테이블 사이에 지정된 조건에 맞는 레코드만을 반환. 양쪽 테이블에 모두 해당 조건에 맞는 값이 있어야 결과에 포함

A테이블에 B테이블을 inner join 한 것과 B테이블에 A테이블을 inner join한 결과는 같다

1. select * from author join post on author.id = post.author_id 글쓴이 중에 글쓴 사람을 골라서 그 사람이 쓴 글을 합 한 테이블
2. select * from post join author on author.id = post.author_id 글 중에 글쓴이가 있는 글을 고르고, 그 글을 쓴 사람을 합한 테이블

outer join

A테이블에 B테이블을 outer join 한 것과 B테이블에 A테이블을 outer join한 결과는 다르다 !

→ A테이블에 B테이블을 outer join : A테이블은 다 나오고 B테이블은 A테이블과 일치하는 레코드를 반환(left기준)

→ B테이블에 A테이블을 outer join : B테이블은 다 나오고 A테이블은 B테이블과 일치하는 레코드를 반환(left기준)

-- inner join
-- 두 테이블 사이에 지정된 조건에 맞는 레코드만 반환. on 조건을 통해 교집합 찾기
select * from author inner join post on author.id=post.author_id;
select * from author a inner join post p on a.id=p.author_id;

-- 글쓴이가 있는 글 목록(id,title,contents)과 글쓴이의 이메일을 출력
-- 익명 글은 다 날아감 -> inner join이 교집합을 찾는 것이기 때문
select p.id, p.title, p.contents, a.email from post p inner join author a on p.author_id=a.id;

-- 모든 글목록을 출력하고, 만약에 글쓴이가 있다면 이메일을 출력
select * from post left join author on post.author_id=author.id;
select p.id, p.title , p.title, a.email  from post p left join author a on p.author_id=a.id;

-- join된 상황에서 where 조건 : on 뒤에 where 조건이 나옴
-- 1) 글쓴이가 있는 글 중에 글의 title과 저자의 email을 출력, 이때 저자의 나이는 25세 이상
select p.title, a.email from post p inner join author a on p.author_id=a.id WHERE a.age >=25;
-- 2) 모든 글 목록 중에 글의 title과 저자가 있다면 email을 출력, 이때 2024-05-01 이후에 만들어진 글만 출력
select p.title, a.email FROM post p left join author a on p.author_id  = a.id WHERE p.created_time > '2024-05-01';