Transaction

 

 

Transaction

• 한 번에 이루어져야 하는 작업의 논리적인 단위
• All or Nothing. 다 되던지 하나도 되면 안 된다.

Table이 있으면 원본 Table에 작업을 하는게 아님. 우리 컴퓨터에 Table이 복사가 되고 그 테이블에 작업이 이루어짐 그리고 내 컴퓨터에서 COMMIT 하면 원본 데이터에 반영함. 로그아웃을 할 때 커밋을 해줌. 
그런데 복사하는 동안 다른 작업을 못하니까, 하루 몇백만건을 처리하는 은행은 중간에 튕기면 모든 거래가 다 날라가버리는 방법 대신, Save Point를 사용한다. 시간 단위로, 혹은 거래건수마다 S1, S2를 만들어서 Save Point로 Roll Back 한다.

 

TCM

COMMIT: 끝났따.

ROLL BACK: 트랜잭션의 시작점으로 돌아가라.

SAVE POINT: 저장했다가 여기로 롤백해. 

 

 

Transaction  실습

데이터베이스 확인

SHOW databases;

 

데이터베이스 생성

 CREATE database 이름;

 

데이터베이스 사용 설정

USE 이름;

 

데이터베이스 삭제

DROP database 이름;

 

 

실습할 테이블 생성

create table 테이블 이름(

    컬럼이름 자료형 컬럼제약조건
    ...
    테이블 제약조건
    ...
)ENGINE, AUTO_INCREMENT의 시작 숫자, 인코딩 등을 설정

create table transaction_test(
	account int,
	balance int);

 

데이터 삽입

insert into transaction_test(account, balance) values(1, 30);

 

데이터 확인

select *
from transaction_test;

 

트랜잭션 취소 - 트랜잭션이 만들어지는 시점으로 이동

ROLLBACK;

- 처음 로그인 했을 때 복사한 테이블로 돌아가는 것

- Commit 하는 동안은 다른 테이블에서는 Lock이 걸린다.

 

savepoint 생성

- 로그를 기록해서 돌아갈 때 다시 불러옴 

--데이터 추가
insert into transaction_test(account, balance) values(2, 40);
insert into transaction_test(account, balance) values(3, 40);

--save point 생성
savepoint s1; 

--데이터 추가 2
insert into transaction_test(account, balance) values(4, 40);
insert into transaction_test(account, balance) values(5, 40);

--rollback 명령어
rollback; -- 데이터 1개
rollback to s1; -- 데이터는 3개

 

 

 

Windows Function

1) 관계형 데이터베이스에서 group by를 사용할 때 주의할 점

> tStaff 테이블을 depart별로 depart와 salary의 평균을 조회 

select depart, salary
from tStaff
group by depart;

Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'sys.tStaff.salary' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

• group by는 그룹화 한 항목 별로 하나만 출력할 수 있는데, salary는 2개 이상이 될 수 있어서 어떤 것을 출력할지 몰라 error.
• mysql의 fork인 Maria DB에서는 이 문법을 허용함.

select depart, avg(salary)
from tStaff
group by depart;

 

2) 사용 형식

SELECT <윈도우함수이름>(매개변수) OVER(
    [PARTITION BY 그룹화 항목 나열]
    ORDER BY 정렬할 항목
    출력할 컬럼이름 나열


FROM 테이블이름
[WHERE 조건];

• partition은 선택인데 그룹을 만드는 것
• partition이 없으면 전체 데이터를 가지고 수행

 

 

3) 순위 관련 함수

ROW_NUMBER: 중첩되지 않는 일련번호

DENSE_RANK: 중복된 순위에 동일한 순위를 할당하고 다음 순위를 건너뛰지 않고 리턴

RANK: 중복된 순위에 동일한 순위를 할당하고 다음 순위를 건너뛰고 리턴

NTILE(등분의 개수): 등분

 

 

4) 분석 함수

CUME_DIST(): 누적합
LEAD(컬럼, 인덱스): 다음 행
LAG(컬럼, 인덱스): 이전 행
FIRST_VALUE(컬럼): 첫 행
LAST_VALUE(컬럼): 마지막 행
PERCENT_RANK(): 백분율 순위

 

 

 

5) 정규화 / 반정규화

종속: 다른 데이터에 영향을 받는 것

1. 함수적 종속

• 하나의 속성이 다른하나의 속성의 값을 결정하는 것

주민등록번호 -> 이름
주민등록번호가 이름을 함수적으로 종속했다.
반대로 하나의 이름에는 주민등록번호가 여러개 있을 수 있기 때문에 종속이 아님. 
주민등록번호를 결정자라고 한다.

 

부분 함수적 종속

• 기본 키에 대해서만 적용이 된다.
• 2개 이상의 속성으로 만들어진 기본키에서 1개의 속성으로 구별할 수 있는 경우

 

이행적 함수 종속

• A → B
• B → C
• A → C

 

이 두 종속을 없애는 것을 정규화라고 한다.

 

 

2. 정규화

• 읽기 이외 (특히 삭제) 할 때 유용하다.

주민등록번호	이름	수업 코드	수업료
1	김	C	30만원
2	이	C	30만원
1	김	D	15만원

• 이런 경우 식별하기 좋은 키가 없다.
• '이'가 수업 'C'를 철회할 경우 '이'에 대한 데이터가 모두 손실될 수 있다.
• 따라서 테이블을 두개로 나눈다.

주민등록번호	이름
1	김
2	이
3	박

수강코드	수업료
C	30만원
D	15만원
F	15만원

 

 

 

3. 반정규화/역정규화

• join을 하면 데이터가 많아져서 속도가 느려짐
• 테이블 하나에 정보가 다 있으면 join을 할 필요가 없다.
• 읽기 속도에 더 유리하다.