DML - INSERT, UPDATE, DELETE, Transaction과 Lock

목차

1. DML - INSERT, UPDATE, DELETE

1-1  DML과 트렌잭션

1-2 INSERT, UPDATE, DELETE

 

2. Transaction과 Lock

2-1 트랜잭션(Transaction)

2-2 LOCK

 

 

1. DML - INSERT, UPDATE, DELETE

1-1  DML과 트렌잭션

DML은 데이터베이스 관리 명령어의 한 종류로, 데이터베이스 테이블의 행을 변화시킨다. DML의 종류는 다음과 같다.

- INSERT : 행을 입력한다.

- UPDATE : 행을 변경한다.

- DELETE : 행을 삭제한다.

 

DML로 한 작업은 트랜잭션 단위로 동작한다. 해당 트랜잭션을 완료하기 위해 반드시 COMMIT 혹은 ROLLBACK 명령을 사용하여 작업을 마친다.

- COMMIT : 현재까지 작업한 내용을 저장하며 트랜잭션을 마친다.

- ROLLBACK : 현재 트랜잭션을 시작하기 전 상태로 되돌린다.

 

※ 트랜잭션 : 작업의 최소 단위로, 테이블 변경 작업을 시작했을 때부터 COMMIT 혹은 ROLLBACK 하기 까지의 작업이다.

 

1-2 INSERT, UPDATE, DELETE

● INSERT

INSERT INTO <테이블> [(컬럼, 컬럼, ... )]
VALUES (값, 값, ...);

 

INSERT 문은 행을 입력하는 명령어로, INSERT 문장 하나로 행 하나만 입력한다.

INSERT 문의 컬럼 개수와 VALUES 문의 값의 개수가 일치해야 한다.

 

ex) EMP 테이블에 값을 입력한다.

SQL> ALTER SESSION SET nls_date_format='YYYY/MM/DD';
 
SQL> DESC emp;
 
SQL> INSERT INTO emp (eno, ename, sex, job, mgr, hdate, sal, comm, dno)
 2  VALUES ('1001', '문시현', '남', '모델링', NULL, '1991/02/01', 4500, 520, '10');

SQL> INSERT INTO emp (eno, ename, sex, job, hdate, sal, comm, dno)
 2  VALUES ('1002', '김주란', '여', '모델링', '1992/03/03', 4100, 330, '20');
 
SQL> INSERT INTO emp
 2  VALUES ('1003', '양선호', '남', '모델링', NULL, '1995/02/21', 4300, NULL, '30');

SQL> COMMIT;

 

- 프로그래밍을 할 때에는 첫 번째 INSERT 문과 같은 형식으로 작성한다. 이외의 형식으로 작성 시 유지 보수가 힘들어진다.

- 두 번째 INSERT 문과 같이 추가할 컬럼에서 빠진 컬럼(mgr)이 있다면 자동으로 NULL 이 들어간다.

 

ex) INSERT 문으로 날짜를 입력한다.

SQL> ALTER SESSION SET nls_date_format='YYYY/MM/DD:HH24:MI:SS';

SQL> INSERT INTO emp (eno, ename, hdate)
 2  VALUES ('0201', '안영숙', TO_DATE('2021/09/25:03:07:15',
                                      'YYYY/MM/DD:HH24:MI:SS'));

 

- 날짜 입력은 터미널의 날짜 형식에 따라 입력하는 것이 아니라 반드시 TO_DATE 함수를 이용해서 날짜를 입력한다.

- 입사일과 같이 시:분:초가 필요없는 데이터 입력 시 반드시 TRUNC 함수를 이용한다.

 

● UPDATE

UPDATE 테이블
SET 컬럼 = 값, 컬럼 = 값, ...
[WHERE 조건];

 

UPDATE 문은 행을 변경하는 명령어로, 조건에 맞는 컬럼의 값을 변경한다.

 

ex) 김주란의 부서 번호를 10번으로, 급여는 10% 인상한다.

SQL> UPDATE emp SET dno = '10', sal = sal*1.1
  2  WHERE ename= '김주란';
SQL> COMMIT;

 

● DELETE

DELETE FROM 테이블
[WHERE 조건];

 

DELETE 문은 행을 삭제하는 명령어로, 조건에 맞는 행을 삭제한다.

 

ex) 10번 부서 사원의 정보를 삭제한다.

SQL> DELETE FROM emp
  2  WHERE dno = '10';
SQL> COMMIT;

 

 

- DML 문은 반드시 COMMIT이나 ROLLBACK을 사용해서 작업을 종료해야 한다.

 

2. Transaction과 Lock

2-1 트랜잭션(Transaction)

트랜잭션은 반드시 함께 실행되어야 할 작업의 단위로, 다음과 같은 특성을 지닌다.

- 원자성

: 트랜잭션은 최소의 작업 단위로서 전체가 처리되거나 취소될 수 있지만 일부만 처리될 수없다.

 

- 일관성

: 트랜잭션이 실행된 이후 데이터베이스의 무결성은 반드시 유지돼야 한다.

 

- 독립성

: 트랜잭션을 여러 개 동시에 실행하더라도 각각의 트랜잭션은 서로 영향을 줄 수 없다. 즉, 실행이 종료되지 않은 트랜잭션의 결과는 다른 트랜잭션에서 참조하는 것이 불가능하다.

 

- 영속성

: 종료된 트랜잭션의 결과는 반드시 데이터베이스에 반영돼야 한다.

 

● 트랜잭션의 시작과 종료

- 시작

: 이전 트랜잭션이 종료된 이후 DML(INSERT, UPDATE, DELETE) 문장이나 DDL(CREATE, ALTER, DROP, TRUNCATE), DCL(GRANT, REVOKE) 문장이 실행됐을 때 시작된다.

 

- 종료

: COMMIT 이나 ROLLBACK 명령이 실행될 때 종료된다.

: DDL이나 DCL 문장의 실행이 완료되면 자동으로 종료된다.

: 사용자의 정상 종료 시에 종료된다.

: 데드락(Deadlock)이 걸리면 트랜잭션의 일부만 종료된다.

 

트랜잭션은 하나의 세션에서 단지 하나만 시작할 수 있으며 DML인 경우 반드시 COMMIT이나 ROLLBACK으로 종료하지만 DDL이나 DCL의 경우 문장이 실행되고 난 후 자동으로 종료된다.

 

● 트랜잭션과 언두 세그먼트(Undo segment)

- 트랜잭션과 DML 데이터

: INSERT 등의 DML 작업이 실행되면 즉시 테이블에 적용된다

: 변경된 행에는 독점 잠금(Exclusive lock )이 발생한다.

: 해당 테이블에는 공유 잠금(Share lock)이 발생한다.

: 트랜잭션이 종료되면 잠금이 해제된다.

 

※ 독점 잠금: 다른 세션에서 현재 작업 중인 행에 대해 수정/삭제 작업을 수행할 수 없다.

※ 공유 잠금: 다른 세션에서 현재 작업 중인 테이블에 대해 읽기 작업은 가능하다.

 

- UNDO SEGMENT

: 트랜잭션이 실행될 때 이전 이미지를 저장한다. 즉, 이전 데이터를 저장하는 테이블같은 공간이다.

: Rollback을 위해 트랜잭션이 수행되는 동안 이전 정보를 저장한다.

 

- 세그먼트

: 물리적인 저장 영역을 가진 오브젝트를 의미한다. 즉, 공간을 차지하는 모든 오브젝트는 세그먼트이다.

: 테이블, 인덱스 등 저장 영역에 공간을 할당받는 오브젝트를 의미한다.

 

행의 값을 변화시킬 경우 작업 과정은 다음과 같다.

트랜잭션과 UNDO segment

 

① undo segment를 결정한다.

 

② 5를 undo segment에 저장한다. 

     - wait enent : undo segment에 남은 공간이 없다면 대기 상태로 들어간다. 

 

③ 7을 테이블에 저장한다.

 

④ 행에 독점 잠금을 건다.

 

⑤ 테이블에 공유 잠금을 건다.

 

⑥ COMMIT : 7을 영구 저장하고 잠금을 해제한다.

 

⑦ ROLLBACK : 5를 복원하고 잠금을 해제한다.

 

2-2 LOCK

● 트랜잭션과 잠금의 이해

동일한 계정으로 두 개의 세션을 열어 트랜잭션과 잠금을 이해할 수 있다.

세션 1에서 다음과 같은 작업을 수행한다.

SQL> UPDATE emp SET sal= sal*2 WHERE ename = '문시현';

 

이후 세션 2에서 해당 정보를 select 하면 출력된 값은 변하지 않은 상태이다.

세션 1에서 commit 명령어를 실행한 후 세션 2에서 해당 정보를 select 하면 변한 값을 출력한다.

 

● 잠금으로 인한 대기 현상

세션 1에서 다음과 같은 작업을 수행한다.

SQL> UPDATE emp SET sal = sal*2 WHERE ename = '문시현';

 

이후 세션 2에서 다음과 같은 작업을 수행한다.

SQL> UPDATE emp SET COMM = 1000 WHERE ename = '문시현';

 

세션 2의 작업은 수행되지 않고 대기 상태에 빠진다. 세션 1에서 commit 명령어를 실행하면 곧바로 세션 2의 작업이 수행된다.

 

● 데드락(Deadlock)

데드락은 서로 다른 세션들이 서로의 작업 완료를 대기하고 있는 상태로, 그대로 두면 서로 영원히 락에 걸려있을 상태를 말한다.

이를 해소하기 위해 시스템에서 강제로 데드락의 원인을 ROLLBACK 시킨다.

세션 1과 2에서 각각 다음과 같은 작업을 수행한다.

// 세션 1
UPDATE emp SET sal = sal*1.2 WHERE ename = '문시현';

// 세션 2
UPDATE emp SET sal = sal*2 WHERE ename = '안영희';

 

이후 세션 1에서 다음과 같은 작업을 수행하여 대기 상태에 빠지는 것을 확인한다.

UPDATE emp SET COMM = 1000 WHERE ename = '안영희'

 

이후 세션 2에서 다음과 같은 작업을 수행한다.

UPDATE emp SET JOB = '경영' WHERE ename = '문시현';

 

세션 1은 세션 2의 작업 완료를 대기하고 세션 2는 세션 1의 작업 완료를 대기하는 상태인 데드락 상태가 된다. 이를 해결하기 위해 다음과 같이 데드락의 원인인 첫 대기 상태 작업을 강제로 ROLLBACK 시키는 것을 확인할 수 있다.

세션 1의 작업 ROLLBACK