Day 4 - Transaction Control Language, TCL (COMMIT·ROLLBACK·SAVEPOINT)

📌 Key Takeaways

• A transaction is a group of operations that must succeed together or be cancelled together. Its properties are atomicity, consistency, isolation, and durability (ACID).
• COMMIT makes changes permanent, and ROLLBACK cancels all changes since the last COMMIT. ROLLBACK TO savepoint cancels only what came after that savepoint.
• When you ROLLBACK TO a savepoint, any savepoints created after it disappear. A savepoint recreated with the same name overwrites the earlier one.
• In Oracle, DDL (CREATE, ALTER, DROP, TRUNCATE) performs an implicit COMMIT before and after it runs. Any DML before it is committed along with it.
• Oracle requires an explicit COMMIT after DML. SQL Server defaults to AUTO COMMIT, so each statement is committed immediately.

Transactions Through a Bank Transfer

A transfer that takes 100,000 won out of account A and puts 100,000 won into account B is two UPDATE statements. If only the first statement takes effect and the second fails, the money vanishes. The two statements must both take effect or both be cancelled.

• DML statements grouped as one logical unit of work like this are called a transaction.
• The commands that confirm or cancel a transaction are TCL (Transaction Control Language): COMMIT, ROLLBACK, and SAVEPOINT.

COMMIT and ROLLBACK

COMMIT;                     -- make all changes so far permanent
ROLLBACK;                   -- cancel all changes since the last COMMIT
SAVEPOINT name;             -- create an intermediate savepoint
ROLLBACK TO [SAVEPOINT] name; -- cancel only the changes after the savepoint

CREATE TABLE T_DEPT AS SELECT * FROM DEPT;
INSERT INTO T_DEPT VALUES (50, 'EDUCATION', 'SEOUL');
COMMIT;
DELETE FROM T_DEPT WHERE DEPTNO = 10;
ROLLBACK;
SELECT DEPTNO, DNAME FROM T_DEPT ORDER BY DEPTNO;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
Commit complete.
1 row deleted.
Rollback complete.
    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS
        50 EDUCATION
5 rows selected.
Table dropped.

• Department 50 was made permanent by COMMIT. The later ROLLBACK cannot undo it.
• The DELETE that removed department 10 was before a COMMIT, so ROLLBACK cancelled it.
• The feedback messages are Commit complete. and Rollback complete.

State Before and After COMMIT and ROLLBACK

• Other sessions cannot see changes before COMMIT, because of isolation. Oracle shows other sessions the pre-change values so that reads are not blocked.
• If another session tries to UPDATE or DELETE the same row, it waits until the lock is released.
• The blocks in the practice lab run in a single session, so you cannot see this difference directly. You can verify it by opening two SQL*Plus windows.

SAVEPOINT — Undoing Only Part of the Work

ROLLBACK cancels everything since the last COMMIT. To cancel only the latter part of a long transaction, set savepoints along the way.

CREATE TABLE EMP_T AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;
UPDATE EMP_T SET SAL = 1000 WHERE ENAME = 'CLARK';
SAVEPOINT SP1;
UPDATE EMP_T SET SAL = 2000 WHERE ENAME = 'KING';
SAVEPOINT SP2;
DELETE FROM EMP_T WHERE ENAME = 'MILLER';
ROLLBACK TO SP2;
SELECT ENAME, SAL FROM EMP_T ORDER BY ENAME;
ROLLBACK TO SAVEPOINT SP1;
SELECT ENAME, SAL FROM EMP_T ORDER BY ENAME;
ROLLBACK TO SP2;
ROLLBACK;
SELECT ENAME, SAL FROM EMP_T ORDER BY ENAME;
DROP TABLE EMP_T PURGE;

Table created.
1 row updated.
Savepoint created.
1 row updated.
Savepoint created.
1 row deleted.
Rollback complete.
ENAME             SAL
---------- ----------
CLARK            1000
KING             2000
MILLER           1300
3 rows selected.
Rollback complete.
ENAME             SAL
---------- ----------
CLARK            1000
KING             5000
MILLER           1300
3 rows selected.
ROLLBACK TO SP2
*
ERROR at line 1:
ORA-01086: savepoint 'SP2' never established in this session or is invalid
Rollback complete.
ENAME             SAL
---------- ----------
CLARK            2450
KING             5000
MILLER           1300
3 rows selected.
Table dropped.

Here it is in chronological order.

CTAS ─ CLARK=1000 ─ [SP1] ─ KING=2000 ─ [SP2] ─ delete MILLER
                                          ◀── ROLLBACK TO SP2 : MILLER restored
                       ◀────────────────────── ROLLBACK TO SP1 : KING restored, SP2 gone
 ◀──────────────────────────────────────────── ROLLBACK        : CLARK restored

• ROLLBACK TO SP2 cancelled only the DELETE that came after SP2. The changes to CLARK and KING remain.
• After ROLLBACK TO SP1, trying to go back to SP2 raised ORA-01086, because SP2, created after SP1, had already disappeared.
• ROLLBACK TO does not end the transaction. The final ROLLBACK cancelled everything, including CLARK's change.
• CTAS is DDL, so it was committed at that point. Even after ROLLBACK, the EMP_T table itself remains.

Savepoints with the Same Name

CREATE TABLE T_DEPT AS SELECT * FROM DEPT;
SAVEPOINT A;
DELETE FROM T_DEPT WHERE DEPTNO = 10;
SAVEPOINT A;
DELETE FROM T_DEPT WHERE DEPTNO = 20;
ROLLBACK TO A;
SELECT DEPTNO FROM T_DEPT ORDER BY DEPTNO;
ROLLBACK;
SELECT COUNT(*) FROM T_DEPT;
ROLLBACK TO A;
DROP TABLE T_DEPT PURGE;

Table created.
Savepoint created.
1 row deleted.
Savepoint created.
1 row deleted.
Rollback complete.
    DEPTNO
----------
        20
        30
        40
3 rows selected.
Rollback complete.
  COUNT(*)
----------
         4
1 row selected.
ROLLBACK TO A
*
ERROR at line 1:
ORA-01086: savepoint 'A' never established in this session or is invalid
Table dropped.

• The second SAVEPOINT A replaces the first A. So ROLLBACK TO A restored only department 20.
• Once the transaction ends with ROLLBACK or COMMIT, all savepoints disappear. That is why the final ROLLBACK TO A gives ORA-01086.

⚠️ Trap: With several savepoints, ROLLBACK TO goes back to the savepoint whose name you wrote, not to the most recent savepoint. Savepoints and changes after it are all gone, and changes before it remain.

Auto-Commit — When Changes Are Committed Without You Intending It

DDL Performs an Implicit COMMIT

Oracle COMMITs any open transaction before executing DDL, and COMMITs again when the DDL finishes.

CREATE TABLE T_DEPT AS SELECT * FROM DEPT;
INSERT INTO T_DEPT VALUES (50, 'EDUCATION', 'SEOUL');
CREATE TABLE T_LOG (MSG VARCHAR2(20));
ROLLBACK;
SELECT COUNT(*) FROM T_DEPT;
DROP TABLE T_LOG PURGE;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
Table created.
Rollback complete.
  COUNT(*)
----------
         5
1 row selected.
Table dropped.
Table dropped.

• We did not write COMMIT after the INSERT, but running CREATE TABLE T_LOG committed it along with itself. Even after ROLLBACK, there are 5 rows.
• It is the same even for DDL on another table that seems unrelated. TRUNCATE, ALTER, DROP, and GRANT all have the same effect.

When a Single Statement Fails

CREATE TABLE T_DEPT AS SELECT * FROM DEPT;
INSERT INTO T_DEPT VALUES (50, 'EDUCATION', 'SEOUL');
INSERT INTO T_DEPT VALUES (60, 'TOO LONG DEPARTMENT NAME', 'SEOUL');
COMMIT;
SELECT COUNT(*) FROM T_DEPT;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
INSERT INTO T_DEPT VALUES (60, 'TOO LONG DEPARTMENT NAME', 'SEOUL')
                               *
ERROR at line 1:
ORA-12899: value too large for column "SCOTT"."T_DEPT"."DNAME" (actual: 24, maximum: 14)
Commit complete.
  COUNT(*)
----------
         5
1 row selected.
Table dropped.

• Only the failed INSERT was cancelled, and the earlier INSERT of department 50 stayed in the transaction. This is called statement-level rollback.
• An error does not cancel the whole transaction. The COMMIT that followed made row 50 permanent.

Oracle and SQL Server

To group several statements into one in SQL Server, write it like this.

BEGIN TRAN;
UPDATE ACCOUNT SET BAL = BAL - 100000 WHERE ID = 'A';
UPDATE ACCOUNT SET BAL = BAL + 100000 WHERE ID = 'B';
COMMIT TRAN;      -- if there is a problem, ROLLBACK TRAN;

• In SQL Server too, SET IMPLICIT_TRANSACTIONS ON puts you in implicit-transaction mode, where you must COMMIT yourself, as in Oracle.
• The practice lab and the blocks in these notes are based on Oracle. In the practice container, SQL*Plus's SHOW AUTOCOMMIT showed autocommit OFF.

Tomorrow we cover DDL (CREATE, ALTER, DROP), which creates and modifies tables, along with constraints, and DCL (GRANT, REVOKE), which grants and revokes privileges.

📖 Glossary

• Transaction : A group of operations that must be committed together or cancelled together. The logical unit in which a database processes work.
• TCL : Commands that control transactions. They are COMMIT, ROLLBACK, and SAVEPOINT.
• ACID : The four properties a transaction must uphold. The initials of atomicity, consistency, isolation, and durability.
• Savepoint (SAVEPOINT) : A marker set in the middle of a transaction. ROLLBACK TO undoes work only back to that point.
• Implicit COMMIT : Changes being committed automatically, even though the user did not write COMMIT, when DDL or DCL runs or on a normal exit.
• AUTO COMMIT : A mode that COMMITs automatically each time a single statement finishes. It is the default in SQL Server.
• Statement-level rollback : The behavior of cancelling only the changes of the one failed statement while keeping earlier changes in the same transaction.