[손에 잡히는 10분 SQL - 벤 포터 지음, 박남혜 옮김] 책의 학습 후 정리자료입니다.
📍 트랜잭션 처리 이해하기
- 트랜잭션 처리는 여러 개의 SQL 작업을 일괄적으로 실행하거나 아니면 아예 실행하지 않도록 하여 데이터베이스의 무결성(
Integrity
)을 보장하는 처리 방식이다.
- 트랜잭션 처리는 데이터베이스가 부분적으로 작업을 수행하는 것을 막기 위해 여러 SQL 작업을 일괄적으로 처리하는 메커니즘이다.
- 트랜잭션 처리를 이용하면, 작업이 중간에 중단되지 않을 수 있다.
- 트랜잭션 처리 시 에러가 발생하지 않으면, SQL 문 전체가 데이터베이스 테이블에 커밋(
Commit
, 영구 변경)된다.
- 에러가 발생하면, 롤백(
Rollback
, 후진 복귀)되어 데이터베이스가 안전한 상태로 복구된다.
📒 트랜잭션과 트랜잭션 처리를 사용할 때 사용하는 키워드
1. 트랜잭션(Transaction) : 일괄 처리할 SQL 명령어들을 묶은 블록(Block)
2. 롤백(Rollback) : 변경된 작업 내용을 모두 취소하는 절차
3. 커밋(Commit) : 변경된 작업 내용을 데이터베이스에 저장
4. 저장점(Savepoint) : 부분적으로 롤백하기 위한 임시 지점
💡 어떤 문을 롤백할 수 있을까?
트랜잭션 처리는 INSERT, UPDATE, DELETE 문을 관리하기 위해 사용한다.
SELECT 문은 롤백을 할 수 없다. CREATE나 DROP 작업도 롤백할 수 없다.
이 문들이 트랜잭션 블록 안에서 쓰일 수는 있지만, 롤백하더라도 작업을 되돌릴 수 없다.
📍 트랜잭션 통제하기
- 트랜잭션을 관리하는 데 있어 가장 중요한 점은 SQL 문을 논리적인 작업 단위로 만들어 데이터가 롤백되어야 하는 시점과 그렇지 않은 시점을 확실하게 명시하는 것이다.
✏️ SQL Server
BEGIN TRANSACTION
...
COMMIT TRANSACTION
✏️ MariaDB, MySQL
START TRANSACTION
...
✏️ Oracle
SET TRANSACTION
...
✏️ PostgreSQL
BEGIN
...
- 대부분의 DBMS는 트랜잭션의 끝을 지정하지 않는다.
- 대신 트랜잭션 끝을 알리는 특정 명령문이 나타날 때까지 유지되는데, 보통은 COMMIT으로 변경 사항을 저장하거나 ROLLBACK으로 되돌릴 떄까지다.
📌 롤백 사용하기
- SQL 문을 되돌리기 위한
ROLLBACK
명령어
- 트랜잭션 내에서 DELETE 작업(INSERT나 UPDATE 작업처럼)은 절대 수행되지 않는다는 것을 볼 수 있다.
DELETE FROM Orders;
ROLLBACK;
📌 커밋 사용하기
- 보통 SQL 문은 실행되면서 데이터베이스 테이블을 바로 변경한다.
- 이렇게 커밋이 자동으로 일어나는 것을 자동 커밋이라고 부른다.
- 하지만 트랜잭션은 자동으로 커밋되지 않는다.
- 일부 DBMS는 트랜잭션의 끝을 자동 커밋으로 다루기도 하고, 일부는 그렇게 다루지 않는다.
- 커밋을 명시하기 위해
COMMIT
문이 사용된다.
✏️ SQL Server
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION
✏️ Oracle
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT;
Orders
와 OrderItems
두 개의 테이블을 변경해야 하기 때문에, 일부분만 삭제되는 것을 막기 위해 트랜잭션을 사용하였다.
- 도중에 에러가 발생하지 않았다면 마지막에 있는 COMMIT 문에서 변경사항을 저장한다.
- 첫 번째 DELETE만 성공하고, 두 번째 DELETE가 실패하면 첫 번째 DELETE도 절대 커밋되지 않는다.
📌 저장점 사용하기
- ROLLBACK 과 COMMIT 문은 단순히 전체 트랜잭션을 저장하거나 되돌리는 작업만 한다.
- 트랜잭션을 부분적으로 롤백하려면, 트랜잭션에서 전략상 중요한 위치들을 임시 지점으로 정해놓고, 롤백이 필요할 때 임시 지점 중 하나로 되돌리면 된다.
- SQL에서는 이 임시 지점을
저장점
이라고 부른다.
- 저장점은 각 저장점을 구별할 수 있는 고유한 이름을 갖기 때문에 롤백할 때 어느 저장점으로 롤백할지 지정할 수 있다.
✏️ MariaDB, MySQL, Oracle
SAVEPOINT delete1;
ROLLBACK TO delete1;
- MariaDB, MySQL, Oracle에서는 저장점을 생성하려면
SAVEPOINT
문을 사용한다.
✏️ SQL Server
SAVE TRANSACTION delete1;
ROLLBACK TRNASACTION delete1;
💡 저장점이 많을 수록 좋다
저장점의 개수에는 제한이 없으며 SQL 코드를 작성할 때 저장점이 많이 사용될수록 좋다.
왜냐면 저장점이 많으면, 정확히 필요한 곳으로 롤백할 수 있기 때문이다.