트랜잭션(Transaction)

Jaeuk Oh·2021년 8월 16일
Database

DataBase

목록 보기
4/4
post-thumbnail
  1. 트랜잭션 소개

  • 트랜잭션이란?

    • 데이터베이스에서 하나의 논리적인 작업 단위를 구성하는 연산들의 집합을 트랜잭션(Transaction)이라고 함

    • Atomic하게 실행되어야 하는 SQL들을 묶어서 하나의 작업처럼 처리하는 방법

    • 은행의 계좌이체를 생각하면 쉽다


  • 특성

    • Atomicity

      • 트랜잭션이 시작되면 트랜잭션에서 정의된 연산들을 모두 성공적으로 실행하든지, 아니면 실행 되기 이전의 상태로 돌아가야한다(all or nothing)

    • Consistency

      • 트랜잭션이 종료된 이후에도 DB의 내용이 일관된 상태로 유지되어야 한다

    • Isolation

      • 트랜잭션이 실행하는 과정에서 갱신한 데이터는 트랜잭션이 완료될 때까지 다른 트랜잭션이 참조할 수 없다

    • Durability

      • 트랜잭션이 성공적으로 완료되면 그 트랜잭션이 갱신한 DB의 내용은 영구적으로 저장되어야 한다


  • 트랜잭션 커밋모드: autocommit(1)

    • autocommit = True

      • 모든 레코드 수정/삭제/추가 작업이 기본적으로 바로 DB 쓰여짐, 이를 commit한다고 함
      • 만일 특정 작업을 트랜잭션으로 묶고 싶다면 BEGIN, END(COMMIT), ROLLBACK으로 처리(BEGIN 이전 상태로 돌아감)

    • autocommit = False

      • 모든 레코드 수정/삭제/추가 작업이 commit이 호출될 때 까지 commit되지 않음

      • 즉 사용자가 명시적으로 commit해야함

      • ROLLBACK이 호출되면 이전 작업들이 무시됨


  • 트랜잭션 커밋모드: autocommit(2)

    • 이는 SQL 클라이언트/라이브러리에 따라 달라짐
    • MYSQL 워크벤치의 기본은 autocommit = True
      • 확인 방법: SHOW VARIABLES LIKE 'AUTOCOMMIT';
      • SET autocommit = 0 (1) 의 실행방법으로 변경가능



  1. 트랜잭션 실습(SQL과 Java)

  • autocommit 실습

    • use test;
drop table if exists jaeuk_name_gender;
create table jaeuk_name_gender(
name varchar(32),
gender enum('Male', 'Female') default NULL
);

insert into jaeuk_name_gender values('Jaeuk', 'Male');
insert into jaeuk_name_gender values('Jane', 'Female');
insert into jaeuk_name_gender values('Unknown');
insert into jaeuk_name_gender values('Hoyeon', 'Female');

-- autocommit = True 실습
SET autocommit = 1; 
show variables like 'autocommit';
select * from jaeuk_name_gender;

begin;
delete from jaeuk_name_gender;
insert into jaeuk_name_gender values('Kebin', 'Male');
ROLLBACK;


-- autocommit = False 실습
SET autocommit = 0; 
SHOW VARIABLES LIKE 'autocommit';

SELECT * FROM jaeuk_name_gender;
-- BEGIN이 없음
DELETE FROM jaeuk_name_gender;
INSERT INTO jaeuk_name_gender VALUES ('Kevin', 'Male');
ROLLBACK;

SELECT * FROM jaeuk_name_gender;

  • Java

    • package com.programmers.java.test;

import java.sql.*;

public class Main {
    static final String DB_URL = "jdbc:mysql://grepp.cpjgktk35rty.ap-northeast-2.rds.amazonaws.com:3306/test";
    static final String USER = "guest";
    static final String PASS = "Guest1!*";

    public static void printResultSet(ResultSet rs) throws SQLException {
        // Ensure we start with first row
        rs.beforeFirst();
        while(rs.next()){
            // Display values
            System.out.print("Name: " + rs.getString("name"));
            System.out.print(", Age: " + rs.getString("gender"));
            System.out.println();
        }
    }

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            // Open a connection
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            // Set auto commit as false.
            conn.setAutoCommit(false);

            // Execute a query to create statment with
            // required arguments for RS example.
            System.out.println("Creating statement...");
            stmt = conn.createStatement(
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);

            // INSERT a row into Employees table
            System.out.println("Inserting one row....");
            stmt.executeUpdate("INSERT INTO keeyong_name_gender VALUES('Ben', 'Male');");

            // Commit data here.
            System.out.println("Commiting data here....");
            conn.commit();

            // Now list all the available records.
            String sql = "SELECT * FROM keeyong_name_gender;";
            ResultSet rs = stmt.executeQuery(sql);
            printResultSet(rs);

            // Clean-up environment
            stmt.close();
            conn.close();
        }catch(SQLException se){
            se.printStackTrace();
            try{
                if(conn!=null)
                    // If there is an error then rollback the changes.
                    System.out.println("Rolling back data here....");
                conn.rollback();
            }catch(SQLException se2){
                se2.printStackTrace();
            }
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            // finally block used to close resources
            try{
                if(stmt!=null)
                    stmt.close();
            }catch(SQLException se2){
                se2.printStackTrace();
            }
            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
    }
}
      만약에 오류가 생긴다면 build.gradle에 

implementation group: 'mysql', name: 'mysql-connector-java', version: '8.0.26'

을 dependencies에 넣어보자



  1. View 소개와 실습

  • View란?

    • 자주 사용하는 SQL 쿼리 (SELECT)에 이름을 주고 그 사용을 쉽게 하는 것

      • 이름이 있는 쿼리가 View로 DB에 저장됨

        • SELECT 결과가 테이블로 저장되는 것이 아니라 View가 사용될 때마다 SELECT가 실행됨
        • 그런 이유로 가상 테이블이라고 부름

      • CREATE OR REPLACE VIEW 뷰이름 AS SELECT


  • SELECT를 자주 사용한다면?

    • -- View 실습
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM prod.session s
JOIN prod.channel c ON c.id = s.channel_id;

CREATE OR REPLACE VIEW test.jaeuk_session_details AS
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM prod.session s
JOIN prod.channel c ON c.id = s.channel_id;

SELECT * FROM jaeuk_session_details;



  1. Stored Procedure, Trigger 소개와 실습(1)

  • Stored Procedure란?

    • MySQL 서버에 저장되는 SQL쿼리들
      • CREATE PROCEDURE 사용
      • DROP PROCEDURE (IF EXISTS)로 제거
    • 프로그래밍 언어의 함수처럼 인자를 넘기는 것이 가능
    • 반환되는 값은 레코드들의 집합
    • 간단한 분기문과 루프를 통한 프로그램이 가능
    • 디버깅이 힘들고 DB서버에 부하가 생김

  • Stored Function

    • 값(scalar)을 하나 반환해주는 서버 함수(특정 DB 밑에 등록됨)

      • 반환값은 Deterministic 혹은 Non Deterministic
      • 현재 guest 계정으로는 test 데이터베이스 밑에 생성가능

    • 모든 함수의 인자는 IN 파라미터

    • SQL 안에서 사용가능: Stored Procedure와 가장 다른 차이점

    • CREATE FUNCTION 사용


  • Trigger란?

    • CREATE TRIGGER 명령 사용

    • INSERT/DELETE/UPDATE 실행 전후에 특정 작업을 수행하는 것이 가능

      • 대상 테이블 지정이 필요

    • NEW/OLD MODIFIER

      • NEW는 INSERT/UPDATE에서 사용가능

      • OLD는 DELETE/UPDATE에서 사용가능




  1. Stored Procedure, Trigger 소개와 실습(2)

  • 코드

    • ---- #Stored Procedure 1
DROP procedure IF EXISTS jaeuk_session_details;
DELIMITER //
CREATE PROCEDURE jaeuk_session_details()
BEGIN
   SELECT *
   FROM jaeuk_session_details;
END //
DELIMITER ;

CALL jaeuk_session_details();


---- #Stored Procedure 2
DROP procedure IF EXISTS jaeuk_session_details;
DELIMITER //
CREATE PROCEDURE jaeuk_session_details(IN channelName varchar(64))
BEGIN
   SELECT *
   FROM jaeuk_session_details
   WHERE channel = channelName;
END //
DELIMITER ;

CALL jaeuk_session_details('Facebook');


----- # 3
DROP procedure IF EXISTS jaeuk_session_counts;
DELIMITER //
CREATE PROCEDURE jaeuk_session_counts(IN channelName varchar(64), INOUT totalRecord int)
BEGIN
   SELECT COUNT(1) INTO totalRecord FROM jaeuk_session_counts
   WHERE channel = channelName;
END //
DELIMITER ;

SET @facebook_count = 0;
CALL jaeuk_session_details('Facebook', @facebook_count);
SELECT @facebook_count;


-- Stored Function
drop FUNCTION IF EXISTS Channel_Type;
DELIMITER $$

CREATE FUNCTION Channel_Type(
	channel varchar(32)
) 
RETURNS VARCHAR(20)
DETERMINISTIC
BEGIN
    DECLARE channel_type VARCHAR(20);

    IF channel in ('Facebook', 'Instagram', 'Tiktok') THEN
		SET channel_type = 'Social Network';
    ELSEIF channel in ('Google', 'Naver') THEN
        SET channel_type = 'Search Engine';
    ELSE
        SET channel_type = channel;
    END IF;
	-- return the customer level
	RETURN (channel_type);
END$$

SELECT channel, Channel_Type(channel)
FROM prod.channel;



-- Trigger
DESCRIBE jaeuk_name_gender;
DROP TABLE IF EXISTS jaeuk_name_gender_audit;      
CREATE TABLE jaeuk_name_gender_audit (
    name varchar(16),
    gender enum('Male', 'Female'),
    modified timestamp
);
SELECT * FROM jaeuk_name_gender;

DROP TRIGGER IF EXISTS before_update_jaeuk_name_gender;
CREATE TRIGGER before_update_jaeuk_name_gender 
    BEFORE UPDATE ON jaeuk_name_gender
    FOR EACH ROW 
 INSERT INTO jaeuk_name_gender_audit
 SET name = OLD.name,
     gender = OLD.gender,
     modified = NOW();

UPDATE jaeuk_name_gender
SET name = 'Keeyong'
WHERE name = 'Keeyong2';

SELECT * FROM jaeuk_name_gender_audit;

USE test;
SHOW TRIGGERS;



  1. 성능 튜닝 Explain SQL과 Index 튜닝과 실습

  • Explain SQL란?

    • SELECT/UPDATE/INSERT/DELETE 등 쿼리가 어떻게 수행되는지 알려주는 내부를 보여주는 SQL명령어
      • MySQL이 해당 쿼리를 어떻게 수행할 것인지에 대한 실행 계획을 보여준다. 이를 바탕으로 느리게 동작하는 쿼리의 최적화가 가능해짐
      • 보통 느린 쿼리 경우, 문제가 되는 테이블의 인덱스를 붙이는 것이 일반적

  • Index란?

    • Index는 테이블에서 특정 찾기 작업을 빠르게 수행하기 위해서 MySQL이 별도로 만드는 데이터 구조를 의미한다
      • 컬럼별로 만들어짐
      • primary/foreign key로 지정된 컬럼은 기본적으로 Index를 생성
      • 특정 컬럼을 바탕으로 검색을 자주 한다면 Index 생성이 큰 도움이 될 수 있음
    • Index와 key는 동의어
    • Index는 SELECT/JOIN/DELETE 명령을 빠르게 하지만 INSERT/UPDATE는 느리게 만드는 단점이 존재한다
      • 테이블에 너무 많은 Index를 추가하면 Index의 로딩으로 인해 오버헤드가 발생해서 시스템이 전체적으로 느려질 수 있음

  • 코드

    • -- EXPLAIN
EXPLAIN SELECT 
  LEFT(s.created, 7) AS mon,
  c.channel,
  COUNT(DISTINCT user_id) AS mau
 FROM prod.session s
 JOIN prod.channel c ON c.id = s.channel_id
GROUP BY 1, 2      
ORDER BY 1 DESC, 2;


-- Index. 이걸 실습을 하고 싶다면 test 밑에 자신만의 테이블을 따로 만들어보세요
DROP TABLE IF EXISTS jaeuk_session_with_index;
CREATE TABLE jaeuk_session_with_index (
    id int NOT NULL auto_increment,
    user_id int not NULL,
    created timestamp not NULL default CURRENT_TIMESTAMP,
    channel_id int not NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(channel_id) references prod.channel(id),
    INDEX user_id(user_id)
);


-- 위의 테이블에 기존 prod.session의 내용을 복사하고 싶다면 아래 명령 실행
INSERT INTO jaeuk_session_with_index SELECT * FROM prod.session;

SHOW INDEX FROM prod.session;
SHOW INDEX FROM prod.session_with_index;
SHOW INDEX FROM prod.channel;

SELECT user_id, COUNT(1) FROM prod.session GROUP BY 1;
SELECT user_id, COUNT(1) FROM prod.session_with_index GROUP BY 1;
profile
Jaeuk Oh
이전 포스트

DML과 JOIN

0개의 댓글