Database/수업내용

Database 수업내용 21.07.14. MYSQL - Transaction, Function, Trigger 기초

HappyFrog 2021. 7. 14. 16:58

트랜잭션을 배워보자.

 

우선 DB엔진이 InnoDB일 경우에 트랜잭션을 사용할 수 있다. MYSQL의 경우에는 5.5버전 이상은 InnoDB가 기본버전이다.

 

트랜잭션의 특성은 4가지가 존재한다. => ACID

  • Atomicity 원자성
    • 데이터의 변경(INSERT, DELETE, UPDATE)을 수반하는 일련의 데이터 조작이 전부 성공할 지 전부 실패할 지 보증하는 구조. (ex. 항공권, 영화관 등 다양한 예약 시스템)
  • Consistency 일관성
    • 일련의 데이터 조작 전후에 그 상태를 유지하는 것을 보증하는 것.(ex. 시스템에 사용자를 등록할 때 사용자를 임의로 식별하기 위해 일련번호를 사용자에게 발급. 이 번호에 유니크 제약을 설정하면 중복된 사용자 번호를 저장할 수 없음)
  • Isolation 고립성 또는 격리성 => Transaction의 핵심
    • 일련의 데이터 조작을 복수 사용자가 동시에 실행해도 각각의 처리가 모순없이 실행되는 것을 보증 (ex. A사용자와 B사용자가 같은 방을 동시에 예약했을 때 먼저 접근한 A사용자는 예약이 되지만 B사용자는 예약할 수 없게하는 것)
  • Durability 지속성
    • 일련의 데이터 조작을 완료하고 완료통지를 받는 시점에서 그 조작이 영구적이 되어 그 결과를 잃지 않는 것 (ex. MySQL을 포함한 많은 데이터베이스의 구현에서는 트랜잭션 조작을 하드 디스크에 로그로 기록하고 시스템에)

 

일단 트랜잭션을 실행하려면 Auto commit을 꺼주어야 한다. 

SELECT @@AUTOCOMMIT;
  • Auto commit은 해당 코드로 확인할 수 잇으며 1이면 켜져있고 0이면 꺼져있는 것이다.
SET AUTOCOMMIT = FALSE;
--또는
SET AUTOCOMMIT = 0;
  • 1이라면 꺼야하니까 위 코드 중 하나를 작성해지면 Auto commit이 꺼진다.
START TRANSACTION;
  • 위 코드로 트랜잭션을 시작할 수 있다.
  • 트랜잭션을 시작한 뒤 INSERT, DELETE, UPDATE등 테이블에 변화를 주는 것에대해 고립성이 있어야 하는데, 이는 우리가 트랜잭션을 시작한 테이블이 아닌 다른 세션에서 접근하여 확인할 수 있다.
  • 따라서 exam11을 다시 켜보자.

  • exam(exam11)이 트랜잭션이 실행된 세션(A)이고 exam11은 새로 실행된 세션(B)이다.
  • A에서 트랜잭션이 시작됐고, 오토커밋이 꺼져있다면 A에서 테이블 수정을 하고 B에서 테이블을 읽으면 B에서는 아무런 변화가 없다. 
  • 트랜잭션을 했으면 commit을 직접해주어야 하기 때문인데, 이로 인한 장점으로는 rollback을 사용할 수 있다는 것이다.
  • 트랜잭션을 시작한 뒤에 쿼리를 수정했다면 commit을 해주어야 다른 세션에서도 적용이 된다.
  • 하지만 다른 세션에서는 트랜잭션이 이미 실행중인 테이블에 접근할 수 없다.
  • 따라서 A가 test에 트랜잭션을 실행중이라면 B에서는 접근할 수 없다. 
  • * 주의 - Drop한 것들이나 Alter테이블로 변경한 요소들은 rollback이 되지 않는다.

 

---트랜잭션 락 검증

  • MySQL을 환경변수 설정해주고 해당 코드를 친 다음 패스워드를 쳐주면 접속할 수 있다.

 

 

  • DB들이 제대로 들어왔는지 확인한다.

  • 테이블들이 정상적으로 있는지 확인해 준다.
  • 그리고 test테이블을 사용한다고 선언해주자.

 

 

  • cmd에서 실행중인 해당 mysql은 권한이 없는 상태이므로 (B상태임) test쿼리를 수정하려 해도 트랜잭션으로 인해 잠겨있어 시간을 초과했다고 나온다.

 

출처 https://www.sqlshack.com/what-is-a-sql-server-deadlock/

트랜잭션을 사용할 때는 교착상태(Dead lock)를 조심해야한다.

 

EX) '가'라는 사용자가 A테이블에 트랜잭션을 실행하고 '나'라는 사용자가 B테이블에 트랜잭션을 실행한 뒤, '가'가 B테이블의 데이터에 접근하고 '나'사용자가 A테이블에 접근하게되면 교착상태가 일어나 무한히 기다려야 하는 상황이 발생한다.

함수를 만들어보자.

 

#함수 선언
DELIMITER //
CREATE FUNCTION getCountOverPrice(targetPrice INT) RETURNS INT
	BEGIN
		DECLARE cnt INT;
        SELECT COUNT(*) INTO cnt FROM products 
        WHERE price > targetPrice;
        RETURN cnt;
    END//
DELIMITER ;

SELECT getCountOverPrice(1000);
  • 우리가 다른 언어를 사용할 때처럼 매개변수를 주며 함수를 만들 수 있다.
  • 델리미터는 함수의 처음과 끝을 구분해주는 역할을 한다.
  • 함수의 내용은 BEGIN과 END사이에 작성해주면 된다.

  • 만약 해당 에러가 발생했다면 cmd에서 다음과 같은 코드를 입력해주자.
SET GLOBAL log_bin_trust_function_creators = 1;
  • cmd에서 비밀번호를 치고 접근이 된 상태여야 한다.

여기까지 했다면 함수가 작동하는 것을 확인할 수 있다. 

 

트리거를 배워보자.

 

  • 트리거는 테이블에 처리하기 전, 후에 호출할 수 있다.
  • 그리고 해당 처리에 따라 불러올 수 있는 컬럼(column)값이 다른데 아래 표와 같이 처리와 호출 시점에 따라 불러올 수 있는 칼럼값이 다르다.
명령 BEFORE/OLD.column AFTER/NEW.column
INSERT X O
DELETE O X
UPDATE O O

AFTER TRIGGER를 사용해보자.

 

DELIMITER //
CREATE TRIGGER log_Date_Insert_After
AFTER INSERT ON products FOR EACH ROW
BEGIN
INSERT INTO log_products (product_id) VALUES (new.id);
END //
DELIMITER ;

DESC products;

#트리거 확인
SHOW TRIGGERS;
  • log_Date_Insert_After은 그저 트리거 이름이다.
  • 시점 / 처리 ON 테이블이름 FOR EACH ROW를 하고
  • BEGIN과 END사이에 어떤 처리를 할 것인지 작성하자.
  • AFTER INSERT를 사용했기 때문에 칼럼값은 NEW를 가져올 수 있다.

  • SHOW TRIGGERS를 하면 우리가 만든 트리거가 생성된 것을 확인할 수 있다.

  • 그리고 SELECT * FROM log_products를 하여 확인해보면 언제 테이블을 수정했는지 나온다.