23.06.05 / MySQL workbench, 트랜잭션

[ MySQL workbench ]

[ navigator ]

• MySQL의 관리 및 운영을 위한 도구 
• 대부분의 작업이 가능하고 MySQL의 명령문이나 SQL문을 몰라도 대부분 작업 수행 가능

[[ Administration ]]
[ Management ]
MySQL 서버의 가동 상태, 설치된 폴더 등 확인
MySQL 서버에 연결되어 있는 클라이언트 정보 확인
사용자 생성, 삭제, 권한 관리
데이터 내보내기 / 가져오기 등
[ Instance ]
MySQL 연결 정보를 관리
인스턴스 중지 도는 시작
Server에 기록된 로그 조회
옵션 파일 설정 정보 확인 및 변경 등
[ Performance ]
성능 상태 및 구성 관련 메뉴

[[ Schmas ]]
데이터베이스 생성 및 삭제
데이터베이스 개체(테이블, 뷰, 인덱스, 스토어드 프로시저 등) 생성, 관리

 

 

[ 트랜잭션 ]

• 하나의 작업을 수행하기 위해 필요한 데이터 베이스 연산을 모아 놓은 것
• 작업 수행에 필요한 SQL문의 모임
• 장애 발생 시, 복구 작업이나 병행 제어 작업을 위한 중요한 단위로 사용
• 데이터베이스의 무결성과 일관성을 보장하기 위해 작업 수행에 필요한 연산들을 하나의 트랜잭션으로 제대로 정의하고관리해야 한다.
• DBMS가 데이터베이스를 다룰 때, 사용하는 작업(프로그램)의 단위.
• 데이터의 무결성을 유지하기 위해 원자성, 일관성, 고립성(독립성), 지속성의 성질을 갖는다.
  EX) A -> B  : 1만원 송금   => A의 계좌에서 -1만원  B의 계좌에 +1만원

START TRANSACTION;              1. A의 계좌에서 -1만원 UPDATE문              2. B의 계좌에 +1만원 UPDATE문              -- 이 곳에 작성하는 SQL문 하나의 명령어처럼 실행된다. COMMIT; / ROLLBACK; -- 정상적으로 완료된 상태일 때 COMMIT; -- 실패 상태일 때 ROLLBACK; A UPDATE -> B UPDATE 같이 실행 -> 인출 여러 번 진행 -- 1,2번 UPDATE문 모두 수행되거나, 또는 아예 수행되지 않아야 한다.

보통 단일 SQL문을 다루기도 하지만, 여러 개의 SQL문을 순차적으로 수행하기도 한다. 왜 정의하는지?

1. 데이터베이스에서 데이터를 조작할 때, 장애가 발생하는 경우가 있다. 이때 트랜잭션은 장애 발생 시, 데이터를 복구하는 작업 단위가 된다.

2. 데이터베이스에서 여러 작업이 동시에 같은 데이터를 다루며 진행될 때, 트랜잭션은 이 작업을 서로 분리하는 단위가 된다.

 

[ 트랜잭션의 특징 - ACID ]

• 원자성 -Automicity
  트랜잭션이 더 이상 쪼개지지 않는 하나의 프로그램 단위로 동작해야 한다.
  일부만 수행되는 일이 없도록 전부 수행 또는 아예 수행하지 않아야 한다.
• 일관성 - Consistency
  트랜잭션은 데이터베이스의 일관성을 유지해야 한다.
• 독립성(격리성) - Isolation
  여러 트랜잭션이 동시에 수행될 때, 각 트랜잭션은 다른 트랜잭션의 방해를 받지 안혹, 독립적으로 작업을 수행해야 한다.
• 영속성(지속성) - Durability
  트랜잭션이 정상적으로 완료 또는 부분 완료한 데이터는 반드시 데이터베이스에 기록되어야 한다.

 

[ 수행 과정 ]

• 성공 : 수행 시작 > 수행 > 부분 완료 > COMMIT > 완료
• 실패 : 수행 시작 > 수행 > 실패 > ROLLBACK > 철회

 

[ 트랜잭션의 상태 ]

• 활동 상태 : 트랜잭션이 현재 수행 중인 상태
• 부분 완료 상태 : 트랜잭션의 마지막 연산이 실행을 종료한 직후의 상태
  수행은 했지만(부분 완료 상태), DBMS가 데이터베이스에 기록(COMMIT)을 완료해야 정상 수행(완료 상태).
  문제가 발생해서, DBMS가 변경 내용을 데이터베이스에 기록하지 못하면, 실패 상태
• 완료 상태 : 트랜잭션이 성공적으로 완료되어 COMMIT 연산을 실행한 상태 -> 데이트베이스에 최종 결과를 반영한 상태
• 실패 상태 : 트랜잭션을 중간에 중단했거나, 부분 완료 상태에서 변경 내용을 데이터베이스에 저장하지 못한 상태
  수행한 작업을 모두 복구 진행해야 한다.
• 철회 상태 : 트랜잭션의 수행 실패로 ROLLBACK 연산을 진행한 상태

SELECT @@AUTOCOMMIT;
-- AUTOCOMMIT 확인 

-- AUTOCOMMIT이 1(ON)이라면, 0으로 초기화 해준다.
-- AUTOCOMMIT -> ON : 1 / OFF : 0
-- AUTOCOMMIT을 꺼준다. 
-- AUTOCOMMIT이 ON 상태면 자동으로 COMMIT 된다 -> ROLLBACK 불가능
SET @@AUTOCOMMIT := 0;

START TRANSACTION;
	SELECT
		*
	FROM
		tb_member2 AS M;
		
	UPDATE 
		tb_member2
	SET
		m_name = '홍 0303'
	WHERE
		m_id = 'id003';
        

		
	DELETE
	FROM
		tb_member2
	WHERE
		m_id = 'id011';
		
	-- COMMIT이 안 된 상태(실패)에서 ROLLBACK 가능
	-- 한 번 COMMIT하면 ROLLBACK 안 됨
	ROLLBACK;
    
	-- 부분 롤백 포인트 지정하기 (여러 개 작성 가능)
	SAVEPOINT 이름;

	-- 부분 롤백
	ROLLBACK SAVEPOINT 이름;
    
	-- 성공 상태일 때 최종 완료 COMMIT
	COMMIT;
    
    
-- 트랜잭션 작업 완료 후 AUTOCOMMIT을 다시 1로 변경
SET @@AUTOCOMMIT := 1;