트리거와 트랜잭션

0taetae·2025년 1월 3일
db
post-thumbnail

📙트리거

데이터에 변경이 생겼을 때 즉, DB에 insert, update, delete가 발생했을 때 자동적으로 실행되는 프로시저

💡예제

  • 사용자의 닉네임 변경 이력을 저장하는 트리거

    delimiter $$
CREATE TRIGGER log_user_nickname_trigger
BEFORE UPDATE  -- update 이전에 트리거 발생 
ON users FOR EACH ROW  -- users 테이블에서 이벤트가 발생하면 트리거 발생, update 된 row에 대해 트리거 실행 
BEGIN
   insert into users_log values(OLD.id, OLD.nickname, now());
END
$$
delimiter ;

select * from users;

select * from users_log;

update users set nickname = 'taetae' where id = 1;

    ✔️ OLD란❓
    update 되기 전의 tuple, delete된 tuple

  • 사용자가 마트에서 상품을 구매할 때마다 지금까지 누적된 구매 비용을 구하는 트리거

    delimiter $$
CRAETE TRIGGER sum_buy_prices_trigger
AFTER INSERT  -- buy 테이블에 insert가 발생한 후에 insert가 발생한 row에 대해 트리거 발생 
ON buy FOR EACH ROW
BEGIN
   DECLARE total INT;
   DECLARE user_id INT DEFAULT NEW.user_id;
   
   select sum(price) into total from buy where user_id = user_id;
   update user_buy_stats set price_sum = total where user_id = user_id;
END
$$
delimiter ;

select * from buy;

select * from user_buy_stats;

insert into buy (user_id, price, buy_at) values (1, 5000, now());

    ✔️ NEW란❓
    insert된 tuple, update된 후의 tuple

  • update, insert, delete 등을 한번에 감지하도록 설정 가능(MySQL은 불가능)

    -- postgresql

CREATE TRIGGER avg_empl_salary_trigger
   AFTER INSERT OR UPDATE OR DELETE
   ON employee
   FOR EACH ROW
   EXECUTE FUNCTION update_avg_empl_salary();
    UPDATE employee SET salary = 1.5 * salary WHERE dept_id = 1003;

    ✔️위 update문을 실행하면,
    1003 부서에 임직원이 다섯명이 있다면 FOR EACH ROW로 트리거를 실행했기 때문에 avg_empl_salary_trigger는 다섯 번 실행된다.

  • row 단위가 아니라 statement 단위로 trigger가 실행될 수 있도록 할 수 있다.(MySQL은 FOR EACH STATEMENT 사용 불가능)

    -- postgresql

CREATE TRIGGER avg_empl_salary_trigger
   AFTER INSERT OR UPDATE OR DELETE
   ON employee
   FOR EACH STATEMENT
   EXECUTE FUNCTION update_avg_empl_salary();
    UPDATE employee SET salary = 1.5 * salary WHERE dept_id = 1003;

    ✔️위 update 문을 실행하면,
    1003 부서에 임직원이 다섯 명이 있어도 FOR EACH STATEMENT에 의해 avg_empl_salary_trigger는 한 번만 실행된다.

  • trigger를 발생시킬 디테일한 조건을 지정할 수 있다 (MySQL은 불가능)

    -- postgresql

CREATE TRIGGER log_user_nickname_trigger
   BEFORE UPDATE
   ON users
   FOR EACH ROW
   WHEN (NEW.nickname IS DISTINCT FROM OLD.nickname)
   EXECUTE FUNCTION log_user_nickname();

💡trigger 사용 시 주의사항

  • 소스 코드로는 발견할 수 없는 로직이기 때문에 어떤 동작이 일어나는지 파악하기 어렵고 문제가 생겼을 때 대응하기 어렵다.
    • RDBMS에 trigger를 만들어서 사용하면 Web Application Server에서는 trigger의 존재를 알지 못한다.
    • Web Application은 소스코드를 기반으로 동작을 하는데, trigger는 RDBMS에서 바로 등록되어 사용되고 DB에 어떠한 이벤트가 발생했을 때 이벤트가 트리거가 되어 실행되는 로직이기 때문에 소스코드에서 직접 호출되는 로직이 아니다.
  • 과도한 트리거 사용은 DB에 부담을 주고 응답을 느리게 만든다.
  • 디버깅이 어렵다.
  • 문서 정리가 중요하다.

📙트랜잭션

💡트랜잭션이란?

  • 단일한 논리적인 작업 단위
  • 논리적인 이유로 여러 SQL문들을 단일 작업으로 묶어서 나눠질 수 없게 만든 것
  • 트랜잭션의 SQL문들 중에 일부만 성공해서 DB에 반영되는 일은 일어나지 않는다. 즉, 모두 성공해야 DB에 반영된다.

💡예제

  • J가 H에게 20만원 이체한 것을 transaction으로 구현

    START TRANSACTION; -- transaction 시작 
UPDATE account SET balance = balance - 200000 WHERE id = 'J';
UPDATE account SET balance = balance + 200000 WHERE id = 'H';
COMMIT;  -- transaction 종료

    ✔️COMMIT 이란❓

    • 지금까지 작업한 내용을 DB에 영구적으로 저장
    • transaction 종료

  • J가 H에게 30만원 이체한 것을 transaction으로 구현

    START TRANSACTION;
UPDATE account SET balance = balance - 300000 WHERE id = 'J';
select * from account;
ROLLBACK;

    ✔️ROLLBACK 이란❓

    • 지금까지 작업들을 모두 취소하고 transaction 이전 상태로 되돌린다.
    • transaction 종료

💡AUTOCOMMIT

  • 각각의 SQL문을 자동으로 transaction 처리 해주는 개념

  • SQL문이 성공적으로 실행하면 자동으로 commit 한다.

  • 실행 중에 문제가 있었다면 알아서 rollback한다.

  • MySQL에서는 default로 autocommit이 enabled되어 있다.

  • 예제 

    SELECT @@AUTOCOMMIT;
insert into account values ('W', 1000000);

    ✔️autocommit이 enabled된 상태이기 때문에
    insert문을 실행하면 자동으로 commit이 되면서 account 테이블에 ('W', 1000000) 데이터가 영구적으로 저장된다. 

    select * from account;
SET autocommit = 0;  -- autocommit 비활성화 
DELETE FROM account WHERE balance <= 1000000;
ROLLBACK;

    ✔️autocommit을 off 한 후에 delete 했기 때문에 rollback을 한다면 다시 이전 상태로 돌아갈 수 있다.

    START TRANSACTION;
UPDATE account SET balance = balance - 200000 WHRER id = 'J';
UPDATE account SET balance = balance + 200000 WHERE id = 'H';

COMMIT;

    ✔️ START TRANSACTION 실행과 동시에 autocommit은 off 된다.
    ✔️ COMMIT / ROLLBACK과 함께 transaction이 종료되면 원래 autocommit 상태로 돌아간다.

💡일반적인 transaction 사용 패턴

  1. transaction을 시작한다.
  2. 데이터를 읽거나 쓰는 등의 SQL문들을 포함해서 로직을 수행한다.
  3. 일련의 과정들이 문제없이 동작했다면 transaction을 commit한다.
  4. 중간에 문제가 발생했다면 transaction을 rollback 한다.

💡ACID

  1. Atomicity

    • ALL or NOTHING
    • transaction은 논리적으로 쪼개질 수 없는 작업 단위이기 때문에 내부의 SQL문들이 모두 성공해야 한다.
    • 중간에 SQL문이 실패하면 지금까지의 작업을 모두 취소하여 아무 일도 없었던 것처럼 rollback 한다.

  2. Consistency

    • transaction은 DB 상태를 consistent 상태에서 또 다른 consistent 상태로 바꿔줘야 한다.
    • constraints, trigger 등을 통해 DB에 정의된 rules을 transaction이 위반했다면 rollback 해야 한다.

  3. Isolation

    • 여러 transaction들이 동시에 실행될 때도 혼자 실행되는 것처럼 동작하게 만든다
    • DBMS는 여러 종류의 isolation level을 제공한다.
      • level이 높을수록 엄격하게 격리시킴 -> 다른 트랜잭션으로부터 영향을 받을 경우 감소 -> 동시성 떨어짐 -> DB서버의 퍼포먼스 줄어듦
      • 개발자는 isolation level 중에서 어떤 level로 transaction을 동작시킬지 설정할 수 있다.
    • concurrency control의 주된 목표가 isolation이다.
  1. Durability
    • commit된 transaction은 DB에 영구적으로 저장한다.(=비활성 메모리에 저장한다.)
    • 즉, DB system에 문제(power fail or DB crash)가 생겨도 commit된 transaction은 DB에 남아 있는다.
    • 기본적으로 transaction의 durability는 DBMS가 보장한다.
profile
0taetae
이전 포스트

물리 계층과 데이터링크 계층

다음 포스트

JPA란 무엇인가?

0개의 댓글