Transaction Control Language (TCL) commands manage transactions in a database. Transactions are a sequence of SQL statements that are executed as a single logical unit of work. TCL commands allow you to ensure data integrity and consistency. It is part of data manipulation language (DML).

COMMIT: Saving Changes Permanently

The COMMIT statement saves all changes made during the current transaction permanently to the database. Once committed, the changes are visible to other users and cannot be undone by ROLLBACK.

Example 1: Oracle 19c SQL COMMIT - After Insert

INSERT INTO regions (region_id, region_name) VALUES (5, 'Antarctica');
-- Inserts a new region.
COMMIT;
-- Permanently saves the new region to the database.

Explanation After inserting a new region, the COMMIT statement ensures that this new data is saved permanently to the database and becomes visible to all other users and sessions.

Example 2: Oracle 19c SQL COMMIT - After Update

UPDATE departments SET location_id = 1800 WHERE department_id = 200;
-- Updates the location of department 200.
COMMIT;
-- Confirms the update operation, making it permanent.

Explanation This example demonstrates committing an UPDATE operation. The COMMIT makes the change to department_id 200's location_id permanent in the database.

Example 3: Oracle 19c SQL COMMIT - After Multiple DMLs

INSERT INTO jobs (job_id, job_title, min_salary, max_salary) VALUES ('DBA', 'Database Administrator', 8000, 20000);
UPDATE employees SET job_id = 'DBA' WHERE employee_id = 100;
DELETE FROM job_history WHERE job_id = 'ST_CLERK';
COMMIT;
-- Saves all three DML operations as a single transaction.

Explanation This illustrates a transaction involving multiple DML statements. All three operations (INSERT, UPDATE, DELETE) are treated as a single logical unit. The COMMIT at the end saves all these changes together, ensuring atomicity.

ROLLBACK: Undoing Changes

The ROLLBACK statement undoes all changes made during the current transaction since the last COMMIT or ROLLBACK. This is crucial for error recovery or when a series of operations needs to be cancelled.

Example 1: Oracle 19c SQL ROLLBACK - After Accidental Delete

DELETE FROM employees;
-- Accidentally deletes all employees.
ROLLBACK;
-- Undoes the deletion, restoring all employee records.

Explanation This code simulates an accidental deletion of all employees. The ROLLBACK statement immediately reverses this action, restoring the table to its state before the DELETE was executed.

Example 2: Oracle 19c SQL ROLLBACK - After Failed Insert

INSERT INTO projects (project_id, project_name) VALUES (1001, 'New Project');
-- Assume a subsequent operation fails or is undesired.
ROLLBACK;
-- Reverts the insert, project 1001 will not be in the table.

Explanation After an INSERT operation, if a subsequent action within the transaction fails or is determined to be incorrect, ROLLBACK will cancel the INSERT, ensuring the database remains in its prior consistent state.

Example 3: Oracle 19c SQL ROLLBACK - Multiple DMLs Reverted

INSERT INTO departments (department_id, department_name) VALUES (300, 'R&D');
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
ROLLBACK;
-- All changes (insert and update) made within this transaction are undone.

Explanation Here, an INSERT and an UPDATE are performed. Because no COMMIT occurs before ROLLBACK, both operations are undone, and the database reverts to its state before these DML statements were executed.

SAVEPOINT: Setting Interim Markers in a Transaction

A SAVEPOINT allows you to set an interim marker within a transaction. You can then ROLLBACK to this specific SAVEPOINT without undoing the entire transaction. This provides finer control over transaction management.

Example 1: Oracle 19c SQL SAVEPOINT - Partial Rollback

INSERT INTO customers (customer_id, first_name, last_name) VALUES (1000, 'John', 'Doe');
SAVEPOINT sp1;
-- First savepoint.
UPDATE customers SET first_name = 'Jonathan' WHERE customer_id = 1000;
SAVEPOINT sp2;
-- Second savepoint.
DELETE FROM customers WHERE customer_id = 1000;
ROLLBACK TO sp1;
-- Undoes the update and delete, but the initial insert remains.
COMMIT;
-- Commits the initial insert.

Explanation This sequence inserts a customer, sets sp1, then updates the customer and sets sp2, and finally deletes the customer. ROLLBACK TO sp1 undoes the UPDATE and DELETE, leaving the initial INSERT in place, which is then committed.

Example 2: Oracle 19c SQL SAVEPOINT - Error Recovery Scenario

INSERT INTO products (product_id, product_name, price) VALUES (500, 'Gadget A', 99.99);
SAVEPOINT before_expensive_update;
-- Savepoint before a potentially risky update.
UPDATE products SET price = price * 100 WHERE product_id = 500;
-- This update is deemed incorrect.
ROLLBACK TO before_expensive_update;
-- Reverts only the expensive update.
UPDATE products SET price = 109.99 WHERE product_id = 500;
-- Apply the correct update.
COMMIT;
-- Save the initial insert and the correct update.

Explanation A product is inserted, and a SAVEPOINT is created. An incorrect update is performed. ROLLBACK TO before_expensive_update undoes only that last update. A correct update is then applied, and the entire transaction (initial insert and correct update) is committed.

Example 3: Oracle 19c SQL SAVEPOINT - Nested Operations

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary) VALUES (208, 'Alice', 'Smith', 'ALICE.S@EXAMPLE.COM', SYSDATE, 'IT_PROG', 6000);
SAVEPOINT insert_employee;
-- Savepoint after employee insertion.
INSERT INTO job_history (employee_id, start_date, job_id, department_id) VALUES (208, SYSDATE, 'IT_PROG', 60);
SAVEPOINT insert_job_history;
-- Savepoint after job history insertion.
DELETE FROM employees WHERE employee_id = 208;
-- This delete is problematic.
ROLLBACK TO insert_job_history;
-- Undoes the employee delete, keeping the employee and job history insertions.
COMMIT;
-- Commits both insertions.

Explanation This example demonstrates two SAVEPOINTs. After inserting an employee and their job history, a problematic DELETE is performed. ROLLBACK TO insert_job_history undoes only the DELETE, preserving both the employee and job history insertions, which are then committed.

Understanding Transactions and ACID Properties

A transaction in Oracle 19c is a logical unit of work, consisting of one or more SQL statements, that is treated as a single, indivisible sequence of operations. For reliable database processing, transactions adhere to ACID properties: Atomicity, Consistency, Isolation, and Durability.

Atomicity

Atomicity ensures that a transaction is treated as a single, indivisible unit. Either all of its operations are successfully completed, or none of them are. There is no partial completion. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged.

Example 1: Oracle 19c SQL Atomicity - Funds Transfer (Success)

-- Assume initial account balances: account_a = 1000, account_b = 500
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'account_a';
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'account_b';
COMMIT;
-- Both updates succeed and are permanently saved, showing a complete transfer.

Explanation In this funds transfer, both debiting account_a and crediting account_b are part of one atomic transaction. The COMMIT ensures that if both operations are successful, they are permanently saved together.

Example 2: Oracle 19c SQL Atomicity - Funds Transfer (Failure)

-- Assume initial account balances: account_a = 1000, account_b = 500
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'account_a';
-- Simulate an error or system crash here before the second update
-- (e.g., attempt to insert into a non-existent table, or process terminates)
-- UPDATE non_existent_table SET value = 1;
ROLLBACK;
-- Both updates are undone; account_a and account_b balances revert to initial state.

Explanation Here, the first update to account_a occurs. If a subsequent error or failure prevents the credit to account_b, the ROLLBACK ensures that the debit from account_a is also undone. The entire transaction is reverted, maintaining data integrity.

Example 3: Oracle 19c SQL Atomicity - Multi-step Order Processing

INSERT INTO orders (order_id, customer_id, order_date) VALUES (101, 10, SYSDATE);
INSERT INTO order_items (order_id, product_id, quantity) VALUES (101, 5, 2);
-- If the next step (e.g., updating inventory) fails, the entire order should be cancelled.
-- UPDATE inventory SET stock = stock - 2 WHERE product_id = 5 AND stock >= 2;
-- IF SQL%ROWCOUNT = 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Insufficient stock'); END IF;
-- If the above fails, a ROLLBACK is implied or explicitly called.
COMMIT;
-- If all steps including inventory update are successful, then commit.

Explanation This example shows an order processing transaction. The INSERT into orders and order_items are atomic. If a subsequent step like updating inventory fails (e.g., insufficient stock, not explicitly coded here for brevity but implied), the entire transaction should be rolled back to ensure the order is not partially processed. The COMMIT only happens if all steps succeed.

Consistency

Consistency ensures that a transaction brings the database from one valid state to another. All data integrity rules (e.g., constraints, triggers) are enforced. If a transaction violates any rules, it is rolled back.

Example 1: Oracle 19c SQL Consistency - Foreign Key Violation

-- Assume department_id 500 does not exist in the departments table.
INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, job_id, salary, department_id)
VALUES (209, 'Robert', 'Brown', 'ROBERT.B@EXAMPLE.COM', SYSDATE, 'SA_REP', 7500, 500);
-- This insert will fail due to foreign key constraint violation.
-- The database state remains consistent; no invalid data is inserted.

Explanation This INSERT statement attempts to add an employee to a non-existent department_id (500). Oracle's foreign key constraint will prevent this, ensuring the database remains consistent by rejecting the invalid data.

Example 2: Oracle 19c SQL Consistency - Check Constraint

-- Assume there's a check constraint on employees.salary that salary must be > 0.
UPDATE employees SET salary = -100 WHERE employee_id = 100;
-- This update will fail due to check constraint violation.
-- The database prevents inconsistent data (negative salary).

Explanation This UPDATE attempts to set an employee's salary to a negative value, which violates a typical CHECK constraint. The database prevents this inconsistent state, maintaining data integrity.

Example 3: Oracle 19c SQL Consistency - Trigger Enforcement

-- Assume a trigger on orders that updates total_amount in customers on new order insert.
-- And the trigger also ensures total_amount is non-negative.
INSERT INTO orders (order_id, customer_id, order_date, total_amount) VALUES (102, 10, SYSDATE, -50);
-- If the trigger or subsequent validation ensures total_amount is positive, this will be rejected or corrected.
-- If the trigger tries to set total_amount to a negative value, and there's a check constraint on customer.total_amount,
-- the transaction will be rolled back.

Explanation When an ORDER is inserted with a negative total_amount, a database TRIGGER might be designed to enforce business rules. If the trigger itself, or a subsequent constraint (like a CHECK constraint on customer.total_amount), detects an inconsistency, the entire transaction will be rolled back, ensuring data consistency.

Isolation

Isolation ensures that concurrent transactions do not interfere with each other. Each transaction executes as if it were the only transaction running, preventing phenomena like dirty reads, non-repeatable reads, and phantom reads. Oracle's default isolation level is READ COMMITTED.

Example 1: Oracle 19c SQL Isolation - Preventing Dirty Reads

-- Session 1:
UPDATE products SET price = 150 WHERE product_id = 1;
-- At this point, Session 2 cannot see the uncommitted price of 150.

-- Session 2 (concurrently):
SELECT price FROM products WHERE product_id = 1;
-- Returns the original price (e.g., 100), not the uncommitted 150.

-- Session 1:
ROLLBACK;
-- The update is undone.

-- Session 2:
SELECT price FROM products WHERE product_id = 1;
-- Returns the original price (100). No dirty read occurred.

Explanation This demonstrates how Oracle's default READ COMMITTED isolation prevents dirty reads. Session 2 cannot see the uncommitted change made by Session 1. Only committed data is visible to other sessions, ensuring data reliability.

Example 2: Oracle 19c SQL Isolation - Preventing Non-Repeatable Reads (Implicit)

-- Session 1:
SELECT SUM(salary) FROM employees WHERE department_id = 50;
-- Returns a sum, e.g., 20000.

-- Session 2 (concurrently, commits immediately after update):
UPDATE employees SET salary = salary + 1000 WHERE employee_id = 101;
COMMIT;

-- Session 1:
SELECT SUM(salary) FROM employees WHERE department_id = 50;
-- If Session 2's commit happens before Session 1's second select,
-- Session 1 will see the *new* sum (e.g., 21000).
-- This demonstrates Read Committed's tolerance for non-repeatable reads.
-- For stricter repeat reads, Serializalbe isolation is needed.

Explanation Under READ COMMITTED (Oracle's default), a second SELECT within the same transaction can see changes committed by other transactions since the first SELECT. While not strictly preventing non-repeatable reads, it prioritizes concurrency.

Example 3: Oracle 19c SQL Isolation - SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

-- Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT COUNT(*) FROM orders WHERE order_date = TRUNC(SYSDATE);
-- Returns count, e.g., 5.

-- Session 2 (concurrently):
INSERT INTO orders (order_id, customer_id, order_date) VALUES (103, 12, TRUNC(SYSDATE));
COMMIT;

-- Session 1:
SELECT COUNT(*) FROM orders WHERE order_date = TRUNC(SYSDATE);
-- Returns the same count (5), not 6. This prevents phantom reads and non-repeatable reads.
-- Session 1's subsequent DML against the orders table would potentially fail with ORA-08177.

Explanation By setting the isolation level to SERIALIZABLE, Session 1 ensures that its view of the data remains consistent throughout the transaction. It will not see new rows (phantom reads) or modified rows (non-repeatable reads) committed by other transactions, even if they commit before Session 1's second select. This provides the highest level of isolation, but can lead to ORA-08177 errors (cannot serialize access) under heavy concurrency.

Durability

Durability ensures that once a transaction has been committed, its changes are permanent and will survive any subsequent system failures (e.g., power outages, crashes). This is typically achieved by writing committed data to persistent storage (disk) and through the use of redo logs.

Example 1: Oracle 19c SQL Durability - Data on Disk After Commit

INSERT INTO products (product_id, product_name, price) VALUES (501, 'New Widget', 25.99);
COMMIT;
-- The change is now written to the redo logs and eventually to data files.
-- Even if the database crashes immediately after this COMMIT, the data for 'New Widget' is safe.

Explanation After COMMIT, Oracle writes the transaction's changes to the online redo logs and ensures they are safely recorded. This guarantees that the data for 'New Widget' is durable; it will persist even if the system fails and then recovers.

Example 2: Oracle 19c SQL Durability - Recovery from Crash

-- Imagine a sequence of DML operations followed by a COMMIT.
INSERT INTO customers (customer_id, first_name) VALUES (1001, 'Eve');
UPDATE inventory SET stock = stock - 1 WHERE item_id = 7;
COMMIT;
-- Database system crashes immediately after COMMIT.

-- Upon database restart and recovery:
-- The changes for customer 1001 and item 7 will be present and intact.

Explanation This illustrates durability in a recovery scenario. Even if the database crashes immediately after the COMMIT, Oracle's recovery mechanisms (using redo logs) ensure that all committed transactions are reapplied upon restart, guaranteeing the data's persistence.

Example 3: Oracle 19c SQL Durability - Redo Logs and Archiving

-- (Conceptual example, no direct SQL code for this)
-- User performs DML operations and COMMITs.
-- Oracle writes the transaction details to the online redo logs.
-- These redo logs are then archived to offline storage (archive redo logs).
-- In case of media failure (disk corruption), data can be recovered from full backups and archived redo logs.

Explanation Durability is strongly supported by Oracle's redo log and archiving mechanisms. Every committed transaction's changes are recorded in redo logs, which are then typically archived. This allows for point-in-time recovery, meaning even if physical data files are lost, the database can be restored to a specific committed state.