[SQL/실습] 트랜잭션의 활용

트랜잭션의 활용

 

-- 트랜잭션 모드 (기본) : 자동 커밋 트랜잭션

update employee
set salary = salary + commission
where eno='103'
;

-- 트랜잭션 모드 : 명시적 트랜잭션

start transaction; -- 명시적 트랜젝션 시작
-- delete from employee;
-- insert into employee(eno,ename,job,manager,hiredate,salary,commission,dno) 
-- values('117','e17','chief','101','2020-11-02',1500,0,30);
-- rollback; -- 취소하기 (되돌리기)
-- commit; -- 반영하기 커미션하면 롤백이 안됨 
insert into employee(eno,ename,job,manager,hiredate,salary, commission,dno)
values('117','e17','chief','101','2010-11-02','1000',0,'30');
savepoint a; -- rollback이 되는 시점 a라는 이름으로 지정 
insert into employee(eno,ename,job,manager,hiredate,salary, commission,dno)
values('118','e18','chief','101','2010-11-02','1000',0,'30');
insert into employee(eno,ename,job,manager,hiredate,salary, commission,dno)
values('119','e19','chief','101','2010-11-02','1000',0,'30');
delete from employee where eno='117';
delete from employee where eno='118';
delete from employee where eno='119';
select * from employee;
rollback to a; -- savepoint의 이름이 a라는 rollback 실행 savepoint ~ rollback to 까지 실행구간 

-- 트랜잭션을 시작해서 update employee set salary * 2; 를 실행하고 커밋을 하지않은 상태에서 workbench를 종료합니다 
-- 그리고 다시 workbench를 실행해서 select*from employee를 실행해서 update되었는지 확인해보세요

start transaction;

update employee
set salary = salary * 2;

select * from employee;