DML and Transactions: INSERT, UPDATE, DELETE, COMMIT, ROLLBACK

Oracle DBA Certification 14 min min read Updated: Nov 16, 2026 Beginner
DML and Transactions: INSERT, UPDATE, DELETE, COMMIT, ROLLBACK
Beginner Topic 3 of 5

What is a Transaction?

A transaction is a group of changes that succeed or fail together. Think of moving money: subtract from account A and add to account B. Either both happen or neither does.

ACIDMeaning
AtomicAll or nothing
ConsistentRules (constraints) are always respected
IsolatedOther sessions do not see half-finished work
DurableOnce committed, the change survives a crash

The Four DML Statements

sql INSERT INTO products (product_name, price) VALUES ('Webcam', 60); UPDATE products SET price = 55 WHERE product_name = 'Webcam'; DELETE FROM products WHERE product_name = 'Webcam';
Always use WHERE: UPDATE or DELETE without WHERE changes every row. Run a SELECT with the same WHERE first to see what you will touch.

COMMIT and ROLLBACK

sql UPDATE products SET price = price * 1.10; -- oops, all rows SELECT product_name, price FROM products; ROLLBACK; -- undo everything since the last commit SELECT product_name, price FROM products; -- original prices are back
CommandWhat it does
COMMITMakes changes permanent and visible to others
ROLLBACKUndoes all changes since the last commit
SAVEPOINT xMarks a point inside the transaction
ROLLBACK TO xUndoes only back to that point
Important: In Oracle, DDL statements (CREATE, ALTER, DROP, TRUNCATE) commit automatically. You cannot roll them back.

SAVEPOINT Example

sql INSERT INTO products (product_name, price) VALUES ('Cable', 5); SAVEPOINT after_cable; INSERT INTO products (product_name, price) VALUES ('Dock', 90); ROLLBACK TO after_cable; -- removes Dock, keeps Cable COMMIT;

MERGE: Update or Insert

sql MERGE INTO products p USING (SELECT 'Mouse' AS name, 18 AS price FROM dual) s ON (p.product_name = s.name) WHEN MATCHED THEN UPDATE SET p.price = s.price WHEN NOT MATCHED THEN INSERT (product_name, price) VALUES (s.name, s.price);

What Other Sessions See

Open two SQL*Plus windows to try this:

  1. Session 1: UPDATE products SET price = 20 WHERE product_name = 'Mouse'; (do not commit)
  2. Session 2: SELECT price FROM products WHERE product_name = 'Mouse'; shows the old price. Oracle never shows uncommitted data.
  3. Session 2: try to update the same row. It waits, because session 1 holds a row lock.
  4. Session 1: COMMIT; and session 2 continues.
DBA tip: Oracle readers never block writers and writers never block readers. Only two writers on the same row block each other. We will find and fix those waits in the performance section.

Conclusion

Group related changes into one transaction, test with SELECT first and COMMIT only when you are sure. ROLLBACK is your safety net until you commit.

Quick Summary: COMMIT saves, ROLLBACK undoes, SAVEPOINT marks. DDL commits automatically.

Get Newsletter

Subscibe to our newsletter and we will notify you about the newest updates on Edugators